14.5.5.1 an InnoDB deadlock Example a InnoDB deadlock instance

14.5.5.1 an InnoDB deadlock Example a InnoDB deadlock instance the following example demonstrates an error that can occur when a lock request causes a deadlock, example design 2 clients, A and b:jekins:/root# MySQL -uroot-p1234567-e "show ENGINE INNODB status\g;" |

Grep-i dead warning:using A password on the command line interface can is insecure.
First, client a creates a table containing a record and then begins a transaction, in which a selecting in the shared mode gets an S-lock mysql> CREATE TABLE T (i INT) ENGINE = InnoDB;
Query OK, 0 rows affected (1.07 sec) mysql> INSERT into T (i) VALUES (1);
Query OK, 1 row affected (0.09 sec) mysql> START TRANSACTION;
Query OK, 0 rows Affected (0.00 sec) mysql> SELECT * from t WHERE i = 1 LOCK in SHARE MODE; +------+
|
I |    +------+
|
1 |
+------+ Then, Client B starts a transaction and attempts to delete the record mysql> start TRANSACTION;



Query OK, 0 rows Affected (0.00 sec) mysql> DELETE from t WHERE i = 1;
Delete operation requires an X lock, lock cannot be immediately authorized because it is not compatible with S lock client a hold, and finally, client a attempts to delete the records of the table mysql> Delete from t WHERE i = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; Try restarting transaction deadlock occurs because CLient a requires an X lock to delete the record.

However, the lock request could not be granted immediately because client B had a request for X to lock in waiting for the customer A to release his S lock. The S lock is held by client A and cannot be upgraded to an X lock. In this way, InnoDB produces an error------------------------LATEST detected deadlock------------------------2016-10-31 10:06:09  30700 * * * * * (1) transaction:transaction 15945, ACTIVE 5 sec starting index read MySQL tables in use 1, locked 1 LOCK wait 2 Lock struct (s), heap size 360, 1 row lock (s) MySQL thread ID 5, OS thread handle 0x7f0341ae9700, query ID 368 localhost r oot updating DELETE from t WHERE i = 1 * * * * (1) Waiting for "LOCK to" Granted:record LOCKS Space ID 583 page No 3 n BITS-index ' gen_clust_index ' of table ' zjzc '. ' t ' Trx ID 15945 lock_mode X waiting record lock, heap No 2 physical Recor D:n_fields 4; Compact format; Info bits 0 0:len 6; Hex 000000005c00;
 ASC \;; 1:len 6; Hex 000000003e47;
 ASC >G;; 2:len 7; Hex c7000001be0110;
 ASC;; 3:len 4; Hex 80000001;

ASC;; (2) Transaction:transaction 15944, ACTIVE sec starting index read MySQLTables in use 1, locked 1 4 lock struct (s), heap size 1184, 3 row lock (s) MySQL thread ID 4, OS thread handle 0x7f0341d307 Query ID 369 localhost root updating DELETE from t WHERE i = 1 * * * * (2) holds the LOCK (S): Record LOCKS space ID 583 PA GE No 3 n bits ' gen_clust_index ' of table ' ZJZC ' The TRX ID 15944 lock mode S record lock, heap No 1 physical REC Ord:n_fields 1; Compact format; Info bits 0 0:len 8; Hex 73757072656d756d;

ASC supremum;; Record lock, Heap No 2 physical record:n_fields 4; Compact format; Info bits 0 0:len 6; Hex 000000005c00;
 ASC \;; 1:len 6; Hex 000000003e47;
 ASC >G;; 2:len 7; Hex c7000001be0110;
 ASC;; 3:len 4; Hex 80000001;

ASC;; (2) Waiting for this LOCK to is Granted:record LOCKS Space ID 583 page No 3 n bits-index ' gen_clust_index ' of tabl E ' zjzc '. ' t ' Trx ID 15944 lock_mode X waiting record lock, heap No 2 physical record:n_fields 4; Compact format; Info bits 0 0:len 6; Hex 000000005c00;
 ASC \;;1:len 6; Hex 000000003e47;
 ASC >G;; 2:len 7; Hex c7000001be0110;
 ASC;; 3:len 4; Hex 80000001;

ASC;; WE ROLL back TRANSACTION (1)