MySQL锁分析读已提交级别下的锁分析

一 事前准备

CREATE TABLE hero (
    number INT,
    name VARCHAR(100),
    country varchar(100),
    PRIMARY KEY (number),
    KEY idx_name (name)
) Engine=InnoDB CHARSET=utf8;

建立一个表,其中人员编号是主建
初始化语句

INSERT INTO hero VALUES
    (1, 'l刘备', '蜀'),
    (3, 'z诸葛亮', '蜀'),
    (8, 'c曹操', '魏'),
    (15, 'x荀彧', '魏'),
    (20, 's孙权', '吴');

二 锁分析

2.1 使用主键查询

2.1.1 使用主键进行等值查询

(1) 使用SELECT … LOCK IN SHARE MODE来为记录加锁

SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

这个记录只给number=8的记录加上共享锁就可以了
(2) SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;

SELECT * FROM hero WHERE number = 8 FOR UPDATE;

这个记录只给number=8的记录加上排他锁就可以了
(3) 使用UPDATE …来为记录加锁

UPDATE hero SET country = '汉' WHERE number = 8;

因为update并没有涉及到对二级别的索引的更新,只为number=8的记录加上排他锁

UPDATE hero SET name = 'cao曹操' WHERE number = 8;

这个update更新了二级索引,所以需要在二级索引和number=8的主键索引上都加上排他行锁,如下图

(4)使用DELETE …来为记录加锁

DELETE FROM hero WHERE number = 8;

首先需要给number=8的聚族索引加上锁,然后对二级索引也上锁

2.1.1 使用主键进行范围查询

(1)使用SELECT … LOCK IN SHARE MODE来为记录加锁

SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

1.首先需要到聚族索引中查询到number<=8的第一条记录
2.判断一下是否符合查询条件
从聚族索引中取出一个记录,都需要判断这个记录是否符合范围查询的边界条件,也就是number<= 8这个条件,如果符合的话将这个值返回给Server层记录处理,否则就释放这个记录上的锁,并且返回一个server层查询完成
3.将该记录返回到server层继续判断
server层如果接受到存储引擎层提供的查询完毕的信息,就结束查询,否则继续执行那些没有进行索引下推的条件,然后在server层在进行一次判断,如果这个记录符合条件,就发送给客户端,否则需要释放掉这个记录上添加的锁
4.然后继续处理聚族索引中的下一个值,重复123步骤
当查询到number为8的那个记录的时候,还需要查询一个记录,就是number为15的记录,在存储引擎读取这个记录的时候,首先需要给这个记录加上锁,然后在server层判断,后续将锁释放掉.
如果在事物T1中执行

# 事务T1
BEGIN;
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

然后再到事物T2中执行

# 事务T2
BEGIN;
SELECT * FROM hero WHERE number = 15 FOR UPDATE;

是没有问题的,因为T2执行时候,事物T1已经释放了number值为15记录的记录的锁,但是如果你先执行T2,再执行T1,由于T2已经持有number值为15的记录的锁,事物T1将因为获取不到这个锁等待
(2)使用SELECT …FOR UPDATE来为记录加锁
和SELECT … FOR UPDATE语句类似，只不过加的是X型排他锁
(3)使用UPDATE来为记录加上锁
如果没有更新二级索引,加锁和SELECT…FOR UPDATE相同
如果更新了二级索引,需要首先给聚族索引加上排他行锁,然后给对应的二级索引加上排他行锁,一直到最后一个不符合的记录出现
(3)使用DELETE来为记录加上锁
和UPDATE一致

2.2 使用二级索引

2.2.1 使用二级索引进行等值查询

(1)使用SELECT … LOCK IN SHARE MODE来为记录加锁

SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;

所以先要对二级索引记录加S型共享锁，然后再给对应的聚簇索引记录加S型共享记录锁，示意图如下：

这里再次说明一些这个语句的加锁顺序

1. 先对name列为’c曹操’二级索引进行加锁
2. 再对相对应的聚族索引记录进行加锁
   idx_name是一个普通的二级索引,到idx_name索引中定位到name='c操作’这个条件的第一个记录后,就需要沿着这个记录一路向后找,当InnoDB存储引擎层查找到当前记录的下一条记录时候,在对其加锁前就直接判断这个记录是否满足等值匹配的条件,如果不满足直接返回,否则的话需要将这个记录加锁后再返回server层,所以不需要给下一个记录加锁了

UPDATE hero SET name = '曹操' WHERE number = 8;

假设两个事物A和B分别执行上述操作,可能导致事物A和B发生死锁

(2)使用SELECT … FOR UPDATE语句

SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;

SELECT … LOCK IN SHARE MODE语句的加锁情况类似，都是给访问到的二级索引记录和对应的聚簇索引记录加锁，只不过加的是排他行锁
(3)使用UPDATE …来为记录加锁
加锁情况和SELECT … FOR UPDATE一致
(4)使用DELETE …来为记录加锁
加锁情况和SELECT … FOR UPDATE一致

2.2.2 使用二级索引进行范围查询

备注
二级索引可以使用索引条件下推这个特性的

(1)使用SELECT … LOCK IN SHARE MODE来为记录加锁

SELECT * FROM hero FORCE INDEX(idx_name) WHERE name <= 'c曹操' LOCK IN SHARE MODE;

对于查询条件name<='c曹操’的语句说,执行这个语句需要使用二级索引,二级索引相关的条件是可以使用索引条件下推这个特性的.设计InnoDB的人规定,如果一个特性记录不符合索引条件下推中的条件,直接跳到下一条记录,如果这个是最后一个记录,那么直接向server层报告查询完毕.如果这是一个记录,那么直接向server层报告查询完毕.
存在一个问题:
先对一个记录加了锁,然后再判断这个记录是不是符合索引条件下推的条件,如果不符合直接跳到下一个记录获取直接向server层报告查询完毕,这个过程并没有把那条被加锁的记录上的锁释放掉.

这个样子会造成一个情况,假设T1执行了上述语句并且尚没有提交,T2再执行这个语句

SELECT * FROM hero WHERE name = 'l刘备' FOR UPDATE;

T2中的语句需要获取name值为l刘备的二级索引记录上的X记录锁,而且T1中仍然持有name值为l刘备的二级索引上的S记录锁,这个造成了T2获取不到锁进入到了等待状态
(2)使用SELECT … FOR UPDATE
和SELECT … LOCK IN SHARE MODE一致,加上的锁是X正经记录锁
(3)UPDATE …语句

UPDATE hero SET country = '汉' WHERE name >= 'c曹操';

假设这个语句执行时候使用了idx_name二级索引进行锁定读,那么这个的加锁方式和上边的SELECT…FOR UPDATE语句一致,如果其他二级索引列也被更新,那么也会对应的二级索引记录进行加锁

UPDATE hero SET country = '汉' WHERE name <= 'c曹操';

索引条件下推这个特性只适用SELECT语句,UPDATE语句中无法使用,那么这个语句就会为name值为’c曹操’和’l刘备’的二级索引记录以及它们对应的聚族索引进行加锁,之后在判断边界条件时候发现name值为‘l刘备’的二级索引记录不符合name <= 'c曹操’条件,再把二级索引记录和对应的聚族索引记录上的锁释放掉

(4)DELETE …
和UPDATE一致

2.3 全表扫描的情况

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上没有建立索引,所以只能采用全表扫描的方式来执行这个查询记录,存储引擎读取一个聚族索引记录,就会为这个记录加上一个S记录锁,然后返回给server层,如果server层判断country = '魏’这个条件是否成立,如果成立将其发送给客户端,否则就释放掉这个记录上的锁

使用SELECT … FOR UPDATE进行加锁的情况与上边类似，只不过加的是X型