MySQL 的锁.png
一、属性锁:Shared and Exclusive Locks
1.1 简介
shared locks 是共享锁,简称 S 锁,exclusive locks 是排它锁,简称 X 锁,它们既可以是表级锁,也可以是行级锁,在 MySQL 的 InnoDB 引擎中是行级锁,可以加在一行或者多行上,那么何时在一行上加锁,何时在多行上加锁,这需要根据索引情况而定
shared locks 允许持有某行 S 锁的事务读取(select)该行,exclusive locks 允许持有某行 X 锁的事务更新(update)和删除(delete)该行
InnoDB 支持通过特定的语句进行显式加锁:
显式加 X 锁:select ... for update
显式加 S 锁:select ... lock in share mode
1.2 S 锁和 X 锁的兼容性
共享锁和排它锁的兼容性列表如下表,该表表示:
如果事务 T1 获得了行 r 的 S 锁,另一个事务 T2 可以获取行 r 的 S 锁,但是不能获取行 r 的 X 锁。即 S 锁可以被多个事务共享,所以称为共享锁
如果事务 T1 获得了行 r 的 X 锁,另一个事务 T2 既不能获取行 r 的 S 锁,也不能获取行 r 的 X 锁,必须等待 T1 释放 X 锁,故称为排他锁
X
S
X
不兼容
不兼容
S
不兼容
兼容
1.3 S 锁和 X 锁的兼容性示例
1.3.1 创建测试表
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xid` int(11) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_xid`(`xid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test`(xid, name) VALUES (1, '1');
INSERT INTO `test`(xid, name) VALUES (5, '5');
INSERT INTO `test`(xid, name) VALUES (9, '9');
1.3.2 S 锁兼容性测试
分别打开两个会话(会话 A 和会话 B),在会话 A 中开启一个事务并执行:
select * from test where xid = 1 lock in share mode;
在会话 B 中开启一个事务并执行:
select * from test where xid = 1 lock in share mode;
select * from test where xid = 1 for update;
效果如下:
1.3.3 X 锁兼容性测试
分别打开两个会话(会话 A 和会话 B),在会话 A 中开启一个事务并执行:
select * from test where xid = 1 for update;
在会话 B 中开启一个事务并执行:
select * from test where xid = 1 lock in share mode;
select * from test where xid = 1 for update;
效果如下:
二、状态锁:Intention Locks
Intention Locks 称为意向锁,它是表级锁,顾名思义,它是用来锁定表的,与行级锁相对应
如果事务 T1 获取了一个表的 intention exclusive 锁(简称 IX 锁),相当于表级别的排它锁,那么事务 T2 就不能再获取表上的 S 和 X 锁了;
如果事务 T1 获取了一个表的 intention shared 锁(简称 IS 锁),那么事务 T2 可以获取表的 S 锁,但不能获取表的 X 锁,它与共享锁和排它锁的关系如下:
(1)一个事务获取一张表中某行的 S 锁之前,必须获取表的 IS 锁或者更强的锁(比如 IX);
(2)一个事务获取一张表中某行的 X 锁之前,必须获取表的 IX 锁;
表级锁的兼容性如下:
X
IX
S
IS
X
Conflict
Conflict
Conflict
Conflict
IX
Conflict
Compatible
Conflict
Compatible
S
Conflict
Conflict
Compatible
Compatible
IS
Conflict
Compatible
Compatible
Compatible
当前事务 T1 想要修改某张表的一些行,那么首先要获取该表的 IX 锁,然后在要修改的行上加上 X 锁,另一个事务 T2 也准备要修改该表的一些行,因为表中除了被当前事务 T1 加锁的行,其他行是可以修改的,所以 T2 可以获取该表的 IX 锁,然后在其他行添加 X 锁,但是如果要修改当前事务 T1 加锁的行就需要等待了
三、算法锁
InnoDB 有三种锁行算法:
Record Lock:单个行记录上的锁
Gap Lock:间隙锁,锁定一个范围,但不包括记录本身。GAP 锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况
Next-Key Lock:Record Lock 和 Gap Lock 的结合。锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题
3.1 for update 简介
for update 可以显式地为表中满足条件的行加 X 锁。当一个事务的操作未完成时候,其他事务可以读取该行,但不能更新或删除该行
使用场景:高并发并且对于数据的准确性很有要求的场景。例如涉及到金钱、库存等。一般这些操作都是很长一串并且是开启事务的。如果库存刚开始读的时候是 1,而立马另一个进程进行了 update 将库存更新为 0 了,而事务还没有结束,会将错的数据一直执行下去,就会有问题。所以需要 for upate 进行数据加锁防止高并发时候数据出错
InnoDB 行锁是通过给索引项加锁来实现的,如果没有索引,InnoDB 将通过隐藏的聚簇索引来对记录加锁
3.2 for update 中算法锁的使用
场景一:明确指定索引,并且有此记录,加 Next-Key Lock
select * from test where xid = 5 for update;
插入操作
此时锁住的范围除了 ② 之外,还会锁住下一个范围,即 ③,这就是所谓的 Next-Key Lock。因此不能在另一个会话中插入 xid 在 1~9 范围内的数据。xid = 1 的数据不能插入,但 xid = 9 的数据可以插入,这可能与 B-Tree 索引的顺序插入有关。我们可以看下图描述的聚簇索引(自增 id)和 xid 的索引结构图:
个人理解(仅供参考):xid = 1 的数据不能插入,因为主键索引 id 是自增的,因此在 id=2 这条记录之前,是不允许插入一条 xid=5 的记录,这样就破坏了主键索引 id 的有序性;xid = 9 的数据可以插入可能是因为 MySQL允许在 (id = 3, xid = 9) 的后面插入一条 (id = 4, xid = 9) 的数据,并不破坏主键索引的有序性
INSERT INTO `test`(xid, name) VALUES (1, '1');
INSERT INTO `test`(xid, name) VALUES (4, '4');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (9, '9');
INSERT INTO `test`(xid, name) VALUES (15, '15');
读取、更新和删除操作
事务 A 执行 select * from test where xid = 5 for update; 将 xid = 5 这一行锁定后,事务 B 可以执行 select,但不能执行 update 和 delete
select * from test where xid = 5;
update test set name = '5-1' where xid = 5;
delete from test where xid = 5;
事务 B 对其他行的操作(select、update 和 delete)则不受影响
场景二:使用范围条件而不是相等条件检索数据时,InnoDB 会给满足条件的索引行加锁,对于索引值在条件范围但不存在的行记录加 Gap Lock
select * from test where xid > 7 for update;
插入操作
此时针对索引 xid 使用的范围查找,会锁住 xid = 9 的行以及 ③、 ④ 。因此除了可以插入 xid = 3 的数据外,其余插入语句均不能执行
INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (8, '8');
INSERT INTO `test`(xid, name) VALUES (15, '15');
读取、更新和删除操作
事务 A 执行 select * from test where xid > 7 for update; 将 xid = 9 这一行锁定后,事务 B 可以执行 select,但不能执行 update 和 delete
select * from test where xid = 9;
update test set name = '9-1' where xid = 9;
delete from test where xid = 9;
事务 B 对其他不满足 xid > 7 的行的操作(select、update 和 delete)则不受影响
场景三:明确指定索引,若查无此记录,加 Gap Lock
select * from test where xid = 7 for update;
此时加锁区间是 ③ ,因此除了 xid = 6 和 xid = 8 无法插入外,其余均可执行
INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (8, '8');
INSERT INTO `test`(xid, name) VALUES (15, '15');
读取、更新和删除操作
Gap Lock 主要是为了防止其他事务在锁定范围内插入数据,不影响其他事务操作其他行数据
场景四:当查询的索引含有唯一属性(主键或唯一索引)的时候,Next-Key Lock 会进行优化,将其降级为 Record Lock,即仅锁住索引本身,不是范围
将 xid 改为 unique index:
ALTER TABLE `db_zll`.`test`
DROP INDEX `idx_xid`,
ADD UNIQUE INDEX `idx_xid`(`xid`) USING BTREE;
select * from test where xid = 5 for update;
事务 A 执行 select * from test where xid = 5 for update; 只锁住 xid = 5 这一行,因此事务 B 对 xid = 5 的行只能 select,事务 B 对其他行的操作不受影响
INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (5, '5');
INSERT INTO `test`(xid, name) VALUES (7, '7');
行锁失效场景:
未指定主键/索引,并且有此记录,表级锁
无主键/索引,表级锁
主键/索引不明确,表级锁,例如 where xid like 、where xid <> 等操作
3.3 for update 超时回滚
超时时间的参数:innodb_lock_wait_timeout ,默认是50秒
超时是否回滚参数:innodb_rollback_on_timeout 默认是OFF
默认情况下,InnoDB 存储引擎不会回滚超时引发的异常,除死锁外。当参数 innodb_rollback_on_timeout 设置成 ON 时,则可以回滚
3.4 for update 注意点
for update 仅适用于 InnoDB,并且必须开启事务,在 begin 与 commit 之间才生效
当开启一个事务进行 for update 的时候,另一个事务也有 for update 的时候会一直等待,直到第一个事务结束吗?
答:会的。除非第一个事务 commit 或者 rollback 或者断开连接,第二个事务会立马拿到锁进行后面操作。不过也可以设置锁等待超时参数innodb_lock_wait_timeout 来解决
如果没查到记录会加锁吗?
答:会的。有主键/索引产生间隙锁,无主键/索引产生表锁表级锁
for update 和 for update nowait 区别(前者阻塞其他事务,后者拒绝其他事务)
for update 锁住表或者锁住行,只允许当前事务进行操作(读写),其他事务被阻塞,直到当前事务提交或者回滚,被阻塞的事务自动执行 for update nowait 锁住表或者锁住行,只允许当前事务进行操作(读写),其他事务被拒绝,事务占据的 statement 连接也会被断开
行锁分析
show status like 'innodb_row_lock%';
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.06 sec)
Innodb_row_lock_current_waits:当前正在等待锁定的数量
Innodb_row_lock_time:从系统启动到现在锁定的时长
Innodb_row_lock_time_avg:每次等待锁所花平均时间
Innodb_row_lock_time_max:从系统启动到现在锁等待最长的一次所花的时间
Innodb_row_lock_waits:系统启动后到现在总共等待锁的次数