MySQL锁系列 之 死锁ITeye - 牛牛娱乐

MySQL锁系列 之 死锁ITeye

2019-01-10 12:57:39 | 作者: 鸿宝 | 标签: 死锁,记载,持有 | 浏览: 3024

TRANSACTION 133601982, ACTIVE 48 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 11900, OS thread handle 140000866637568, query id 25108 localhost dba statistics select * from tb_a where id = 2 for update -----session1 持有tb_a中记载为2的锁 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 303 page no 3 n bits 72 index PRIMARY of table `lc_5`.`tb_a` trx id 133601982 lock_mode X locks rec but not gap waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000002; asc ;; --session 1 需求tb_a中记载为2的锁( session1 - session2 ) 1: len 6; hex 000007f69ab2; asc ;; 2: len 7; hex dc000027100110; asc ;; *** (2) TRANSACTION: TRANSACTION 133601983, ACTIVE 28 sec starting index read, thread declared inside InnoDB 5000 mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 11901, OS thread handle 140000864773888, query id 25109 localhost dba statistics select * from tb_b where id_2 = 1 for update *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 303 page no 3 n bits 72 index PRIMARY of table `lc_5`.`tb_a` trx id 133601983 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000002; asc ;; --session 2 持有tb_a中记载等于2的锁 1: len 6; hex 000007f69ab2; asc ;; 2: len 7; hex dc000027100110; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 304 page no 3 n bits 72 index PRIMARY of table `lc_5`.`tb_b` trx id 133601983 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000001; asc ;; --session 2 需求tb_b中记载为1的锁 ( session2 - session1 ) 1: len 6; hex 000007f69ab8; asc ;; 2: len 7; hex e0000027120110; asc ;; 终究的成果: 死锁途径:[session1 - session2 , session2 - session1] ABBA死锁发生 3.2 死锁事例二

同一个业务中,官网:www.fhadmin.org S-lock 晋级为 X-lock 不能直接承继

* session 1:
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; --获取S-lock
+------+
| i |
+------+
| 1 |
+------+
* session 2:
mysql START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql DELETE FROM t WHERE i = 1; --想要获取X-lock,可是被session1的S-lock 卡住,现在处于waiting lock阶段                          
			
版权声明
本文来源于网络,版权归原作者所有,其内容与观点不代表牛牛娱乐立场。转载文章仅为传播更有价值的信息,如采编人员采编有误或者版权原因,请与我们联系,我们核实后立即修改或删除。

猜您喜欢的文章