How to handle MySQL deadlock situations on an application level?
A transaction can fail. Deadlock is a case of fail, you could have more fails in serializable levels as well. Transaction isolation problems is a nightmare. Trying to avoid fails is the bad way I think.
I think any well written transaction code should effectively be prepared for failing transactions.
As you have seen recording queries and replaying them is not a solution, as when you restart your transaction the database has moved. If it were a valid solution the SQL engine would certainly do that for you. For me the rules are:
- redo all your reads inside the transactions (any data you have read outside may have been altered)
- throw everything from previous attempt, if you have written things outside of the transaction (logs, LDAP, anything outside the SGBD) it should be cancelled because of the rollback
- redo everything in fact :-)
This mean a retry loop.
So you have your try/catch block with the transaction inside. You need to add a while
loop with maybe 3 attempts, you leave the while loop if the commit part of the code succeed. If after 3 retry the transaction is still failing then launch an Exception to the user -- so that you do not try an inifinite retry loop, you may have a really big problem in fact --. Note that you should handle SQL error and lock or serializable exception in different ways. 3 is an arbitrary number, you may try a bigger number of attempts.
This may give something like that:
$retry=0;$notdone=TRUE;while( $notdone && $retry<3 ) { try { $transaction->begin(); do_all_the_transaction_stuff(); $transaction->commit(); $notdone=FALSE; } catch( Exception $e ) { // here we could differentiate basic SQL errors and deadlock/serializable errors $transaction->rollback(); undo_all_non_datatbase_stuff(); $retry++; }}if( 3 == $retry ) { throw new Exception("Try later, sorry, too much guys other there, or it's not your day.");}
And that means all the stuff (read, writes, fonctionnal things) must be enclosed in the $do_all_the_transaction_stuff();
. Implying the transaction managing code is in the controllers, the high-level-application-functional-main code, not split upon several low-level-database-access-models objects.