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

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

542

分类专栏：【数据库】 java

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

本文链接：https://scx-white.blog.csdn.net/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 会自动对 select 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
评论
分享
x
海报分享

扫一扫，分享海报
收藏
手机看

分享到微信朋友圈
x

扫一扫，手机阅读
- 打赏
- 文章举报
打赏

甄情

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

5C币 10C币 20C币 50C币 100C币 200C币

确定
关注

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

环境

共享锁

排它锁

update / insert / delete 自动加锁

锁的实现方式

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