14.5.5.1 An InnoDB Deadlock Example 一個InnoDB 死結執行個體

14.5.5.1 An InnoDB Deadlock Example  一個InnoDB 死結執行個體下面的例子示範了一個錯誤可以發生當一個lock 請求會導致一個死結,例子設計2個用戶端，A和B:Jekins:/root# mysql -uroot -p1234567 -e"SHOW ENGINE INNODB STATUS\G;" | grep -i deadWarning: 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 transactionDeadlock 發生 因為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 readmysql tables in use 1, locked 1LOCK 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 updatingDELETE 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 waitingRecord 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 readmysql tables in use 1, locked 14 lock struct(s), heap size 1184, 3 row lock(s)MySQL thread id 4, OS thread handle 0x7f0341d30700, query id 369 localhost root updatingDELETE 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 SRecord 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 waitingRecord 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)