MySQL Deadlock Detection via PHP

Question

What is the best-practice in dealing with MySQL Dead-Locks in PHP? Should I wrap all database calls in a try{}catch{} block and look for the DeadLock error code from the database? Do I then reissue the whole transaction again (I presume the one that failes rolled back)?

Answer 1

Yeah putting every database call in a try/catch is the best solution.

But using an ORM (for Object Relationnal Mapper) would allow you not to have to worry about it. The library would manage it for you.
In PHP, there is Doctrine which is used by Symfony.

If you do not wish to use any open source library, my advice would be to develop your own and to use it instead of making direct mysql_query calls.
So you could have your try/catch directly in this library and be able to do whatever you want when a query fails.

Answer 2

A deadlock returns error 1213 which you should process on the client side

Note that a deadlock and lock wait are different things. In a deadlock, there is no "failed" transaction: they are both guilty. There is no guarantee which one will be rolled back.

A deadlock occurs in a scenario like this:

UPDATE  t_first -- transacion 1 locks t_first
SET     id = 1;

UPDATE  t_second -- transaction 2 locks t_second
SET     id = 2;

UPDATE  t_second -- transaction 1 waits for transaction 2 to release the lock on t_second
SET     id = 2;

UPDATE  t_first -- transaction 2 waits for transaction 1 to release the lock on t_first. DEADLOCK
SET     id = 2;

Are you sure you're not confusing it with a lock wait?

A lock wait occurs whenever a transaction tries to lock a resource already locked by another transaction.

In the example above a lock wait occurs on step 3.

Since this is a normal situation (unlike a deadlock), which can be resolved from the outside by committing or rolling back the transaction that holds the lock, InnoDB will not attempt to rollback the transaction that holds the lock.

Instead, it will just cancel the statement that tried to acquire the lock after the timeout occurs.

The timeout by default is 50 seconds and is set using innodb_lock_wait_timeout.

The failed statement (that which tried to acquire the lock) will return error 1205.