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

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

在 ​​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 transactionmysql> 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 transactionmysql> select * from test where id = 2 lock in share mode;ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transactionmysql> 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 transactionmysql> 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 transactionmysql>

​​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: testCreate 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=utf8mb41 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​​ 锁，即行锁+间隙锁。

此时数据

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

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

下面开启 ​​sessionB​​ 做测试

> 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 transactionmysql> insert into test (score,id) values(1, 1);Query OK, 1 row affected (0.00 sec)

我们发现插入

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

继续往下看

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

插入

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

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​​ 的插入就不再解释，与这个同理

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

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。