在 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
的插入就不再解释,与这个同理
为了方便读者演示,在上面建表语句已经全部贴出。相信读者只要仔细阅读文章,并且动手去做,就能够深刻的理解间隙锁