一文搞定mysql中的锁

 

目录

一 了解一致性非锁定读(快照读)和锁定读(当前读)

二 MySQL 锁有哪些

锁的分类

共享锁/排他锁（Shared and Exclusive Locks）

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

记录锁（Record Locks）

先看一个排他记录锁

再来一个共享记录锁

在非唯一索引上查询指定行产生间隙锁

间隙锁（Gap Locks）

范围查询建立间隙锁

查询不存在的记录建立间隙锁

再看一个联合索引上的间隙锁

临键锁（Next-key Locks）

 

插入意向锁（Insert Intention Locks）

自增锁（Auto-inc Locks）

三 不同隔离级别锁的不同

---

一 了解一致性非锁定读(快照读)和锁定读(当前读)

1. 一致性非锁定读(consistent nonlocking read)(快照读)

通常指的使用MVCC的读，之前已经写了一篇专门介绍MVCC的文章，看完肯定有收获，这里不再赘述了。

2. 锁定读(locking read)（当前读）

两种锁定读：

select...lock in share mode ||  select...for share(共享锁select，for share是mysql8.0提供的跟lock in share mode一样)
select...for update (排他锁select)
此外dml语句update , delete , insert，需要先查询表中的记录，此时也使用锁定读，加锁

锁定度（当前读）, 读取的是最新版本, 并且对读取的记录加锁, 阻塞其他事务同时改动相同记录，避免出现安全问题

如果你先查询数据，然后，在同一个事务内 插入/更新 相关数据，普通的SELECT语句是不能给你足够的保护的。其他事务可以 更新/删除 你刚刚查出的数据行。

 

二 MySQL 锁有哪些

以下例子都是针对的innodb,RR隔离级别

锁的分类

mysql中的锁分为四种（分类方式不属于官方纯属个人理解）：

1、粒度锁： 表锁、行锁；

2、算法锁：(基于行锁的算法)：记录锁，间隙锁，临键锁；

3、属性锁：共享锁，排它锁；

4、状态锁：(基于属性锁的状态)：意向共享锁，意向排它锁；

 

先来看一下我们用来测试的表结构，主键id另外name和score上的联合索引

create table test(
    id int primary key,
    name varchar(32),
    score int,
    key idx_name (name, score)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试表';

insert into test(id, name, score)
 values
(1, 'a', 60),
(3, 'c', 90),
(4, 'd', 80),
(6, 'f', 85);

共享锁/排他锁（Shared and Exclusive Locks）

讲真，它并不是一种锁的类型，而是一种锁的模式，每种锁都有shared或exclusive两种模式。

当我们说到共享锁（S锁）或排它锁（X锁）时，一般是指行上的共享锁或者行上的排它锁。需要注意的是，表锁也存在共享锁和排它锁，即表上的S锁和表上的X锁，表上的锁除了这两种之外，还包括下面将会提到的意向共享锁(Shard Intention Locks)即IS锁、意向排它锁(Exclusive Intention Locks)即IX锁。表上的锁，除了这四种之外，还有其他类型的锁，这些锁都是在访问表的元信息时会用到的（create table/alter table/drop table等）。

数据行r上共享锁(S锁)和排它锁(X锁)的兼容性如下：

假设T1持有数据行r上的S锁，则当T2请求r上的锁时：

1. T2请求r上的S锁，则，T2立即获得S锁。T1和T2同时都持有r上的S锁。

2. T2请求r上的X锁，则，T2无法获得X锁。T2必须要等待直到T1释放r上的S锁。

假设T1持有r上的X锁，则当T2请求r上的锁时：

T2请求r上的任何类型的锁时，T2都无法获得锁，此时，T2必须要等待直到T1释放r上的X锁。
 

总结一哈：

对于共享锁大家可能很好理解，就是多个事务只能读数据不能改数据。mysql InnoDB引擎默认的修改数据语句，update, delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select ...for update语句，加共享锁可以使用select ... lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select ...from...查询数据，因为普通查询没有任何锁机制。

意向共享锁/意向排他锁（Intention Shared and Exclusive Locks）

 Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table. 

意向锁（Intention Locks），是一种表锁，并且表明一个事务在某行数据上接下来要用什么锁。

• 意向共享锁（IS）：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
• 意向排他锁（IX）：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

为什么没有意向锁的话，表锁和行锁不能共存？
举个粟子（此时假设行锁和表锁能共存）： 事务A锁住表中的一行（写锁）。事务B锁住整个表（写锁）。

但你就会发现一个很明显的问题，事务A既然锁住了某一行，其他事务就不可能修改这一行。这与”事务B锁住整个表就能修改表中的任意一行“形成了冲突。所以，没有意向锁的时候，行锁与表锁共存就会存在问题！

Table-level lock type compatibility is summarized in the following matrix.（表级别锁兼容性，这里是表级别的X，S）

	X	IX	S	IS
X	Conflict	Conflict	Conflict	Conflict
IX	Conflict	Compatible	Conflict	Compatible
S	Conflict	Conflict	Compatible	Compatible
IS	Conflict	Compatible	Compatible	Compatible

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。 

Intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.

说明意向锁只会阻塞表级别的锁定，不会锁定行级锁定。比如某个事务想获取某个表的排他锁，他不用去逐行检查所有的行上面是否有锁，只需要看意向锁即可，因为加行锁之前要先加意向锁。

意向锁的主要目的就是展示有某些事务正在锁定行数据或者即将锁定行数据！网上很多文章会把大家带偏了还是看官方文档的好。

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html#innodb-intention-locks

记录锁（Record Locks）

A record lock is a lock on an index record. Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.
在索引上的锁。行锁只会锁定索引命中的行。

由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。前提条件是必须是命中索引并且索引是唯一索引。否则使用的是间隙锁

先看一个排他记录锁

事务T1

mysql> select * from test where id =1 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | a    |    60 |
+----+------+-------+
1 row in set (0.01 sec)

事务T2，总共3句sql， 只有中间的普通select可以成功，另外的两条都加锁失败。因为事务T1中的排他行锁锁定了id=1的行，事务2无法再加锁

mysql> update test set name ='aa' where id =1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test where id = 1 ;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | a    |    60 |
+----+------+-------+
1 row in set (0.01 sec)

mysql> select * from test where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

再来一个共享记录锁

事务T1加共享行锁

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

事务T2尝试加排它锁失败，但是可以加共享锁.

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

在非唯一索引上查询指定行产生间隙锁

事务T1给name='a'的记录加锁

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

此时事务t2， (name,score)值为(a,60)->(c,90)之间已经不能插入数据了但是(c,90)可以插入成功。不能理解为啥的可以先看后面间隙锁的例子再回过头来看这个。

mysql> insert into test values(10,'b',80);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(10,'c',80);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test values(10,'c',91);
Query OK, 1 row affected (0.01 sec)

mysql> insert into test values(11,'c',90);
Query OK, 1 row affected (0.00 sec)

记录锁特征：锁冲突概率低，并发性高，但是会有死锁的情况出现。

间隙锁（Gap Locks）

A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record。

索引记录之间的间隙上的锁，锁定尚未存在的记录(看到这里也就明白了innodb通过间隙锁解决了幻读的问题)，即索引记录之间的间隙。有shared或exclusive两种模式，但两种模式没有任何区别，二者等价。gap lock可以共存(co-exist)。事务T1持有某个间隙上的gap lock 并不能阻止 事务T2同时持有 同一个间隙上的gap lock，相当于没有独占模式。

在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待。

在普通索引（非唯一索引）记录上查找给定记录时，InnoDB会在到第一个不满足查询条件的记录之间加gap lock（从满足条件的记录开始向左向右两个gap），防止新的满足条件的记录插入。

范围查询建立间隙锁

事务T1

mysql> select * from test where id <6 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | a    |    60 |
|  3 | c    |    90 |
|  4 | d    |    80 |
+----+------+-------+
3 rows in set (0.01 sec)

T1会在id<6的区间上加gap锁，事务T2不管是想插入id =2, 5还是想在id=1的索引上加锁都会失败

mysql> insert into test(id,name,score) values (5,'a',10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into test(id,name,score) values (2,'a',10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

查询不存在的记录建立间隙锁

事务T1查询一个不存在的值

mysql> select * from test where id = 10 for update;
Empty set (0.00 sec)

这里会在id>6的区间加上间隙锁

事务T2尝试插入数据

mysql> insert into test(id,name,score) values (9,'a',10);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

再看一个联合索引上的间隙锁

事务T1间隙锁在联合索引上 

mysql> select * from test where name > 'c' and name < 'f' for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  4 | d    |    80 |
+----+------+-------+
1 row in set (0.01 sec)

事务T2 name,score ：c,50插入成功， c,100插入失败为啥呢，这个还是要看索引，加上顺序看（索引默认升序排列，name,score的联合索引的话就是先name升序后score升序）。

 

mysql> insert into test(id,name,score) values (10,'c',50);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test(id,name,score) values (11,'c',100);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

普通索引上查找指定记录的间隙锁

mysql> select * from test where name = 'c' for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  3 | c    |    90 |
+----+------+-------+
1 row in set (0.01 sec)

事务T2插入name为b的数据失败 

mysql> insert into test values(2,'b',40);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

 结论

      在普通索引列上，不管是何种查询，只要加锁，都会产生间隙锁，这跟唯一索引不一样；在普通索引（非唯一索引）记录上查找给定记录时，InnoDB会在到第一个不满足查询条件的记录之间加gap lock（从满足条件的记录开始向左向右两个gap），防止新的满足条件的记录插入。

普通索引数据间隙的分析，数据行是优先根据普通索引排序，再根据唯一索引排序，本文中使用了组合索引来说名问题，如果是单列的二级索引的话是现根据普通索引排序再根据唯一索引排序，思路类似。

 

临键锁（Next-key Locks）

A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.

这句话表明临键锁是行锁+间隙锁，即临键锁是是一个左开右闭的区间，比如（3，5]。
间隙锁只会出现在可重复读的事务隔离级别中，mysql5.7默认就是可重复读。间隙锁锁的是一个区间范围，查询命中索引但是没有匹配到相关记录时，锁定的是查询的这个区间范围，上述代码中，所锁定的区间就是 (1,3]这个区间,不包含1，但是包含3，并且不包含4，也就是说这里是一个左开右闭的区间。

事务T1，会在 3，4上加record锁，并且在（1，3）和（3，4）加gap锁, 当然（3，4）中间没啥，这里只是为了举个例子。

这个就是所谓的临键锁了，记录锁+间隙锁两种算法之和。

事务T1: 

mysql> select * from test where id >1 and id <=4 for update;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  3 | c    |    90 |
|  4 | d    |    80 |
+----+------+-------+
2 rows in set (0.04 sec)

事务T2： insert语句失败，间隙锁防止数据插入；select语句成功，因为查询的id=2数据不存在，在（1，3）的间隙加间隙锁成功，印证了前文提到的 gap lock可以共存(co-exist)

mysql> insert into test values(2,'d',80);
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from test where id =2 for update;
Empty set (0.00 sec)

 

插入意向锁（Insert Intention Locks）

对已有数据行的修改与删除，必须加强互斥锁X锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

它的玩法是：

多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。

事务T1, 在id（6，10）区间插入id为8数据 成功

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

mysql> select * from test;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | a    |    60 |
|  3 | c    |    90 |
|  4 | d    |    80 |
|  6 | f    |    85 |
| 10 | i    |    90 |
+----+------+-------+
5 rows in set (0.00 sec)

mysql> insert into test values (8,'b',80);
Query OK, 1 row affected (0.01 sec)

事务T2在id（6，10）区间插入id为7数据 成功

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

mysql> insert into test values(7,'b',80);
Query OK, 1 row affected (0.00 sec)

自增锁（Auto-inc Locks）

自增锁是一种特殊的表级别锁（table-level lock），专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

官网是这么说的

An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns. In the simplest case, if one transaction is inserting values into the table, any other transactions must wait to do their own inserts into that table, so that rows inserted by the first transaction receive consecutive primary key values.

与此同时，InnoDB提供了innodb_autoinc_lock_mode配置，可以调节与改变该锁的模式与行为

 

MySQL官网中还提到了一种预测锁，这种锁主要用于存储了空间数据的空间索引，本文暂不讨论

三 不同隔离级别锁的不同

主要注意RR级别有GAP锁，RC没有

Read Uncommitted

仅仅对改动数据的并发操作做限制。一个事务不能改动其它事务正在改动的数据，但能够读取到其它事务中尚未提交的改动。

Serializable

最强事务隔离机制，全部的事务必须串行化运行，即使有事务在对表进行查询，那么在此事务提交前，不论什么其它事务的改动都会被堵塞。这攻克了一切并发问题。但会造成大量的等待、堵塞甚至死锁。使系统性能降低

https://blog.csdn.net/iceman1952/article/details/85504278

https://blog.csdn.net/Saintyyu/article/details/91269087

https://blog.csdn.net/zcl_love_wx/article/details/82015281 

https://mp.weixin.qq.com/s?__biz=MjM5ODYxMDA5OQ==&mid=2651961455&idx=1&sn=4c26a836cff889ff749a1756df010e0e&chksm=bd2d0db38a5a84a53db91e97c7be6295185abffa5d7d1e88fd6b8e1abb3716ee9748b88858e2&scene=21#wechat_redirect 插入InnoDB自增列，居然是表锁？