14.5.5.1 An InnoDB Deadlock Example 一个InnoDB 死锁实例 下面的例子演示了一个错误可以发生当一个lock 请求会导致一个死锁,例子设计2个客户端,A和B: Jekins:/root# mysql -uroot -p1234567 -e"SHOW ENGINE INNODB STATUSG;" | grep -i dead Warning: Using a password on the command line interface can be insecure. 首先, client A 创建一个表包含一个记录, 然后开始一个事务,在事务中,通过selecting在共享模式得到一个S锁 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 | +------+ 接着,Client B 开始一个事务和尝试删除记录 mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM t WHERE i = 1; 删除操作需要一个X锁,lock 不能被立即授权 因为它不兼容S锁 客户端A持有的, 最后,Client A 尝试删除表的记录 mysql> DELETE FROM t WHERE i = 1; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction Deadlock 发生 因为client A 需要一个X锁来删除记录。 然而,lock 请求不能被立即授予因为Client B有一个请求X锁 在等待客户端A 来释放他的S锁。 S锁被client A持有的也不能升级为X锁 。这样,InnoDB 产生一个错误 ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2016-10-31 10:06:09 7f0341d30700 *** (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 root updating DELETE FROM t WHERE i = 1 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 583 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `zjzc`.`t` trx id 15945 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 ;; *** (2) TRANSACTION: TRANSACTION 15944, ACTIVE 24 sec starting index read mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 3 row lock(s) MySQL thread id 4, OS thread handle 0x7f0341d30700, query id 369 localhost root updating DELETE FROM t WHERE i = 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 583 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `zjzc`.`t` trx id 15944 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;; 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 BE GRANTED: RECORD LOCKS space id 583 page no 3 n bits 72 index `GEN_CLUST_INDEX` of table `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)