MySQL - 事务的启动 / 设置 / 锁 / 解锁——入门

The article upgrade at 2022.06.22

废话

本篇的名字简直可以起成《事务操作：从入门到放弃》。

力图解决：在MySQL 5.5 版本及更高版本时，使用事务的完整流程和细节记录，而无需面对互联网上纷繁零散的事务笔记。

实践 - 基础

首先，在你的空数据库上（譬如Test预留数据库），创建一个test表，有id和text(varchar 50)两个字段。

现在来模拟事务依次查询并占用 ID = 2/5 的行，且两个事务接近同时间执行的情况：

A端	B端
SET AUTOCOMMIT=0;	SET AUTOCOMMIT=0;
SELECT text FROM test WHERE id = 2 FOR UPDATE;	无
无	UPDATE test SET text='UioYang' WHERE id = 5;
SELECT text FROM test WHERE id = 5 FOR UPDATE;	无
无	UPDATE test SET text='UioSun' WHERE id = 2;

当 A 事务进行 FOR UPDATE 查询时，导致 ID2 行已经被加锁，而 B 事务将 ID5 行通过 UPDATE 也加锁，由此，尽管两个事务都顺利执行了第一步，但它们需要的另一半数据行，已经被对方占用。

于是在任何事务进行下一步时，都会接收到下一步的行已被占用，从而导致的等待，但由于两者都在等待对方，就导致必将有一方需要放弃自己的事务。

这种错误状态被称为死锁，你可以通过解锁相关的内容，来 Kill it。MySQL 本身也会进行死锁检测（如果 innodb_deadlock_detect 配置开启），它将在超时后回滚某一个事务，从而保证另一个事务可进行，为此，事务可能要考虑重试。

常规的迷你逻辑中，很少会出现这种情况，尽可能将逻辑涉及的数据精简，且使用自动提交；
伪原子性业务越复杂，导致死锁的可能性就越高，所以倘若有必要，请在业务某个时刻，对所有后续需要的数据加额外锁——譬如用 Redis 预先锁死后续相关 ID。

这就是事务的基础演示，最后，通过ROLLBACK或COMMIT，你可以完成事务的结束。

实践 - 锁

在上一部分，你完成了一个事务的基础流程，启动、进行、并最终得到结果（或许是意外结果）。
至少我在上一部分结尾处，脑海中有两个问题：

1. 我听过事务的锁，它通过锁完成独享目标，并在完成修改后释放它的独享权，但我该如何设置它的级别？
2. 锁的阻塞时间为多久？我如何检测它？

当然，为了另一种思路的编程玩家，我也将在本节末尾放上当前支持锁的优缺比较。

---

行级锁，页级锁，表级锁。闻其名知其意，比较少见的是：页级锁，它锁定的是一组相邻数据。

而MySQL的不同引擎，对锁级别支持是不一样的，以最常用的InnoDB为代表，默认采用行级锁，也支持表级锁，但这是有条件的，只有在针对索引SQL操作时，才会使用行级锁，否则这个操作将采取表级锁。

表级锁锁定的数量最多，占据内存最多，但有在做内部处理时中，它的操作速度是相当快的，而且几乎不存在死锁问题，所以在中大型内部处理机制中，表级锁的应用场景大于行级锁。

行级锁又分为共享锁和独占锁（排它锁，翻译差异），允许读取的共享锁是默认锁，而独占锁是不允许读写的完全占有——废话。

共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读写。
另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

页级锁（间隙锁）

@gaoyulong 表示：间隙锁被吃啦？

对此我表示抱歉。

间隙锁是个很重要的锁，当对非唯一索引操作时，它会锁定一组索引，从而保证该区间的可用性。

倘若是唯一索引且做精确匹配时，则会直接走行锁。

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁。

InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对一个AutoID 100条数据的表做ID为102的查询，要是不使用间隙锁，如果其他事务插入了ID大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

本段内容源于：间隙锁（Next-Key锁） - xiaobluesky

在此基础上，如果在查询锁表时，对不存在ID进行Insert操作，将导致等待阻塞。

额外的锁

除了DB本身分类外，在框架层面，还有乐观锁与悲观锁之分。注意层面，这种锁属于应用程序设计的锁，而非数据库设计的锁。

以我最熟悉的Yii2框架为例。简述：

1. 乐观锁就是一个可对比序列号，但存在高频并发时的对比错位 BUG；
2. 悲观锁就是一个严谨可对比序列号，并提供解锁功能。事实上，由于悲观锁的使用复杂度，Yii2并没有提供悲观锁功能。

解锁

说完锁，我们肯定需要一个解锁机制，脑海里忽然蹦出冷段子：一人去买门锁，安好了才发现，这门只能从外面开，进去锁门就出不来了。

很冷吧。没有解锁机制的事务处理系统，是一个只能进，不能出的事务处理系统——死锁尽管会自动解锁，但反馈时间是一个很刚性的设置。

先说这个很刚的设置，如果你想修改它，可以去 my.ini 文件的innodb_lock_wait_timeout这一行，默认为 50sec 的等待时间。

应用层面的锁可以通过校对序列号来自行解锁，而MySQL层面的锁，可以通过information_scheme的PROCESSLIST表，来完成解锁——确认无法完成事务。

这里说一下PROCESSLIST表，当一个关闭自动提交的事务已经启动，另一个同类事务也启动，双方冲突后，在这个表内是存在冲突SQL Status，你可以自己去观察。

最后：无论解锁机制多么健全，死锁本身是代码逻辑引起的，不修正/优化代码逻辑，单纯的解锁机制不过是对系统的额外负担。

解决方案很简单：自己写一个简单的Log功能，将所有触发解锁机制的情况，记录在Log里，自行优化。

隔离

配合锁机制的就是隔离机制，它可以尽可能有效的设置：事务间的可见度。

1. 读取未提交（RU，Read Uncommitted）：最低隔离，问题是脏读（未被提交的UPDATE，仍然可被读取）。
2. 读取提交（RC，Read Committed）：语句提交以后即执行了COMMIT以后别的事务就能读到这个改变. 问题：不可重复读（同事务时，前后读取到不一致数据）。
3. 可重复读（RR，Repeatable Read）：在同一个事务里面先后执行同一个查询语句的时候,得到的结果是一样的，问题：幻读（并发事务同时处理同内容，并导致一方内容覆盖了另一方，令对方感觉出现了幻觉）。
4. 序列化（S，Serializable）：在这个级别下，所有的事务的完整性都被保留，意味着所有的事务都可以被序列化的执行，只有当两个事务之间没有任务冲突时，才能并发的执行。

四个级别中，高级隔离不会遇到比自己低级隔离的问题，但隔离级别越高，对并发的损失性越高。

MySQL默认采用RR级别。

题外

提到锁，就想到以前做过的秒杀后端，当时的处理机制很简单，时间戳 + 事务。

时光荏苒，现在回头看，忽然发现有一些改进的地方，一笔带过：秒杀最大数量 缓存对比 → 服务器端 微秒级时间戳 + 事务/悲观锁 插入 + 插入失败 缓存队列及二次插入尝试，这样已经能够解决极大程度的并发问题了。