14.5.5 Deadlocks in InnoDB

14.5.5 deadlocks in InnoDB 14.5.5.1 a InnoDB deadlock Example 14.5.5.2 deadlock detection and Rollback 14.5.5.3 how to M

Inimize and Handle deadlocks deadlocks are a situation in which different transactions are not handled because each hold lock is required by other sessions. Because all transactions are waiting for a resource to become available, neither of them releases the lock it holds. A deadlock can occur when transaction locks are recorded in multiple tables (through statements such as Update or SELECT ...).


For UPDATE), but in reverse order, a deadlock can also occur when the statement locks the range and interval of the index record, each transaction requires some locks. To reduce the likelihood of deadlocks, use a transaction instead of a lock TABLES statement to make the transaction insert and update data small enough that they do not sing time to keep open; When different transactions update multiple tables or a large number of records, use the same sequence of operations (for example, select ...). For UPDATE) on each transaction create index on column for select ... For update and update ...


WHERE to pose.


Deadlocks may not be affected by transaction isolation because transaction isolation alters the behavior of read operations, and deadlocks occur because they are write operations.

If a deadlock occurs, InnoDB detects the status and rolls back one of the transactions.


Therefore, even if your application logic is correct, you must still handle the case when a transaction must be retried.
Look at the latest deadlock information in INNODB user affairs, show ENGINE INNODB status\g; LATEST detected deadlock------------------------2016-10-28 17:59:20 7f4acb87f700 * * * (1) transaction:transaction 14917 , ACTIVE 3180 sec inserting MySQL tables in use 1, locked 1 lock wait 5 lock struct (s), Heap size 2936, 2 row LOCK (s), und O Log Entries 6 MySQL thread ID 2, OS thread HAndle 0x7f4acb8c0700, query ID 555 localhost root update insert INTO T1 VALUES (a) * * * (1) Waiting for this LOCK to be GRA Nted:record LOCKS Space ID 580 page No 3 n bits ' PRIMARY ' of table ' zjzc '. ' T1 ' Trx ID 14917 lock_mode X Insert I Ntention waiting record Lock, heap No 1 physical record:n_fields 1; Compact format; Info bits 0 0:len 8; Hex 73757072656d756d;

ASC supremum;;  (2) Transaction:transaction 14919, ACTIVE 5 sec inserting MySQL tables in use 1, locked 1 4 lock struct (s), Heap size  1184, 2 row lock (s) MySQL thread ID 3, OS thread handle 0x7f4acb87f700, query ID 556 localhost root update insert INTO T1 VALUES (2) holds the LOCK (S): Record LOCKS Space ID 580 page No 3 n bits-index ' PRIMARY ' of table ' zjzc '. ' T1 ' TRX ID 14919 Lock mode S record lock, heap No 1 physical record:n_fields 1; Compact format; Info bits 0 0:len 8; Hex 73757072656d756d;

ASC supremum;; (2) Waiting for this LOCK to is Granted:record LOCKS Space ID 580 page No 3 n bits 72 Index ' PRIMARY ' of table ' zjzc '. ' T1 ' Trx ID 14919 lock_mode X insert intention waiting record lock, heap No 1 physical RE Cord:n_fields 1; Compact format; Info bits 0 0:len 8; Hex 73757072656d756d;

ASC supremum;;
 WE ROLL back TRANSACTION (2)