mysql locking_MySQL 锁（InnoDB Locking）

最新推荐文章于 2022-07-16 09:21:12 发布

Aeroergy

最新推荐文章于 2022-07-16 09:21:12 发布

阅读量95

点赞数

文章标签： mysql locking

本文链接：https://blog.csdn.net/weixin_36256447/article/details/113431484

版权

b7e13a100faa

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 锁，故称为排他锁

不兼容

兼容

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;

效果如下：

b7e13a100faa

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;

效果如下：

b7e13a100faa

二、状态锁：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 锁；

表级锁的兼容性如下：

Conflict

Compatible

Conflict

Compatible

Conflict

Compatible

Conflict

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 中算法锁的使用

b7e13a100faa

场景一：明确指定索引，并且有此记录，加 Next-Key Lock

select * from test where xid = 5 for update;

b7e13a100faa

插入操作

此时锁住的范围除了 ② 之外，还会锁住下一个范围，即 ③，这就是所谓的 Next-Key Lock。因此不能在另一个会话中插入 xid 在 1~9 范围内的数据。xid = 1 的数据不能插入，但 xid = 9 的数据可以插入，这可能与 B-Tree 索引的顺序插入有关。我们可以看下图描述的聚簇索引(自增 id)和 xid 的索引结构图：

b7e13a100faa

个人理解(仅供参考)：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');

b7e13a100faa

读取、更新和删除操作

事务 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;

b7e13a100faa

插入操作

此时针对索引 xid 使用的范围查找，会锁住 xid = 9 的行以及 ③、 ④ 。因此除了可以插入 xid = 3 的数据外，其余插入语句均不能执行

b7e13a100faa

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');

b7e13a100faa

读取、更新和删除操作

事务 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;

b7e13a100faa

事务 B 对其他不满足 xid > 7 的行的操作(select、update 和 delete)则不受影响

场景三：明确指定索引，若查无此记录，加 Gap Lock

select * from test where xid = 7 for update;

b7e13a100faa

此时加锁区间是 ③ ，因此除了 xid = 6 和 xid = 8 无法插入外，其余均可执行

b7e13a100faa

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');

b7e13a100faa

读取、更新和删除操作

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');

b7e13a100faa

行锁失效场景：

未指定主键/索引，并且有此记录，表级锁

无主键/索引，表级锁

主键/索引不明确，表级锁，例如 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：系统启动后到现在总共等待锁的次数

Aeroergy

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql locking_MySQL 锁（InnoDB Locking）

MySQL 的锁.png一、属性锁：Shared and Exclusive Locks1.1 简介shared locks 是共享锁，简称 S 锁，exclusive locks 是排它锁，简称 X 锁，它们既可以是表级锁，也可以是行级锁，在 MySQL 的 InnoDB 引擎中是行级锁，可以加在一行或者多行上，那么何时在一行上加锁，何时在多行上加锁，这需要根据索引情况而定shared lock...
复制链接

扫一扫