MySQL 锁相关概念

基础概念

在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)

  • 快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。
  • 当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

 

快照读 (snapshot read)：简单的select操作，属于快照读，不加锁(特殊除外)

  • select ...... where ......

 

当前读 (current read)：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁，保证其他并发事务不能修改当前记录

  • select ...... lock in share mode (共享锁)
  • select ...... for update
  • insert/update/delete ......

 

RR隔离级别下，InnoDB行锁是通过给索引上的索引项加锁来实现的，InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则进行全表扫描的当前读，那么InnoDB将会给所有记录加锁，同时会锁上聚集索引内的所有GAP，杜绝所有并发的增删改操作。

 

总结加锁规则：

原则 1：加锁的基本单位是 next-key lock。next-key lock 是前开后闭区间，比如(5,10]。
原则 2：查找过程中访问到的对象才会加锁。
优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。
优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。
一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

一、MySQL 锁级别

  • 表锁
  • 行锁

 

二、InnoDB存储引擎锁

  • S 行级共享锁
  • X 行级排他锁
  • IS 意向共享锁
  • IX 意向排他锁
  • II 插入意向锁 insertion intention lock ，隐式锁，只有在发生等待时才会转换成显式锁，同时是gap lock；不冲突时，不堵塞后面的插入操作
  • AI 自增锁

 

意向锁：

  • 揭示下一层级请求的锁类型
  • IS 事务想要获得的一张表中的某几行的共享锁
  • IX 事务想要获得的一张表中的某几行的排他锁
  • InnoDB存储引擎中意向锁都在表上

 

例子：在表1中更新某行记录，则会在表1上加上IX锁，同时在记录上上加上排他锁：即在表1的层级上揭示，要对下一层级（记录行）加X锁，所以在表上是IX锁

 

 

AI自增锁：

  • 在表上加锁，RR隔离级别下，innodb_autoinc_lock_mode=1时，上锁时间为SQL的执行时间，执行期间，效果相当于表级排他锁。

 

三、锁的算法

  • Record Lock 行锁
  • Gap Lock 间隙锁，锁范围，不锁记录本身
  • Next-Key Lock 等于Gap Lock + Record Lock的结合，锁定范围，同时锁定记录本身

 

Gap Lock 与 Next-Key Lock兼容

四、show engine innodb status中锁的展示

版本：MySQL 5.7.37

隔离级别：RR

innodb_status_output_locks：打开该参数，可以看到详细的锁信息

表：

CREATE TABLE `l` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`a`),
  UNIQUE KEY `idx_c` (`c`),
  KEY `idx_b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

insert into l select 2,4,6,8;
insert into l select 4,6,8,10;
insert into l select 6,8,10,12;
insert into l select 8,10,12,14;

 

--session 1
begin;
select * from l where b=6 for update;


--session 2: show engine innodb status\G

4 lock struct(s), heap size 1136, 3 row lock(s) #4个锁对象结构，占用内存大小1136字节，3个行锁
MySQL thread id 51, OS thread handle 140082310711040, query id 9504495 localhost root
TABLE LOCK table `test`.`l` trx id 636566 lock mode IX
RECORD LOCKS space id 129 page no 6 n bits 72 index idx_b of table `test`.`l` trx id 636566 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000006; asc ;; #二级索引列b的值
1: len 4; hex 80000004; asc ;; #对应的主键值

RECORD LOCKS space id 129 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 636566 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000004; asc ;;          #主键a列的值
1: len 6; hex 00000009b67e; asc ~;;     #隐藏列，事务ID，长度6
2: len 7; hex bd000001260110; asc & ;;  #隐藏列，回滚指针，长度7
3: len 4; hex 80000006; asc ;;          #b列的值
4: len 4; hex 80000008; asc ;;          #c列的值
5: len 4; hex 8000000a; asc ;;          #d列的值

RECORD LOCKS space id 129 page no 6 n bits 72 index idx_b of table `test`.`l` trx id 636566 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 80000008; asc ;; #二级索引列b的值
1: len 4; hex 80000006; asc ;; #对应的主键值

 

上面结果中锁分别有：

1、表级意向排他锁

TABLE LOCK table `test`.`l` trx id 636553 lock mode IX

 

2、Next-Key Lock 锁：当有lock_mode X但没有锁算法提示的，就是Next-Key Lock 锁

RECORD LOCKS space id 129 page no 6 n bits 72 index idx_b of table `test`.`l` trx id 636566 lock_mode X

在索引idx_b上加上锁： ((4,2),(6,4)]  ，其中（4,2）为b=6的前一行二级索引值

 

3、Record Lock 行锁：主键a=4的行级排他锁

RECORD LOCKS space id 129 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 636566 lock_mode X locks rec but not gap

 

4、Gap Lock锁：gap before rec即记录行前面的间隙

RECORD LOCKS space id 129 page no 6 n bits 72 index idx_b of table `test`.`l` trx id 636566 lock_mode X locks gap before rec

在索引idx_b上加锁：((6,4),(8,6)) ，其中（8,6）为b=6的后一行二级索引值

 

五、heap no的理解

Innodb 行级加锁是通过bitmap位图来表示具体锁住了那几行，lock bitmap这个位图是整个页的所有heap no，head no表示数据插入的顺序，而插入的顺序不代表数据大小的顺序。在一个页中，存在mysql自己生成的heap no 0和heap no 1，这是用来存放虚拟的最小记录(heap no 0--infimum)和最大记录(heap no 1--supremum)，业务记录都是heap no 2开始。

 

 

 

--session 1
begin;
select * from l where b=12 for update; 

--session 2 : show engine innodb status\G
2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 51, OS thread handle 140082310711040, query id 9504505 localhost root
TABLE LOCK table `test`.`l` trx id 636568 lock mode IX
RECORD LOCKS space id 129 page no 6 n bits 72 index idx_b of table `test`.`l` trx id 636568 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;;

 

可以理解为b列的记录在索引中表示为：infimum、4、6、8、10、supremum

 

可解读为 在idx_b索引上加锁：（（10,8），+∞）

 

六、插入意向锁

--session 1
insert into l select 20,22,24,26;
begin;
select * from l where a<=20 for update;

--session 2
begin;
insert into l select 10,12,14,16;

--session 3 : show engine innodb status\G
TABLE LOCK table `test`.`l` trx id 636579 lock mode IX
RECORD LOCKS space id 129 page no 3 n bits 72 index PRIMARY of table `test`.`l` trx id 636579 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 6 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
 0: len 4; hex 80000014; asc     ;;
 1: len 6; hex 00000009b69d; asc       ;;
 2: len 7; hex b4000001240110; asc     $  ;;
 3: len 4; hex 80000016; asc     ;;
 4: len 4; hex 80000018; asc     ;;
 5: len 4; hex 8000001a; asc     ;;

在主键上产生的意向锁（8,20）：gap lock + insert intention

lock_mode X locks gap before rec insert intention

当session 1提交或者回滚后，session2会产生一个gap insert inetntion锁（10,20），在此时，如果有a=15的记录插入，是可以的，即不堵塞其他插入操作

 

一个有趣的插入意向锁锁等待情况：

CREATE TABLE `t3` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) DEFAULT NULL,
  `num` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4

[5.7.37-log localhost:mysql.sock]>select * from t3;
+----+------+------+
| id | name | num  |
+----+------+------+
|  1 | a    |   10 |
|  2 | d    |   20 |
|  3 | c    |  100 |
+----+------+------+


--session 1
[5.7.37-log localhost:mysql.sock]>begin;
Query OK, 0 rows affected (0.00 sec)

[5.7.37-log localhost:mysql.sock]>insert into t3(name,num) values('d',50) on duplicate key update num=50;
Query OK, 2 rows affected (0.00 sec)

--session 2(会出现锁等待的情况)
[5.7.37-log localhost:mysql.sock]>insert into t3(name,num) values('x',200);
# 此处换成insert into t3 values(100,'x',200);结果是一样的

--session 3 查看锁信息
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 228, OS thread handle 140651052574464, query id 413 localhost root update
insert into t3(name,num) values('x',200)
------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 191 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 652784 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;;

------------------
TABLE LOCK table `test`.`t3` trx id 652784 lock mode IX
RECORD LOCKS space id 191 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 652784 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;;

---TRANSACTION 652783, ACTIVE 30 sec
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 227, OS thread handle 140651052844800, query id 411 localhost root
TABLE LOCK table `test`.`t3` trx id 652783 lock mode IX
RECORD LOCKS space id 191 page no 4 n bits 80 index uk_name of table `test`.`t3` trx id 652783 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 1; hex 64; asc d;;
 1: len 4; hex 80000002; asc     ;;

RECORD LOCKS space id 191 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 652783 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 00000009f5ef; asc       ;;
 2: len 7; hex 3d00001ab31be5; asc =      ;;
 3: len 1; hex 64; asc d;;
 4: len 4; hex 80000032; asc    2;;

RECORD LOCKS space id 191 page no 3 n bits 80 index PRIMARY of table `test`.`t3` trx id 652783 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;;

 

从上述结果中可以看出，session 1中，唯一键存在冲突时，会加上4把锁：

1、t3表的IX意向排他锁

2、唯一建冲突健的行锁（d,2），即name=d的唯一索引行行锁

3、唯一健name=d对应的主键id=2的PRIMARY KEY行锁

4、自增主键列ID的最大值锁： supermum 行锁，此处虽然显示是行锁，但实际上应该是主键 (3,+∞)的gap锁

 

所以session 2的插入操作会出现插入意向锁等待

posted @ 2022-10-23 22:56  百老汇大管家  阅读(157)  评论(0)    收藏  举报