MySQL上的死锁与锁定等待超时 [英] Deadlock vs Lockwait Timeout on MySQL

问题描述

谁能详细解释MySQL 5.1上的Deadlock和Lockwait错误的区别.一样吗?何时发生死锁错误以及何时发生锁等待超时?

Can anyone explain me on details the difference of Deadlock and Lockwait errors found on MySQL 5.1. Is it just the same? When does the deadlock error occur and when does the lockwait timeout occur?

推荐答案

发生死锁的地方是，在交易必须获取的锁之间存在循环依赖关系，才能继续进行交易:例如，假设该交易1拥有锁A，但需要获取锁B才能继续；交易2拥有锁B，但需要获取锁A才能继续进行交易-交易立即被死锁(无需超时)，并且直到有人释放其锁才可以进行交易.因此，唯一的解决方案是将一个事务回滚(一个人的应用程序代码应检测到这种可能性并通常通过再次尝试该事务来进行相应的处理).

A deadlock occurs where a circular dependency exists amongst the locks that transactions must acquire in order to proceed: for example, imagine that transaction 1 holds lock A but needs to acquire lock B to proceed; and transaction 2 holds lock B but needs to acquire lock A to proceed—the transactions are immediately deadlocked (no timeout required) and neither can proceed until one releases its locks. Thus the only solution is for one transaction to be rolled back (one's application code should detect this eventuality and handle accordingly, usually by attempting the transaction again).

当配置的超时时间(例如

A wait timeout occurs when the configured timeout period (e.g. innodb_lock_wait_timeout in the case of InnoDB locks) elapses whilst a transaction awaits a lock, perhaps because a slow query is holding the lock and has not finished executing. It's possible (even, likely) that the lock would have become available and been acquired if the transaction had waited longer, but the timeout exists to avoid applications waiting on the database indefinitely.

这篇关于MySQL上的死锁与锁定等待超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！