mysql各种锁机制

2022/1/3 2:08:23

编程Tag： mysql 机制 查询 索引 id 事务 记录 age 各种

本文主要是介绍mysql各种锁机制，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

生成间隙(gap)锁、临键(next-key)锁的前提条件 是在 RR 隔离级别下

先给出结论:
当Mysql的事务隔离级别是 REPEATABLE READ

  1. 当使用唯一索引来等值查询的语句时, 如果这行数据存在，不产生间隙锁，而是记录锁。
  2. 当使用唯一索引来等值查询的语句时, 如果这行数据不存在，会产生间隙锁。
  3. 当使用唯一索引来范围查询的语句时，对于满足查询条件但不存在的数据产生间隙(gap)锁，如果查询存在的记录就会产生记录锁，加在一起就是临键锁(next-key)锁。
  4. 当使用普通索引不管是锁住单条，还是多条记录，都会产生间隙锁；
  5. 在没有索引上不管是锁住单条，还是多条记录，都会产生表锁；

间隙锁会封锁该条记录相邻两个键之间的空白区域，防止其它事务在这个区域内插入、修改、删除数据，这是为了防止出现 幻读 现象；

间隙的范围？

根据检索条件向下寻找最靠近检索条件的记录值A作为左区间，向上寻找最靠近检索条件的记录值B作为右区间，即锁定的间隙为（A，B] 左开右闭。

常用的查看MySQL锁语句

#查看正在被锁定的的表
show OPEN TABLES where In_use > 0;
# 查看进程号
show processlist;
#查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 
#查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
#查看系统事务隔离级别
select @@global.tx_isolation;
#查看当前事务
select @@tx_isolation;
#设置全局事务隔离级别为RC
set global transaction isolation level repeatable read;
#设置当前事务隔离级别为RC
set session transaction isolation level read committed;

数据和环境准备

测试环境：

MySQL版本5.7.27
事务隔离级别：Repeatable Read

CREATE TABLE `t` (
  `id` int NOT NULL AUTO_INCREMENT COMMENT '主键',
  `age` int NOT NULL COMMENT '年龄',
  `mobile` int DEFAULT NULL COMMENT '手机号',
  `name` varchar(8)  DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

id为主键(唯一索引)、age是普通索引、mobile没有加索引

插入数据如下：

id (主键)age(普通索引)mobile（无索引）name
1118142219401Andew1
4418142219404Andew4
7718142219407Andew7

在进行测试之前，我们先来看看t表中存在的隐藏间隙：
在这里插入图片描述

(-∞, 1]
(1, 4]
(4, 7]
(7, +supernum]

（其中supernum是数据库维护的最大的值。为了保证间隙锁都是左开右闭原则。）

关闭事务默认提交

打开mysql 的连接客户端navicate ,每打开一个查询窗口就是一个事务

mysql> SHOW VARIABLES LIKE 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set (0.00 sec)

结果显示，autocommit 的值是 ON，表示系统开启自动提交模式。

在 MySQL 中，可以使用 SET autocommit 语句设置事务的自动提交模式，语法格式如下：

SET autocommit = 0|1|ON|OFF

唯一索引示例：

1. 等值查询且数据存在示例

在这里插入图片描述

步骤事务1事务2
1beginbegin
2select * from t where id=4 for update;-
3select * from t where id=4 for update;
4commit阻塞

事务1等值查询id=4，因为id是主键，同时是等值查询存在该记录,所以只会在id=4这条记录上加记录锁，不会加间隙锁。

事务2 等值查询id=4，这个时候会产生行锁

#查看正在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

在这里插入图片描述
在事务2的MySQL控制台上会报错：

1205 - Lock wait timeout exceeded; try restarting transaction

事务字段解释：

lock_id:     锁的id
lock_trx_id: 事务的ID
lock_mode:   锁的模式
lock_type:   锁的类型，表锁还是行锁
lock_table:  要加锁的表
lock_index:  锁住的索引
lock_space:  锁住对象的 space_id
lock_page:   事务锁住页的数量，若是表锁，则为null
lock_rec:    被锁的记录号
lock_data:   事务锁定记录的主键值，若是表锁，则该值为null

2. 等值查询且数据不存在示例

步骤事务1事务2
1beginbegin
2select * from t where id=4 for update;-
3select * from t where id=5 for update;
4commit不阻塞

事务1等值查询id=4，因为id是主键，同时是等值查询存在该记录,所以只会在id=4这条记录上加记录锁，不会加间隙锁。
事务2 等值查询id=5，没有锁冲突，所以查询正常，不会堵塞。(如果事务B 等值查询id=4,因为事务A加了记录锁，所以会堵塞)，但是事务2等值查询id=5，因为查询记录不存在，所以无法加记录锁，但这里会存在一个(5,7]的间隙锁。

示例如下：

步骤事务1事务2
1beginbegin
2select * from t where id=5 for update;-
3-INSERT INTO t (id, age, mobile, name) VALUES (6, 6, 122, ‘安路6’);
4commit不阻塞

事务1 等值查询id=5，因为查询记录不存在，所以无法加记录锁，但这里会存在一个(5,7]的间隙锁。
事务2 插入一条id=6的数据，因为上面存在了(5,7]的间隙锁，所以会堵塞

查看当前mysql 事务锁
在这里插入图片描述
在事务2的MySQL控制台上会报错：

1205 - Lock wait timeout exceeded; try restarting transaction

3、范围查询示例

步骤事务1事务2
1beginbegin
2select * from t where id>4 for update;-
3-INSERT INTO t (id, age, mobile, name) VALUES (6, 6, 122, ‘安路6’);
4commit阻塞

事务1查询id大于4的记录，会生产一个(4,+supernum]的临键(next-key)锁
事务2 插入一个id=6、age=6的数据，因为age值在上面临键锁，范围内，所以也会堵塞。
如果 事务B 是更新 id=7 的记录，同样会堵塞
在这里插入图片描述

普通索引实例

1. 等值查询值

步骤事务1事务2
1beginbegin
2select * from t where age=4 for update;-
3-INSERT INTO t (id, age, mobile, name) VALUES (6, 6, 122, ‘安路6’);
4commit阻塞

事务1 等值查询age=4，因为age是普通索引，所以会产生临键(next-key)锁(1,4]和(4,7],左开右闭原则。
事务2 插入一个id=6、age=6的数据，因为age值在上面临键锁，范围内，所以也会堵塞

左开右闭原则

按照上面的例子，如果事务2插入一条 id=6，age=1 的数据会不会堵塞呢，因为按照左开右闭原则，上面的age=1是开的，所以正常应该是可以插入的。

但实际上你真是实践之后，你发现同样也会堵塞。

通过实践之后，会发现，所谓的左开右闭原则，跟主键id有关系。

上面的事务1 等值查询age=4，它的当前主键id=4，上一条记录主键id=1，下条记录主键id=7。

如果插入 id<1, age 在(1,7)范围内，是 左闭右开原则。即age=1能插入，age=7会堵塞。

如果插入 1<id<7,age 在(1,7)范围内，是 左闭右闭原则。即age=1会堵塞，age=7也会堵塞。

如果插入 id>7,age 在(1,7)范围内，是 左开右闭原则。即age=1会堵塞，age=7能插入

无索引实例

1.等值查询值

步骤事务1事务2
1beginbegin
2select * from t where mobile=‘18142219404’ for update;-
3-INSERT INTO t (id, age, mobile, name) VALUES (100, 100, ‘18142219500’, ‘安路8’);
4commit阻塞

事务1 等值查询 mobile = 18142219404，因为mobile是无索引的，所以这个for update，变成表级排他(X)锁。

事务2 因为事务A已经加了表级的排他锁，所以其它事务无法进行任何的增删改操作。

查看当前MySQL锁

这是表中所有行加锁的效果，而不是锁表，不过效果一样，表中数据是无法操作的
在这里插入图片描述

2.范围查询

步骤事务1事务2
1beginbegin
2select * from t where mobile=‘18142219404’ for update;-
3-INSERT INTO t (id, age, mobile, name) VALUES (100, 100, ‘18142219500’, ‘安路8’);
4commit阻塞

总结

根据唯一索引、非唯一索引和无索引，以及数据库中是否存在查询边界，进行测试。结果如下，其中边界5存在索引中，边界6不在。

在这里插入图片描述

结论：

  1. 凡是涉及范围锁，都会在行记录上加Next-Key Lock（X和GAP锁）。其中，GAP间隙锁，防止幻读现象（一个事务中后边读的行比前一次多）

  2. 小于时，唯一索引和非唯一索引加锁最大范围，都是索引中最接近边界的下一个索引，并且包含该索引（Next-Key），如索引5，和离边界6最近的索引9；

  3. 大于时，唯一索引和非唯一索引加锁最小范围，都是索引中最接近边界的前一个索引，但不包含该索引，如索引5，和离6最近的索引5；

如果插入的值超过索引中最大值，锁类型变为X锁，被锁的最大行为supremum pseudo-record，它具有大于所有索引的值，不是真正的索引记录。此时，锁的范围扩大到正无穷。

  1. 等于时，

① 唯一索引，在索引中存在该值，锁由Next-Key Lock降级为Record锁，只锁住该行；索引中不存在边界值，锁的范围为离边界值最近的两个索引，但不包含这两个索引，如5和9；

② 非唯一索引，无论索引中存在该边界值与否，都会找离边界值最近的两个索引（边界值在索引中，也会去前边一个索引，如5，会取3）

  1. 无索引情况下，对全表行加锁。

  2. InnoDB存储引擎中底层锁定的实际范围会根据插入值而变化，给用户感觉锁情况为上表。

  3. 如果删除范围锁中的上下边界索引（当然是未被锁定的索引），范围锁会动态地将锁的范围扩大到下一个索引位置。

  4. 在唯一索引或非唯一索引上加锁时，还会在对应聚集索引上的主键加锁，防止通过索引修改记录和通过主键修改记录冲突



这篇关于mysql各种锁机制的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程