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.

Dauntless

@Martin Thanks for your help, there was no need to delete your answer, my question was quite generic, even though I'll indeed be happy if someone can confirm what you said for MySQL! – Dauntless

I've never actually had a lock timeout on a commit (and I've had plenty of timeout issues with updates), but in general you should assume that all db operations can error out. Even in the best case, the commit can fail due to IO errors. – Xenophon

@skishore The commit can definitely fail, but I'm not willing to catch these failures. I want to retry the deadlocks, not the other failures. – Dauntless

@Benjamin This shouldn't be a problem. Deadlocks throw a specific SQL error (Error: 1213; SQLSTATE: 40001 (ER_LOCK_DEADLOCK)). Check this state in order to decide whether or not try the transaction again. – Lillis

@Lillis The question is, can this deadlock be triggered at commit time, or only when the actual statement is executed? Otherwise, I know how to catch it, no problem. – Dauntless

@Benjamin I was just reacting to your previous comment. I cannot answer your main question (and I am equally looking forward to seeing a formal answer). – Lillis

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.

Christiansen

As far as I understand it, if lock there is, connection A should be in a waiting state then, and therefore the statement has not returned yet. When B creates the deadlock, if A is chosen as the victim, the error should then happen when the pending statement on A returns, not at commit time. – Dauntless

Not true, consider this sequence of events. <br /> <br /> B: BEGIN<br /> A: BEGIN <br /> A: UPDATE <br /> A: UPDATE (done) <br /> B: UPDATE <br /> B: UPDATE (done) <br /> A: UPDATE <br /> A: UPDATE (done) <br /> B: UPDATE <--- This update causes a deadlock <br /> <br /> The DBMS decides to rollback A and allow B to continue <br /> <br /> So, finally: <br /> <br /> A: COMMIT <--- We inform A of the DEADLOCK <br /> <br /> Note that A is not "waiting" as you assumed it would be.<br /> – Christiansen

@Benjamin, I'm unable to edit my comment and make the sequence of events look more readable but to answer the question you posed in the comments section; yes a deadlock can be reported to the client connection at COMMIT time. It may have been flagged by the DBMS earlier but the first available opportunity to report it may well be when the COMMIT is issued. Hope that helps. – Christiansen

Wrong, I just made a real life example of your scenario above here, and the deadlock happens at statement time, not at commit time. – Dauntless

@Benjamin how did you forcce the dbms to choose one particular connection as the victim? In your example the database chose (it appears) to kill connection B. – Christiansen

Also, I suggest you check out optimistic concurrency control (or optimistic locking) which is another circumstance that could increase the possibility of a deadlock during commit; I've seen this particular variant quite often. – Christiansen

You don't choose the victim, MySQL chooses it automatically. About optimistic locking, please provide an example backing up your sayings, as so far nothing proves that a deadlock can ever occur on COMMIT. When you're using an ORM with optimistic locking via a version field, yes an optimistic locking exception can occur on commit, but that's not at the database level. – Dauntless

@Benjamin, the question did not specify MySQL. Furthermore, just search for optimistic locking commit deadlock. You should at least find this article mysqlperformanceblog.com/2012/08/17/… Observe about half way down the following ... node1 mysql> commit; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction As a general programming practice, it is good to assume that all operations in a transaction can generate a deadlock. – Christiansen

@Benjamin, on other databases (for example) SQL Server, you can specify as follows. SET DEADLOCK_PRIORITY technet.microsoft.com/en-us/library/ms186736.aspx This can cause a connection that is sitting fat, dumb and happy to be chosen as the deadlock victim. – Christiansen

For SQL Server though, @Martin Smith said just a day ago that deadlocks can never happen on commits. So that doesn't help backing up your sayings..! – Dauntless

I just checked your link, that's interesting, however that wasn't really a transaction starting with BEGIN, but just autocommit=off, so yes, I'm not surprised it happens on commit then! – Dauntless