php执行mysql update死锁,通过PHP进行MySQL死锁检测

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)?

解决方案

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.