文末有惊喜哦 ！
1995年 ，MySQL 1.0发布，仅供内部使用！
开发多用户、数据库驱动的应用时，最大的一个难点是：一方面要最大程度地利用数据库的并发访问，另一方面还要确保每个用户能以一致性的方式读取和修改数据。
MVCC 并发控制主要是解决读写问题，而写写问题主要由所系统来解决！
什么是锁
锁是数据库系统区别于文件系统的一个关键特性。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。
虽然数据库系统做的越来越类似，但有多少种数据库，就可能有多少种锁的实现方法。
- 对于 MyISAM 引擎，其锁是表锁设计。并发情况下的读没有问题，但是并发插入时的性能就要差一些，若插入是在“底部”，MyISAM 存储引擎还是有一定的并发写入操作。
- 对于 Microsoft SQL Server 数据库，在 2005 版本之前其都是页锁。相对表锁的 MyISAM 引擎来说，并发性能有所提高。页锁容易实现，然而对于热点数据页的并发问题依然无能为力，到 2005 版本， Microsoft SQL Server 开始支持乐观并发和悲观并发，在乐观并发下开始支持行锁，但其实现方式与 InnoDB 存储引擎的实现方式完全不同。在 Microsoft SQL Server 数据库中，锁是一种稀有资源，锁越多开销越大，因此它会有锁升级。这种情况下，行锁会升级到表锁，这时并发性能又回到了以前。
- InnoDB 存储引擎锁的实现和 Oracle 数据库非常类似，提供一致性的非锁定读、行级锁支持。行级锁没有相关额外的开销，并可以同时得到并发性和一致性。
lock 与 latch
在数据库中，lock 与 latch 都可以被称为“锁”，但二者有着截然不同的定义，这里主要关注 lock。
- latch 一般称闩（shuan）锁，它是一种轻量级的锁，因为其要求锁定的时间必须非常短。若持续的时间长，则应用的性能会非常差。在 InnoDB 存储引擎中，latch 又可以分为 mutex（互斥量）和 rwlock（读写锁）。其目的是用来保证并发线程操作临界资源（即内存中的资源）的正确性，并且通常没有死锁检测机制。
- lock 的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般 lock 的对象仅在事务 commit 或 rollback 后进行释放（不同事务隔离级别释放的时间可能不同）。此外，lock 正如在大多数数据库中一样，有死锁机制。下表显示了 lock 和 latch 的不同。
| lock | latch | |
| 对象 | 事务 | 线程 |
| 保护 | 数据库内容 | 内存数据结构 |
| 持续时间 | 整个事务过程 | 临界资源 |
| 模式 | 行锁、表锁、意向锁 | 读写锁、互斥量 |
| 死锁 | 通过 waits-for graph、time out 等机制进行死锁检测与处理 | 无死锁检测与处理机制。仅通过应用程序加锁的顺序（lock leveling）保证无死锁的情况发生 |
| 存在于 | Lock Manager 的哈希表中 | 每个数据结构的对象中 |
对于 InnoDB 存储引擎中的 latch，可以通过命令：show engine innodb mutex，来进行查看，如下推所示。在 Debug 版本下，通过命令：show engine innodb mutex，可以看到 latch 的更更多信息。
mysql> show engine innodb mutex;
+--------+----------------------------+---------+
| Type | Name | Status |
+--------+----------------------------+---------+
| InnoDB | rwlock: fil0fil.cc:3293 | waits=2 |
| InnoDB | rwlock: hash0hash.cc:117 | waits=1 |
| InnoDB | sum rwlock: buf0buf.cc:791 | waits=3 |
+--------+----------------------------+---------+
3 rows in set (0.00 sec)列 Type 显示的总是 InnoBD，列 Name 显示的是 latch 的信息以及所在源码的位置（行数）。列 Status 显示的 waits 表示操作系统等待的次数，即当 spin lock 通过自旋还不能获得 latch 时，则会进入操作系统等待状态，等待被唤醒。
注：在 Debug 模式下，列 Status 除了显示 waits 还会显示其它很多信息，具体查看：MySQL 技术内幕 P251
相对于 latch 的查看，lock 信息就显得直观多了。用户可以通过命令：show engine innodb status 及 information_schema 架构下的表 INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS 来观察锁的信息。这将在下面详细介绍。
InnoDB 存储引擎中的锁
锁的类型
InnoDB 存储引擎实现了如下两种标准的行级锁：
- 共享锁（S Lock），允许事务读一行数据；
- 排他锁（X Lock），允许事务删除或更新一行数据；
如果一个事务 T1 已经获得了行 r 的共享锁，那么另外的事务 T2 可以立即获得行 r 的共享锁，因为读取并没有改变行 r 的数据，称这种情况为锁兼容（Lock Compatible）。但若有其他的事务 T3 想获得行 r 的排他锁，则其必须等待事务 T1、T2 释放行 r 上的共享锁——这种情况称为锁不兼容。下表显示了共享锁和排他锁的兼容性。
| X | S | |
| X | 不兼容 | 不兼容 |
| S | 不兼容 | 兼容 |
从上表可以发现 X 锁与任何锁都不兼容，而 S 锁仅和 S 锁兼容。需要特别注意的是 X 和 S 锁都是行锁，兼容是指对同一记录锁的兼容情况。
此外，InnoDB 存储引擎支持多粒度（granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁，InnoDB 存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度（fine granularity）上进行加锁，下面举例说明。
若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象进行上锁，那么首先需要对粗粒度的对象上锁。即，如果需要对页上的记录 r 进行上 X 锁，那么分别需要对数据库 A、表、页上意向锁 IX，最后对记录 r 上 X 锁，如下图所示。
若其中任何一部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，在对记录 r 加 X 锁之前，已经有事务对表 1 加了 S 表锁，那么表 1 上已存在 S 锁，之后事务需要对记录 r 在表 1 上加 IX，由于不兼容，所以该事务需要等待表锁操作的完成。
InnoDB 存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行被请求的锁类型。其支持两种意向锁：
- 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
- 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁
由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞全表扫以外的任何请求。表级意向锁和行级意向锁兼容性如下表所示。
| IS | IX | S | X | |
| IS | 兼容 | 兼容 | 兼容 | 不兼容 |
| IX | 兼容 | 兼容 | 不兼容 | 不兼容 |
| S | 兼容 | 不兼容 | 兼容 | 不兼容 |
| X | 不兼容 | 不兼容 | 不兼容 | 不兼容 |
一致性非锁定读
一致性的非锁定读（consistent nonlocking read），也称快照读，是指 InnoDB 存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据，如下图所示。之所以称其为非锁定读，因为不需要等待访问行上 X 锁的释放。
快照数据是指当前行的历史版本数据，该实现是通过 undo 段来完成。而 undo 用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。值得注意的是，并不是在每个事务隔离级别下都是采用非锁定的一致性读。即使都是采用非锁定的一致性读，但对于快照数据的定义也各不相同。
通过上图可以知道，一个行记录可能有不止一个快照数据，一般称这种技术为多版本并发控制（Multi Version Concurrency Control，MVCC）。
在事务隔离级别 READ COMMITTED 和 REPEATABLE READ 下，InnoDB 存储引擎使用非锁定的一致性读。然后对于快照数据的定义却不相同。在 READ COMMITTED 事务隔离级别下，非锁定一致性读总是读取被锁定行的最新快照数据（即在事务中每执行一次 select 语句都会创建新的一致性视图）。而在 REPEATABLE READ 事务隔离级别下，非锁定一致性读总是读取事务开始时的行数据版本（即在事务中只会在第一次执行 select 语句时，才会创建一致性视图）。
解析：RR 只在事务第一次执行 select 时生成 ReadView ，之后一直使用该 ReadView。而 RC 级别则在每次执行 select 时，都会生成一个 ReadView，所以在第二次执行 select 时，读取到了事务 2 对于 a 的修改值。
一致性锁定读
一致性非锁定读适用于对数据一致性要求不是很高的情况，比如在 READ COMMITTED 隔离级别下，即使行被锁定了（可以是执行了 select……for update），一致性非锁定读也可以读到该行记录的最新版本快照。而一致性锁定读适用于对数据一致性要求比较高的情况，此时需要对读操作进行加锁以保证数据逻辑的一致性。
InnoDB 存储引擎默认事务隔离级别为 REPEATABLE READ，即 select 操作默认使用一致性非锁定读。但在某些情况下，用户需要显示地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于 select 的只读操作。InnoDB 存储引擎对于 select 语句支持两种一致性的锁定读（locking read）操作：
- select……for update
- select……lock in share mode
注：select……lock in share mode 已经被 select……for share 替代，但仍兼容前者；
select……for update 对读取的行记录加一个 X 锁，其它事务不能对已锁定的行加任何锁。select……lock in share mode 对读取的行记录加一个 S 锁，其它事务可以向被锁定的行加 S 锁，但是如果加 X 锁，则会被阻塞。
此外，select……for update 和 select……lock in share mode 必须在一个事务中，当事务提交了，锁也就释放了。因此，在使用上述两个 select 语句时，务必加上 begin 或 start transaction，或者执行 set autocommit = 0。
MySQL 官方文档：MySQL :: MySQL 8.0 Reference Manual :: 15.7.2.4 Locking Reads
自增长与锁
在 InnoDB 存储引擎的内部结构中，对每个含有自增长值得表都有一个自增长计数器（auto-increment counter）。当对含有自增长计数器的表进行插入操作时，这个计数器会被初始化，并执行如下语句来得到计数器的值：
select max(auto_inc_col) from t for update;插入操作会依据这个自增长的计数器值加 1，然后赋予自增长列，这个实现方式称做 auto-inc locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的 SQL 语句后立即释放。虽然 auto-inc locking 从一定程度上提高了并发插入的效率，但其性能仍然较差，因为事务必须等待前一个插入的完成（虽然不用等待前一个事务的完成）。
从 MySQL 5.1.22 版本开始，InnoDB 存储引擎提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能。并从该版本开始，InnoDB 存储引擎提供了一个参数 innoDB_autoinc_lock_mode 来控制自增长的模式，该参数默认值为 1；
此外，还需特别注意的是 InnoDB 存储引擎中自增长的实现和 MyISAM 不同，MyISAM 存储引擎是表锁设计，自增长不用考虑并发插入问题。因此在 master 上用 InnoDB 存储引擎，在 slave 上用 MyISAM 存储引擎的 replication 架构下，用户必须考虑这种情况。
在 InnoDB 存储引擎中，自增长值的列必须是索引，且如果在联合索引中，必须是联合索引的第一个列。而 MyISAM 存储引擎没有这个限制，示例如下：
mysql> create table t(a int auto_increment, b int, key(b, a))engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> create table t(a int auto_increment, b int, key(b, a))engine=myisam;
Query OK, 0 rows affected (0.00 sec)锁的算法
根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表锁和行锁三类。下面对这三种锁进行详细介绍！
全局锁
顾名思义，全局锁就是对整个数据库实例加锁。MySQL 提供了一个加全局读锁的方法，命令是 Flush talbes with read lock（FTWRL）。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其它线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。
全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都 select 出来存成文本。
以前有一种做法，通过 FTWRL 确保不会有其他线程对数据库做更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。
但是整个库都处于只读，听上去就很危险：
- 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
- 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的 binlog，会导致主从延迟；
从上述分析结果来看，加全局锁不太好！
问题：备份为什么要加锁呢？
假设你现在要维护“极客时间”的购买系统，关注的是用户账户余额表和用户课程表。
现在发起一个逻辑备份。假设备份期间，有一个用户，他购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。
如果时间顺序上是先备份账户余额表（u_account），然后用户购买，最后备份用户课程表（u_course），会怎么样呢？可以看一下这个图：
可以看到，这个备份结果里，用户 A 的数据状态是”账户余额没扣，但是用户课程表里面已经多了一门课“。如果后面用这个备份来恢复数据的话，用户 A 就发现自己赚了。
作为用户可别觉得这样可真好啊，你可以试想以下：如果备份表的顺序反过来，先备份用户课程表再备份账户余额表，又可能会出现什么后果呢？
也就是说，不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。
官方自带的逻辑备份工具是 mysqldump，当 mysqldump 使用参数 -single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。
问题：有了 mysqldump 工具，为什么还需要 FTWRL 呢？
一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于 MyISAM 这种不支持事务的引擎，如果备份过程中有更新，总是只能取到最新的数据，那么就破坏了备份的一致性。这时，就需要使用 FTWRL 命令了。
所以，-single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。
问题：既然要全库只读，为什么不使用 set global readonly=true 的方式呢？
确实， readonly 方式也可以让全库进入只读状态，但我还是会建议你用 FTWRL 方式，主要有两个原因：
1）在有些系统中，readonly 的值会被用来做其它逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用；
2）在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到正常更新状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高；
注：
- 业务的更新不只是增删改数据（DML），还有可能是加字段等修改表结构的操作（DDL），不论是哪种方法，一个库被全局锁上以后，你要对里面任何一个表做加字段操作，都会 被锁住。
- 全局锁主要用在逻辑备份过程中，对于全部是 InnoDB 引擎的库，一般建议选择使用 -single-transaction（一致性视图）参数，对应用会更友好。
但是，即使没有被全局锁住，加字段也不是就能一帆风顺的，因为你还会碰到接下来要介绍的表级锁！
表级锁
MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（mete data lock，MDL）。
表锁的语法是 lock tables ... read/write。与 FTWRL 类似，可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。需要注意，lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
举个例子，如果在某个线程 A（或 session A）中执行 lock tables t1 read, t2 write; 这个语句，则其他线程（或 session B）写 t1、读 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。
在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。
另一类表级的锁是 MDL（metedata lock）。MDL 不需要显示使用，在访问一个表的时候会被自动加上。DML 的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果跟表结构对不上，肯定是不行的。
因此，在 MySQL 5.5 版本中引入了 MDL，当对一个表做增删改查操作的时候，自动加 MDL 读锁；当要对表做结构变更操作的时候，自动加 MDL 写锁。
- 读锁之间不互斥，因此你可以有多个线程（或 session）同时对一张表增删改查；
- 读写锁之间、写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程（或 session）要同时给一个表加字段，其中一个要等另一个执行完才能开始执行；
虽然 MDL 锁是系统默认会加的，但却是你不能忽略的一个机制。
问题：为什么给一个小表加个字段，导致整个库挂了？
给一个表加字段，或者修改字段，或者加索引，需要扫描全表的数据。在对大表操作的时候，你肯定会特别小心，以免对线上服务造成影响。而实际上，即使是小表，操作不慎也会出问题。假设表 t 是一个小表，我们来看一下下面的操作序列：
可以看到 session A 先启动，这时候会对表 t 加一个 MDL 读锁；由于 session B 需要的也是 MDL 读锁，因此可以正常执行。之后 session C 会被 blocked，是因为 session A 的 MDL 读锁还没有释放，而 session C 需要 MDL 写锁，因此只能被阻塞。
如果只有 session C 自己被阻塞还没有什么关系，但是之后所有要在表 t 上新申请 MDL 读锁的请求也会被 session C 阻塞。前面我们说了，所有对表的增删改查操作都需要先申请 MDL 读锁，就都被锁住，等于这个表现在完全不可读写了。
如果某个表上的查询语句频繁，而且客户端有重试机制，也就是说超时后会再起一个新 session 再请求的话，这个库的线程很快就会爆满。
你现在应该知道了，事务中 MDL 锁，在语句执行开始时申请，但是语句结束后并不会马上释放，而会等到整个事务提交后再释放。
问题：基于上面的分析，如何安全地给小表加字段？
首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。
问题：如果你要变更的表是一个热点表，虽然数据量不大，但是上面的请求很频繁，而你不得不加个字段，你该怎么做呢？
这个时候 kill 可能未必管用，因为新的请求马上就来了。比较理想的机制是，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 BDA 再通过重试命令重复这个过程。
MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。
alter table tbl_name nowait add column ... alter table tbl_name wait add column ...
注：表锁一般是在数据库引擎不支持行锁的时候才会被用到。如果你发现你的应用程序里有 lock tables 这样的语句，你需要追查以下，比较可能的情况是：
要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那么安排升级换引擎；
要么是你的引擎升级了，但是代码还没升级（此时，业务开发把 lock tables 和 unlock tables 改成 begin 和 commit，就能解决该问题）；
MDL 会直到事务提交才释放，在做表结构变更的时候，你一定要小心不要导致锁住线上查询和更新。
问题：备份都会在备库上执行，你在用 -single-transaction 方法做逻辑备份的过程中，如果主库的一个小表做了一个 DDL，比如给一个表上加了一列。这时候，从备库上会看到什么现象呢？
参考：06.全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
行级锁
InnoDB 存储引擎有 3 种行锁的算法，其分别是：
- Record Lock：单个行记录上的锁；
- GapLock：间隙锁，锁定一个范围，但不包含记录本身；
- Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身；
上述三种锁都是基于索引的；
Record Lock
Record Lock 总是会去锁住索引记录，如果 InnoDB 存储引擎表在建立的时候没有设置任何一个索引，那么这时 InnoDB 存储引擎会使用隐式的主健来进行锁定。
在 InnoDB 事务中，行锁是在需要的时候才加上，但并不是不需要了就立刻释放，而是等到事务结束时才释放（即两阶段锁协议）；
问题：知道了两阶段协议，对我们使用事务有什么帮助呢？
如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放，以提高并发度。（具体示例参考：MySQL 45 讲第 7 讲 ”行锁功与过“）
GapLock
间隙锁（Gap Locks）是InnoDB存储引擎中的一种锁机制，用于在多个事务之间保持一定的间隔，防止出现幻读（Phantom Read）的情况。
具体来说，间隙锁是用于锁定索引范围而不是实际存在的记录。它会在索引上的间隙（两个索引值之间的区域）上设置锁，阻止其他事务在该间隙内插入新记录，从而保证了查询的一致性。
举个例子来说明：
假设有一个名为 orders 的表，其中包含订单信息，包括订单号（order_id）和订单金额（amount）等字段。我们希望查询订单金额大于 100 的订单数量。
首先，我们执行以下查询语句：
SELECT COUNT(*) FROM orders WHERE amount > 100;在执行这个查询时，InnoDB 存储引擎会使用间隙锁来保护查询范围。它会在满足条件的订单金额大于 100 的记录上设置行锁，并在金额为100的记录之前和之后的索引间隙上设置间隙锁。
这样，如果另一个事务尝试在这个查询的范围内插入一个新的订单记录（比如金额为 110 的订单），间隙锁会阻止该插入操作（只要是金额大于 100 的订单都会被阻止），从而避免了幻读的问题。只有当查询事务释放了间隙锁后，其他事务才能在该间隙内插入新记录。
Next-Key Lock
Next-Key Lock 是结合了 GapLock 和 Record Lock 的一种锁定算法，在 Next-Key Lock 算法下，InnoDB 对于行的查询都是采用这种锁定算法。例如一个索引有 10，11，13，20 这四个值，那么该索引可能被 Next-Key Lock 的区间为：(-∞, 10]、(10, 11]、(11, 13]、(13, 20]、(20, +∞)。采用 Next-Key Lock 的锁定技术称为 Next-Key Lockling，其设计的目的是为了解决 Phantom Problem 问题。这种锁定技术锁定的不是单个值，而是一个范围，是谓词锁（predict lock）的一种改进。除了 next-keylocking，还有 previous-keylocking 技术。同样上述的索引 10、11、13、20，若采用 previous-key locking 技术，那么可锁定的区间为：(-∞, 10)、[10, 11)、[11, 13)、[13, 20)、(20, +∞)。
然而，当查询的索引是唯一索引时，InnoDB 存储引擎会对 Next-Key Lock 进行优化，将其降级为 Record Lock，即仅锁住当前记录本身，而不是范围，下面举例说明。
假设有表 t，其结构和数据如下：
create table t(a int primary key);
insert into t select 1;
insert into t select 2;
insert into t select 5;
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
| 5 |
+---+
3 rows in set (0.00 sec)接着执行 SQL 语句：
| 时间 | 会话 A | 会话 B |
| 1 | begin; | |
| 2 | select * from t where a=5 for update; | |
| 3 | begin; | |
| 4 | insert into t select 4; | |
| 5 | commit;（成功，无需等待） | |
| 6 | commit; |
在上述例子中，会话 A 首先对 a=5 进行 X 锁定，由于 a 是主键且唯一，所以锁定的仅是 a=5 这条记录，而不是 (2, 5] 这个范围，这样在会话 B 中插入值 4 不会阻塞。因为锁由 Next-Key Lock 算法降级为了 Record Lock，从而提高了应用的并发性。若是辅助索引，则情况完全不同，具体如下。
假设有表 z，其结构和数据如下：
create table z(a int, b int, primary key(a), key(b));
insert into z select 1, 1;
insert into z select 3, 1;
insert into z select 5, 3;
insert into z select 7, 6;
insert into z select 10, 8;
mysql> select * from z;
+----+------+
| a | b |
+----+------+
| 1 | 1 |
| 3 | 1 |
| 5 | 3 |
| 7 | 6 |
| 10 | 8 |
+----+------+
5 rows in set (0.00 sec)若在会话 A 中执行：select * from z where b=3 for update;
由于 b 是辅助索引，会话 A 对其加的是 Next-Key Lock 锁，且锁定范围是（1，3），同时要锁定 a=3 的记录，所以进一步优化为 (1，3]。特别需要注意的是，InnoDB 存储引擎还会对辅助索引下一个键值加上 GapLock，即还有一个辅助索引范围为（3，6）的锁（由于 b 不是唯一索引，所以还需要继续向后查找，找到的下一条记录是 b=6，此时 Next-Key Lock 会退化为 GapLock，所以锁定范围是（1，6））。因此，若在会话 B 中运行以下 SQL 语句，都会被阻塞：
select * from z where a = 5 lock in share mode; // 会话 A 中执行的 SQL 语句对聚集索引中 a=5 的值加了 X 锁，因此执行会被阻塞 insert into z select 4, 2; // 主键插入的 4 没问题，但插入的辅助索引值 2 在锁定范围 (1, 3) 中，因此执行会被阻塞 insert into z select 6, 5; // 插入的主键 6 没问题，但插入的辅助索引值 5 在锁定范围 (3, 6) 中，因此执行会被阻塞
而下面的 SQL 语句，不会被阻塞：
insert into z select 8, 6; insert into z select 2, 0; insert into z select 6, 7;从上述例子中可以看到，GapLock 的作用是为了阻止多个事务将记录插入到同一范围内，从而避免了 Phantom Problem 问题的产生。例如在上面的例子中，会话 A 中用户已经锁定了 b=3 的记录。若此时没有 GapLock 锁定（3，6），那么用户可以插人索引 b 列为 3 的记录，这会导致会话 A 中的用户再次执行同样查询时会返回不同的记录，即导致 PhantomProblem（幻读）问题的产生。
用户可以通过以下两种方式来显示地关闭 Gap Lock：
- 将事务的隔离级别设置为 READ COMMITTED；
- 将参数 innodb_locks_unsafe_for_binlog 设置为 1；
显示地关闭 Gap Lock 后，除了外键约束和唯一性检查依然需要的 Gap Lock，其余情况仅使用 Record Lock 进行锁定。但需要牢记的是，上述设置破坏了事务的隔离性，并且对于 replication，可能会导致主从数据的不一致。此外，从性能上来看 READ COMMITTED 也不会优于默认的事务隔离级别 READ REPEATABLE。
注：对于唯一键值的锁定，Next-Key Loclk 降级为 Record Lock 仅存在于查询所有的唯一索引列。若唯一索引由多个列组成（此时是指联合索引是唯一索引，而单个索引不一定是唯一索引的情况），而查询仅是查找多个唯一索引列中的其中一个，那么查询其实是 range 类型查询，而而不是 point 类型查询，故 InnoDB 存储引擎依然使用 Next-Key Lock 进行锁定。
解决 Phantom Problem
在默认的事务隔离级别下，即 REPEATABLE READ 下，InnoDB 存储引擎采用 Next-Key Locking 机制来避免 Phantom Problem。Phantom Problem 是指在同一事务下，连续执行两次同样的 SQL 语句可能导致不同的结果，第二次的 SQL 语句可能返回之前不存在的行（即幻读）。
假设存在表 t，其结构和数据如下：
create table t(a int);
insert into t select 1;
insert into t select 2;
insert into t select 5;
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
| 5 |
+---+
3 rows in set (0.00 sec)执行 select * from t where a > 2 for update；
| 时间 | 会话 A | 会话 B |
| 1 | SET SESSION tx_isolation='READ-COMMITTED'; | |
| 2 | begin; | |
| 3 | select * from t where a > 2 for update； ***************** 1.row ***************** a：5 | |
| 4 | begin; | |
| 5 | insert into t select 4; | |
| 6 | commit; | |
| 7 | select * from t where a > 2 for update； ***************** 1.row ***************** a：4 ***************** 2.row ***************** a：5 |
InnoDB 存储引擎采用 Next-Key Locking 机制来避免 Phantom Problem。对于上述的 SQL 语句 select * from table where id > 2 for update，其锁住的不仅仅是 a=5，而是对（2，+∞）这个范围加了 X 锁。因此任何对于这个范围的插入都不被允许，从而避免 Phantom Problem。
注：InnoDB 存储引擎默认的事务隔离级别是 REPEATABLE READ，在该隔离级别下，其采用 Next-Key Locking 的方式来加锁。而在事务隔离级别 READ COMMITTED 下，其仅采用 Record Lock 的方式加锁。
此外，用户可以通过 InnoDB 存储引擎的 Next-Key Locking 机制在应用层面实现唯一性检查。假设有如下 SQL 语句：
select * from table where col = xxx lock in share mode;在执行上述 SQL 语句时，若没有返回任何行，那么新插入的值一定是唯一的。
问题：如果在进行第一步 select ... lock in share mode 操作时，有多个事务并发操作，那么这种唯一性检查机制是否存在问题？
不会，因为这时会导致死锁，即只有一个事务的插入操作会成功，而其余的事务会抛出死锁的错误。
锁问题
通过锁定机制可以实现事务的隔离性要求,使得事务可以并发地工作。锁提高了并发，但是却会带来潜在的问题。不过好在因为事务隔离性的要求，锁只会带来三种问题，如果可以防止这三种情况的发生，那将不会产生并发异常常。
脏读
在理解脏读（Dirty Read）之前，需要理解脏数据的概念。但是脏数据和脏页完全是两种不同的概念，具体如下：
- 脏页：脏页是指在缓冲池中已经被修改的页，但是还没有刷新到磁盘中，即数据库实例内存中和磁盘中的页的数据不一致，当然在刷新到磁盘之前，日志都已经被写到重做日志文件；
- 脏数据：脏数据是指事务对缓冲池中行记录的修改，并且还没有提交；
对脏页的读取，是非常正常的，二者最终会达到一致性，即当脏页都刷回到磁盘。并且因为脏页的刷新是异步的，不影响数据库的可用性，因此可以带来性能的提高。
脏数据是指未提交的数据，如果读到了脏数据，即一个事务可以读到另一个事务中未提交的数据，则显然违反了数据库的隔离性。
脏读是指在不同事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。示例如下：
| 时间 | 会话 A | 会话 B |
| 1 | set @@tx_isolation='read-uncommitted'; | |
| 2 | set @@tx_isolation='read-uncommitted'; | |
| 3 | begin; | |
| 4 | select * from t\G； ***************** 1.row ***************** a：1 | |
| begin; | ||
| 5 | insert into t select 2; | |
| 6 | select * from t\G； ***************** 1.row ***************** a：1 ***************** 2.row ***************** a：2 |
上述示例，在会话 A 中读到了会话 B 中未提交的数据，即产生了脏读，违反了事务的隔离性。
注：胀读发生的前提是事务的隔离级别为 READ UNCOMMITTED，而目前绝大部分的数据库都至少设置成 READ COMMITTED。InnoDB 存储引擎默认的事务隔离级别为 READ
REPEATABLE,，Microsoft SQL Server 数据库为 READ COMNAITTED，Oracle 数据库同样也是READ COMMITTED。
事务隔离级别 READ UNCOMMITTED 看似毫无用处，但在一些比较特殊的情况下还是可以将事务的隔离级别设置为 READ UNCOMMITTED。例如 replication 环境中的 slave 节点，并且在该 slave上的查询并不需要特别精确的返回值。
不可重复读
不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该同一数据集合，并做了一些 DML 操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。
不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。具体示例如下：
| 时间 | 会话 A | 会话 B |
| 1 | set @@tx_isolation='read-committed'; | |
| 2 | set @@tx_isolation='read-committed'; | |
| 3 | begin; | |
| 4 | select * from t\G； ***************** 1.row ***************** a：1 | |
| begin; | ||
| 5 | insert into t select 2; | |
| commit; | ||
| 6 | select * from t\G； ***************** 1.row ***************** a：1 ***************** 2.row ***************** a：2 |
一般来说，不可重复读的问题是可以接受的，因为其读到的，是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商（如 Oracle、Microsoft SQL Server）将其数据库事务的默认隔离级别设置为 READ COMMITTED，在这种隔离级别下允许不可重复读的现象。
在 InnoDB 存储引擎中，通过使用 Next-Key Lock 算法来避免不可重复读的问题。在 MySQL 官方文档中将不可重复读的问题定义为 Phantom Problem，即幻像问题。
丢失更新
丢失更新是锁导致的另一个问题，简单来说就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据不一致。
具体示例如下：
1）事务 T1 将行记录 r 更新为 v1，但是事务 T1 并未提交；
2）与此同时，事务 T2 将行记录 r 更新为 v2，事务 T2 未提交；
3）事务 T1 提交；
4）事务 T2 提交；
从上述示例来看，当前数据库的任何隔离级别下，都不会导致数据库理论意义上的丢失更新问题。这是因为，即使是 READ UNCOMMITTED 事务隔离级别，对于行的 DML 操作，需要对行或其他粗粒度级别的对象加锁。因此在上述步骤 2）中，事务 T2 并不能对行记录 r 进行更新操作，其会被阻塞，直到事务 T1 提交。
虽然数据库能阻止丢失更新问题的产生，但在生产应用中还有另一个逻辑意义的丢失更新问题，而导致该问题的并不是数据库本身。
具体示例如下：
1）事务 T1 查询一行数据，放入本地内存，并显示给一个终端用户 User1；
2）事务 T2 也查询该行数据，并将取得的数据显示给终端用户 User2；
3）User1 修改这行记录，更新数据库并提交；
4）User2 修改这行记录，更新数据库并提交；
从上述示例来看，User1 的修改更新操作”丢失“了，而这可能会导致一个”恐怖“的结果。假设银行发生丢失更新现象，例如一个用户账号中有 10000 元人民币，他用两个网上银行的客户端分别进行转账操作。第一次转账 9000 人民币，因为网络和数据的关系，这时需要等待。但是这时用户操作另一个网上银行客户端，转账 1 元，如果最终两笔操作都成功了，用户的账号余款是 9999 人民币，第一次转的 9000 人民币并没有得到更新，但是在转账的另一个账号却会收到这 9000 元，这导致的结果就是钱变多，而账不平。也许有读者会说，不对，我的网银是绑定 USB Key 的，不会发生这种情况。是的，通过 USB Key 登录也许可以解决这个问题，但是更重要的是在数据库层解决这个问题，避免任何可能发生丢失更新的情况。
要避免丢失更新发生，需要让事务在这种情况下的操作变成串行化，而不是并行操作。即在上述四个步骤的 1）中，对用户读取的记录加上一个排他 X 锁。同样，在步骤 2）的操作过程中，用户同样也需要加一个排他 X 锁。通过这种方式，步骤 2）就必须等待一步骤 1）和步骤 3）完成，最后完成步骤 4）。
具体示例如下：
| Time | 会话 A | 会话 B |
| 1 | begin; | |
| 2 | select cash as @cash from account where user = pUser for update; | |
| begin; | ||
| 3 | select cash into @cash from account where user = pUser for update; # 等待 | |
| …… | …… | …… |
| m | update account set cash=@cash-9000 where user = pUser; | |
| m+1 | commit; | |
| m+2 | update account set cash=@cash-1 where user = pUser; | |
| m+3 | commit; |
问题：在上述例子中为什么不直接只用 update 语句，而是先用 select ... for update 操作？
直接使用 update 可以避免丢失更新问题。然而在实际应用中，应用程序可能需要首先检查用户的余额信息，查看是否可以进行转账操作以及现有余额，然后再进行最后的 update 操作。如果直接使用 update 可能难以发现丢失更新问题。
阻塞
因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞主要是为了确保事务可以并发正常地运行。
在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来控制等待的时间（默认是 50 秒），innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是 off，代表不回滚）。参数 innodb_lock_wait_timeout 是动态的，可以在 MySQL 数据库运行时进行调整，而 innodb_rollback_on_timeout 是静态的，不可以在启动时进行修改，具体如下：
注：在默认情况下 InnoDB 存储引擎不会回滚超时引发的错误异常，且 InnoDB 存储引擎在大部分情况下都不会对异常进行回滚。
假设在会话中执行了如下语句：
mysql> show create table t;
+-------+--------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+--------------------------------------------------------------------------------------------------------+
| t | CREATE TABLE `t` (
`a` int(11) DEFAULT NULL,
KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
+-------+--------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
-- 会话 A
mysql> select * from t;
+----+
| a |
+----+
| 1 |
| 2 |
| 4 |
+----+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from t where a < 4 for update;
+----+
| a |
+----+
| 1 |
| 2 |
+----+
2 rows in set (0.00 sec)在会话 A 中开启了一个事务，在 Next-Key Lock 算法下锁定了小于 4 的所有记录（也锁定了 4 这个记录本身），此时在会话 B 中执行如下语句：
-- 会话 B
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into t select 5;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 3;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> select * from t;
+-----+
| a |
+-----+
| 1 |
| 2 |
| 4 |
| 5 |
+------+
4 rows in set (0.00 sec)可以看到会话 B 中插入记录 5 是可以的，但是在插入记录 3 时，因为会话 A 中 Next-Key Lock 算法的关系，需要等待会话 A 中事务释放这个资源，所以等待产生了超时。但在超时后用户再进行 select 操作时发现，5 这个记录依然在。
这是因为这时会话 B 中的事务虽然抛出了异常，但是既没有进行 commit 操作，也没有进行 rollback。而这是十分危险的状态，因此用户必须判断是否需要 commit 还是 rollback，之后再进行下一步的操作。
注：Record Lock、GapLock、Next-Key Lock 三种行锁都是基于索引的，即若上述表 t 中 a 字段没有索引，在会话 A 提交之前，会话 B 是无法操作表 t 的；只有 a 字段有索引时，会话 A 中 Next-Key Lock 算法才会生效，Next-Key Lock 锁生效后，会话 B 中才能操作表 t（此处操作是指对会话 A 中对 select * from t where a < 4 for update; 语句不产生影响的操作）。从这也能看出 Next-Key Lock 算法提高了并发性能。
死锁
死锁的概念
死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。
问题：解决死锁的思路有哪些？
1）不要有等待，将任何的等待都转化为回滚，并且事务重新开始。这的确可以避免死锁问题的产生，然而在线上环境中，这可能导致并发性能下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为其很难被发现且浪费资源。
2）超时，当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来设置超时的时间。这种机制虽然简单，但是其仅通过超时后对事物进行回滚的方式来处理，或者是根据 FIFO 的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用 FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会多很多。
3）采用 wait_for graph（等待图）的方式来进行死锁检测（目前数据库使用较普遍的一种方式）。与超时相比，这是一种更为主动的死锁检测方式，InnoDB 存储引擎也采用这种方式。wait_for graph 要求数据库保存以下两种信息：
- 锁的信息链表；
- 事务等待链表；
通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。在 wait_for graph 中，事务为图中的节点，而在图中，事务 T1 指向 T2 边的定义为：
- 事务 T1 等待事务 T2 所占用的资源；
- 事务 T1 最终等待 T2 所占用的资源，也就是事务之间在等待相同的资源而事务 T1 发生在事务 T2 的后面；
下面来看一个例子，事务状态和锁的信息如下图所示：
在 Transaction Wait Lists 中可以看到共有 4 个事务 t1、t2、t3、t4，故在 wait-for graph 中应有 4 个节点。而事务 t2 对 row1 占用 x 锁，事务 t1 对 row2 占用 s 锁。事务 t1 需要等待事务 t2 中 row1 的资源，因此在 wait-for graph 中有条边从节点 t1 指向节点 t2。事务 t2 需要等待事务 t1、t4 所占用的r ow2 对象，故而有在节点 t2 到节点 t1、t4 的边。同样，存在节点 t3 到节点 t1、t2、t4 的边，因此最终的 wait-for graph 如下图所示。
通过上图可以发现存在回路 （t1，t2），因此存在死锁。通过上述的介绍，可以发现 wait-for graph 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在，则有死锁，通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。
注：在 InnoDB 1.2 版本之前，wait-for graph 都是采用递归方式实现，在 1.2 版本之后做了优化，采用深度优先算法实现。
死锁示例
如果程序是串行的，那么不可能发生死锁。死锁只存在于并发的情况，而数据库本身就是一个并发运行的程序，因此可能会发生死锁。下面介绍死锁的一种经典情况，即 A 等待 B，B 在等待 A，这种死锁问题被称为 AB-BA 死锁。
| Time | 会话 A | 会话 B |
| 1 | begin; | |
| 2 | select * from t where a =1 for undate; ***************** 1.row ***************** a：1 | |
| begin; | ||
| 3 | select * from t where a =2 for update; ***************** 1.row ***************** a：2 | |
| 4 | select * from t where a =2 for update; # 等待 | |
| 5 | select * from t where a =1 for undate; ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction |
在上述操作，会话 B 中的事务抛出了 1213 这个错误提示，即表表示事务发生了死锁。死锁的原因是会话 A 和 B 的资源在互相等待。大多数的死锁 InnoDB 存储引擎本身可以侦测到，不需要人为进行干预。但是在上面的例子中，在会话 B 中的事务抛出死锁异常后，会话A中马上得到了记录为 2 的这个资源，这其实是因为会话 B 中的事务发生了回滚，否则会话 A 中的事务是不可能得到该资源的。InnoDB 存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务，这点是需要注意的。
Oracle 数据库中产生死锁的常见原因是没有对外键添加索引，而 InnoDB 存储引擎会自动对其进行添加，因而能够很好地避免了这种情况的发生。而人为删除外键上的索引，MySQL 数据库会抛出一个异常：
// 创建主表
mysql> create table p(a int primary key);
// 创建子表 + 外键
mysql> create table c(b int, foreign key(b) references p(a));
mysql> show index from c\G
*************************** 1. row ***************************
Table: c
Non_unique: 1
Key_name: b
Seq_in_index: 1
Column_name: b
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: NULL
1 row in set (1.95 sec)
mysql> drop index b on c;
ERROR 1553 (HY000): Cannot drop index 'b': needed in a foreign key constraint通过上述例子可以看到，虽然在建立子表时指定了外键，但是 InnoDB 存储引擎会自动在外键列上建立了一个索引 b。并且，人为地删除这个列是不被允许的。
此外还存在另一种死锁，即当前事务持有了待插入记录的下一个记录的 X 锁，但是在等待队列中存在一个 S 锁的请求，则可能会发生死锁。来看一个例子，首先根据如下代码创建测试表 t，并导入一些数据：
mysql> create table t(a int primary key);
Query OK, 0 rows affected (0.07 sec)
mysql> insert into t values(1),(2),(4),(5);
Query OK, 4 rows affected (0.02 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from t;
+---+
| a |
+---+
| 1 |
| 2 |
| 4 |
| 5 |
+---+
4 rows in set (0.00 sec)表 t 仅有一个列 a，并插入 4 条记录。接着进行如下查询：
| Time | 会话 A | 会话 B |
| 1 | begin; | |
| 2 | begin; | |
| 3 | select * from t where a =4 for undate; | |
| 4 | select * from t where a -- 等待 | |
| 5 | insert into t values(3); -- ERROR 1213 (40001): Deadlock found when trying to get lock;try restarting transaction | |
| 6 | -- 事务获得锁，正常运行 |
可以看到，会话 A 中已经对记录 4 持有了 X 锁，但是会话 A 中插入记录 3 时会导致死锁发生。这个问题的产生是由于会话 B 中请求记录 4 的 S 锁而发生等待，但之前请求的锁对于主键值记录 1、2 都已经成功，若在事件点 5 能插入记录，那么会话 B 在获得记录 4 持有的 S 锁后，还需要向后获得记录 3 的记录，这样就显得有点不合理。因此 InnoDB 存储引擎在这里主动选择了死锁，而回滚的是 undo log 记录大的事务，这与 AB-BA 死锁的处理方式又有所不同。
获取更多数据库内核干货，请关注微信公众号：东周沉静的青蒿
扫一扫
3133
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
