Redshift connection is getting lock during replication from one schema to another schema

Hello,

Locking is a protection mechanism that controls how many sessions can access a table at the same time and determines which operations can be performed in those sessions. Amazon Redshift uses table-level locks, but not schema-level locks. You might experience locking conflicts if you perform frequent DDL statements on user tables or DML queries.

[+] For more information, please refer - detect and release locks in Amazon Redshift

In your usecase i.e. replicating tables from one schema to another schema, we would recommend performing a deep copy, where after creating target table, the data from source table is inserted to target table using insert into target_table (select * from source_table); statement. While doing this, usually there will be no locking issues, however kindly ensure there are no concurrent WRITE operations on source table to avoid any data loss while migrating to new table.

If the suggestions above do not help resolve the issue, we might need to troubleshoot based on your current schema-to-schema replication setup. Could you please create a support case instead so we may discuss details on behavior being observed and further mitigations?

Please do not post any sensitive information over re:Post since this is a public platform.

As always, feel free to reach back with any further questions or concerns in the meantime!