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

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

使用 mysql 数据库的都知道我们经常使用的数据库引擎有 MyISAMInnoDB

  • MyISAM

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

  • InnoDB

支持事务安全的引擎,支持外键、行锁、事务是他的最大特点。如果有大量的updateinsert,建议使用 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>

id2 的数据加上排它锁。

开启 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 LockRecord Lock + Gap Lock,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题

RR 隔离级别下,唯一索引使用的是行锁,非唯一索引使用的 是 Next-Key Lock 。既然 Next-Key Lock 包含了 Record LockGap 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 表又两个字段 idscoreid 为主键,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; 语句后,由于我们设置的隔离级别为RRscore3 的数据就被加上了 Next-Key Lock 锁,即行锁+间隙锁。

此时数据

idscore
43
73

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

那么这个意思就是 score1 或者 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)

我们发现插入

idscorestatus
31失败
11成功

为什么呢?两条 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)


插入

idscorestatus
45失败
65成功

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

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)] 的左边可以插入
关于 score5 的插入就不再解释,与这个同理

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

©️2020 CSDN 皮肤主题: 猿与汪的秘密 设计师:上身试试 返回首页