mysql超时死锁_MySQL上的死锁与锁定等待超时

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?

解决方案

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.