10bet网址
MySQL 5.7 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr)- 36.3Mb
PDF (A4)- 36.3Mb
Man Pages (TGZ)- 235.4Kb
Man Pages (Zip)- 347.1Kb
Info (Gzip)- 3.3Mb
Info (Zip)- 3.3Mb
Excerpts from this Manual

14.7.5 Deadlocks in InnoDB

A deadlock is a situation where different transactions are unable to proceed because each holds a lock that the other needs. Because both transactions are waiting for a resource to become available, neither ever release the locks it holds.

A deadlock can occur when transactions lock rows in multiple tables (through statements such asUPDATEorSELECT ... FOR UPDATE), but in the opposite order. A deadlock can also occur when such statements lock ranges of index records and gaps, with each transaction acquiring some locks but not others due to a timing issue. For a deadlock example, seeSection 14.7.5.1, “An InnoDB Deadlock Example”.

To reduce the possibility of deadlocks, use transactions rather thanLOCK TABLESstatements; keep transactions that insert or update data small enough that they do not stay open for long periods of time; when different transactions update multiple tables or large ranges of rows, use the same order of operations (such asSELECT ... FOR UPDATE) in each transaction; create indexes on the columns used inSELECT ... FOR UPDATEandUPDATE ... WHEREstatements. The possibility of deadlocks is not affected by the isolation level, because the isolation level changes the behavior of read operations, while deadlocks occur because of write operations. For more information about avoiding and recovering from deadlock conditions, seeSection 14.7.5.3, “How to Minimize and Handle Deadlocks”.

When deadlock detection is enabled (the default) and a deadlock does occur,InnoDBdetects the condition and rolls back one of the transactions (the victim). If deadlock detection is disabled using theinnodb_deadlock_detectvariable,InnoDBrelies on theinnodb_lock_wait_timeoutsetting to roll back transactions in case of a deadlock. Thus, even if your application logic is correct, you must still handle the case where a transaction must be retried. To view the last deadlock in anInnoDBuser transaction, useSHOW ENGINE INNODB STATUS. If frequent deadlocks highlight a problem with transaction structure or application error handling, enableinnodb_print_all_deadlocksto print information about all deadlocks to themysqlderror log. For more information about how deadlocks are automatically detected and handled, seeSection 14.7.5.2, “Deadlock Detection”.