简介： 无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制问题，MySQL 通过多版本并发控制和加锁实现并发控制。

无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制问题，MySQL 通过多版本并发控制和加锁实现并发控制。

多用户并发执行事务访问同一个数据库时，可能引发脏写、脏读、不可重复读、幻读等一致性问题。并发事务访问相同记录的情况，可分为以下三种。

1. 读-读

即多个并发事务相继读取相同的记录。这种情况是允许的，因为读取操作不会修改记录的内容。

1. 写-写

即多个并发事务相继对相同的记录进行修改。这种情况下会发生“脏写”现象，任何一种隔离级别都不允许这种现象发生。此时，需要通过锁机制，使这些未提交的并发事务排队依次执行。

当一个事务想对这条记录进行改动时，需要先加锁，加锁成功事务才能继续操作；若加锁失败事务需要等待。

1. 读-写或写-读

即一个事务在进行读操作，另一个事务在进行写操作。这种情况下可能会出现脏读、不可重复读、幻读的现象。MySQL 采用了以下两种解决方案。

(1) 读操作使用多版本并发控制(MVCC)，写操作进行加锁。

(2) 读操作和写操作都采用加锁的方式。

采用 MVCC 方式，读操作和写操作彼此并不冲突，性能更高。而采用加锁方式，读操作和写操作需要排队执行，从而影响性能。

一般情况下，可以采用 MVCC 来解决读操作和写操作并发执行的问题，但一些业务场景要求每次都必须读取记录的最新版，不允许读取记录的旧版本，这种情况下就只能采用加锁的方式了。

01、MVCC

MVCC(Multi-Version Concurrency Control，多版本并发控制)是一种用来解决读-写冲突的无锁并发控制机制，在数据库中用来控制并发执行的事务，使事务隔离进行。其本质是为了在进行读操作时代替加锁，减少加锁带来的负担。写操作使用记录的最新版本，读操作使用记录的历史版本，这样使不同事务的读-写、写-读操作可以并发执行，提高数据库并发性能。

MVCC 是通过保存数据在某个时间点的快照(Read View)来进行控制的。同一个数据记录可以拥有多个不同的版本，并通过聚簇索引记录和 undo 日志的 roll_pointer 属性串联成一个记录的版本链，通过生成的快照来判断记录的某个版本的可见性。在查询时通过添加相对应的约束条件，获取用户想要的对应版本的数据。

MVCC 只适用于 MySQL 隔离级别中的 READ COMMITTED(提交读)和 REPEATABLE READ(可重复读)级别。MVCC 其实就是使用这两种隔离级别的事务执行普通的读操作时访问记录的版本链的过程。

1. MVCC 与四种隔离级别的关系

1) READ UNCOMMITTED(未提交读)

由于存在脏读，即能读到未提交事务的数据行，所以不适用 MVCC。

2) SERIALIZABLE(序列化)

由于 InnoDB 会对所涉及的表加锁，并非行级锁，不存在行的版本控制问题，所以也不适用 MVCC。

3) READ COMMITTED(提交读)

每次读取数据时都生成一个快照，更新旧的快照，保证能读取到其他事务已经提交的内容。

4) REPEATABLE READ(可重复读)

只在第一次读取数据时生成一个快照，以后不会再更新，后续所有的读操作都是复用这个快照，可以保证每次读操作的一致性。

由此可见，虽然 REPEATABLE READ(可重复读)比 READ COMMITTED(提交读)隔离级别高，但是开销反而相对少，因为不用频繁更新快照。

2. 两种读取数据记录的方式

1) 当前读

读取当前数据的最新版本，而且读取到这个数据之后会对这个数据加锁，防止别的事务更改。在进行写操作时就需要进行“当前读”，读取数据记录的最新版本。

2) 快照读

其实就是读取 MVCC 中的快照，可以读取数据的所有版本信息，包括旧版本的信息。也就是说，“快照读”读到的不一定是数据的最新版本，有可能是之前的历史版本。

在 READ COMMITTED(提交读)隔离级别下，“快照读”和“当前读”结果一样，都是读取已提交的最新版本数据。

在 REPEATABLE READ(可重复读)隔离级别下，“当前读”是其他事务已经提交的最新版本数据，“快照读”是当前事务之前读到的版本，创建快照的时机决定了读到的版本。

在 MySQL 中，MVCC 是由记录中的三个隐式列、undo 日志和快照等来实现的。

02、锁机制

MySQL 支持不同的存储引擎，不同存储引擎的锁定机制也是不同的。例如：MyISAM 和 MEMORY 存储引擎只支持表级锁；InnoDB 存储引擎既支持行级锁，也支持表级锁，但默认情况下采用行级锁。

1. 锁的分类

1) 按锁粒度分类

理论上，每次只锁定当前操作的数据，会得到最大的并发度，但是管理锁是很耗费资源的事情。因此，数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度”的概念。

锁粒度，即锁定的数据范围，可以衡量管理锁的开销和并发性能的关系。锁粒度越大，锁定范围越大，管理锁的开销越小，并发性越差。按锁粒度从大到小，可分为以下三种锁。

(1) 表级锁(Table-level Locking)：也称表锁，用于锁定某个表。根据锁定的类型，其他用户不能向表中插入记录，甚至从中读数据也受到限制。表级锁有读锁和写锁两种类型。

表级锁的特点是开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

(2) 页级锁(Page-level Locking)：用于锁定数据记录所在的某个页，是 MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中并不常见。页是磁盘和内存之间交互的基本单位，页的大小一般为 16KB。

页级锁的特点是开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

(3) 行级锁(Row-level Locking)：也称行锁，用于锁定某行(即某条记录)。在这种情况下，只有线程使用的行是被锁定的，表中的其他行对于其他线程都是可用的，所以行级锁可以最大限度地支持并发处理。行级锁定并不是由 MySQL 提供的锁定机制，而是由存储引擎自己实现的，其中 InnoDB 的锁定机制就是行级锁定。

行级锁的特点是开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。行级锁有排他锁、共享锁和意向锁三种类型。

表级锁由数据库服务器实现，行级锁由存储引擎实现。数据锁定的范围越小，数据库的并发性越好。

2) 按对数据库操作的类型分类

事务可以对数据库进行读或写操作，按对数据库操作的类型分，可分为以下四种锁。

(1) 共享锁(Shared Lock)：也叫读锁，简称 S 锁。事务要读取一条记录时，需要先获取该记录的共享锁。读操作不会修改记录数据，多个读操作可以同时进行，不会互相影响，多个事务可以同时给同一记录加共享锁。有了共享锁，就不能获取排他锁了。

(2) 排他锁(Exclusive Lock)：也叫独占锁、写锁，简称 X 锁。事务要改动一条记录时，需要先获取该记录的排他锁。当前事务的写操作完成之前，它会阻断其他排他锁和共享锁。当前事务提交之后，排他锁会被释放。

(3) 意向共享锁(Intention Shared Lock)：简称 IS 锁。当事务准备在某条记录上添加共享锁时，需要先在表级别添加一个意向共享锁。

(4) 意向排他锁(Intention Exclusive Lock)：简称 IX 锁。当事务准备在某条记录上添加排他锁时，需要先在表级别添加一个意向排他锁。

提示

意向共享锁、意向排他锁是表级锁，MySQL 设计它们的目的是在之后加表级共享锁和排他锁时，可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。表级别的这四种锁的兼容关系如表 12-1 所示。

■ 表 12-1 表级别锁的兼容关系

image.png

2. 管理 InnoDB 存储引擎中的锁

1) 表级别的共享锁和排他锁

(1) 设置表级锁的基本语法格式如下所示。

image.png

语法说明如下。

tbl_name ［［AS］ alias］是“表名［as 别名］”。

READ 是给表加共享锁。

WRITE 是给表加排他锁。

(2) 加锁完成对数据表的操作后，需要解锁，基本语法格式如下所示。

image.png

提示

InnoDB 存储引擎中表级别的共享锁和排他锁只会在一些特殊情况下(例如系统崩溃恢复时)用到，在对某个表执行 SELECT、INSERT、UPDATE、DELETE 等语句时，InnoDB 存储引擎是不会为这个表添加表级别的共享锁或排他锁的。当然，用户可以根据需要，在使用 InnoDB 存储引擎的表上用 LOCK TABLES 这样的手动锁表语句手工添加表级锁，但是应该尽量避免，因为这不但不能提供额外的保护，反而会降低并发能力。

另外，在对某个表执行诸如 ALTER TABLE、DROP TABLE 等的 DDL 语句时，其他事务在对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE 等 DML 语句时，会发生阻塞。同理，某个事务在对某个表执行 DML 语句时，其他对这个表执行 DDL 语句的事务也会被阻塞。

这个过程是通过在服务器层使用元数据锁(MetaData Lock，MDL)来实现的，一般情况下不会使用存储引擎提供的表级别的共享锁和排他锁。

元数据锁由于篇幅所限，在此不做介绍，请自行查阅其他资料。

【例 12-11】以共享锁的方式锁定 bank 数据库中的账户表 accounts。

(1) 给账户表 accounts 加共享锁。

image.png

(2) 查询账户表 accounts 的数据。

image.png

可以查询到账户表 accounts 里的所有记录，说明给表加共享锁之后，可以对表进行正常的读操作。

(3) 删除表的一条记录。

image.png

此时删除记录出错，提示错误信息：［Err］ 1099 - Table ‘accounts’ was locked with a READ lock and can’t be updated，说明在加共享锁后，无法对表进行删除操作。

(4) 保持现有会话窗口不关闭，在一个新的会话窗口中查询账户表 accounts 的数据。可以在新的会话窗口中查询到账户表 accounts 中的所有记录，说明共享锁可以与其他会话兼容。

(5) 继续在这个新会话窗口添加一条插入语句。

image.png

从执行结果可以看到，该语句一直处于“处理中”的等待状态，没有结果显示。因为账户表 accounts 加了共享锁，其他用户是不可以对其进行写操作的。

(6) 在之前的会话窗口输入解锁语句。

image.png

从执行结果可以看到，账户表 accounts 的共享锁解除之后，插入语句马上执行成功，可用查询语句查询到新增的记录，只是这个插入语句的执行时间为等待时间与语句的执行时间之和。

2) 行级别的共享锁和排他锁

当用户对 InnoDB 存储引擎的表执行 INSERT、UPDATE、DELETE 等写操作前，存储引擎会自动为相关记录添加行级排他锁。语句执行完毕时，存储引擎再自动为其解锁。

但对于普通的 SELECT 语句，InnoDB 存储引擎是不会自动加锁的。若要保证当前事务中查询出的数据不会被其他事务更新或删除，避免出现脏读、不可重复读、幻读等一致性问题，需要为查询操作显式地添加行级别的共享锁和排他锁。

(1) 在查询语句中设置行级共享锁，基本语法格式如下所示。

image.png

语法说明如下。

FOR SHARE 表示查询时添加行级共享锁，后面可以跟 NOWAIT | SKIP LOCKED，这两个参数是 MySQL 8.0 的新特性。

NOWAIT 是可选选项，表示使 FOR SHARE 或 FOR UPDATE 查询立即执行，如果由于另一个事务持有的锁而无法获取行锁，则返回错误。

SKIP LOCKED 是可选选项，表示立即执行 FOR SHARE 或 FOR UPDATE 查询，结果集中不包括由另一个事务锁定的行。

LOCK IN SHARE MODE 也表示在查询时添加行级共享锁，与 FOR SHARE 功能相同。

(2) 在查询语句中设置行级排他锁，基本语法格式如下所示。

image.png

语法说明：FOR UPDATE 表示在查询时添加行级排他锁，后面可以跟 NOWAIT | SKIP LOCKED 参数，含义同上。

提示

上述行级锁的生命周期非常短暂，可以通过手动开启事务来延长行级锁的生命周期，事务中行级锁的生命周期从加锁开始，直到事务提交或回滚才结束。

【例 12-12】在 bank 数据库中的账户表 accounts 上添加行级锁。

(1) 打开两个会话窗口，并都切换到 bank 数据库。

(2) 在会话窗口 1 中，为 accounts 表中 id 值为 1 的行添加排他锁。

image.png

(3) 在会话窗口 2 中，输入下面代码。

image.png

执行上述代码后，可以正常查询账户 A 的信息。

(4) 在会话窗口 2 继续输入下面的 SQL 语句。

image.png

执行上述代码后，一直在处理中，没有结果显示，进入排他锁等待状态。等待一段时间后，会显示“［Err］1205 -Lock wait timeout exceeded; try restarting transaction”的锁等待超时提示。

如果在会话窗口 2 等待的时间内，在会话窗口 1 输入“ROLLBACK;”命令，则会话窗口 2 会马上执行成功。

(5) 在会话窗口 2 中输入“ROLLBACK;”语句或“COMMIT;”语句结束事务。

提示

行级锁只适用于 InnoDB 存储引擎，如果表不是 InnoDB 存储引擎，可以使用“ALTER TABLE tablename ENGINE = 存储引擎名称;”语句进行更改。当然，在数据量很庞大的实际生产环境中，最好不要随便更改存储引擎。

可以使用下面语句查看表的存储引擎，执行结果如图 12-2 所示。

image.png

image.png

■ 图 12-2 查看表的存储引擎

3. InnoDB 中常用的行级锁类型

InnoDB 存储引擎常用的行级锁类型有如下三种。

(1) Record Lock：记录锁，只对记录本身加锁，通过对索引行加锁实现。即使一张表没有定义任何索引，记录锁也会锁定索引记录。如果表在建立的时候没有设置任何一个索引，InnoDB 存储引擎会使用隐式的主键来进行锁定。

(2) Gap Lock：间隙锁，用于锁住记录间的间隙，防止别的事务向该间隙插入新记录，针对 REPEATABLE READ(可重复读)事务隔离级别而设置，可以最大限度防止幻读现象发生。

(3) Next-key Lock：Record Lock 和 Gap Lock 的结合体，既保护记录本身，也防止别的事务向该间隙插入新记录。InnoDB 存储引擎对于行的查询都采用这种锁定方式。

4. 查看事务加锁情况

在 InnoDB 存储引擎中，可以用以下方法，查看事务的加锁情况。

(1) 在 MySQL 控制台下使用如下语句。

image.png

此命令输出的信息量非常大，分为多段输出，每一段对应 InnoDB 存储引擎不同部分的信息，可以让用户了解 InnoDB 存储引擎的运行状态，对于开发和维护人员有很大的利用价值，尤其是在进行死锁分析和性能调优时。

其中的 TRANSACTIONS 部分是关于事务的统计信息，但只用这个语句还无法显示到底哪个事务对哪些记录加了哪些锁。可以先将系统变量 innodb_status_output_locks 设置为 ON，再运行此命令，SQL 语句运行如下。

image.png

以例 12-12 的事务为例，TRANSACTIONS 部分输出如下。

image.png

这样，哪个事务为哪些记录加了哪些锁，就显示得很清楚了。部分输出分析如下。

① RECORD LOCKS space id 27 page no 4 n bits 72 index PRIMARY of table 'bank1'.'accounts' trx id 3859 lock_mode X locks rec but not gap waiting

这里的输出表示一个锁结构，space id 是 27，page no 是 4，n_bits 属性值为 72，对应的索引为聚簇索引 PRIMARY，存放的锁类型是 X 型记录锁(排他锁)。这条语句后面的输出是加锁记录的详细信息。

② TABLE LOCK table 'bank1'.'accounts' trx id 3859 lock modeIX

这里的输出表示 id 为 3859 的事务对 bank 数据库中的 accounts 表加了表级别的意向排他锁。

(2) 可以通过系统数据库 information_schema 中的 INNODB_TRX 表查看当前正在执行的事务信息，部分输出如图 12-3 所示。

image.png

■ 图 12-3INNODB_TRX 表的部分输出

从上面的输出可以看到事务的 id、状态、开始时间、隔离级别等信息。其中 trx_tables_locked 表示该事务加了多少个表级锁，trx_rows_locked 表示加了多少个行级锁，trx_lock_structs 表示该事务生成了多少个内存中的锁结构。

5. 死锁

死锁(Dead Lock)是指两个或两个以上的进程需要使用相同的数据，在执行过程中，一直处于等待对方释放资源的状态，若无外力作用，它们将一直处于等待状态，这样就产生了死锁。

MySQL 检测到死锁时，会选择一个较小的事务进行回滚，并提示错误信息：［Err］ 1213 -Deadlock found when trying to get lock; try restarting transaction。

提示：所谓较小的事务是指在事务执行过程中受影响的记录条数较少的事务。

在 MySQL 8.0 中，如果获取不到锁，添加 NOWAIT、SKIP LOCKED 参数会跳过锁等待，或跳过锁定。

可以使用“SHOW ENGINE INNODB STATUS ＼G”语句查看最近发生的一次死锁信息。如果死锁频繁发生，可以将全局系统变量 innodb_print_all_deadlocks 设置为 ON，将每次死锁发生时的信息都记录在 MySQL 的错误日志中，这样就可以通过查看错误日志来分析更多的死锁情况。

image.png