MySql关于锁的一些总结

概述

锁在计算机中通常用于多进程或者多线程并发访问同一数据资源时保证数据的一致性。 在MYSQL是十分重要的一部分也是主要用于并发控制。在MYSQL中根据不同的维度（实现机制、算法、兼容性、锁粒度）定义了不同的锁。如下图：

下面逐一总结归纳。

锁粒度

通过锁定的资源或者粒度来区分可以定义为：表锁、页锁、行锁；在实际的应用中主要使用的是表锁以及行锁。

行锁

自动加锁(针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁）。

ps ：对于 UPDATE、DELETE 和 INSERT 语句， InnoDB 会自动给涉及数据集加排他锁；对于普通 SELECT 语句， InnoDB 不会加任何锁；

页锁

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间，并发度一般。

表锁

对整张表加锁。开销小，加锁快;不会出现死锁;锁定粒度大，发生锁冲突的概率最高，并发度最低。

总结

实现机制

悲观锁

总是假设最坏的情况，每次取数据时都认为其他线程会修改，所以都会加（悲观）锁。一旦一个事务给数据加锁 其他的事务只能在入口处等待，直到锁被释放（如行锁，表锁，读锁，写锁等）。

实现（使用）方式：

• 共享锁：SELECT ... LOCK IN SHARE MODE

• 排它锁：SELECT ... FOR UPDATE

乐观锁

每次读写数据的时候都认为别人不会修改该数据，所以不会上锁，但是在提交更新的时候会判断一下版本在此期间别人有没有去更新这个数据。需要业务手动实现，需自己维护一个版本字段。

如：要在table中更新id=1记录的a字段：

1. select * from table where id =1; // 比如查询到 version=1

2. update table set a=1,version=2 where id=1 and version = 1 // 更新的时候将之前查询到的version作为更新条件

兼容性

排它锁(X锁)

会一直锁住资源，直到其释放；其他事务无法对锁定的数据进行加锁。

加锁方式：select ... for update

共享锁(S锁)

共享锁又称读锁，是读取操作创建的锁。当有事务对数据加上改锁之后，其他事务仍然可以并发读取数据，但都不能对数据进行修改（获取数据上的排他锁），直到释放所有共享锁。

加锁方式：select ... share in mode

意向锁(表锁)

意向共享锁 （IS）：事务即将给表中的各个行设置共享锁，事务给数据行加 S 锁前必须获得该表的 IS 锁。 意向排他锁 （IX）：事务即将给表中的各个行设置排他锁，事务给数据行加 X 锁前必须获得该表 IX 锁。

注意：共享锁和排它锁是行锁，意向锁是表锁，意向锁是InnoDB自动加的，不需要用户干预。

兼容性比较

从上图可知意向锁与意向锁之间是相互兼容的，

参考链接：意向锁的作用

算法（行锁）

Record Locks

该锁为索引记录上的锁，如果表中没有定义索引，InnoDB 会默认为该表创建一个隐藏的聚簇索引，并使用该索引锁定记录。

Gap Locks

该锁会锁定一个范围，但是不括记录本身。（可以通过修改隔离级别为 READ COMMITTED 或者配置 innodb_locks_unsafe_for_binlog 参数为 ON） 。

Next-key Locks

该锁就是 Record Locks 和 Gap Locks 的组合（前开后闭），即锁定一个范围并且锁定该记录本身。

加锁规则（Repeatable Read）

以下来源于丁奇《My Sql45讲》：

原则 1：加锁的基本单位是 next-key lock。

原则 2：查找过程中访问到的对象才会加锁。

优化 1：索引上的等值查询，给唯一索引加锁的时候，next-key lock 退化为行锁。

优化 2：索引上的等值查询，向右遍历时且最后一个值不满足等值条件的时候，next-key lock 退化为间隙锁。

一个 bug：唯一索引上的范围查询会访问到不满足条件的第一个值为止。

ps: MySQL 后面的版本可能会改变加锁策略，所以这个规则只限于截止到现在的最新版本，即 5.x 系列 <=5.7.24，8.0 系列 <=8.0.13。

简单示例（未包含到所有情况）：

如下表(test):id为 主键，age为普通索引

即相关的索引Next-key 如下:

id: (-∞,1],(1,3],(3,7],(7,18],(18,+∞)

age: (-∞,10],(10,15],(15,18],(19,+∞)

1. 查询列没有索引

   sql：select * from test where sex = 1 for UPDATE ;

   由于没有索引会给整张表的所有数据行的加行锁；如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤，所以最终只有符合条件的数据会加锁，但是每条记录加锁的操作是不会省略的；

2. 唯一索引等值查询且索引值存在。

   sql：select * from test where id = 3 for UPDATE ;

   默认在(1,3]加Next-key Locks锁；唯一索引值存在，降为Record Locks只锁id=3这一行记录。

3. 唯一索引等值查询且索引值不存在。

   sql：select * from test where id = 5 for UPDATE;

   默认在(1,7]加Next-key Locks锁；唯一索引值不存在，降为Gap Locks锁住(3,7)。

4. 唯一索引范围查询。

   sql：select * from test where id > 5 for UPDATE;

   默认在(3,7]加Next-key Locks, 会继续在索引树遍历后续Next-key 直到最后一个值不满足id > 5的Next-key;所以 Next-key Locks锁加在(3,7],(7,18],(18,+∞);

   sql：select * from test where id < 5 for UPDATE;

   默认在(3,7]加Next-key Locks, 会继续在索引树遍历后续Next-key 直到最后一个值不满足id < 5的Next-key;所以 Next-key Locks锁加在(-∞,1],(1,3],(3,7]；

5. 非唯一索引等值查询且索引值存在。

   sql：select * from test where age= 10 for UPDATE;

   默认在(-∞,10]加Next-key Locks，然后会一直查询 后续Next-key 直到最后一个值不满足age= 10 的Next-key 区间，并且加上Next-key Lock 后续再 退化为Gap Locks 锁。即锁的范围为(-∞,10],(10,15)；且会将age=10的主键索引加上Record Locks（id=1、3）；

6. 非唯一索引等值查询且索引值不存在。

   sql：select * from test where age= 11 for UPDATE;

   因为age=11索引是在(10,15]这个Next-key上，所以默认在(10,15]加Next-key Locks，同样非唯一索引会继续在索引树遍历后续Next-key 直到最后一个值不满足age=10的Next-key ；这里也就是(10,15]，退化为Gap Locks 锁。即锁的范围为(10,15)

7. 非唯一索引范围查询且索引值存在。

   sql：select * from test where age > 11 for UPDATE;

   因为age=11索引是在(10,15]这个Next-key上，所以默认在(10,15]加Next-key Locks，同样非唯一索引会继续在索引树遍历后续Next-key 直到最后一个值不满足age>11的Next-key ；这里不存在上述条件的Next-key ，所以这些Next-key (10,15],(15,18],(19,+∞) 都会上锁。

8. 非唯一索引范围查询且索引值不存在。

   sql：select * from test where age > 20 for UPDATE;

   因为age>20索引是在(19,+∞)这个Next-key上，所以默认在(19,+∞)加Next-key Locks，同样非唯一索引会继续在索引树遍历后续Next-key 直到最后一个值不满足age>20的Next-key ；这里不存在上述条件的Next-key ，所以这些Next-key (19,+∞)都会上锁。

9. 其他case

其他

还有许多场景可以具体举例分析，如查询字段是否走覆盖索引、相同普通索引值不同的主键索引值等等；都会影响具体加锁范围；这些都需要case by case具体分析了。

最后

• 上述如有bad case 请指出，持续修正。

• 相关参考 MySQL实战45讲

本作品采用《CC 协议》，转载必须注明作者和本文链接