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.