0%

关于MySQL各类锁的总结

想要用好 MySQL,索引与锁是一个绕不开的话题。最近一直在维护以前的业务系统,线上频繁报出数据库死锁的异常警告,为了排查以及规避死锁的问题,因此对 MySQL 的锁(Innodb引擎)做了一个比较深入学习,顺便加深自己对 MySQL 的理解程度。个人感觉 MySQL 中的锁还是非常的杂,官网对于锁的介绍也是和盘托出,并没有分门别类的罗列出来,下面分别从锁的模式与算法来分析。

锁的模式

MySQL 中有锁的模式这个概念,在 Innodb 中锁的模式有共享锁(S锁)、排它锁(X锁)两大类。除了这两大类,还有一个意向锁的概念,意向锁在锁模式中有意向共享锁(IS锁)、意向排它锁(IX锁)两类。

共享锁(行级别)

共享锁也叫读锁,对于同一个资源,大家都可以同时加上共享锁,可以理解成非独家的资源,大家都可以获取。共享锁与排它锁互斥,在 MySQL 中可以使用“lock in share mode”语法显式的加共享锁。

1
select * from test where id = 1 lock in share mode;

排它锁(行级别)

排它锁也叫写锁,排它锁不仅与共享锁互斥,排它锁与排它锁也互斥,在 MySQL 中可以使用“for update”语法显示的加排它锁。

1
select * from test where id = 1 for update;

意向锁(表级别)

InnoDB 存储引擎支持多粒度锁定,当数据库对行记录进行加锁时,首先会在粗粒度的表级别加上意向锁。意向锁的主要目的是为了提升表锁的加锁效率,假设没有意向锁,数据库在对表进行加锁时,需要扫描表中所有的记录,查看是否有行锁与当前要加的表锁冲突。意向锁之间是没有冲突的,意向锁与普通的表锁之间有冲突。

  • 意向共享锁

意向共享锁无法通过语法显式的操作,当加上行级别的共享锁时,意向共享锁就会存在,意向共享锁与排它锁(表级)冲突。

  • 意向排它锁

意向排它锁与上面的意向共享锁类似,它与共享锁(表级)、排它锁(表级)都冲突。

普通锁(表级别)

表级别的锁因为粒度更粗,所以在判断是否有冲突时不仅要考虑表级别下的其它锁,还要考虑表中的行锁。

  • 共享锁

除了对行可以加共享锁,也可以对表加共享锁,通过“lock table read”语法来对表加共享锁。表级别的共享锁不仅与表级别的排它锁冲突,而且与行级别的排它锁冲突。

1
lock table test read;
  • 排它锁

除了对行可以加排它锁,也可以对表加排它锁,通过“lock table write”语法来对表加共享锁。表级别的排它锁不仅与表级别的共享锁、排它锁冲突,而且与行级别的排它锁也冲突。

1
lock table test write;

锁的算法

锁的模式侧重锁的意图,锁的算法更加偏重锁的范围,例如下面列举的这些锁都是跟锁住的范围区间有关系。为了更好的演示锁住的区间,下面给出测试的表结构以及数据方便论证。

表结构:

1
2
3
4
5
6
7
8
CREATE TABLE `test_info` (
`id` int(11) NOT NULL,
`no` int(20) NOT NULL AUTO_INCREMENT,
`description` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_no` (`no`) USING BTREE,
KEY `idx_description` (`description`(191)) USING BTREE
);

数据:

1
2
3
4
5
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (1, 10, '100');
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (2, 20, '200');
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (5, 50, '500');
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (10, 100, '1000');
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (11, 110, '1000');

记录锁

记录锁作用在精确匹配的唯一索引上,它与间隙锁最大的差别在于间隙锁锁住的是不存在的区间。

记录锁 SQL 示例:

  • 主键索引加锁

事物一:

1
select * from test_info where id = 1 for update;

事物二:

1
select * from test_info where id = 1 for update;

事物二阻塞。

  • 唯一索引加锁

事物一:

1
select * from test_info where no = 10 for update;

事物二:

1
select * from test_info where no = 10 for update;

事物二阻塞

  • 普通索引加锁

事物一:

1
select * from test_info where description = 1000 for update;

事物二:

1
select * from test_info where id = 10 for update;

事物二阻塞,结束事物二,执行事物三

事物三:

1
select * from test_info where id = 11 for update;

事物三阻塞,结束事物三,执行事物四

事物四:

1
select * from test_info where id = 5 for update;

事物四未被阻塞。

普通索引作为二级索引被加锁时,对应的主键索引也是会被加锁的,所以可以看到 id 为 10、11 的记录都会被阻塞住。

间隙锁

间隙锁一般存在于 RR 的隔离级别,MySQL 在 RR 隔离级别下可以解决部分幻读的问题,依靠的就是间隙锁。间隙锁的上界、下界都是开区间,间隙锁存在的目的主要是为了阻塞插入操作,这样就不会存在相同的条件下查询的结果集不一致的情况。

间隙锁示例(RR隔离级别):

  • 普通索引加锁(未命中记录)

事物一:

1
select * from test_info where description = 300 for update;

事物二:

1
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (3, 21, '210');

事物二会阻塞,因为示例中的事物隔离级别为 RR,并且在事物一中没有查询到记录,为了避免幻读发生,所以将 (200,500) 的期间锁住了。上面的介绍中有提到间隙锁一般存在于 RR 的隔离级别中,所以上面的示例在 RC 隔离级别下执行事物二是不会被阻塞的。

  • 普通索引加锁(命中记录)

事物一:

1
select * from test_info where description = 200 for update;

事物二:

1
select * from test_info where description = 210 for update;

事物二阻塞,结束事物二,执行事物三

事物三:

1
select * from test_info where description = 190 for update;

事物三阻塞。

如果普通索引命中了记录,那么被锁定的区间不仅有 (200,500),还有 (100,200],所以普通索引的前后都会被锁住。

  • 唯一索引加锁

对于唯一索引如果命中记录,那么会加上记录锁(临间锁退化为记录锁,记录锁已演示这种场景),如果没有命中记录则会加上间隙锁。

事物一:

1
select * from test_info where no = 21 for update;

事物二:

1
INSERT INTO `test_info`(`id`, `no`, `description`) VALUES (3, 22, '210');

事物二阻塞

临键锁

临键锁是由记录锁与间隙锁组成的(区间为左开右闭),所以记录锁与间隙锁可以理解成特殊场景下的临键锁。Innodb 引擎行锁默认采用的就是临键锁的算法,只是在不同的场景下会退化成记录锁或者间隙锁。

特殊场景的锁

插入操作

插入操作加锁的操作是一个复杂的过程,并不会只存在一把锁。插入过程中首先会加插入意向锁,接着检查是否有唯一键冲突的问题,针对唯一性冲突还有可能会加上 S GAP(RC的隔离级别也会存在),当记录插入成功时还会对该条记录加上 X 的记录锁。

事物一:

1
2
3
delete from test_info where no = 50;

insert into `test_info`(`id`, `no`, `description`) values (5, 50, 500);

事物二:

1
insert into `test_info`(`id`, `no`, `description`) values (3, 30, 300);

事物二阻塞

如果觉得我的文章对您有用,请查看广告(Google Ads)或扫码。您的支持将鼓励我继续创作!