Mysql之案例分析（二）

间隙锁加锁分析

以下案例均基于以下表及数据

CREATE TABLE `t` (
`id` int(11) NOT NULL,
`c` int(11) DEFAULT NULL,
`d` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

案例一：等值查询间隙锁

session A	session B	session C
begin;
update t set d=d+1 where id=7;
	insert into t values(8,8,8);
		update t set d=d+1 where id=10;

加锁范围(5,10)，B被堵塞，C正常执行

案例二：非唯一索引等值锁

session A	session B	session C
begin;
select id from t where c=5 lock in share mode;
	update t set d=d+1 where id=5;
		insert into t values(7,7,7);

加锁范围：(0,10)，C被堵塞
查找c=5仅需要返回id，id作为主键索引，在二级索引上有，走覆盖索引即可。因此，主键索引没有被访问，不用加锁，B正常执行

案例三：主键索引范围锁

session A	session B	session C
begin;
select * from t where id>=10 and id<11 for update;
	insert into t values(8,8,8);
	insert into t values(13,13,13);
		update t set d=d+1 where id=15;

加锁范围[10,15]：

B中第一次插入成功，第二次插入堵塞
C中更新堵塞

把 A中id>=10 and id<11改为id>=10 and id<=11，C不再堵塞，为什么？

锁是一个一个申请的，要分开来看。id>=10 and id<=11可分解为

id=10：加锁范围行锁(10)，主键且数据存在，退化为行锁，去除间隙锁(5,10)
id>10 and id<11：第一个扫描到的数是15，默认加锁范围(10,15]
id=11：第一个扫描到的数也是15，由于是主键退化为间隙锁(10,15)，(15)上不再加锁

案例四：非唯一索引范围锁

session A	session B	session C
begin;
select * from t where c>=10 and c<11 for update;
	insert into t values(8,8,8);
		update t set d=d+1 where c=15;

加锁范围(5,15]，B、C均堵塞

把 A中c>=10 and c<11改为c>=10 and c<=11`，C依然堵塞，对比案例三

锁是一个一个申请的，要分开来看。id>=10 and id<=11可分解为

c=10：加锁范围行锁(5,10]
c>10 and c<11：第一个扫描到的数是15，默认加锁范围(10,15]
c=11：第一个扫描到的数也是15，加锁范围(10,15]，(15)上仍然有锁

把 A中c>=10 and c<11改为c>10 and c<11`，锁范围如何变化

首个扫描到的数据为c=15，加锁范围(10,15]

案例五：唯一索引范围锁 bug

session A	session B	session C
begin;
select * from t where id>10 and id<=15 for update;
	insert into t values(16,16,16);
		update t set d=d+1 where id=20;

加锁范围(10,20]，B、C均堵塞

案例六：limit 语句加锁

insert into t values(30,10,30);
insert into t values(40,10,40);

session A	session B	session C
begin;
delete from t where c=10 limit 2;
	insert into t values(12,12,12);
		update t set d=d+1 where id=40;
		update t set d=d+1 where id=30;

加上limit 2，加锁范围由(5,15)退化为(5,10]，B中插入不再堵塞
limit N决定了右区间的边界，C中第一次正常执行，第二次更新堵塞

案例七：死锁

session A	session B
begin;
select id from t where c=10 lock in share mode;
	update t set d=d+1 where c=10;
insert into t values(8,8,8);

A中lock in share mode加锁范围(5,15)，B先堵塞，在A中执行插入后检测到死锁异常。原因在于间隙锁和行锁是分开申请的，间隙锁之间不冲突。B先申请到(5,10)的间隙锁，再申请c=10的行锁，由于行锁已被A获取而堵塞。接下来，A执行插入，和B中间隙锁冲突，形成循环等待。

案例八：数据删除，锁范围扩大

session A	session B	session C
begin;
select id from t where c=5 lock in share mode;
	insert into t values(13,13,13);
	delete from t where c=10;
		insert into t values(12,12,12);

A中lock in share mode加锁范围(0,10)，B中插入数据c=13成功，C中插入数据c=12堵塞，均值间隙锁之外为什么后者会堵塞？

B中删除破坏了原有间隙锁结构，间隙锁扩大到(0,13)

案例九：排序对加锁的影响

session A	session B
begin;
select * from t where c>=15 and c<=20 order by c desc lock in share mode;
	insert into t values(6,6,6);

先不关心排序带来的影响，A中范围查询正常加锁范围为(10,25]，B中c=6插入是不会堵塞的。倒序排列后，如果还是从左边开始查找，最终结果是倒序的，还需要把结果集倒置。因此，从右边开始查找，即20开始找，加锁规则不变，还是左开右闭，一直查找到10为止，加锁是以next-key lock为单位，所以会加到(5,10]上，导致B中插入语句堵塞。

案例十：加锁顺序带来的死锁

session A	session B
begin;
select id from t where c in(5,20,10) lock in share mode;
	select id from t where c in(5,20,10) order by c desc for update;

A、B都需要在c=5、c=10、c=20上加锁，由于B中使用了倒序导致查找顺序相反，加锁顺序也刚好相反，一定并发下就会存在相互等待从而死锁。例如：

A先在c=5上成功加锁
B在c=20、c=10上依次成功加锁
A在c=10上加锁时，需要等待B中c=10上的行锁释放
B在c=5上加锁时，同样需要等待A中c=5上的行锁释放

insert加锁分析

session A	session B	session C
begin;
insert into t values(6,6,6);
	insert into t values(7,7,7);
		insert into t values(4,4,4);

-- A中插入语句执行后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t           | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

A中只有一个表级锁IX(排他意向锁)，对B、C的插入语句没有影响，B、C正常执行。A、B、C均申请各自的插入意向锁，分属不同的行，不存在冲突

insert唯一键冲突堵塞

-- 把字段c上索引改为唯一索引
ALTER TABLE `t`
DROP INDEX `c` ,
ADD UNIQUE INDEX `c` (`c`) USING BTREE ;

session A	session B
begin;
insert into t values(11,10,10);
1062 - Duplicate entry '10' for key 'c'	insert into t values(9,9,9);
	1205 - Lock wait timeout exceeded; try restarting transaction

-- A中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       109 | demo          | t3          | c          | RECORD    | S         | GRANTED     | 10        |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

-- B中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       109 | demo          | t3          | c          | RECORD    | S         | GRANTED     | 10        |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X,GAP     | WAITING     | 10        |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

根据上述加锁情况可发现，唯一键冲突的时候，在冲突的索引c=10上加了一个读锁。B中执行insert语句需要在c=10上加一个next-key lock，需要获取c=10的X锁，与A中c=10的S冲突，造成B堵塞。

根据官方唯一冲突加锁规则，非主键唯一冲突应该加的是间隙锁，B中插入时申请插入意向锁与间隙锁是同一间隙范围，被堵塞了，也解释的通，但和上述加锁对不上

insert唯一键冲突死锁

session A	session B	session C
begin;
insert into t values(6,6,6);
	insert into t values(7,6,6);
		insert into t values(8,6,6);
rollback;

rollback后，B、C死锁，如开启死锁检测，其中一个事务异常返回，为什么？

-- A中执行insert后加锁情况，只看了一个表级IX
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
1 row in set

/**
B中执行insert后加锁情况，申请到了c=6的写锁，等待c=6的读锁，为什么？
此时c=6不存在，X锁加锁成功，但由于c是唯一索引，需要去验证唯一性，A中插入未提交，等待读锁
**/
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | S         | WAITING     | 6         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
4 rows in set

/** 
C中执行insert后加锁情况，等待c=6的读锁
**/
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | S         | WAITING     | 6         |
| INNODB |       114 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       114 | demo          | t3          | c          | RECORD    | S         | WAITING     | 6         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

A回滚后，B、C都成功获取S锁，执行插入需要相互等待对方的S锁，进行死锁，是否可优化？

session A	session B	session C
begin;
insert into t values(6,6,6);
	insert into t values(7,6,6) on duplicate key update d=7;
	Query OK, 1 rows affected	insert into t values(7,6,6) on duplicate key update d=8;
rollback;		Query OK, 2 rows affected

-- A中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

-- B中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | WAITING     | 6         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

-- C中执行insert后加锁情况
mysql> select ENGINE,THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA 
from performance_schema.data_locks;
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| ENGINE | THREAD_ID | OBJECT_SCHEMA | OBJECT_NAME | INDEX_NAME | LOCK_TYPE | LOCK_MODE | LOCK_STATUS | LOCK_DATA |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+
| INNODB |       109 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | GRANTED     | 6         |
| INNODB |       108 | demo          | t3          | c          | RECORD    | X         | WAITING     | 6         |
| INNODB |       114 | demo          | t3          | NULL       | TABLE     | IX        | GRANTED     | NULL      |
| INNODB |       114 | demo          | t3          | PRIMARY    | RECORD    | X         | WAITING     | 7         |
| INNODB |       114 | demo          | t3          | PRIMARY    | RECORD    | X         | WAITING     | 7         |
+--------+-----------+---------------+-------------+------------+-----------+-----------+-------------+-----------+

insert into … on duplicate key update会给索引 c 上 (5,10] 加一个排他的 next-key lock（写锁），所以A回滚前B、C不会持有锁。回滚后，先抢到写锁的执行插入，提交后另一个事务执行更新

posted @ 2021-04-08 10:03 肆玖爺阅读(153) 评论(0) 收藏举报

刷新页面返回顶部

坐而论道，起而行之