看了很多文章,感觉加锁是个很复杂的事情,最近遇到LTS库进程发生死锁. 还是RC隔离级别.
只好自己做实验来完善加锁理论知识!
我们知道，谈及关系型数据库的基础知识是绕不开MVCC
和锁
的，而谈到MySQL，一般我们说的都是InnoDB引擎的锁相关内容。比如我们熟知的表锁、record lock、next-key lock 以及意向锁等吧啦吧啦一堆。
今天主要围绕行锁相关，分析在日常的使用过程中RR
或RC
下的加锁情况。因为平时使用中主要以RR和RC为主，当然RC偏多一些。在这两种隔离级别下分别介绍主键、唯一索引、普通索引和无索引字段上操作的情况。
比如我们有一张 mis_pointer
表，结构如以下：
复制
CREATE TABLE `mis_pointer` (
`id` int NOT NULL,
`p_name` varchar(64) DEFAULT NULL,
`p_addr` int DEFAULT NULL,
`p_type` mediumint DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `p_addr` (`p_addr`),
KEY `p_type` (`p_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;复制
复制
这里没有自增键,AI表数据如下，后面介绍过程中数据可能发生变化哈。
测试SQL
set transaction_isolation='repeatable-read';
begin;
select * from mis_pointer mp where mp.id < 10 for update;
select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA
from performance_schema.data_locks;
rollback;复制
使用的工具如下: 它比较好看结果
先简单介绍下MYSQL的锁
15.7.1 InnoDB Locking
This section describes lock types used by InnoDB
.
Shared and Exclusive Locks 共享和独占锁 S,X
Intention Locks 意向锁
Record Locks 记录锁
Gap Locks 间隙锁
Next-Key Locks
Insert Intention Locks 插入意向锁
AUTO-INC Locks 自增锁
Predicate Locks for Spatial Indexes 空间索引锁
1 表锁, 有独占锁(X),共享锁(S) ,读意向锁（IS 锁）写意向锁（IX 锁）自增锁(AI)四种模式
手工给表 上 X 和S 锁
mysql> lock table user read(write);mysql> unlock tables;复制
类似ORACLE 的TM, 目的就是告诉后来者目前该表正在干啥, 避免你每行去检查,提高性能.
2 行锁 有四种类型:
Record Locks 记录锁
Gap Locks 间隙锁
Next-Key Locks
Insert Intention Locks 插入意向锁 实际是插入间隙 IGAP
加锁 分为 加锁对象,加锁的类型,加锁的模式,加锁的数据
这里我们看到在MIS_POINTER表上加锁,
INDEX_NAME :把锁加在 P_ADDR索引上 (加锁对象)
LOCK_TYPE: 加锁的类型: 表和记录锁;
LOCK_MODE:加锁的模式 IX, X ;
LOCK_DATA 加锁锁定的数据 1012值和对应的主键ID 9
RR隔离级别下
1主键索引加锁
1.1范围条件
使用以下语句进行范围更新时，LOCK_MODE显示加X锁，其实是加了Next key Lock（即锁住主键索引和前面的间隙），范围后面的11也加了X和GAP锁
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.id < 10 for update;
这里能看出加了个表锁, IX意向更新锁, 然后对于小于10的记录,全加X锁, 9到11之间加GAP锁
update mis_pointer mp set p_name='xxx' where mp.id < 10;复制
这里 UPDATE 和FOR UPDATE 是一样的.
1.2范围条件未命中
set transaction_isolation='repeatable-read';begin;update mis_pointer mpset p_name='xxx'where mp.id > 6 and mp.id<9;
会加GAP间隙锁
1.3等值条件
select * from mis_pointer mp where mp.id = 9 for update复制
只加主键索引,记录锁,X
1.4等值条件未命中
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.id = 10 for update
数据在中间就加间隙锁
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.id = 13 for update复制
数据在边上加SUPREMUM... 无穷+
它不会阻碍其它会话更新
UPDATE mis_pointer mpSET p_name='xxx'WHERE mp.id = 9;mysql> select * from mis_pointer;+----+--------+--------+--------+| id | p_name | p_addr | p_type |+----+--------+--------+--------+| 1 | a | 1000 | 1 || 2 | b | 1028 | 1 || 5 | a | 1004 | 1 || 6 | a | 1008 | 2 || 9 | xxx | 1012 | 3 || 11 | a | 1016 | 3 || 12 | c | 1088 | 1 |+----+--------+--------+--------+7 rows in set (0.00 sec)
会阻塞后续边界上的插入
mysql> insert into mis_pointer(id,p_name,p_addr,p_type) values(15,'e','1099',4);复制
形成阻塞效果
2 唯一索引加锁
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.p_addr < 1012 for update
先在唯一索引加X锁,这里要多加一把锁,然后在主键索引加X锁
2.2范围条件未命中
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mpwhere mp.p_addr < 1011 and mp.p_addr > 1008 for update
也要加一把 X 锁 这把锁显示加载 唯一索引上
2.3 等值条件
begin;select * from mis_pointer mp where mp.p_addr = 1012 for update;复制
2.4 等值条件未命中
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.p_addr = 1011 for update
而 等值下未命中要在唯一索引加X 和GAP间隙锁. 也就是 NEXT-KEY
3 普通索引加锁
3.1范围条件
select * from mis_pointer mp where mp.p_type < 3 for update复制
普通索引 IDX_P_TYPE{(1,1);(1,2)
; (1,5) ; (2,6) ; (3,9) ; (3,11) ; (1,12)}
LOCK_DATE选中的是 {(1,1);(1,2) ; (1,5) ; (2,6) ; (3,9) ; (1,12)}
也就是说二级索引的小于符合< 实际上是<= 要在边界也加把X锁. 到了主键索引上就没有边界的对应的锁
3.2范围条件未命中
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mpwhere mp.p_type >1 and mp.p_type< 3 for update;select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATAfrom performance_schema.data_locks;
只加索引的X
3.3等值条件
在等值查找条件下，RR隔离级别下，索引记录加Next Key Lock，对应主键加锁
select * from mis_pointer mp where mp.p_type = 3 for update复制
这里出现了SUPREMUM PSEUNDO-RECORD (上确界伪记录) 也是一种NEXT-KEY
插入 P_TYPE=4 后的记录就出现GAP锁
SHOW ENGINE INNODB STATUS;---TRANSACTION 19418440, ACTIVE 0 sec4 lock struct(s), heap size 1128, 5 row lock(s)这里表示4个锁结构,锁定5行数据复制
3.4 等值条件未命中
这里把原来的2改成5, 然后再改回来
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.p_type =2 for update;
select * from mis_pointer mp where mp.p_type =5 for update;复制
4 无索引字段
4.1范围条件下
比如范围条件下
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.p_name > 'b' for update
这下就悲催了，所有主键索引加X，即相当于全主键索引加锁(全表)
连A 也不放过哎无索引真不行
4.2范围条件下未命中
select * from mis_pointer mpwhere mp.p_name > 'd' and mp.p_name < 'e' for update;复制
4.3等值条件下
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.p_name = 'b' for update
4.4等值条件下未命中
set transaction_isolation='repeatable-read';begin;select * from mis_pointer mp where mp.p_name = 'E' for update
这里无论怎么样都要锁全主键索引(全表)
不过后期得到优化, 这里涉及到了MYSQL架构的问题. MYSQL分服务层和引擎层. 服务层要什么数据,引擎层就返回什么数据,然后由服务层完成过滤.
这个图表示 引擎加锁后,服务层发现不是需要更新的,通知引擎解锁. 再高并发下是个问题.
5 更新索引字段
从下面的语句无法发现被修改的索引的加锁信息
select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATAfrom performance_schema.data_locks;复制
查询加锁跟上面各种情况一致.
6 DELETE语句
跟UPDATE语句一样, 同时也要维护索引 前面1到4+5的情况
7 插入语句
insert into mis_pointer(id,p_name,p_addr,p_type)value(16,'d',1086,7);复制
这个语句无法发现插入语句其它加锁的信息
理论上说 对唯一索引加GAP间隙锁或者是NEXT-KEY边界锁,对自增ID要加AI锁.
2 RC隔离级别下
1主键索引加锁
2.1.1范围条件
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.id < 11 for update;rollback;
只加主键记录锁，不会加GAP锁
2.1.2范围条件未命中
set transaction_isolation='read-committed';begin;update mis_pointer mpset p_name='xxx'where mp.id > 6 and mp.id<9;
表意向X锁 不阻碍其它会话插入
2.1.3等值条件
set transaction_isolation='read-committed';begin;update mis_pointer mpset p_name='xxx'where mp.id =9;
2.1.4等值条件未命中
set transaction_isolation='read-committed';begin;update mis_pointer mpset p_name='xxx'where mp.id =10;
表意向X锁
2 唯一索引加锁
2.2.1范围条件
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_addr < 1012 for update
可以看到不会加GAP，只是在对应唯一索引上加记录锁，相对应的主键索引加记录锁
这里明显 唯一索引加了4把锁, 而主键索引上只加了3把锁.
2.2.2范围条件未命中
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_addr < 1011 and mp.p_addr > 1008 for update
也要在唯一索引加把锁
2.2.3等值条件
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_addr = 1012 for update
只加
2.2.4等值条件未命中
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_addr = 1011 for update;select THREAD_ID,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATAfrom performance_schema.data_locks;
只加表意向X锁
3 普通索引加锁
2.3.1范围条件
begin;select * from mis_pointer mp where mp.p_type < 3 for update
可[3,9] 跑不掉也要加锁
2.3.2范围条件未命中
set transaction_isolation='read-committed';begin;select * from mis_pointer mpwhere mp.p_type >1 and mp.p_type< 3 for update;
2.3.3等值条件
在等值查找条件下，RC隔离级别下，索引记录和对应主键加记录锁
begin;select * from mis_pointer mp where mp.p_type = 3 for update复制
2.3.4等值条件未命中
select * from mis_pointer mp where mp.p_type = 2 for update;复制
4 无索引字段
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_name > 'b' for update
范围条件下，不会锁全表，会锁定记录对应的主键索引，加记录锁，不会锁定间隙
该字段值条件范围内对应主键和其他字段的操作也会被阻塞
阻塞以该字段为条件的更新删除操作的会话
由于没有GAP锁的存在，并不影响写入操作
2.4.2范围条件下未命中
select * from mis_pointer mpwhere mp.p_name > 'd' and mp.p_name < 'e' for update;复制
2.4.3等值条件下未命中
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_name = 'e' for update
只加意向修改表锁
2.4.4 等值条件下
set transaction_isolation='read-committed';begin;select * from mis_pointer mp where mp.p_name = 'b' for update
最后 我们发现RC隔离下 唯一索引和普通索引都要加一把锁,命中和没有命中情况下都要加.这也是没有办法索引上只有这个值! 也就是说维持范围的边界特性而多加一把记录锁; RR隔离下也有!
不过RR隔离下唯一和普通索引 未命中下要加X, GAP 也就是NEXT-KEY
隐式锁 简介
隐式锁主要用在插入场景中。在Insert语句执行过程中，必须检查两种情况，一种是如果记录之间加有间隙锁，为了避免幻读，此时是不能插入记录的，另一中情况如果Insert的记录和已有记录存在唯一键冲突，此时也不能插入记录。除此之外，insert语句的锁都是隐式锁，但跟踪代码发现，insert时并没有调用lock_rec_add_to_queue函数进行加锁, 其实所谓隐式锁就是在Insert过程中不加锁。
只有在特殊情况下，才会将隐式锁转换为显示锁。这个转换动作并不是加隐式锁的线程自发去做的，而是其他存在行数据冲突的线程去做的。例如事务1插入记录且未提交，此时事务2尝试对该记录加锁，那么事务2必须先判断记录上保存的事务id是否活跃，如果活跃则帮助事务1建立一个锁对象，而事务2自身进入等待事务1的状态
如何判断隐式锁是否存在
InnoDB的每条记录中都一个隐含的trx_id字段，这个字段存在于聚集索引的B+Tree中。假设只有主键索引，则在进行插入时，行数据的trx_id被设置为当前事务id；假设存在二级索引，则在对二级索引进行插入时，需要更新所在page的max_trx_id。
因此对于主键，只需要通过查看记录隐藏列trx_id是否是活跃事务就可以判断隐式锁是否存在。对于对于二级索引会相对比较麻烦，先通过二级索引页上的max_trx_id进行过滤，如果无法判断是否活跃则需要通过应用undo日志回溯老版本数据，才能进行准确的判断。
由于二级索引的记录不包含事务ID，如何判断二级索引记录上是否有隐式锁呢？前面提到二级索引页的PAGE_MAX_TRX_ID字段保存了一个最大事务ID，当二级索引页中的任何记录更新后，都会更新PAGE_MAX_TRX_ID的值。因此，我们先可以通过PAGE_MAX_TRX_ID进行判断，如果当前PAGE_MAX_TRX_ID的值小于当前活跃事务的最新ID，说明修改这条记录的事务已经提交，则不存在隐式锁，反之则可能存在隐式锁，需要通过聚集索引进行判断，其判断过程由函数row_vers_impl_x_locked_low完成，
不同类型行锁的兼容矩阵
MYSQL 加锁影响因素
MYSQL | 隔离机制 | 版本 | SQL语句 | 查询范围 | 索引状态 |
服务层 | RC | 5.7 | INSERT | = | 主键 |
引擎层 | RR | 8.0 | DELETE | > | 唯一索引 |
UPDATE | != | 普通索引 | |||
无索引 | |||||
更新索引列 |
