通过PHP进行MySQL死锁检测 [英] MySQL Deadlock Detection via PHP

问题描述

在PHP中处理MySQL死锁的最佳实践是什么?是否应该将所有数据库调用都包装在try {} catch {}块中，并从数据库中查找DeadLock错误代码?然后，我是否要重新发出整个交易(我认为失败的交易会回滚)?

解决方案

死锁将返回错误1213，您应在客户端进行处理

请注意，死锁和锁等待是不同的东西.在僵局中，没有失败"的交易:它们都是有罪的.无法保证会回滚哪一个.

在这种情况下会发生死锁:

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;

您确定不会将其与锁定等待混淆吗?

每当一个事务试图锁定另一个事务已经锁定的资源时，就会发生锁定等待.

在上面的示例中，锁等待发生在步骤3上.

由于这是正常情况(与死锁不同)，可以通过提交或回滚持有该锁的事务来从外部解决，所以InnoDB不会尝试回滚持有该锁的事务. /p>

相反，它只会在发生超时后取消试图获取锁的语句.

默认情况下，超时时间为50秒，可使用 innodb_lock_wait_timeout .

失败的语句(尝试获取的锁)将返回错误1205.

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.

这篇关于通过PHP进行MySQL死锁检测的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！