1. 技术掘金
  2. 技术文章推荐
  3.  InnoDB的锁机制 | wsztrush 

 InnoDB的锁机制 | wsztrush 

JerryXia 发表于 , 阅读 (0)
死锁

在两个及以上的事务在执行过程中，可能因为争夺资源而互相等待：

  • T1拥有资源A，尝试获取资源B；
  • T2拥有资源B，尝试获取资源A；

如果不暴力终结某个事务，T1、T2会永远等下去，这就是死锁。数据库通过wait-for graph（等待图）来检测是否存在死锁，当发现有回路时，引擎会选择回滚undo量最小的事务。

程序上为防止死锁一般会统一更新顺序。

加锁分析

下面对常见的几条SQL做加锁分析，建表如下：

1
2
3
4
5
6
7
8
9
10
CREATE TABLE `my_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `a` bigint(20) NOT NULL,
  `b` bigint(20) NOT NULL,
  `c` bigint(20) NOT NULL,
  `d` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_a_b`(`a`,`b`),
  KEY `idx_c`(`c`)
);
初始化数据如下：
image
在执行完成SQL后，可以从information_schema中的：
  • INNODB_TRX
  • INNODB_LOCKS
  • INNODB_LOCK_WAITS
来查看锁和事务的状态，此外也可以用show engine innodb status来看整体情况，需要增加锁监控：
1
create table innodb_lock_monitor(x int) engine=innodb;
才可以看到比较详细的锁信息。
RC+主键
1
select * from my_test where id = 1 for update;
加锁信息：
1
2
3
4
5
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 2, OS thread handle 0x1648, query id 148 localhost 127.0.0.1 root cleaning up
TABLE LOCK table `mysql`.`my_test` trx id 2863 lock mode IX
RECORD LOCKS space id 38 page no 3 n bits 72 index `PRIMARY` of table `mysql`.`my_test` trx id 2863 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
加锁逻辑为：
  1. 表上加IX锁；
  2. 行上加X锁；
RC+唯一约束
1
select * from my_test where a = 1 and b = 1 for update;
加锁信息如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x1648, query id 152 localhost 127.0.0.1 root cleaning up
TABLE LOCK table `mysql`.`my_test` trx id 2864 lock mode IX
RECORD LOCKS space id 38 page no 4 n bits 72 index `unique_a_b` of table `mysql`.`my_test` trx id 2864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 8; hex 8000000000000001; asc         ;;
 2: len 8; hex 8000000000000001; asc         ;;
RECORD LOCKS space id 38 page no 3 n bits 72 index `PRIMARY` of table `mysql`.`my_test` trx id 2864 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500110; asc     P  ;;
 3: len 8; hex 8000000000000001; asc         ;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 8; hex 8000000000000001; asc         ;;
 6: len 8; hex 8000000000000001; asc         ;;
加锁逻辑为：
  1. 表上加IX锁；
  2. 唯一约束上加X锁；
  3. 行上加X锁；
RC+普通索引
1
select * from my_test where c = 3 for update;
加锁信息如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
3 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 2, OS thread handle 0x1648, query id 166 localhost 127.0.0.1 root cleaning up
TABLE LOCK table `mysql`.`my_test` trx id 2872 lock mode IX
RECORD LOCKS space id 38 page no 5 n bits 72 index `idx_c` of table `mysql`.`my_test` trx id 2872 lock_mode X
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 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 8; hex 8000000000000003; asc         ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 8; hex 8000000000000004; asc         ;;
RECORD LOCKS space id 38 page no 3 n bits 72 index `PRIMARY` of table `mysql`.`my_test` trx id 2872 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500130; asc     P 0;;
 3: len 8; hex 8000000000000003; asc         ;;
 4: len 8; hex 8000000000000003; asc         ;;
 5: len 8; hex 8000000000000003; asc         ;;
 6: len 8; hex 8000000000000003; asc         ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000004; asc         ;;
 1: len 6; hex 000000000b37; asc      7;;
 2: len 7; hex ac0000015e0110; asc     ^  ;;
 3: len 8; hex 8000000000000004; asc         ;;
 4: len 8; hex 8000000000000004; asc         ;;
 5: len 8; hex 8000000000000003; asc         ;;
 6: len 8; hex 8000000000000004; asc         ;;
加锁逻辑为：
  1. 表上加IX锁；
  2. 索引上加X锁（2）；
  3. 行上加X锁（2）；
RC+无索引
1
select * from my_test where d = 1 for update;
加锁信息：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
2 lock struct(s), heap size 360, 5 row lock(s)
MySQL thread id 2, OS thread handle 0x1648, query id 172 localhost 127.0.0.1 root cleaning up
TABLE LOCK table `mysql`.`my_test` trx id 2874 lock mode IX
RECORD LOCKS space id 38 page no 3 n bits 72 index `PRIMARY` of table `mysql`.`my_test` trx id 2874 lock_mode X
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 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500110; asc     P  ;;
 3: len 8; hex 8000000000000001; asc         ;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 8; hex 8000000000000001; asc         ;;
 6: len 8; hex 8000000000000001; asc         ;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500120; asc     P  ;;
 3: len 8; hex 8000000000000002; asc         ;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000000002; asc         ;;
 6: len 8; hex 8000000000000002; asc         ;;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000003; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500130; asc     P 0;;
 3: len 8; hex 8000000000000003; asc         ;;
 4: len 8; hex 8000000000000003; asc         ;;
 5: len 8; hex 8000000000000003; asc         ;;
 6: len 8; hex 8000000000000003; asc         ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000004; asc         ;;
 1: len 6; hex 000000000b37; asc      7;;
 2: len 7; hex ac0000015e0110; asc     ^  ;;
 3: len 8; hex 8000000000000004; asc         ;;
 4: len 8; hex 8000000000000004; asc         ;;
 5: len 8; hex 8000000000000003; asc         ;;
 6: len 8; hex 8000000000000004; asc         ;;
加锁逻辑：
  1. 表上加X锁；
  2. 行上加X锁（4）；
RR
在事务1中执行：
1
delete from my_test where c = 3;
在事务2中执行：
1
insert into my_test values(6, 6, 6, 3, 6);
查看事务2的锁信息如下：
1
2
3
4
5
6
7
8
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x2378, query id 198 localhost 127.0.0.1 root update
insert into my_test values(6, 6, 6, 3, 6)
------- TRX HAS BEEN WAITING 20 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 5 n bits 72 index `idx_c` of table `mysql`.`my_test` trx id 2897 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
查看锁等待信息select * from information_schema.innodb_locks如下：
image
而对于lock_data：
If a gap lock is taken for key values or ranges above the largest value in the index, LOCK_DATA reports “supremum pseudo-record”.
根据上面的Next-Key算法分析，锁定的不只是单个记录，而是一个范围：[3, 4)。
死锁分析
首先模拟常见的死锁，事务1：
1
2
begin;
select * from my_test where id = 1 for update;
事务2：
1
2
begin;
select * from my_test where id = 2 for update;
事务1：
1
select * from my_test where id = 2 for update;
此时处于锁等待，接着事务2：
1
select * from my_test where id = 1 for update;
此时事务2会报死锁，然后将事务回滚，查看死锁信息如下：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
------------------------
LATEST DETECTED DEADLOCK
------------------------
2016-11-22 21:45:37 2378
*** (1) TRANSACTION:
TRANSACTION 2899, ACTIVE 48 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 2, OS thread handle 0x1648, query id 217 localhost 127.0.0.1 root statistics
select * from my_test where id = 2 for update
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 3 n bits 80 index `PRIMARY` of table `mysql`.`my_test` trx id 2899 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500120; asc     P  ;;
 3: len 8; hex 8000000000000002; asc         ;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000000002; asc         ;;
 6: len 8; hex 8000000000000002; asc         ;;
*** (2) TRANSACTION:
TRANSACTION 2900, ACTIVE 28 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 3, OS thread handle 0x2378, query id 218 localhost 127.0.0.1 root statistics
select * from my_test where id = 1 for update
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 3 n bits 80 index `PRIMARY` of table `mysql`.`my_test` trx id 2900 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000002; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500120; asc     P  ;;
 3: len 8; hex 8000000000000002; asc         ;;
 4: len 8; hex 8000000000000002; asc         ;;
 5: len 8; hex 8000000000000002; asc         ;;
 6: len 8; hex 8000000000000002; asc         ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 3 n bits 80 index `PRIMARY` of table `mysql`.`my_test` trx id 2900 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 8000000000000001; asc         ;;
 1: len 6; hex 000000000b23; asc      #;;
 2: len 7; hex 9c000001500110; asc     P  ;;
 3: len 8; hex 8000000000000001; asc         ;;
 4: len 8; hex 8000000000000001; asc         ;;
 5: len 8; hex 8000000000000001; asc         ;;
 6: len 8; hex 8000000000000001; asc         ;;
*** WE ROLL BACK TRANSACTION (2)
从死锁信息上能看到事务分别拥有哪些锁、在申请哪些锁的时候互相等待了。
INSERT + Unique Key
还是沿用上面的表，开启事务1：
1
2
begin;
insert into my_test values (7, 7, 7, 0, 0);
开启事务2：
1
2
begin;
insert into my_test values (8, 7, 7, 0, 0);
此时会锁等待，再开启事务3：
1
2
begin;
insert into my_test values (9, 7, 7, 0, 0);
此时的锁情况为：
image
原因：
事务1在插入是需要加X锁，而事务2、事务3在插入前需要做唯一性检查，所以需要加S锁，因为与X锁不兼容所以事务2、3处于等待状态。
接着事务1进行rollback话，事务2会插入成功，事务3会报死锁：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
2016-11-22 22:36:36 1ea4
*** (1) TRANSACTION:
TRANSACTION 2904, ACTIVE 300 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 3, OS thread handle 0x2378, query id 238 localhost 127.0.0.1 root update
insert into my_test values (8, 7, 7, 0, 0)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index `unique_a_b` of table `mysql`.`my_test` trx id 2904 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 2905, ACTIVE 290 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 0x1ea4, query id 239 localhost 127.0.0.1 root update
insert into my_test values (9, 7, 7, 0, 0)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 72 index `unique_a_b` of table `mysql`.`my_test` trx id 2905 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;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 72 index `unique_a_b` of table `mysql`.`my_test` trx id 2905 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)
原因：
在事务1回滚后，事务2、3都获得了S锁，接着要执行INSERT要去获取X锁，事务2申请X锁时发现与事务3的S锁不兼容，事务3也是同样的情况，于是出现死锁。
特殊的唯一约束：主键也有相同的问题。
总结
对InnoDB了解的还是很少，最近把锁相关的突击了一下，抽时间把底层的实现也了解一下，其他部分再慢慢补上。
      

      

          
          [ 技术文章推荐 ]
      

  

  

      
          Creative Commons License
      
      本文基于
      署名 2.5 中国大陆
      许可协议发布，欢迎转载，演绎或用于商业目的，但必须保留本文署名 InnoDB的锁机制 | wsztrush (包含链接）。