1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Jérôme Verstrynge
  • 57,710
  • 92
  • 283
  • 453

1 Answers1

2

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • Ok, I guess SERIALIZABLE is the way to go for me. In this case, do I need to check against 'serialization_failure' only or against all Class 40 — Transaction Rollback exceptions? – Jérôme Verstrynge Mar 28 '14 at 17:36
  • @JVerstry: Everything else stays the same. A rollback only happens after an exception (unless triggered manually) and should never occur. It can't hurt to prepare your code for any exception. But you should only *retry* where it makes sense (like for a serialization failure). Be careful not to trigger an endless loop here. I added some more to the answer. – Erwin Brandstetter Mar 28 '14 at 17:50