【MySQL第五章】MySQL-锁

1. 锁的概述

开发多用户、数据库驱动应用时，最大的难点就是：一方面要最大程序利用并发访问，另一方面还要确保每个用户能以一致的方式读取和修改数据。从此就有了锁（locking）的机制，同时这也是数据库系统区别于文件系统的一个关键特性

InnoDB存储引擎会在行级别对表数据上锁。不过InnoDB存储引擎也会在数据库内部其他地方使用锁，从而允许对多种不同资源提供并发访问（如：LRU列表。线程锁，主键id自增）。数据库系统使用锁是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。对于MyISAM引擎，其锁是表锁设计（InnoDB存储也有表锁，但是与行锁不冲突）。并发情况下读没有问题，但是并发插入时的性能要差一些，若插入是在“底部”，MyISAM存储引擎还是可以有一定的并发写入操作。

在数据库中，lock与latch都可以被称为“锁”。但是两者有着截然不同的含义。本次主要关注lock。

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

lock的对象是事物，用来锁定的是数据库中的对象。如表、页、行。并且一般lock的对象仅在事物commit或rollback后进行释放（不同事物隔离级别释放的时间可能不同）。此外，lock是有死锁机制的。

latch更像是JUC，像我们在做开发，而lock是针对事务

2. InnoDB存储引擎中的锁

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

• 共享锁（S lock），允许事物读一行数据。
• 排他锁（X lock），允许事物删除或者更新一行数据

X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S锁和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级别上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁（Intention Lock）。

InnoDb储存引擎的意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

1. 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁
2. 意向排他锁（IX Lock），事务想要获得一张表中

其中 IS IS，IS IX，IX IS，IX IX不会有不兼容的情况。（IX IX：当修改不是表中同一个行，那么没有问题，如果是修改同一个行，稍微阻塞一下就好了）

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。

意向锁存在的意义？

（我有一个需求，我现在就需要查询出所有的表里的数据。select * from table；这个语句是不是 innodb引擎进行执行啊? 是不是需要确保所有的行都没有被x加锁啊？如何保证所有的行没有加x锁啊？ x 行锁 可以吗？ 可以啊：一条条查不就行了。这样有点慢。Innodb存储引擎想了招儿，IX 锁不就行了，直接检查是否有IX 锁就行了，如果有就阻塞。）

从InnoDB1.0开始，在INFORMATION_SCHEMA架构下添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS。通过这三张表，用户可以更简单地监控当前事务并分析可能存在的锁问题。

从下表可以看出，我们拿到了事务ID就可以拿到锁ID，可以去INNODB_LOCKS表中查询。

lock_data这个值并非是“可信”的值。例如当用户运行一个范围查找时，lock_data只会返回第一行的主键值（1-100只会返回1，所以一定要注意INNODB_TRX表中的trx_query属性---SQL）。与此同时，如果当前资源被锁住，若锁住的页因为InnoDB存储引擎缓冲池中的容量，导致该页从缓冲池中被刷出，则看INNODB_LOCKS表时，改值同样会显示为NULL，即InnoDB存储引擎不会从磁盘进行再一次查找。

在通过表INNODB_LOCKS查看了每张表上锁的情况后，用户就可以来判断由此引发的等待情况。当事务较小时，用户可以人为的、直观的进行判断。但是当事务量非常大，其中锁和等待时常发生，这个时候就不这么容易判断。但是通过INNODB_LOCK_WAITS,可以很直观的反映当前事务的等待。如下，用户可以很清楚直观的看到哪个事务阻塞了另一个事务。

3. 一致性非锁定读（MVCC）

MVCC是指InnoDB存储引擎通过多版本控制的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行delete或update操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB存储引擎回去读取行的一个快照数据。之所以称之为非锁定读，因为不需要等待访问的行上X锁的释放。快照数据是指该行的之前版本的数据，该实现是通过undo段来完成的。而undo是用来在事务回滚数据，因此快照数据本身是没有额外的开销的。此外，读取快照数据是不需要上锁的，没有任何操作会对历史数据进行修改操作的。

一个行记录可能有不止一个版本快照数据，一般称之为行多版本技术，由此带来的并发控制，称之为多版本并发控制（MVCC）。

在事务隔离级别READ_COMMITTED和REPEATABLE_READ（InnoDB存储引擎默认隔离级别）下，InnoDB存储引擎使用非锁定的一致性读。然而，对于快照数据的定义不相同。在READ_COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据（另外一个线程修改了这个数据，我当前这个事务能够感知到）。而在REPEATABLE_READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

在默认配置下，即事务的隔离级别为REPEATABLE READ模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。但是在某些情况下，用户需要显式地对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句，即使是对于SELECT的只读操作。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定读（locking read）操作：

• SELECT…FOR UPDATE
• SELECT…LOCK IN SHARE MODE

SELECT…FOR UPDATE对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。SELECT…LOCK IN SHARE MODE对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

4. 自增长与锁

自增长在数据库中是非常常见的一种属性，插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称做AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制，为了提高插入的性能，锁不是在一个事务完成后才释放，而是在完成对自增长值插入的SQL语句后立即释放。

虽然AUTO-INC Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。首先，对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。其次，对于INSERT…SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会被阻塞。

从MySQL 5.1.22版本开始，InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制，这种机制大大提高了自增长值插入的性能（线程级别的锁）。并且从该版本开始，InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式，该参数的默认值为1。

什么是simple inserts？什么是bulk inserts？

simple inserts是指能在插入前就确定的行数，bulk inserts指不能再插入前确定的行数。

simple怎么使用互斥量的？如100条数据，我已经知道了最小ID到最大ID是多少，只需要做一次增长（一次性全给）就可以确定下来，减少了至少99次的ID自增的情况。对于不确定的条数，我们只能一条条的增加上去，直到自增完成。并且在simple inserts方式下，只要不考虑回滚操作，他的自增长的值还是连续的，statement-based方式的replication（主从同步）也能很好的工作。

tips：在大型企业中，如果数据库是读写分离一般选用2，一定要用row-base replication，前提是插入频繁的场景。对于一些场景主键的值是一定的、一致的、有序的，如在进行mysql的垂直拆分，或者是通过hash进行mysql数据的负载到不同的服务器上，如果要看ID的连续性，就要选择1。

5. 锁的算法

InnoDB存储引擎有3种行锁的算法，其分别是：

• Record Lock：单个行记录上的锁
• Gap Lock：间隙锁，锁定一个范围，不包括记录本身
• Next-Key Lock：Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

Record Lock总是会去锁住索引记录，如果InnoDB存储引擎表在建立的时候没有设置任何一个索引，那么这时InnoDB存储引擎会使用隐式的主键来进行锁定。

Next-Key Lock是结合了Gap Lock和Record Lock的一种锁定算法，在Next-Key Lock算法下，InnoDB对于行的查询都是采用这种锁定算法。例如一个索引有10，11，13和20这四个值，那么该索引可能被Next-Key Locking的区间为：

(-∞,10]

(10,11]

(11,13]

(13，20]

(20,+∞)

采用Next-Key Lock的锁定技术称为Next-Key Locking。其设计的目的是为了支持一些不需要使用幻读和不可重复读的场景。当查询的索引含有唯一属性时，InnoDB存储引擎会对Next-Key Lock进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是范围

示例SQL：

CREATE TABLE `z` (
  `a` int NOT NULL,
  `b` int DEFAULT NULL,
  PRIMARY KEY (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

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;

通过上面的SQL语句，如果我们此时执行 select * from z where a = 5 for update.那么就只会锁定第三行记录，添加一个Record Lock，因为a是唯一索引。

如果此时执行 select * from z where b = 3 for update.对于辅助索引 b，因为不是唯一索引，因此其使用，并且由于有两个索引，其需要分别进行锁定。Next-Key Locking技术加锁会锁定 辅助索引（1,3） 和 （3,6）。Record Lock 会锁定 主键 a = 5的记录。因此效果上感觉锁定的是辅助索引的范围 （1,3】和（3,6）。

用户可以通过两种方式显示的关闭Gap Lock：

• 将事务的隔离级别设置为READ COMMITTED (RR 模式下使用的是 next key locking，RC 模式下使用的是 Gap Lock-外键约束和唯一性检查)
• 将参数innodb_locks_unsafe_for_binlog设置为1

在上述的配置下，除了外键约束和唯一性检查依然需要的Gap Lock，其余情况仅使用Record Lock进行锁定。

6. 脏读（不可以接受）

脏读指的就是在不同的事务下，当前事务可以读到另外事务未提交的数据，简单来说就是可以读到脏数据。

脏读现象在生产环境中并不常发生，从上面的例子中就可以发现，脏读发生的条件是需要事务的隔离级别为READ UNCOMMITTED，而目前绝大部分的数据库都至少设置成READ COMMITTED。InnoDB存储引擎默认的事务隔离级别为READ REPEATABLE

会话A设置事务为read-ncommitted，会话B也设置read-ncommitted，然后会话B开启事物，查询G表，返回a，B会话没有结束，A会话添加了一条数据2（还没有commit），B会话再次查询发现有2条数据。这就是脏读

7. 幻读

同一个事物执行两次相同的查询，第一次查询的结果数量与第二次查询的结果数量不一致。

幻读现象只存在于 Read Commited事物隔离级别下。

一般来说，幻读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商（如Oracle、Microsoft SQL Server）将其数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许幻读的现象。在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免幻读的问题。

8. 不可重复读

同一个事物执行两次相同的查询，第一次查询的结果的值与第二次查询的结果的值不一致。

不可重复读问题只存在于 Read Commited事物隔离级别下。

一般来说，不可重复读的问题是可以接受的，因为其读到的是已经提交的数据，本身并不会带来很大的问题。因此，很多数据库厂商（如Oracle、Microsoft SQL Server）将其数据库事务的默认隔离级别设置为READ COMMITTED，在这种隔离级别下允许不可重复读的现象。

在InnoDB存储引擎中，通过使用Next-Key Lock算法来避免不可重复读的问题。

9. 死锁

因为不同锁之间的兼容性关系，在有些时刻一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。阻塞并不是一件坏事，其是为了确保事务可以并发且正常地运行。

在InnoDB存储引擎中，参数innodb_lock_wait_timeout用来控制等待的时间（默认是50秒），innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是OFF，代表不回滚--没有插入成功，就没必要回滚）。

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。

除了超时机制，当前数据库还都普遍采用wait-for graph（等待图）的方式来进行死锁检测。较之超时的解决方案，这是一种更为主动的死锁检测方式。InnoDB存储引擎也采用的这种方式。wait-for graph要求数据库保存以下两种信息：

• 锁的信息链表
• 事务等待链表

通过图可以发现存在回路（t1，t2），因此存在死锁。通过上述的介绍，可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。wait-for graph的死锁检测通常采用深度优先的算法实现，在InnoDB1.2版本之前，都是采用递归方式实现。而从1.2版本开始，对wait-for graph的死锁检测进行了优化，将递归用非递归的方式实现，从而进一步提高了InnoDB存储引擎的性能。

如果程序是串行的，那么不可能发生死锁。死锁只存在于并发的情况，而数据库本身就是一个并发运行的程序，因此可能会发生死锁。下图的操作演示了死锁的一种经典的情况，即A等待B，B在等待A，这种死锁问题被称为AB-BA死锁。

在上述操作中，会话B中的事务抛出了1213这个错误提示，即表示事务发生了死锁。

发现死锁后，InnoDB存储引擎会马上回滚一个事务，会话A中马上得到了记录为2的这个资源，这其实是因为会话B中的事务发生了回滚，否则会话A中的事务是不可能得到该资源的。