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 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)
上一篇:fcntl记录锁实例


下一篇:opencv 相关一个很好的博客