关于 mysql 的共享锁排它锁以及锁的实现方式行锁间隙锁 Next-Key Lock

甄情 2019-02-13 16:20:59

744

分类专栏：【数据库】 java 文章标签： mysql 间隙锁共享锁/排它锁 next-key lock mysql锁

最后发布:2019-02-13 16:20:59首次发布:2019-02-13 16:20:59

本文链接：https://blog.csdn.net/su20145104009/article/details/87076085

版权

在 mysql 中有事务四大基本特性，隔离级别，锁类型，b+ 树等各种需要我们学习的知识，这里简单分享一下 mysql 中的锁。
对于 mysql 乐观锁就不再赘述，通常是增加 last_ver 字段，通过 mvcc 实现的。
下面就讲讲 mysql 的悲观锁以及锁的实现方式

使用 mysql 数据库的都知道我们经常使用的数据库引擎有 MyISAM 和 InnoDB。

MyISAM

默认表类型，它是基于传统的 ISAM 类型，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的 select，insert时， MyISAM 比较适合。

InnoDB

支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update 和 insert，建议使用 InnoDB，特别是针对多个并发和QPS较高的情况。

环境

mysql版本：5.7.21
隔离级别：REPEATABLE-READ(RR)
创建测试表

Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

准备数据

mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
|  3 |     2 |
|  5 |     3 |
|  4 |     4 |
|  1 |   111 |
+----+-------+
5 rows in set (0.00 sec)

共享锁

共享锁又称读锁，即多个事务对同一数据共享一把锁，都能访问数据，但是只能读不能写。
关键语句 lock in share mode

以下所有的 session 隔离级别均为RR(REPEATABLE-READ)，下面不再赘述。

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.00 sec)

开启 sessionA:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id = 2 lock in share mode;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
+----+-------+
1 row in set (0.00 sec)

开启sessionB:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test where id = 2;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from test where id = 2 lock in share mode;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
+----+-------+
1 row in set (0.00 sec)
mysql> update test set score = 100 where id = 2 ;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> update test set score = 100 where id = 2 lock in share mode;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'lock in share mode' at line 1

会发现当我们执行 select 语句时，成功返回结果。
select 语句我写了两种，一种是简单查询语句，一种是加了共享锁的查询语句。
此时发现一个事务对数据加了共享锁以后，其它事务还能再对该事务加共享锁查询，但是不能加排它锁。

当我们执行 update 语句时发生了错误。

update test set score = 100 where id = 2 ; 语句时出现获得锁超时。
update test set score = 100 where id = 2 lock in share mode; 时出现了语法错误，原因是 mysql 会自动对 insert update 语句添加排它锁。

排它锁

排它锁又称写锁，当对数据上了排它锁之后，就不能再被其它事务加任何锁，包括共享锁和排它锁，获取排它锁的事务可以读取和修改数据
关键语句 lock in share mode

开启 sessionA:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id = 2 for update;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
+----+-------+
1 row in set (0.00 sec)

mysql>

对id 为 2 的数据加上排它锁。

开启 sessionB:

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id = 2 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test where id = 2 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test where id = 2;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
+----+-------+
1 row in set (0.00 sec)

mysql>

此时我们发现由于 sessionA 已经对 id=2 的数据加上了排它锁，此时我们再进行共享锁/排它锁的查询是失败的，但是对于简单查询(不加锁)是成功的。

update / insert / delete 自动加锁

当我们执行 update / insert / delete 语句时，mysql 会自动为该语句加上排它锁。

测试一个 update 语句，读者可以自行测试更多

开启 sessionA：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql> update test set score = 100 where id = 2;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

执行更新语句，不显示加任何锁

开启 sessionB

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update test set score = 99 where id = 2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test where id = 2;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
+----+-------+
1 row in set (0.00 sec)
mysql> select * from test where id = 2 lock in share mode;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>

update test set score = 99 where id = 2;不加任何锁，发现获取锁超时
select * from test where id = 2; 简单查询成功
select * from test where id = 2 lock in share mode; 加共享锁查询失败
证明 mysql 会为 update / insert / delete 自动加排它锁。

锁的实现方式

Record Lock：行锁，单个行记录上的锁
Gap Lock：间隙锁，锁定一个范围，但不包括记录本身。GAP 锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况
Next-Key Lock：Record Lock + Gap Lock，锁定一个范围，并且锁定记录本身。对于行的查询，都是采用该方法，主要目的是解决幻读的问题

在 RR 隔离级别下，唯一索引使用的是行锁，非唯一索引使用的是 Next-Key Lock 。既然 Next-Key Lock 包含了 Record Lock 和 Gap Lock，那么我们就了解下Next-Key Lock 。

mysql> show create table test \G;
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `score_ind` (`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
|  4 |     3 |
|  7 |     3 |
|  5 |     5 |
+----+-------+
4 rows in set (0.00 sec)

对于 test 表又两个字段 id 和 score。id 为主键，score 为辅助索引

表中数据如上所示

开启 sessionA：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where score = 3 for update;
+----+-------+
| id | score |
+----+-------+
|  4 |     3 |
|  7 |     3 |
+----+-------+
2 rows in set (0.00 sec)

mysql>

当在事务中执行完 select * from test where id = 3 for update; 语句后，由于我们设置的隔离级别为RR，score 为 3 的数据就被加上了 Next-Key Lock 锁，即行锁+间隙锁。

此时数据

id	score
4	3
7	3

加上行锁。那么间隙锁又加在哪里呢？
考虑到B+树的连续性，能够插入score 为 3 的数据只有在区间(1,3)，(3,3) ，(3,5)

那么这个意思就是 score 为 1 或者 5 的数据无法插入了吗？

下面开启 sessionB 做测试


mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test (score,id) values(1, 3);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (score,id) values(1, 1);
Query OK, 1 row affected (0.00 sec)

我们发现插入

id	score	status
3	1	失败
1	1	成功

为什么呢？两条 score 为1的数据，一个成功一个失败

继续往下看

mysql> insert into test (score,id) values(5, 4);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test (score,id) values(5, 6);
Query OK, 1 row affected (0.00 sec)

插入

id	score	status
4	5	失败
6	5	成功

也是一条成功一条失败。为什么呢？
细心的你肯定发现了规律，表中的数据为

mysql> select * from test;
+----+-------+
| id | score |
+----+-------+
|  2 |     1 |
|  4 |     3 |
|  7 |     3 |
|  5 |     5 |
+----+-------+

当我们在 sessionA 中执行 select * from test where score = 3 for update; 语句时，行锁+间隙锁会把(score,id) 分别为 [(1,2),(3,4)]，[(3,4),(3,7)]，[(3,7),(5,5)] 之间的数据全部锁住。范围之外的数据可以插入。
下面对刚刚 sessionB 的插入进行解释

mysql> insert into test (score,id) values(1, 3);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

数据 (1,3) 在 [(1,2),(3,4)] 范围之内无法插入

mysql> insert into test (score,id) values(1, 1);
Query OK, 1 row affected (0.00 sec)

数据(1,1) 在[(1,2),(3,4)] 的左边可以插入
关于 score 为 5 的插入就不再解释，与这个同理

为了方便读者演示，在上面建表语句已经全部贴出。相信读者只要仔细阅读文章，并且动手去做，就能够深刻的理解间隙锁

点赞 3
评论 2
分享
x
海报分享

扫一扫，分享海报
收藏
打赏

打赏

甄情

你的鼓励将是我创作的最大动力

C币余额

2C币 4C币 6C币 10C币 20C币 50C币

确定
举报
关注关注
一键三连
点赞Mark关注该博主, 随时了解TA的最新博文

已标记关键词清除标记

插入表情

还能输入1000个字符 “速评一下”

关于 mysql 的共享锁 排它锁以及锁的实现方式 行锁 间隙锁 Next-Key Lock

环境

共享锁

排它锁

update / insert / delete 自动加锁

锁的实现方式

关于 mysql 的共享锁排它锁以及锁的实现方式行锁间隙锁 Next-Key Lock