MySQL 锁

介绍了数据库中的锁。

InnoDB 存储引擎中的锁

锁的形态

锁的形态分为 Lock 和 Latch。

对于 InnoDB 的 Latch，可通过 SHOW ENGINE INNODB MUTEX 进行查看。Lock 信息可通过 SHOW ENGINE INNODB STATUS 及 information_schema 架构下的表 INNODB_LOCK_WAITS 来观察锁信息。

锁的类型

行级别的 S、X 锁

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

• 共享锁（Shared Lock，S Lock）
• 排他锁（Exclusive Lock，X Lock）

InnoDB 引擎在事务读取一条记录时要先获取该记录的 S 锁，在事务要改动一条记录时要先获取该记录的 X 锁。

以下是 InnoDB 引擎对 SELECT 语句支持的两种一致性锁定读操作：

• SELECT … FOR UPDATE （加 X 锁）
• SELECT … LOCK IN SHARE MODE （加 S 锁）

当事务提交时，锁会自动释放。

表级别的 S、X 锁

在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，InnoDB 存储引擎是不会为这个表添加表级别的 S 锁或者 X 锁的。
另外，在对某个表执行一些诸如 ALTER TABLE、DROP TABLE 的 DDL 语句时，其他事务在对这个表并发执行诸如 SELECT、INSERT、DELETE、UPDATE 等语句，会发生阻塞。同理，某个事务在对某个表执行 SELECT、INSERT、DELETE、UPDATE 语句时，在其他会话中对这个表执行 DDL 语句也会发生阻塞。这个过程其实是通过在 server 层使用一种称为元数据锁（Metadata Lock, MDL）的东西来实现的，一般情况下也不会使用 InnoDB 存储引擎自己提供的表级别的 S 锁和 X 锁。

表级别的 S、X 锁只会在一些特殊情况（如系统崩溃恢复时）用到。

表级别的 IS、IX 锁

InnoDB 存储引擎还支持多粒度（granular）锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。InnoDB 通过意向锁的方式实现了多粒度锁定的功能。当对使用 InnoDB 存储引擎的表的某些记录加 S 锁之前，需要先在表级别加一个 IS 锁；当对使用 InnoDB 存储引擎的表的某些记录加 X 锁之前，需要先在表级别加一个 IX 锁。IS 锁和 IX 锁的使命只是为了后续在加表级别的 S 锁和 X 锁时，判断表中是否有已经被加锁的记录，以避免用遍历的方式来查看表中有没有上锁的记录。

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

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

由于 InnoDB 存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫描以外的任何请求。故表级意向锁与行级锁的兼容性如下表所示。

用户可通过 SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS 命令查看当前锁请求信息。也可在 information_schema 架构的 innodb_trx、innodb_locks、innodb_lock_waits 监控当前事物并分析可能存在的锁问题。

表级别的 AUTO-INC 锁

一般对于自增序列实现方式有下面两个：

1. 采用 AUTO-INC 锁，也就是在执行插入语句时就加一个表级别的 AUTO-INC 锁，然后为每条待插入记录的 AUTO_INCREMENT 修饰的列分配递增的值。在该语句执行结束后，再把 AUTO-INC 锁释放掉。这样一来，一个事务在持有 AUTO-INC 锁的过程中，其他事务的插入语句都要被阻塞，从而保证一个语句中分配的递增值是连续的。在无法预计即将插入记录的数量时（如 INSERT…SELECT、REPLACE…SELECT 或者 LOAD DATA）一般使用此种方法。

2. 采用一个轻量级的锁，在为插入语句生成 AUTO_INCREMENT 修饰的列的值时获取这个轻量级锁，然后在生成本次插入语句需要用到的 AUTO_INCREMENT 修饰的列的值之后，就把该轻量级锁释放掉，而不需要等到整个插入语句执行完后才释放锁。

   如果我们的插入语句在执行前就可以确定具体要插入多少条记录，比如在语句执行前就可以确定要插入 2 条记录，那么一般采用轻量级锁的方式对 AUTO_INCREMENT 修饰的列进行赋值。这种方式可以避免锁定表，可以提升插入性能。

下面是自增长的插入类型：

下面是参数 innodb_autoinc_lock_mode 对自增的影响：

此外，还需要特别注意的是 InnoDB 存储引擎中自增长的实现和 MyISAM 不同，MyISAM 存储引擎是表锁设计，自增长不用考虑并发插入的问题。因此在 master 上用 InnoDB 存储引擎，在 slave 上用 MyISAM存储引擎的 replication 架构下，用户必须考虑这种情况。

外键关联锁

外键主要用于引用完整性的约束检查。在 InnoDB 存储引擎中，对于一个外键列，如果没有显式地对这个列加索引，InnoDB 存储引擎自动对其加一个索引，因为这样可以避免表锁。这比 Oracle 数据库做得好，Oracle 数据库不会自动添加索引，用户必须自己手动添加，这也导致了 Oracle 数据库中可能产生死锁。

对于外键值的插人或更新，首先需要查询父表中的记录，即 SELECT 父表。但是对于父表的 SELECT 操作，不是使用一致性非锁定读的方式，因为这样会发生数据不一致的问题，因此这时使用的是 SELECT..LOCK IN SHARE MODE 方式，即主动对父表加一个 S 锁。如果这时父表上已经这样加 X 锁，子表上的操作会被阻塞。

一致性非锁定读

一致性的非锁定读（consistent nonlocking read）是指 InnoDB 存储引擎通过行多版本控制（multi versioning）的方式来读取当前执行时间数据库中行的数据。如果读取的行正在执行 DELETE 或 UPDATE 操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB 存储引擎会去读取行的一个快照数据。

如上图所示，之所以称其为非锁定读，因为不需要等待访问的行上 X 锁的释放。快照数据是指该行的之前版本的数据，该实现是通过 undo 段来完成。而 undo 用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外，读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

快照数据其实就是当前行数据之前的历史版本，每行记录可能有多个版本，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制（Multi Version Concurrency Control, MVCC）。

在事务隔离级别 READ COMMITTED 和 REPEATABLE READ 下，InnoDB 存储引擎使用非锁定的一致性读。然而，对于快照数据的定义却不相同。在 READ COMMITTED 事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据。而在 REPEATABLE READ 事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

并发事务的问题

并发的事务如果没有有效的机制进行避免就会导致出现脏读、不可重复读、幻读、第一类更新丢失、第二类更新丢失。

脏读

在不同事务下，当前事务可以读取到另外事务未提交的数据。

不可重复读

不可重复读是指在一个事务内多次读取同一数据集合。在这个事务还没有结束时，另外一个事务也访问该同一数据集合，并做了一些 DML 操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的情况，这种情况称为不可重复读。

不可重复读和脏读的区别是：脏读是读到未提交的数据，而不可重复读读到的却是已经提交的数据，但是其违反了数据库事务一致性的要求。

幻读

更新丢失

丢失更新是另一个锁导致的问题，简单来说其就是一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。这种现象只是零星的、随机的，极难发现。

第一类更新丢失

第二类更新丢失

锁机制

InnoDB 存储引擎有三种行锁算法：

• Record Lock，单个行记录上的锁
• Gap Lock，锁定一个范围，但不包含记录本身
• Next-Key Lock，锁定一个范围并锁定记录本身，即左开右闭区间
• Intention Lock，意向锁

需要重点说明的是 Gap Lock，间隙锁存在的目的主要是在可重复读的隔离条件下防止幻读的出现。实际的做法是将可能导致幻读区域的记录锁定，一是防止间隙内有新数据被插入，二是防止已存在的数据更新成间隙内的数据。例如有以下四条数据 (id:1, s_index:2)、(id:3, s_index:3)、(id:5, s_index:5)、(id:6, s_index:5)。如果要插入 (id:4, s_index:4)，间隙锁锁住的就是 (3,5) 这个区间，具体点说也就是插入位置所在的间隙，即 (id:3, s_index:3)、(id:5, s_index:5)这两条记录中间形成的间隙。

需要注意的是，在通过 SHOW ENGINE INNODB STATUS 查询锁信息时，有以下几种对应关系：

1. Record Lock 对应 lock_mode X locks rec but not gap 或 lock_mode S locks rec but not gap
2. Gap Lock 对应 lock_mode X locks gap before rec 或 lock_mode S locks gap before rec
3. Next-Key Lock 对应 lock_mode X

4. Intention Lock 对应 lock mode IX 或 lock mode IS

   另外，在使用之前需要 set GLOBAL innodb_status_output_locks=ON，这样在查看 engine innodb status 的时候可以更加清晰的查到到锁的情况。

根据二级索引修改一条记录

以根据非唯一索引删除一条存在记录举例

l
> DELETE FROM t WHERE c1=5;
Query OK, 1 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146749, ACTIVE 9 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 104 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146749 lock mode IX
RECORD LOCKS space id 53 page no 5 n bits 72 index `idx_c1` of table `test`.`t` trx id 146749 lock_mode X
RECORD LOCKS space id 53 page no 3 n bits 72 index `PRIMARY` of table `test`.`t` trx id 146749 lock_mode X locks rec but not gap
RECORD LOCKS space id 53 page no 5 n bits 72 index `idx_c1` of table `test`.`t` trx id 146749 lock_mode X locks gap before rec

根据非唯一索引进行删除的时候，锁情况为：

1. 4 lock struct(s)：4 种锁结构，分别为 IX，idx_c1 的 next key 锁(3,5] ，主键的行锁，还有 idx_c1 的 gap 锁 (5,7)
2. 3 row lock(s)：除去 IX 的都是算在 row lock 里面

以根据非唯一索引删除一条不存在记录举例

> delete from t where c1 = 4;
Query OK, 0 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146786, ACTIVE 1 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 671 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146786 lock mode IX
RECORD LOCKS space id 53 page no 5 n bits 80 index `idx_c1` of table `test`.`t` trx id 146786 lock_mode X locks gap before rec

根据非唯一索引删除一条不存在记录，锁情况为：

1. 2 lock struct(s)：2种锁结构，分别为 IX 和 X 类型的 gap 锁
2. 1 row lock(s)：为非唯一索引的 gap(X) 锁

根据唯一索引修改一条记录

以根据唯一索引删除一条存在记录举例

> delete from tu where c1=5;
Query OK, 1 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146751, ACTIVE 2 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 134 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146751 lock mode IX
RECORD LOCKS space id 45 page no 5 n bits 72 index `uniq_c1` of table `test`.`tu` trx id 146751 lock_mode X locks rec but not gap
RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146751 lock_mode X locks rec but not gap

根据唯一索引进行删除的时候，锁情况为：

1. 3 lock struct(s)：3 种锁结构，分别为 IX，idx_c1 和主键的行锁，没有 gap 锁
2. 2 row lock(s)：除去 IX 的都是算在 row lock 里面，没有 gap，因此为 2 个

以根据唯一索引删除一条不存在记录举例

> delete from tu where c1 = 4;
Query OK, 0 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146787, ACTIVE 2 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 711 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146787 lock mode IX
RECORD LOCKS space id 45 page no 5 n bits 72 index `uniq_c1` of table `test`.`tu` trx id 146787 lock_mode X locks gap before rec

根据唯一索引删除一条不存在记录，发现和非唯一索引一样，锁情况为：

1. 2 lock struct(s)：2 种锁结构，分别为 IX 和 X 类型的 gap 锁
2. 1 row lock(s)：为唯一索引的gap(X)锁

根据主键修改一条存在记录

以根据主键删除一条存在记录举例

> delete from tu where id=2;
Query OK, 1 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146753, ACTIVE 2 sec
2 lock struct(s), heap size 360, 1 row lock(s), undo log entries 1
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 147 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146753 lock mode IX
RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146753 lock_mode X locks rec but not gap

根据主键进行删除的时候，锁情况为：

1. 2 lock struct(s)：2 种锁结构，分别为 IX 和主键的行锁，没有 gap 锁
2. 1 row lock(s)：就主键记录上的行锁，没有 gap，因此为1个

以根据主键删除一条不存在记录举例

delete from tu where id = 6;
Query OK, 0 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146831, ACTIVE 24 sec
2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 881 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146831 lock mode IX
RECORD LOCKS space id 45 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146831 lock_mode X locks gap before rec

根据主键删除一条不存在记录，发现和非唯一索引一样，锁情况为：

1. 2 lock struct(s)：2种锁结构，分别为IX和X类型的gap锁
2. 1 row lock(s)：为主键上的gap(X)锁

根据主键删除两条存在的记录

用 in 关键字删除

根据主键删除两条的时候，使用 in 的锁情况为：

1. 2 lock struct(s)：2 种锁结构，分别为 IX 和主键的行锁，没有 gap 锁
2. 2 row lock(s)：两条主键记录上的行锁，没有 gap，因此为 2 个

有3,4两条记录

> delete from tu where id in (3,4);
Query OK, 2 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146880, ACTIVE 1 sec
2 lock struct(s), heap size 360, 2 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 928 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146880 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146880 lock_mode X locks rec but not gap

用 range 方式删除

根据主键删除两条的时候，使用>,<,>=,<=,比较符号的锁情况为：

1. 无论如何，匹配到 2 条记录，因此必须会有 2 row lock(s)
2. 如果只有 >、<，那么毫无疑问，是不会锁定两个边界的记录，因此他只会锁定边界到边界内的整个范围，锁的类型为 next key，此时为 2 lock struct(s) ，3 row lock(s)
3. 碰到 >= 的时候，判断 >= 的值是否存在，如果存在，则锁定该记录。所以除了IX,next key锁，还有行锁，因此存在的时候为3 lock struct(s), 3 row lock(s)。如果不存在，和第二种是一样的，为2 lock struct(s) ，3 row lock(s) 。

左闭右闭区间

有 5,9 这两条记录

> delete from tu where id>=5 and id<=9;
Query OK, 2 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146912, ACTIVE 12 sec
3 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1022 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146912 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146912 lock_mode X locks rec but not gap
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146912 lock_mode X

左闭右开区间

有 5,10 这两条记录

> delete from tu where id>=5 and id<10;
Query OK, 2 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146900, ACTIVE 35 sec
3 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 995 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146900 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146900 lock_mode X locks rec but not gap
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146900 lock_mode X

左开右开区间

有 4,10 这两条记录

> delete from tu where id>4 and id<10;
Query OK, 2 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146906, ACTIVE 13 sec
2 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1011 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146906 lock mode IX
RECORD LOCKS space id 56 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146906 lock_mode X

左边界未匹配上的左闭右闭区间

有 10 没 7 

> delete from tu where id>=7 and id<=10;
Query OK, 2 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146966, ACTIVE 2 sec
2 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1172 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146966 lock mode IX
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146966 lock_mode X

右边界未匹配上的左闭右闭区间

有 4 没 8 

> delete from tu where id>=4 and id<=8;
Query OK, 2 rows affected (0.00 sec)

> SHOW ENGINE INNODB STATUS;
---TRANSACTION 146972, ACTIVE 20 sec
3 lock struct(s), heap size 360, 3 row lock(s), undo log entries 2
MySQL thread id 1, OS thread handle 0x7f61ab1c7700, query id 1201 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146972 lock mode IX
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146972 lock_mode X locks rec but not gap
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146972 lock_mode X

非唯一索引删除一条已经标记删除的记录

Sess1	Sess2	Sess3
begin;
delete from t where c1=8;
	begin;
	delete from t where c1=8;
		@1 show engine innodb status
commit;
		@2 show engine innodb status

@1 show engine innodb status


---TRANSACTION 146981, ACTIVE 12 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2804 localhost msandbox updating
delete from t where c1=8
------- TRX HAS BEEN WAITING 12 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X waiting
------------------
TABLE LOCK table `test`.`t` trx id 146981 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X waiting
---TRANSACTION 146980, ACTIVE 16 sec
4 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 1
MySQL thread id 355, OS thread handle 0x7f61ab145700, query id 2802 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146980 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146980 lock_mode X
RECORD LOCKS space id 54 page no 3 n bits 80 index `PRIMARY` of table `test`.`t` trx id 146980 lock_mode X locks rec but not gap
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146980 lock_mode X locks gap before rec

@2 show engine innodb status

---TRANSACTION 146981, ACTIVE 50 sec
3 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2804 localhost msandbox cleaning up
TABLE LOCK table `test`.`t` trx id 146981 lock mode IX
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X
RECORD LOCKS space id 54 page no 4 n bits 80 index `idx_c1` of table `test`.`t` trx id 146981 lock_mode X locks gap before rec

非唯一索引删除一条已经标记删除的记录的锁情况为：

• 加锁等待时: 2 lock struct(s) ，持有 IX 锁，等待记录上的 next key 锁
• 加锁成功时：3 lock struct(s)，持有 IX、next key 锁和 gap 锁，这个和非唯一索引删除一条不存在的记录是基本一样的，多了个因 Sess1 提交成功后多获得的 next key 锁。

唯一索引删除一条已经标记删除的记录

Sess1	Sess2	Sess3
begin;
delete from tu where c1=8;
	begin;
	delete from tu where c1=8;
		@1 show engine innodb status
commit;
		@2 show engine innodb status

@1 show engine innodb status

---TRANSACTION 146984, ACTIVE 2 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2842 localhost msandbox updating
delete from tu where c1=8
------- TRX HAS BEEN WAITING 2 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X waiting
------------------
TABLE LOCK table `test`.`tu` trx id 146984 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X waiting
---TRANSACTION 146983, ACTIVE 9 sec
3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 355, OS thread handle 0x7f61ab145700, query id 2839 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146983 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146983 lock_mode X locks rec but not gap
RECORD LOCKS space id 57 page no 3 n bits 80 index `PRIMARY` of table `test`.`tu` trx id 146983 lock_mode X locks rec but not gap

@2 show engine innodb status

---TRANSACTION 146984, ACTIVE 23 sec
3 lock struct(s), heap size 360, 1 row lock(s)
MySQL thread id 363, OS thread handle 0x7f61ab1c7700, query id 2842 localhost msandbox cleaning up
TABLE LOCK table `test`.`tu` trx id 146984 lock mode IX
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X
RECORD LOCKS space id 57 page no 4 n bits 80 index `uniq_c1` of table `test`.`tu` trx id 146984 lock_mode X locks gap before rec

唯一索引删除一条已经标记删除的记录的锁情况为：

• 加锁等待时: 2 lock struct(s) ，持有 IX 锁，等待记录上的 next key 锁
• 加锁成功时：3 lock struct(s)，持有 IX，next key，和 gap 锁，和非唯一索引删除一条标记为已删除的记录的情况一模一样。

总结

1. 在非唯一索引的情况下，删除一条存在的记录是有 gap 锁，锁住记录本身和记录之前的 gap
2. 在唯一索引和主键的情况下删除一条存在的记录，因为都是唯一值，进行删除的时候，是不会有 gap 存在
3. 非唯一索引，唯一索引和主键在删除一条不存在的记录，均会在这个区间加 gap 锁
4. 通过非唯一索引和唯一索引去删除一条标记为删除的记录的时候，都会请求该记录的行锁，同时锁住记录之前的 gap
5. RC 情况下是没有 gap 锁的，除了遇到唯一键冲突的情况，如插入唯一键冲突。

阻塞及死锁

阻塞、超时及回滚

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

在 InnoDB 存储引擎中，参数 innodb_lock_wait_timeout 用来控制等待的时间（默认是 50 秒），innodb_rollback_on_timeout 用来设定是否在等待超时时对进行中的事务进行回滚操作（默认是 OFF，代表不回滚）。数据库默认不回滚除死锁以外的事务。

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。若无外力作用，事务都将无法推进下去。解决死锁问题最简单的方式是不要有等待，将任何的等待都转化为回滚，并且事务重新开始。毫无疑问，这的确可以避免死锁问题的产生。然而在线上环境中，这可能导致并发性能的下降，甚至任何一个事务都不能进行。而这所带来的问题远比死锁问题更为严重，因为这很难被发现并且浪费资源。

检测死锁

超时机制

解决死锁问题最简单的一种方法是超时，即当两个事务互相等待时，当一个等待时间超过设置的某一阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。

超时机制虽然简单，但是其仅通过超时后对事务进行回滚的方式来处理，或者说其是根据 FIFO 的顺序选择回滚对象。但若超时的事务所占权重比较大，如事务操作更新了很多行，占用了较多的 undo log，这时采用 FIFO 的方式，就显得不合适了，因为回滚这个事务的时间相对另一个事务所占用的时间可能会很多。

wait-for graph

除了超时机制，当前数据库还都普遍采用 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 所占用的 row2 对象，故而存在节点 t2 到节点 t1、t4 的边。同样，存在节点 t3 到节点 t1、t2、t4 的边，因此最终的 wait-for graph 如图所示。

通过上图可以发现存在回路(t1, t2)， 因此存在死锁。wait-for graph 是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，若存在则有死锁，通常来说 InnoDB 存储引擎选择回滚 undo 量最小的事务。wait-for graph 采用的是非递归深度优先的方式。

死锁概率

死锁应该非常少发生，若经常发生，则系统是不可用的。此外，死锁的次数应该还要少于等待，因为至少需要 2 次等待才会产生一次死锁。本节将从纯数学的概率角度来分析，死锁发生的概率是非常小的。

假设当前数据库中共有 n+1 个线程执行，即当前总共有 n+1 个事务。并假设每个事务所做的操作相同。若每个事务由 r+1 个操作组成，每个操作为从 R 行数据中随机地操作一行数据，并占用对象的锁。每个事务在执行完最后一个步骤释放所占用的所有锁资源。最后，假设 nr<<R，即线程操作的数据只占所有数据的一小部分。

在上述的模型下，事务获得一个锁需要等待的概率是多少呢？当事务获得一个锁，其他任何一个事务获得锁的情况为：

$$(1+2+3+...r)/(r+1) \approx r/2$$

由于每个操作为从 R 行数据中取一条数据，每行数据被取到的概率为 1/R，因此，事务中每个操作需要等待的概率 PW 为：

$$PW=nr/2R$$

事务是由 r 个操作所组成，因此事务发生等待的概率 PW(T) 为：

$$PW(T)=1-(1-PW)^r \approx r*PW \approx \frac{nr^2}{2R}$$

死锁是由于产生回路，也就是事务互相等待而发生的，若死锁的长度为 2，即两个等待节点间发生死锁，那么其概率为：

$$一个事务发生死锁的概率 \approx 1-(1-PW(T))^2 \approx r*PW \approx \frac{nr^4}{4R^2}$$

由于大部分死锁发生的长度为 2，因此上述公式基本代表了一个事务发生死锁的概率。从整个系统来看，任何一个事务发生死锁的概率为：

$$系统中任何一个事务发生死锁的概率 \approx \frac{n^2r^4}{4R^2}$$

从上述的公式中可以发现，由于 nr<<R，因此事务发生死锁的概率是非常低的。同时，事务发生死锁的概率与以下几点因素有关：

• 系统中事务的数量(n)，数量越多发生死锁的概率越大。
• 每个事务操作的数量(r), 每个事务操作的数量越多，发生死锁的概率越大。
• 操作数据的集合(R)，越小则发生死锁的概率越大。

锁升级

锁升级（Lock Escalation）是指将当前锁的粒度降低。举例来说，数据库可以把一个表的 1000 个行锁升级为一个页锁，或者将页锁升级为表锁。如果在数据库的设计中，认为锁是一种稀有资源，而且想避免锁的开销，那数据库中会频繁出现锁升级现象。

Microsoft SQL Server 数据库的设计认为锁是一种稀有的资源，在适合的时候会自动地将行、键或分页锁升级为更粗粒度的表级锁。这种升级保护了系统资源，防止系统使用太多的内存来维护锁，在一定程度上提高了效率。

即使在 Microsoft SQL Server 2005 版本之后，SQL Server 数据库支持了行锁，但是其设计和 InnoDB 存储引擎完全不同，在以下情况下依然可能发生锁升级：

• 由一句单独的 SQL 语句在一个对象上持有的锁的数量超过了阈值，默认这个阈值为 5000。值得注意的是，如果是不同对象，则不会发生锁升级。
• 锁资源占用的内存超过了激活内存的 40% 时就会发生锁升级。

在 Microsoft SQL Server 数据库中，由于锁是一种稀有的资源，因此锁升级会带来一定的效率提高。但是锁升级带来的一个问题却是因为锁粒度的降低而导致并发性能的降低。

InnoDB 存储引擎不存在锁升级的问题。因为其不是根据每个记录来产生行锁的，相反，其根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录，其开销通常都是一致的。

假设一张表有 3 000 000 个数据页，每个页大约有 100 条记录，那么总共有 300 000 000 条记录。若有一个事务执行全表更新的 SQL 语句，则需要对所有记录加 X 锁。若根据每行记录产生锁对象进行加锁，并且每个锁占用 10 字节，则仅对锁管理就需要差不多需要 3GB 的内存。而 InnoDB 存储引擎根据页进行加锁，并采用位图方式，假设每个页存储的锁信息占用 30 个字节，则锁对象仅需 90MB 的内存。由此可见两者对于锁资源开销的差距之大。