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

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.

Answer 2

I'd like to quote these warm words from MySQL's How to Cope with Deadlocks

Always be prepared to re-issue a transaction if it fails due to deadlock. Deadlocks are not dangerous. Just try again.

This could be achieved with a pattern like this:

for ($i = 3; true; $i--) {
    $pdo->beginTransaction();
    try {

        // Do the unit of work

        $pdo->commit();
        break;

    } catch (\PDOException $e) {
        $pdo->rollback();
        if ($i <= 0) {
            throw $e;
        }
    }
}