mysql innodb不同隔离级别对唯一索引和非唯一索引时的加锁情况
程序员文章站
2022-06-02 12:28:05
...
今天测试READ COMMITTED没有间隙锁卡住了，这里探究下innodb在不同隔离级别对唯一索引和非唯一索引的加锁情况。
参看
https://blog.csdn.net/qingsong3333/article/details/78024931
REPEATABLE READ
非唯一索引
创建测试环境
-- 建表
create table testUn (year int);
-- 插入数据
insert into testUn values(2000),(2005),(2007),(2010),(2012),(2017),(2019);
-- 建立非唯一索引
create index idx on testUn(year);
非唯一索引，范围查询
-- session A
set autocommit = 0;
select * from testUn where year between 2007 and 2010 for update;
-- session B
insert into testUn values(2004); -- 可以插入说明2015这条记录上没有next-key 锁
delete from testUn where year=2005; -- 可以删除说明2015这条记录上没有record 锁
insert into testUn values(2005); -- 阻塞; 说明有gap 锁
insert into testUn values(2006); -- 阻塞;
insert into testUn values(2007); -- 阻塞;
delete from testUn where year=2007; -- 说明;说明2007上有record锁，结合gap锁，组成了next-key锁
insert into testUn values(2008); -- 阻塞;
insert into testUn values(2009); -- 阻塞;
insert into testUn values(2010); -- 阻塞;
insert into testUn values(2011); -- 阻塞; 说明2010与2012的间隙有gap 锁
delete from testUn where year=2012; -- 阻塞，说明
锁情况：索引idx上有3个next-key锁（由于idx非clustered index，所以clustered index上也要加3个行锁）
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
TABLE LOCK table `test-env`.`testun` trx id 3621 lock mode IX
RECORD LOCKS space id 22 page no 5 n bits 80 index idx of table `test-env`.`testun` trx id 3621 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007d7; asc ;;
1: len 6; hex 000000000247; asc G;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000248; asc H;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007dc; asc ;;
1: len 6; hex 000000000249; asc I;;
RECORD LOCKS space id 22 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`testun` trx id 3621 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000247; asc G;;
1: len 6; hex 000000000e14; asc ;;
2: len 7; hex 81000000aa012e; asc .;;
3: len 4; hex 800007d7; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000248; asc H;;
1: len 6; hex 000000000e14; asc ;;
2: len 7; hex 81000000aa013d; asc =;;
3: len 4; hex 800007da; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000249; asc I;;
1: len 6; hex 000000000e14; asc ;;
2: len 7; hex 81000000aa014c; asc L;;
3: len 4; hex 800007dc; asc ;;
非唯一索引,唯一查询
-- session A
set autocommit = 0;
select * from testUn where year = 2010 for update;
-- session B
delete from testun where year=2007; -- 可以删除，说明2007这条记录上没有record 锁
insert into testun values(2007); -- 阻塞，说明有gap锁
insert into testun values(2008); -- 阻塞
insert into testun values(2009); -- 阻塞
insert into testun values(2010); -- 阻塞
delete from testun where year=2010 -- 阻塞，说明有record锁，结合gap锁，2010上有next-key锁
insert into testun values(2011); -- 阻塞，说明2010与2012的间隙有gap 锁
delete from testun where year=2012; -- 删除成功，说明2012上没有record 锁
即：2010上有个next-key锁，2010-2012有gap锁
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
TABLE LOCK table `test-env`.`testun` trx id 3667 lock mode IX
RECORD LOCKS space id 23 page no 5 n bits 80 index idx of table `test-env`.`testun` trx id 3667 lock_mode X
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000254; asc T;;
RECORD LOCKS space id 23 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`testun` trx id 3667 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000254; asc T;;
1: len 6; hex 000000000e42; asc B;;
2: len 7; hex 82000000bb013d; asc =;;
3: len 4; hex 800007da; asc ;;
RECORD LOCKS space id 23 page no 5 n bits 80 index idx of table `test-env`.`testun` trx id 3667 lock_mode X locks gap before rec
Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007e1; asc ;;
1: len 6; hex 000000000256; asc V;;
唯一索引
创建测试环境
-- 建表
create table testUni (year int);
-- 插入数据
insert into testUni values(2000),(2005),(2007),(2010),(2012),(2017),(2019);
-- 创建唯一索引
create unique index idx on testUni (year);
唯一索引，范围查询
-- session A
set autocommit=0;
start transaction;
select * from testuni where year between 2007 and 2010 for update;
-- session B
set autocommit=0;
start transaction;
delete from testuni where year=2005; -- 删除成功，说明2005上没有record锁
insert into testuni values(2006); -- 阻塞，说明有gap锁
delete from testuni where year=2007; -- 阻塞，说明有record锁，结合gap锁，说明2007为next-key锁
insert into testuni values(2008); -- 阻塞，说明有gap锁
insert into testuni values(2009); -- 阻塞，说明有gap锁
delete from testuni where year=2010; -- 阻塞，说明有record锁，结合gap锁，说明2010为next-key锁
insert into test1 values(2011); -- 阻塞，说明有gap锁
delete from testuni where year=2012; -- 阻塞，说明有record锁，结合gap锁，说明2012为next-key锁
insert into test1 values(2013); -- 成功，说明2012后，没有gap锁
有三个next-key锁，分别在2007/2010/2012上（由于idx非clustered index，所以clustered index上也要加3个行锁）
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
TABLE LOCK table `test-env`.`testuni` trx id 3747 lock mode IX
RECORD LOCKS space id 25 page no 5 n bits 80 index idx of table `test-env`.`testuni` trx id 3747 lock_mode X
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007d7; asc ;;
1: len 6; hex 000000000265; asc e;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000266; asc f;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007dc; asc ;;
1: len 6; hex 000000000267; asc g;;
RECORD LOCKS space id 25 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`testuni` trx id 3747 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000265; asc e;;
1: len 6; hex 000000000e96; asc ;;
2: len 7; hex 81000000dd012e; asc .;;
3: len 4; hex 800007d7; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000266; asc f;;
1: len 6; hex 000000000e96; asc ;;
2: len 7; hex 81000000dd013d; asc =;;
3: len 4; hex 800007da; asc ;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000267; asc g;;
1: len 6; hex 000000000e96; asc ;;
2: len 7; hex 81000000dd014c; asc L;;
3: len 4; hex 800007dc; asc ;;
唯一索引，唯一查询
-- session A
set autocommit=0;
start transaction;
select * from testuni where year=2010 for update;
-- session B
set autocommit=0;
start transaction;
insert into testuni values(2009); -- 成功，说明没有间隙锁
delete from testuni where year=2010; -- 失败，2010有record锁
insert into testuni values(2011); -- 成功，说明没有间隙锁
2010上有一个record锁
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
RECORD LOCKS space id 26 page no 5 n bits 80 index idx of table `test-env`.`testuni` trx id 3779 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000270; asc p;;
RECORD LOCKS space id 26 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`testuni` trx id 3779 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000270; asc p;;
1: len 6; hex 000000000eb6; asc ;;
2: len 7; hex 81000000f0013d; asc =;;
3: len 4; hex 800007da; asc ;;
Read Committed
非唯一索引
创建测试环境
-- 建表
create table test(year int);
-- 插入数据
insert into test values(2000),(2005),(2007),(2010),(2012),(2017),(2019);
-- 创建唯一索引
create index inx on test(year)
非唯一索引，范围查询
-- session A
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
set autocommit=0;
start transaction;
select * from test where year between 2007 and 2010 for update;
-- session B
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into test values(2006); -- 成功，说明没有gap锁
delete from test where year=2007; -- 阻塞，说明2007上有record锁
insert into test values(2008); -- 成功，说明没有gap锁
delete from test where year=2010; -- 阻塞，说明2010上有record锁
insert into test values(2011); -- 成功，说明没有gap锁
锁情况：2007/2010上有record锁。
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
---TRANSACTION 3897, ACTIVE 1146 sec
3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 49, OS thread handle 12680, query id 2706 localhost ::1 root
TABLE LOCK table `test-env`.`test` trx id 3897 lock mode IX
RECORD LOCKS space id 29 page no 5 n bits 80 index inx of table `test-env`.`test` trx id 3897 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007d7; asc ;;
1: len 6; hex 000000000288; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000289; asc ;;
RECORD LOCKS space id 29 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`test` trx id 3897 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000288; asc ;;
1: len 6; hex 000000000f2c; asc ,;;
2: len 7; hex 8100000087012e; asc .;;
3: len 4; hex 800007d7; asc ;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000289; asc ;;
1: len 6; hex 000000000f2c; asc ,;;
2: len 7; hex 8100000087013d; asc =;;
3: len 4; hex 800007da; asc ;;
非唯一索引，唯一查询
-- session A
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
set autocommit=0;
start transaction;
select * from test where year=2010 for update;
-- session B
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into test values(2008); -- 成功，说明没有gap锁
delete from test where year=2010; -- 阻塞，说明2010上有record锁
insert into test values(2011); -- 成功，说明没有gap锁
锁情况：2010上有record锁。
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
---TRANSACTION 3969, ACTIVE 178 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 49, OS thread handle 12680, query id 2826 localhost ::1 root
TABLE LOCK table `test-env`.`test` trx id 3969 lock mode IX
RECORD LOCKS space id 31 page no 5 n bits 80 index inx of table `test-env`.`test` trx id 3969 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 00000000029c; asc ;;
RECORD LOCKS space id 31 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`test` trx id 3969 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 00000000029c; asc ;;
1: len 6; hex 000000000f74; asc t;;
2: len 7; hex 820000009f013d; asc =;;
3: len 4; hex 800007da; asc ;;
唯一索引
创建测试环境
-- 建表
create table testun (year int);
-- 插入数据
insert into testun values(2000),(2005),(2007),(2010),(2012),(2017),(2019);
-- 创建唯一索引
create unique index idx on testun (year);
唯一索引，范围查询
-- session A
-- 修改事务隔离级别
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
set autocommit=0;
start transaction;
select * from testun where year between 2007 and 2010 for update;
-- session B
-- 修改事务隔离级别
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into testun values(2006); -- 成功，没有gap锁
delete from testun where year=2007; -- 阻塞，2007上有record锁
insert into testun value(2008); -- 成功，没有gap锁
insert into testun value(2009); -- 成功
delete from testun where year=2010; -- 阻塞，2010上有record锁
insert into testun value(2011); -- 成功，没有gap锁
delete from testun where year=2012; -- 成功，没有record锁
锁情况：2007/2010上有record锁。
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
---TRANSACTION 3816, ACTIVE 771 sec
3 lock struct(s), heap size 1136, 4 row lock(s)
MySQL thread id 45, OS thread handle 2460, query id 2514 localhost ::1 root
TABLE LOCK table `test-env`.`testun` trx id 3816 lock mode IX
RECORD LOCKS space id 27 page no 5 n bits 80 index idx of table `test-env`.`testun` trx id 3816 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007d7; asc ;;
1: len 6; hex 000000000276; asc v;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000277; asc w;;
RECORD LOCKS space id 27 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`testun` trx id 3816 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000276; asc v;;
1: len 6; hex 000000000ed6; asc ;;
2: len 7; hex 81000000f6012e; asc .;;
3: len 4; hex 800007d7; asc ;;
唯一索引，唯一查询
-- session A
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
set autocommit=0;
start transaction;
select * from testun where year = 2010 for update;
-- session B
set SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
insert into testun value(2009); -- 成功，说明没有gap锁
delete from testun where year=2010; -- 阻塞，说明2010上有record锁
insert into testun value(2011) -- 成功，说明没有gap锁
锁情况：2010上有record锁。
show engine innodb status命令查看真实情况
mysql> show engine innodb status \G;
---TRANSACTION 3866, ACTIVE 177 sec
1 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 44, OS thread handle 11708, query id 2666 localhost ::1 root
TABLE LOCK table `test-env`.`testun` trx id 3866 lock mode IX
---TRANSACTION 3865, ACTIVE 275 sec
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 49, OS thread handle 12680, query id 2648 localhost ::1 root
TABLE LOCK table `test-env`.`testun` trx id 3865 lock mode IX
RECORD LOCKS space id 28 page no 5 n bits 80 index idx of table `test-env`.`testun` trx id 3865 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 4; hex 800007da; asc ;;
1: len 6; hex 000000000281; asc ;;
RECORD LOCKS space id 28 page no 4 n bits 80 index GEN_CLUST_INDEX of table `test-env`.`testun` trx id 3865 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 6; hex 000000000281; asc ;;
1: len 6; hex 000000000f07; asc ;;
2: len 7; hex 8200000105013d; asc =;;
3: len 4; hex 800007da; asc ;;
总结
REPEATABLE READ中的范围查询时，非唯一索引和唯一索引加锁情况相同；唯一查询时，非唯一索引会在行上添加next-key锁，并在于下一个记录的间隙上加gap锁，唯一索引只会在行上添加record锁。
READ COMMITTED中的范围查询/唯一查询，加锁方式相同都是加上record锁，只是涉及的范围不同，范围查询是该范围的记录上都加上record锁，而唯一查询最多只会在一行数据上加上record锁。
注1：在show engine innodb status\G输出中:
如果是单纯的record lock，显示的是：locks rec but not gap
如果是单纯的gap lock， 显示的是：locks gap before rec
如果是gap+record，也就是next key lock，显示的是：lock_mode X <空>
上一篇： 数据库SQL优化总结