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

摘要：行级锁，页级锁，表级锁。闻其名知其意，比较少见的是页级锁，它锁定的是一组相邻数据。排他锁允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的读写。意向排他锁事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的锁。

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

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

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

请开启两个MySQL操作端，分别依次键入：

注意你的查询提示栏，你能发现：在A端未提交之前，默认状态下，B端的UPDATE是没有反馈的——被挂起。等待一段时间后，你能收到关于Transaction失败的消息。

这种错误状态被称为死锁，你可以通过解锁相关的内容，来Kill it。

上述是很极端的情况，正常来说，事务是通过自动插入来完成，基本上可以避免死锁情况。

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

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

我听过事务的锁，它通过锁完成独享目标，并在完成修改后释放它的独享权，但我该如何设置它的级别？

锁的阻塞时间为多久？我如何检测它？

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

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

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

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

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

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

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

对此我表示抱歉，之前一直了解的都是页级锁（也就是间隙锁），偏偏页级锁在互联网上的信息又不够丰富，所以就没考虑到。

页级锁是个很重要的锁，它会锁定一组数据，但这个锁并不是那么好用（更多的考虑是安全性）。

对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。
InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对一个AutoID 100条数据的表做ID为102的查询，要是不使用间隙锁，如果其他事务插入了ID大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。
本段内容源于：间隙锁（Next-Key锁） - xiaobluesky

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

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

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

乐观锁就是一个可对比序列号，但存在高频并发时的对比错位 BUG；

悲观锁就是一个严谨可对比序列号，并提供解锁功能。事实上，由于悲观锁的使用复杂度（我没看出来），Yii 2并没有提供悲观锁功能。

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

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

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

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

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

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

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

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

读取未提交（RU，Read Uncommitted）：最低隔离，问题是脏读（未被提交的UPDATE，仍然可被读取）。

读取提交（RC，Read Committed）：语句提交以后即执行了COMMIT以后别的事务就能读到这个改变. 问题：不可重复读（同事务时，前后读取到不一致数据）。

可重复读（RR，Repeatable Read）：在同一个事务里面先后执行同一个查询语句的时候,得到的结果是一样的，问题：幻读（并发事务同时处理同内容，并导致一方内容覆盖了另一方，令对方感觉出现了幻觉）。

序列化（S，Serializable）：在这个级别下，所有的事务的完整性都被保留，意味着所有的事务都可以被序列化的执行，只有当两个事务之间没有任务冲突时，才能并发的执行。

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

MySQL默认采用RR级别。

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

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

如果这样都会出现重复插入问题，那按我目前的水准，在应用层面是解决不了了。