CODEWITHSUNDEEP
sqlpostgresqlconcurrencytriggersdeadlock
Jérôme Verstrynge
Jérôme Verstrynge

Reputation: 59616

Can simple UPDATE and DELETE statements trigger deadlocks and rollback in PostgreSQL?

Can simple UPDATE and DELETE statements performed in triggers cause deadlocks or rollbacks when other more sophisticated statements are performed at the same time on the same tables? 

UPDATE "s_mat"
    SET "req_st" = my_st, "l_upd" = retr
    WHERE "req_id" = my_id;

DELETE FROM "mat" WHERE "req_id" = my_id;

Should I anticipate for potential deadlock_detected or transaction_rollback exceptions for those statements?

Upvotes: 1

Views: 933

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 657982

Basically yes. If two triggers run in two separate transactions concurrently, let's call them t1 and t2:

t1                t2
update row x
                 update row y
delete row y
                 delete row x

This would deadlock. Postgres detects the condition automatically and aborts all but one of the competing transactions. Details in the manual.

If all your code processes rows in the same (deterministic) order, this cannot happen. But sometimes this cannot be guaranteed.

If you lock all rows to be processed manually with an exclusive lock (in canonical order), you can dramatically reduce the chance for deadlocks.

To be absolutely sure, run with SERIALIZABLE transaction isolation. You have to prepare your code for serialization failures and retry in that case.

Transactions in serializable mode are slower and should only be used if necessary. If you are not facing heavy concurrent load or don't have critical combinations of statements in your code, you might be just fine with the default (faster) READ COMMITTED isolation level.

Optimizing performance of your code and minimizing windows of opportunity for race conditions can go a long way. Besides always processing rows in the same order, you can use data-modifying CTEs to combine UPDATE and DELETE in a single statement. Does not exclude the race condition, but minimizes the time frame for a deadlock. Example with details:

Upvotes: 2

Related Questions