Can a deadlock occur on commit?

Question

In all the SQL deadlock examples I've seen so far, a deadlock appeared while executing a SELECT / UPDATE etc.

If all my statements were executed successfully, is there any chance the deadlock appears when I COMMIT?

I'm trying to catch deadlock exceptions with my ORM, and wondering if using try{} around flush() is enough, or if it should wrap commit() as well.

Answer 1

Yes, a deadlock can occur when you execute a COMMIT. More precisely, your application may be informed of a deadlock when it executes a COMMIT.

Assume you are connection A and you perform some sequence of operations. Independently, a different connection (Connection B) does some things that cause a deadlock and the DBMS decides to rollback connection A.

However, connection A has done all of the things it wants to do and decides to COMMIT. Well, this is the first operation that the client is performing on Connection A after the DBMS decides to perform the rollback and therefore you get notified of it at that point.

You should handle an error on every operation, even a COMMIT.