How to refresh table with deadlock?

Question

We Azure postgresql database build. on that we have a ADF process that is doing truncate and load from source schema to prod schema every morning. Teams are querying to prod schema continuously to pull data which causes deadlock on table. then adf load from source to prod fails. How can we refresh table in prod without getting rid of deadlock?

Answer 1

truncate and load from source schema to prod schema

If you do a full load to an empty table, then the complete table gets locked; that's quite normal.

In PostgreSQL you can set the transaction isolation to "READ UNCOMMITED" to read the "new" data anyway, see https://www.postgresql.org/docs/8.4/transaction-iso.html

Answer 2

Grover, Jyoti,

After read completes from teams deadlocks usually get self removed.

Hope this helps.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.

Answer 3

Hello Grover, Jyoti

It seems like you are facing a deadlock issue while querying the production schema of your Azure PostgreSQL database.

Deadlocks occur when two or more transactions are waiting for each other to release locks. In your case, it seems like the ADF process is holding locks on the table while the teams are querying the same table, causing a deadlock.

To avoid this issue, you can consider using a different approach to refresh the table in the production schema.

One approach is to use a staging table to load the data from the source schema and then merge the data into the production table. This approach can help you avoid locking the production table during the load process.

Here are the steps you can follow:

1. Create a staging table in the production schema with the same schema as the production table.
2. Load the data from the source schema into the staging table using the ADF process.
3. Merge the data from the staging table into the production table using an SQL statement. The merge statement will update the existing rows and insert the new rows into the production table. Here is an example of the merge statement:

MERGE INTO production_table AS target 
	USING staging_table AS source ON (target.id = source.id) 
WHEN MATCHED THEN 
	UPDATE SET target.column1 = source.column1, target.column2 = source.column2 
WHEN NOT MATCHED THEN 
	INSERT (id, column1, column2) VALUES (source.id, source.column1, source.column2);

1. This approach can help you avoid locking the production table during the load process and prevent deadlocks. Additionally, you can consider optimizing the queries that the teams are running on the production schema to reduce the chances of deadlocks.

I hope this helps.

---

I hope that this response has addressed your query and helped you overcome your challenges. If so, please mark this response as Answered. This will not only acknowledge our efforts, but also assist other community members who may be looking for similar solutions.