Deadlock vs Lockwait Timeout on MySQL [closed]

Question

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?

Answer 1

A deadlock occurs whenever a circular dependency arises among 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 database picks a transaction to abort/rollback; application code should detect this eventuality and handle accordingly, usually by attempting the transaction again. A deadlock is analogous to a policeman solving gridlock (the situation at a road junction when no vehicle is able to move forward) by ordering a random participant to reverse.

A wait timeout occurs when the configured timeout period (e.g. innodb_lock_wait_timeout in the case of InnoDB locks) elapses while a transaction awaits a lock, perhaps because a slow transaction is holding the lock and has not finished executing or perhaps because a number of transactions are queuing for the lock. It's possible (even, likely) that the lock would have become available and have been acquired if the transaction had waited longer, but the timeout exists to avoid applications waiting on the database indefinitely. A wait timeout is analogous to a driver giving up and turning back because of delays.