Mysql中的各种锁详解

锁的基本概念

1、什么是锁？

在数据库中，锁主要用于解决并发访问时保证数据的一致性和有效性。

锁是计算机协调多个进程或线程并发访问某一资源的机制。

2、锁的区分

3.1、按照锁的粒度划分：行锁(Record Lock)、表锁(table lock)、页锁(page lock)。
3.2、按照锁的使用方式划分(悲观锁的一种实现)：共享锁(S Lock)、排它锁(X Lock)。
3.3、还有两种思想上的锁：悲观锁(PCC)、乐观锁(OCC)。
3.4、InnoDB中有几种行级锁类型：行锁（Record Lock）、间隙锁（Gap Lock）、后码锁（Next-key Lock）。

部分名次解释：

行锁：锁直接加在索引记录上面，锁住的是key。
间隙锁：锁定索引记录间隙，确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别而已的。
后码锁：行锁和间隙锁组合起来就叫Next-Key Lock。当InnoDB扫描索引记录的时候，会首先对索引记录加上行锁（Record Lock），再对索引记录两边的间隙加上间隙锁（Gap Lock）。加上间隙锁之后，其他事务就不能在这个间隙修改或者插入记录。
页锁：BDB存储引擎支持页级锁(不常用)。页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。

3、悲观锁的分类

InnoDB 存储引擎实现了如下两种标准的行级锁：

允许事务读一行数据。

共享锁也叫 读锁，允许持有锁的事务读取一行。即不能进行写操作来提供一致性读取。如果资源上没有写锁，事务可以立即获得读锁，多个事务可以在同时获得读锁，如果读锁没有释放，写锁不能被获取，写事务只能放入等待队列。
若事务 T 对数据对象 A 加上 S 锁，则事务 T 可以读 A 但不能修改 A。共享锁就是允许多个线程同时获取一个锁，一个锁可以同时被多个线程拥有

select ... lock in share mode;

排他锁（X Lock）（ for update）

允许事务删除或者更新一行数据。

排它锁也叫 写锁 ，允许持有锁的事务更新或者删除行。在一定的时间范围内，只能存在一个写锁。
写锁的优先级高于读锁。当一个资源上没有锁时，或者所有的锁请求都在等待队列中，
若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。这保证了其他事务在T释放A上的锁之前不能再读取和修改A。排它锁，也称作独占锁，一个锁在某一时刻只能被一个线程占有，其它线程必须等待锁被释放之后才可能获取到锁。

select ... for update

下面是锁的授予方式：

首先将锁授予写锁队列中等待的请求；
如果写锁队列中没有对资源的锁请求，那么将锁授予读锁队列中的第一个请求。

区别总结：

相同点：

都是属于悲观锁(PCC)
for update 与 lock in share mode 都是用于确保被选中的记录值不能被其它事务更新（上锁）

不同点：

lock in share mode 不会阻塞其它事务读取被锁定行记录的值，而 for update 会阻塞其他锁定性读对锁定行的读取（非锁定性读仍然可以读取这些记录，lock in share mode 和 for update 都是锁定性读）

举例说明：

这么说比较抽象，我们举个计数器的例子：在一条语句中读取一个值，然后在另一条语句中(UPDATE table SET num=num+1 WHERE id=x)更新这个值。使用 lock in share mode 的话可以允许两个事务读取相同的初始化值，所以执行两个事务之后最终计数器的值+1；而如果使用 for update 的话，会锁定第二个事务对记录值的读取直到第一个事务执行完成，这样计数器的最终结果就是+2了。

4、Lock 与 Latch

在数据库中，lock 与 latch 都可以被称之为 “锁”，但是两者的意义截然不同，本文主要关注 lock ：

**Latch: **latch一般称之为闩锁（轻量级的锁）。因为其要求锁定的时间必须非常短。若持续的时间比较长，则性能会非常差。在InnoDB引擎中，latch又可以分为 mutex（互斥锁）和 rwlock（读写锁）。其目的用于保证并发线程操作临界资源的正确性，并且通常没有死锁检测的机制。

Lock：lock的对象是事务，用于锁定数据库中的对象，例如：表、页、行。并且一般 lock 的对象仅在事务 commit 或者 rollback 后进行释放（不同隔离级别释放的时间可能不同）。此外，lock 正入其他大多数数据库意义，是有死锁机制的。

测试示例

1、悲观锁(PCC)测试：

测试注意事项：需要关闭 mysql 中的 autocommit 属性，因为 mysql 默认使用自动提交模式，也就是说当我们进行一个sql操作的时候，mysql会将这个操作当做一个事务并且自动提交这个操作。

-- 开始事务
begin; / begin work; / start transaction; (三者选一就可以)
-- 查询出商品信息(加一个锁)
select ... for update;
-- 提交事务（则会释放锁）
commit; / commit work;

1.1、间隙锁（Gap Lock）测试

间隙锁，是在索引的间隙之间加上锁，这是为什么 RR隔离级别 下能防止幻读的主要原因。

当我们用范围条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(NEXT-KEY)锁。

危害：

因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定值范围内的任何数据，在某些场景下这可能会针对性造成很大的危害。

数据表此时的数据如下：

mysql> select * from user;
+----+------------+------+
| id | username   | age  |
+----+------------+------+
|  1 | ZAAAA York |   20 |
|  2 | starsky    |   20 |
|  4 | will       |   10 |
|  5 | harry      |   10 |
|  7 | cara       |   30 |
|  8 | AAAA       |   40 |
+----+------------+------+
6 rows in set (0.00 sec)

注意表中的数据，ID包含【1，2，4，5，7，8】缺少【3，6】。此处 id 不连续

-- 事务1
select * from user id between 1 and 5 for update

-- 事务2
insert into user (id,username,age)values(3,'php',30)

可以看到，事务2在执行新增 id 为 2的数据时出现了所等待现象。说明id为2的数据被事务1进行的范围查询加锁锁住，其他事务需要等到事务1进行提交或者回滚之后才能继续操作事务1锁住的数据。

2、乐观锁(OCC)测试：

比较常见的是，我们有这么几种方式实现乐观锁。例如：CAS、MVCC、Redis分布式锁。

2.1、CAS

CAS（比较与交换，Compare and swap）是一种有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。实现非阻塞同步的方案称为“无锁编程算法”（ Non-blocking algorithm）。

2.1.1、使用数据库版本字段version实现

何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年龄',
  `version` int(11) unsigned zerofill NOT NULL COMMENT '数据版本',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 得到数据的version
mysql> SELECT id,`name`,version FROM test_table;

-- 根据数据version修改数据记录
mysql> UPDATE test_table SET `name`='xx', version=version+1  WHERE version = #{version};

所以，当你用 version 实现一个乐观锁的时候，可以不用事务，也不用锁表。

2.1.2、使用数据库时间戳字段timestamp实现

和第一种version差不多，同样是在需要乐观锁控制的table中增加一个字段，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。

CREATE TABLE `test_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '自增ID',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `timestamp` int(11) NOT NULL COMMENT '时间戳',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 得到数据的timestamp
mysql> SELECT id,`name`,timestamp FROM test_table;

-- 根据数据version修改数据记录
mysql> UPDATE test_table SET `name`='xx', timestamp=unix_timestamp(now())  WHERE timestamp = #{timestamp};

2.2、MVCC

维基百科: 多版本并发控制(Multiversion concurrency control， MCC 或 MVCC)，是数据库管理系统常用的一种并发控制，也用于程序设计语言实现事务内存。

关于MVCC请阅读这篇文章：https://blog.mailjob.net/posts/2327774433.html

2.3、分布式锁

比较常见的分布式锁的实现方式有：Redis分布式锁、Zookeeper分布式锁

3、不同锁的优缺点比较：

悲观锁

悲观锁的优点：

悲观锁实际上是采取了 “先取锁在访问” 的策略，为数据的处理安全提供了保证

悲观锁的不足：

在效率方面，由于额外的加锁机制产生了额外的开销，并且增加了死锁的机会。并且降低了并发性；当一个事物所以一行数据的时候，其他事物必须等待该事务提交之后，才能操作这行数据。

乐观锁

乐观锁的优点：

乐观并发控制相信事务之间的数据竞争 (data race) 的概率是比较小的，因此尽可能直接做下去，直到提交的时候才去锁定，所以不会产生任何锁和死锁。

乐观锁的不足：

但如果直接简单这么做，还是有可能会遇到不可预期的结果，例如两个事务都读取了数据库的某一行，经过修改以后写回数据库，这时就遇到了问题。

锁的常见问题：

1、锁升级现象

问题：

在不通过索引条件查询的时候，InnoDB使用的是表锁。InnoDB 升级为表锁后，届时并发性将大大折扣。

由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁。所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。

当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。另外，不论是使用主键索引、唯一索引、普通索引。InnoDB 都会使用行锁来对数据加锁。

解决问题：

如果 MySQL 认为全表扫效率更高。比如对一些很小的表，它就不会使用索引。这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时,，别忘了检查 SQL 的执行计划（explain查看），以确认是否真正使用了索引。

2、死锁

注意：MyISAM中是不会产生死锁的，因为MyISAM总是一次性获得所需的全部锁，要么全部满足，要么全部等待。而在InnoDB中，锁是逐步获得的，就造成了死锁的可能。

问题：

在InnoDB中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

当两个事务同时执行，一个锁住了主键索引，在等待其他相关索引。另一个锁定了非主键索引，在等待主键索引。这样就会发生死锁。

1.1、两个 session （窗口）的两条语句

在这里插入图片描述

首先session1获得 id=1的锁 session2获得id=5的锁，然后session想要获取id=5的锁等待，session2想要获取id=1的锁，也等待！（互相等待，则发生了死锁）

1.2、两个session的一条语句

在这里插入图片描述

这种情况需要我们了解数据的索引的检索顺序原理简单说下：普通索引上面保存了主键索引，当我们使用普通索引检索数据时，如果所需的信息不够，那么会继续遍历主键索引。

假设默认情况是RR隔离级别

针对session 1 从name索引出发，检索到的是（hdc,1）（hdc,6）不仅会加name索引上的记录X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10] 这个顺序是因为B+树结构的有序性。
而Session 2，从pubtime索引出发，[10,6],[100,1]均满足过滤条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10]，后[1,hdc,100]。
发现没有，跟Session 1的加锁顺序正好相反，如果两个Session恰好都持有了第一把锁，请求加第二把锁，死锁就发生了。

解决方案：

如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会。
在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率。
对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

参考文献

https://blog.csdn.net/horses/article/details/103324323

李斌

此平台用作学习交流日常分享

Mysql中的各种锁详解

锁的基本概念

1、什么是锁？

2、锁的区分

3、悲观锁的分类

排他锁（X Lock）（ for update）

区别总结：

4、Lock 与 Latch

测试示例

1、悲观锁(PCC)测试：

1.1、间隙锁（Gap Lock）测试

2、乐观锁(OCC)测试：

2.1、CAS

2.2、MVCC

2.3、分布式锁

3、不同锁的优缺点比较：

悲观锁

乐观锁

锁的常见问题：

1、锁升级现象

2、死锁

问题：

解决方案：

参考文献

锁的基本概念

1、什么是锁？

2、锁的区分

3、悲观锁的分类

共享锁（S Lock）（ lock in share mode）

排他锁（X Lock）（ for update）

区别总结：

4、Lock 与 Latch

测试示例

1、悲观锁(PCC)测试：

1.1、间隙锁（Gap Lock）测试

2、乐观锁(OCC)测试：

2.1、CAS

2.2、MVCC

2.3、分布式锁

3、不同锁的优缺点比较：

悲观锁

乐观锁

锁的常见问题：

1、锁升级现象

2、死锁

问题：

解决方案：

参考文献