InnoDB中锁的模式

Ⅰ、总览

• S行级共享锁
  lock in share mode
• X行级排它锁
  增删改
• IS意向共享锁
• IX意向排他锁
• AI自增锁

Ⅱ、锁之间的兼容性

兼	X	IX	S	IS
X	×	×	×	×
IX	×	√	×	√
S	×	×	√	√
IS	×	√	√	√

2.1 意向锁

意向锁揭示了下一层级请求的锁类型,意向锁全兼容

• IS：事务想要获得一张表中某几行的共享锁
• IX：事务想要获得一张表中某几行的排它锁

InnoDB存储引擎中意向锁都是表锁,是不是读下来很懵逼？

如果没有意向锁,当你去锁一张表的时候,你就需要对表下的所有记录都进行加锁操作,且对其他事务刚刚插入的记录(游标已经扫过的范围)就没法在上面加锁了,此时就没有实现锁表的功能

对一棵树加锁的概念：
从上往下的,先加意向锁再加记录锁,内存操作,很快,释放操作则是从记录锁开始从下往上进行释放

假设数据库四个层级,库,表,页,记录

假如此时有事务tx1需要在记录A上进行加X锁：
1. 在该记录所在的数据库上加一把意向锁IX
2. 在该记录所在的表上加一把意向锁IX
3. 在该记录所在的页上加一把意向锁IX
4. 最后在该记录A上加上一把X锁
假如此时有事务tx2需要对记录B(假设和记录A在同一个页中)加S锁：
1. 在该记录所在的数据库上加一把意向锁IS
2. 在该记录所在的表上加一把意向锁IS
3. 在该记录所在的页上加一把意向锁IS
4. 最后在该记录B上加一把S锁
假如此时有事务tx3需要在记录A上进行加S锁：
1. 在该记录所在的数据库上加一把意向锁IS
2. 在该记录所在的表上加一把意向锁IS
3. 在该记录所在的页上加一把意向锁IS
4. 发现该记录被锁定(tx1的X锁),那么tx3需要等待,直到tx1进行commit

tips：

• 共享锁和排它锁不是说只能加在记录级别上,是可以加在各个级别上的
  innodb表锁的获取:

  lock table l read;
  lock table l write;  
  unlock tables;
  这是server层的锁(mdl锁)
  

  从原理上讲innodb也是可以对表加X锁的,但是没有一个具体的命令来触发,也可以把lock table l read; 理解为加X锁

  通常来说不需要加表级别的锁,mysqldump都不加,ddl不支持online的时候就是先对一张表先加一个S锁,现在不一样了

• 为什么意向锁都是互相兼容的？因为在当前级别上并没有加锁啊

但是在MySQL中没有数据库级别的锁和页级别的锁,这就意味着一共就两层,所有的意向锁都是表锁,意向锁是innodb层级的

tips：
MySQL8.0中所有的锁都在innodb层,现在的锁一部分在innodb层一部分在server层,server层的不好理解

Ⅱ、自增锁

• 一个表一个自增列,自增锁做自增并发处理
• auto_increment pk 代表这个列的自增有一把锁
• 在事务提交前释放
  其他锁在事务提交时才释放
• Think about
  insert ... select ...

tips：
MySQL的自增存在一个回溯的问题,5.7版本之前都是非持久化的,都是服务启动时候执行下面这个sql获取自增值,从下个位置开始继续自增,如果数据库重启了,之前的自增值可能被重复使用,8.0已解决,这个值会被写到元数据表(innodb引擎)中。

select max(auto_inc_col) from t for update;

2.1 自增列的约束

(root@localhost) [test]> create table t (a int auto_increment, b int) engine = innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
(root@localhost) [test]> create table t (a int auto_increment, b int, key(b,a)) engine = innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
(root@localhost) [test]> create table t (a int auto_increment, b int, key(a,b)) engine = innodb;
Query OK, 0 rows affected (0.04 sec)

InnoDB自增列必须被定义为一个key，且必须是这个key的开始部分

WHY?

select max(auto_inc_col) from t for update;

避免重启执行上面这句的时候扫全表 ,myisam是非聚集索引的,不是用这个方式来采集自增值的,8.0虽然持久化了,但还是有这个限制

经测试,myisam自增列也需要被定义为一个key,但是不需要是key的开始部分

2.2 自增的参数

(root@localhost) [test]> show variables like 'auto_increment%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| auto_increment_increment | 1     |    -- 步长
| auto_increment_offset    | 1     |    --初始值
+--------------------------+-------+
2 rows in set (0.01 sec)

多节点全局唯一
N台服务器：A:[offset = 1, increment=N] ， B:[offset = 2, increment=N] ， C:[offset = 3, increment=N]...N:[offset = N, increment=N]

注意,这不能用来做多主,如果有额外的唯一索引就保证不了全局唯一了

2.3 自增锁分析

session1：

(root@localhost) [test]> create table t_ai_l(a int auto_increment, b int, primary key(a));
Query OK, 0 rows affected (0.02 sec)

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> insert into t_ai_l values(NULL, 10);
Query OK, 1 row affected (0.00 sec)

事务不提交

session2：

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> insert into t_ai_l values(NULL, 20);
Query OK, 1 row affected (0.00 sec)

咦？没等待耶,amazing!

AI锁在事务提交前就释放了,类似latch,使用完就释放了

session1&2：

(root@localhost) [test]> rollback;
Query OK, 0 rows affected (0.02 sec)

session1：

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> insert into t_ai_l values(NULL, 30);
Query OK, 1 row affected (0.00 sec)

(root@localhost) [test]> commit;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> select * from t_ai_l;
+---+------+
| a | b    |
+---+------+
| 3 |   30 |
+---+------+
1 row in set (0.00 sec)

可以看到虽然rollback,但AI锁是提交过了的,自增值不会跟着回滚,这样自增值就不连续,但连续也没什么用

也就是说,仅仅是这条sql执行的这段时间里,其他session是不可以对这个表操作的,插入过程太长,对insert也会阻塞

执行这条sql的时候,自增是被锁住的,所以插进去之后都是连续的值

2.4 利用sleep()分析自增锁

session1：

(root@localhost) [test]> begin;
Query OK, 0 rows affected (0.00 sec)

(root@localhost) [test]> insert into t_ai_l (a,b) select NULL, sleep(1) from tmp limit 10000;
~~~

session2：

(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421958478908128, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31217775, ACTIVE 10 sec
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1136, 11 row lock(s), undo log entries 10
MySQL thread id 2255, OS thread handle 140482757068544, query id 3006342 localhost root User sleep
insert into t_ai_l (a,b) select NULL, sleep(1) from tmp limit 10000
TABLE LOCK table `test`.`tmp` trx id 31217775 lock mode IS
RECORD LOCKS space id 1408 page no 4 n bits 624 index PRIMARY of table `test`.`tmp` trx id 31217775 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001cd15db; asc       ;;
 2: len 7; hex d4000001760110; asc     v  ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001cd15dc; asc       ;;
 2: len 7; hex d5000001300110; asc     0  ;;
 3: len 4; hex 80000002; asc     ;;

...

TABLE LOCK table `test`.`t_ai_l` trx id 31217775 lock mode AUTO-INC
TABLE LOCK table `test`.`t_ai_l` trx id 31217775 lock mode IX
...

插入数据过程分析：

• tmp表被加了IS锁,表中记录被加S锁,注意不会一次性所有记录加锁,是被查到的记录就被锁住,最终事务结束后释放所有锁
• t_ai_l表上有两个锁AUTO-INC和IX

session2：

(root@localhost) [test]> insert into t_ai_l (a,b) select NULL, sleep(1) from tmp limit 10000;
~~~

session3：

(root@localhost) [test]> show engine innodb status\G
...
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421958478909040, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 31218060, ACTIVE 15 sec setting auto-inc lock
mysql tables in use 2, locked 2
LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2255, OS thread handle 140482757068544, query id 3006385 localhost root Sending data
insert into t_ai_l (a,b) select NULL, b from tmp limit 10000
------- TRX HAS BEEN WAITING 15 SEC FOR THIS LOCK TO BE GRANTED:
TABLE LOCK table `test`.`t_ai_l` trx id 31218060 lock mode AUTO-INC waiting
------------------
TABLE LOCK table `test`.`tmp` trx id 31218060 lock mode IS
RECORD LOCKS space id 1408 page no 4 n bits 624 index PRIMARY of table `test`.`tmp` trx id 31218060 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001cd15db; asc       ;;
 2: len 7; hex d4000001760110; asc     v  ;;
 3: len 4; hex 80000001; asc     ;;

TABLE LOCK table `test`.`t_ai_l` trx id 31218060 lock mode AUTO-INC waiting
---TRANSACTION 31218051, ACTIVE 40 sec
mysql tables in use 2, locked 2
4 lock struct(s), heap size 1136, 40 row lock(s), undo log entries 39
MySQL thread id 2254, OS thread handle 140482756536064, query id 3006383 localhost root User sleep
insert into t_ai_l (a,b) select NULL, sleep(1) from tmp limit 10000
TABLE LOCK table `test`.`tmp` trx id 31218051 lock mode IS
RECORD LOCKS space id 1408 page no 4 n bits 624 index PRIMARY of table `test`.`tmp` trx id 31218051 lock mode S
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000001cd15db; asc       ;;
 2: len 7; hex d4000001760110; asc     v  ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000001cd15dc; asc       ;;
 2: len 7; hex d5000001300110; asc     0  ;;
 3: len 4; hex 80000002; asc     ;;
...

insert into t_ai_l (a,b) select NULL, b from tmp limit 10000 在等待三个锁

• t_ai_l表上的AUTO-INC锁
• tmp表上的IS锁
• tmp表中第一条记录上的S锁

这样设计的初衷是希望批量插入的自增值是连续的,但实际上是牺牲了并发度的

2.5 自增锁的分类

-	说明
insert-like	所有插入语句都属于此类
simple inserts	插入之前能确定插入多少行(insert into table_1 values(NULL, 1), (NULL, 2)😉
bulk inserts	插入之前不确定插入多少行(insert into table_1 select * from t;)
mixed-mode inserts	插入内容部分自增部分确定(insert ... on duplicate key update不推荐)

2.6 如何提升自增并发度

(root@localhost) [test]> show variables like 'innodb_autoinc_lock_mode';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_autoinc_lock_mode | 1     |
+--------------------------+-------+
1 row in set (0.00 sec)

此参数可设置为[0|1|2]

• 0 sql语句执行完释放AI锁,若数据量大sql执行完之前其他事务是无法插入的,保证了在此sql语句内插入的数据自增值是连续的
• 1(default,大部分情况用1) 对于bulk inserts,和设置0一样

simple inserts则可以并发插入,在sql运行完之前确定自增值之后就可以释放AI锁了

							+
			bulk inserts 	| 	simple inserts
							|
+-------------------------------------------------------+
							|
	   acquire AI_Lock   	|   acquire AI_Lock
							|
 insert ... select ...  |   ai = ai + M
							| 
           ai = ai + N   	|   release AI_Lock
							|
       release AI_Lock   	|   insert ... select ...
							+
bulk inserts不知道要插入多少行,所以只能等insert结束后,才知道N的值,然后一次性(ai + N)
simple inserts知道插入的行数(M),所以可以先(ai + M),然后将锁释放掉,给别的事务用,然后自己慢慢插入数据

• 2 所有自增都可以并发(不同于Simple inserts的方式 ) 同一sql语句自增可能不连续

row-based binlog

for (i = ai; until_no_rec; i++) {
	acquire AI_Lock 		# 插入前申请锁
	insert one record... 	# 只插入一条记录
	ai = ai + 1 			# 自增值+1
	release AI_Lock 		# 释放锁
}

并发度增加了,但性能不一定变好,尤其是单线程的时候,频繁申请和释放锁会导致开销大
虽然不连续,但插入进去至少是单调递增所以基本满足业务需求

tips：
这种情况严格意义上是不连续,但由于并发度不够再加上limit是预先批量申请分配这种不阻塞不是很好演示,所以看上去是连续的,其实不是,limit大一点应该是可以的,但等待时间太长了,也可以通过mysqlslap测测