MySQL——锁（全面总结）

锁

锁机制用于管理对共享资源的并发访问。

lock和latch

在数据库中，lock和Latch都称为锁，但是两者意义不同。

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

lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。并且一般lock的对象仅在事务commit或者rollback后进行释放。有死锁检测机制。

通过show engine innodb mutex可以查看InnoDB存储引擎的中latch，具体字段详情如下表：

锁的类型

有几个索引，需要分别向索引加锁。

共享锁、排他锁

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

共享锁（S Lock）：允许事务读一行数据

排他锁（X Lock）：允许事务删除 或 更新一行数据

如果一个事务T1已经获取了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁。因为读取并不会改变行的数据，所以可以多个事务同时获取共享锁，称这种情况为锁兼容。但若有其他的事务T3想获得行R的排他锁，则其必须等待事务T1、T2释放行r上面的共享锁，称这种情况为锁不兼容。下面显示了共享锁和排他锁的兼容性：

从表6-3可以看出X锁与任何锁都不兼容，而S锁仅和S锁兼容。S锁和X锁都是行锁，兼容是指对同一行记录锁的兼容情况。

普通 select 语句默认不加锁，而CUD操作默认加排他锁。

记录锁

Record Lock，仅锁定一行记录（如共享锁、排他锁）

• 记录锁总是会去锁定索引记录，如果表在建立的时候，没有设置任何一个索引，那么InnoDB会使用隐式的主键来进行锁定。
• 查询条件的列是唯一索引的情况下，临键锁退化为记录锁

间隙锁

Gap Lock，锁定一个范围，但不包含记录本身。

关闭间隙锁的2种方式：

（1）将事务隔离级别变为read committed

（2）将参数innodb_locks_unsafe_for_binlog设置为1

在上述配置下，除了外键和唯一性检查依然需要间隙锁，其余情况仅适用行锁进行锁定。

临键锁

Next-Key Lock，等于记录锁 + 间隙锁，锁定一个范围，并且锁定记录本身。主要是阻止多个事务将记录插入到同一个范围内，从而避免幻读。

假如一个索引有10、11、13、20这四个值，那么该索引可能被锁定的区间为：

若事务T1已经通过临键锁锁定了如下范围：

当插入新的记录12时，则锁定的范围变成：

当查询的索引是唯一索引的时候，InnoDB会将临键锁优化成记录锁，从而提高并发。这时候，将不再由间隙锁避免幻读的问题。

在可重复读的情况下，MVCC的SELECT操作只会查找行版本号小于当前事务版本号的记录，其他事务（事务开启时间比当前事务晚）新插入的记录版本号不满足条件，就不会查出来。

对于辅助索引，当执行类似select * from z where b = 3 for update;加锁语句时，会加上临键锁，并且下一个键值的范围也会加上间隙锁。（这里参考《MySQL技术内幕》，里面有具体例子）

值得注意的是，对于唯一键值的锁定，由临键锁优化为记录锁，仅存在于查询所有的唯一索引。若唯一索引由多列组成，而查询仅是查找多个唯一索引中的一个，那么查询其实是range类型查询，而不是point类型查询，故InnoDB存储引擎还是继续使用临键锁。

在InnoDB存储引擎中，通过使用临键锁来避免不可重复读的问题（即幻读）。在使用临键锁的情况下，对于索引的扫描，不仅仅锁住扫描的到索引，而且还锁住这些索引覆盖的范围。因此，在这些范围内插入都是不允许的。这样子就避免了其他事务在这些范围内插入数据导致不可重复读的问题。

意向锁

概念：事务可能要加共享/排它锁了，先提前声明一个意向

意向锁有这样一些特点：

（1）意向锁是表级别的锁

（2）意向锁分为：

• 意向共享锁(intention shared lock, IS)，它预示着，事务有意向对表中的某些行加共享S锁

• 意向排它锁(intention exclusive lock, IX)，它预示着，事务有意向对表中的某些行加排它X锁

（3）意向锁协议：

• 事务要获得某些行的共享锁，必须先获得表的意向共享锁IS
• 事务要获取某些行的排他锁，必须先获得表的意向排他锁IX

（4）由于意向锁仅仅表明意向，它其实是比较弱的锁，意向锁之间并不相互互斥，而是可以并行，其兼容互斥表如下：

​ IS IX

IS 兼容 兼容

IX 兼容 兼容

（5）既然意向锁之间都相互兼容，那其意义在哪里呢？它会与共享锁/排它锁互斥，其兼容互斥表如下：

​ S X

IS 兼容 互斥

IX 互斥 互斥

（排它锁是很强的锁，不与其他类型的锁兼容。这也很好理解，修改和删除某一行的时候，必须获得强锁，禁止这一行上的其他并发，以保障数据的一致性。）

InnoDB支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持不同粒度上进行加锁操作，InnoDB存储引擎支持一种额外的锁方式，称之为意向锁。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更粗的粒度上进行加锁。如图6-3所示：

若将上锁的对象看成一棵树，那么对最下层的对象上锁，也就是对最细粒度的对象上锁，那么首先需要对粗粒度的对象进行上锁。如上图，如果需要对页上的记录上X锁，那么需要分别对数据库A、表、页 上意向锁IX，最后对记录r上排他锁X。

若其中任何一部分导致等待，那么该操作需要等待粗粒度锁的完成。举例来说，事务T1在对记录r加X锁之前，已有事务T2对表1进行了S表锁，那么表1上面已经存在S锁，之后事务T1试图在表1上加IX锁（获取记录r的X锁必须先获取表1的IX锁），由于不兼容，所以事务T1需要等待事务T2释放表锁。

插入意向锁

对已有数据行的修改与删除，必须排他锁，那对于数据的插入，是否还需要加这么强的锁，来实施互斥呢？插入意向锁，孕育而生。

插入意向锁，是间隙锁(Gap Locks)的一种（所以，也是实施在索引上的），它是专门针对insert操作的。

它的用处是：多个事务，在同一个索引上插入记录时，如果插入的位置不冲突，不会阻塞彼此。

示例

在MySQL，InnoDB，RR下：

t(id unique PK, name);

 

数据表中有数据：

10, shenjian

20, zhangsan

30, lisi

 

事务A先执行，在10与20两条记录中插入了一行，还未提交：

insert into t values(11, xxx);

 

事务B后执行，也在10与20两条记录中插入了一行：

insert into t values(12, ooo);

 

(1)会使用什么锁？

(2)事务B会不会被阻塞呢？

 

回答：虽然事务隔离级别是RR，虽然是同一个索引，虽然是同一个区间，但插入的记录并不冲突，故这里：

使用的是插入意向锁

并不会阻塞事务B

自增锁

自增锁是MySQL一种特殊的锁，如果表中存在自增字段，MySQL便会自动维护一个自增锁。

在InnoDB存储引擎的内存结构中，对每个含有自增长值的表都有一个自增长计数器。当对含有自增长计数器的表进行插入操作时，这个计数器会被初始化，执行如下操作来得到计数器的值：

select max(auto_inc_col) from t for update

插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式成为Auto-Inc Locking。这种锁其实是采用一种表锁的机制，为了提高插入的性能，自增长锁不是在一个事务完成以后才释放，而是在完成自增长值插入的SQL后立即释放。

虽然Auto-Inc Locking从一定程度上提高了并发插入的效率，但还是存在一些性能上的问题。对于有自增长值的列的并发插入性能较差，事务必须等待前一个插入的完成（虽然不用等待事务的完成）。

从MySQL5.12版本开始，InnoDB存储引擎提供了一种轻量级互斥量的自增长实现方式。这种方式大大提高了自增长值插入的性能。并且从该版本开始，InnoDB存储引起提供了一个参数innodb_innodb_autoinc_lock_mode来控制自增长模式，该参数的默认值为1。首先看下自增长的插入分类，如下图：

下图展示了innodb_innodb_autoinc_lock_mode的不同值对自增的影响：（值为1、2的时候，看不懂。。）

InnoDB存储引擎中自增长的实现和MyISAM不同。MyISAM存储引擎是表锁设计，自增长不用考虑并发插入的问题。在InnoDB存储引擎中，自增长值的列必须是索引，同时必须是索引的第一个列，如果不是第一个列，则MySQL会抛出异常。MyISAM存储引擎没有这个问题。

参考：http://blog.itpub.net/15498/viewspace-2141640/

外键与锁

如果没有为外键显式添加索引，InnoDB自动为外键创建索引，这样子避免表锁。

对于外键值的插入或更新，首先需要查询父表中的记录，即select父表。但不使用一致性非锁定读，因为这样子会发生数据不一致的问题。因此这时使用的是select…lock in share mode，即主动对父表加一个共享锁（这也解释了为了外键一定要索引，如果没有索引，就只能使用表锁了）。如果这时父表已经加了X锁，子表上面的操作将会被阻塞，如下图：

MVCC

概述

MVCC数据多版本又称为一致性非锁定读。指InnoDB通过行多版本控制的方式来读取当前数据库中行的数据。如果读取的行正在执行delete或者update操作，这时读操作不会因此去等待行上锁的释放。相反的，InnoDB存储引擎会去读取行的一个快照数据。

在默认配置下，即事务的隔离界别为**REPEATABLE READ（可重复读）**模式下，InnoDB存储引擎的SELECT操作使用一致性非锁定读。

快照数据是指该行的之前版本的数据，该实现是通过undo段来完成。而undo用来在事务中回滚数据，因此快照数据本身是没有额外的开销。此外读取快照数据是不需要上锁的，因为没有事务需要对历史的数据进行修改操作。

非锁定度机制极大的提高了数据库的并发性。这是InnoDB默认的读取方式，即读取不会占用表上的锁。但是在不同事务隔离界别下，读取的方式不同，并不是在每个事务隔离界别下都是采用非锁定的一致性读。此外，即使都是使用非锁定的一致性读，但是对于快照数据的定义也是各不相同。

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

在事务隔离界别read committed 和 repeatable read（InnoDB默认的事务隔离界别）下，InnoDB使用非锁定一致性读。然而，对于快照数据的定义却不相同。

• 在read committed隔离级别下，非一致性读总是读取被锁定行的最新一份快照数据（如果没有被锁定，则读取行的最新数据；如果行锁定了，则读取该行的最新一个快照）。
• 在repeatable read事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的快照。

MVCC的优缺点

MVCC在大多数情况下代替了行锁，实现了对读的非阻塞，读不加锁，读写不冲突，极大的提高了读效率。

缺点是每行记录都需要额外的存储空间，需要做更多的行维护和检查工作。

注意写写不能并行。一个事务的写操作是会加排他锁的，其他事务如果对该记录进行修改，必须等待之前的事务释放锁。

MVCC的实现原理

undo log

undo log是为回滚而用，具体内容就是复制事务开始前的行到undo buffer，在适合的时间把undo buffer中的内容刷新到磁盘。undo buffer与redo buffer一样，也是环形缓冲，但当缓冲满的时候，undo buffer中的内容会也会被刷新到磁盘；与redo log不同的是，磁盘上不存在单独的undo log文件，所有的undo log均存放在主ibd数据文件中（表空间），即使客户端设置了每表一个数据文件也是如此。在Innodb中，undo log被划分为多个段，具体某行的undo log就保存在某个段中，称为回滚段。可以认为undo log和回滚段是同一意思。

为了便于理解MVCC的实现原理，这里简单介绍一下undo log的工作过程

在不考虑redo log 的情况下利用undo log工作的简化过程为：

序号	动作
1	   开始事务
2		 记录数据行数据备份到undo log
3		 更新数据
4		 将undo log写到磁盘
5		 提交事务

（1）undo log的持久化必须在在数据持久化之前，这样才能保证系统崩溃时，可以用undo log来回滚事务

（2）Innodb通过undo log保存了已更改行的旧版本的快照。

（3）提交事务做的事情有：写redo log和binlog，并且把数据持久化到磁盘（可以通过参数控制）

InnoDB中的隐藏列

InnoDB的内部实现中为每一行数据增加了三个隐藏列用于实现MVCC。

列名	长度(字节)	作用
DB_TRX_ID	6	插入或更新行的最后一个事务的事务ID。（删除视为更新，将其标记为已删除）
DB_ROLL_PTR	7	写入回滚段的撤消日志记录（若行已更新，则撤消日志记录包含在更新行之前重建行内容所需的信息）
DB_ROW_ID	6	行ID（隐藏单调自增id）

一行记录的结构如下：

数据列	…	DB_ROW_ID	DB_TRX_ID	DB_ROLL_PTR

MVCC的SQL规则

MVCC只在READ COMMITED 和 REPEATABLE READ 两个隔离级别下工作。READ UNCOMMITTED总是读取最新的数据行，而不是符合当前事务版本的数据行。而SERIALIZABLE 则会对所有读取的行都加锁。另外事务的版本号是递增的。

SELECT

InnoDB只查找 行的事务ID 小于当前事务ID 的数据行（避免幻读）

INSERT

新插入的每一行保存当前事务ID作为行的事务ID

DELETE

删除的每一行保存当前事务ID作为行的事务ID

UPDATE

实际上是删除旧行，插入新行。

保存当前的事务ID作为新行的事务ID，同时保存当前事务ID到旧行的事务ID。

MVCC插入示例

F1_{F6是字段名称，1}6是对应的数据。后面3个隐藏字段分别对应行ID、事务ID、回滚指针。

初始状态

假如有一条初始的数据，可以认为行ID为1，其他两个字段为空。

事务1更改该行的值

当事务1更改该行的值时，会进行如下操作：

• 用排他锁锁定该行
• 把该行修改前的值复制到undo log，即上图中下面的行
• 使回滚指针指向undo log中的行
• 修改当前的行的值，填写事务编号
• 释放锁

事务2更改该行的值

与事务1相同，此时undo log中有2条记录，并且通过回滚指针连在一起。

因此，如果undo log一直不删除，则可以通过当前记录的回滚指针回溯到该行创建时的初始内容，所幸的是在InnoDB中存在清理线程，它会查询比现在最老的事务还早的undo log，并删除它们，从而保证undo log文件不会无限增长。

read view

主要用来判断当前版本数据的可见性。

在InnoDB中，创建一个新事务的时候，InnoDB会将当前系统中的活跃事务列表（trx_sys->trx_list）创建一个副本（read view），副本中保存的是系统当前不应该被本事务看到的其他事务id列表。当用户在这个事务中要读取该行记录的时候，innodb会将该行当前的版本号与该read view进行比较。
具体的算法如下:

1. 设该行的当前事务id为trx_id_0，read view中最早的事务id为trx_id_1, 最迟的事务id为trx_id_2。
2. 如果trx_id_0< trx_id_1的话，那么表明该行记录所在的事务已经在本次新事务创建之前就提交了，所以该行记录的当前值是可见的。跳到步骤6.
3. 如果trx_id_0>trx_id_2的话，那么表明该行记录所在的事务在本次新事务创建之后才开启，所以该行记录的当前值不可见.跳到步骤5。
4. 如果trx_id_1<=trx_id_0<=trx_id_2, 那么表明该行记录所在事务在本次新事务创建的时候处于活动状态，从trx_id_1到trx_id_2进行遍历，如果trx_id_0等于他们之中的某个事务id的话，那么不可见。跳到步骤5.
5. 从该行记录的DB_ROLL_PTR指针所指向的回滚段中取出最新的undo-log的版本号，将它赋值该trx_id_0，然后跳到步骤2.
6. 将该可见行的值返回。

需要注意的是，新建事务(当前事务)与正在内存中commit 的事务不在活跃事务链表中。

适用场景

读操作远远大于写操作的场景，因此适用于绝大多数互联网场景。

一致性锁定读

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

• select … for update
• select … lock in share mode;

select…for update对读取的行记录加一个X锁，其他事务不能对已锁定的行加上任何锁。

select…lock in share mode对读取的行记录加一个S锁，其他事务可以向被锁定的行加S锁，但是如果加X锁，则会被阻塞。

对于一致性非锁定读，即时读取的行已经被执行了select…for update，也是可以进行读取的。

select…for update或者select…lock in share mode必须在事务中，因为当事务提交了，锁也就释放了。从而避免锁没有释放，可能导致死锁的情况。

如果不加筛选条件（或者筛选条件不走索引），会升级为表锁

索引数据重复率太高会导致全表扫描：当表中索引字段数据重复率太高，则MySQL可能会忽略索引，进行全表扫描，此时使用表锁。可使用 force index 强制使用索引。

参考：https://blog.csdn.net/u012099869/article/details/52778728

锁问题

脏读

脏数据：指的是事务对缓冲池中行记录的修改，并且还没有提交。即事务未提交的数据。

脏读：指当前事务可以读到其他事务的未提交的数据。如果读到了脏数据，即一个事务可以读到另外一个事务中未提交的数据，显然违反了事务的隔离性。

脏读的条件：需要事务的隔离级别为读未提交。

示例：

解决：设置事务的隔离级别为“读已提交”或者更高的隔离界别。

​ 那为什么“读已提交”能解决脏读的问题？ 我的理解是，在“读已提交”的情况下，读取的是快照的最新版本的数据，那么既然读取的是快照数据了，那么这些数据肯定是之前的事务提交过了的。

不可重复读

不可重复读：指在在一个事务内多次读取同一个数据集合，在这个事务还没有结束时，另外一个事务也访问了同一个数据集合，并且做了一些修改或新增操作。因此，在第一个事务的两次读数据之间，由于第二个事务的修改，第一个事务两次读取到的数据可能是不一样的（具体看隔离级别）。这种称为不可重复读。

示例：

幻读：同一事务下，连续执行2次相同的sql，可能返回不同的结果，第二次的sql可能返回之前不存在的行。

丢失更新

丢失更新：指一个事务的更新操作被另外一个事务的更新操作所覆盖，从而导致数据的不一致。

丢失更新的实例：

解决办法：对用户读取的记录加上一个排他锁，这样子其他事务就必须等待前一个事务的完成。从而避免并发问题。

解决办法的示例：

阻塞

阻塞：事务因为等待其他事务释放锁而等待

超时：等待其他事务释放锁，超过超时时间，就认为是超时。

innodb_lock_wait_timeout：用来控制超时时间，默认是50秒。可以在MYSQL运行时进行设置。

innodb_rollback_on_timeout：用来设定是否在等待超时时对进行中的事务进行回滚操作。默认是OFF，不回滚。不可以在MySQL启动时进行修改。用户在超时的情况下，必须判断是是否需要commit或者rollback，之后再进行下一步的操作。

死锁

概念：死锁是指两个或者两个以上的事务，因争夺资源而造成的一种互相等待的现象。若无外力作用，所有事务都将无法推进下去。

FIFO处理死锁

解决数据库死锁最简单的方法：设置超时时间。即当两个事务互相等待时，当一个等待时间超过设置的阈值时，其中一个事务进行回滚，另外一个等待的事务就能继续执行。

超时机制虽然简单，但是其使用FIFO的方式来选择超时回滚的事务，假如第一个超时的事务 更新了很多行，远比第二个事务多，因此占用了更多的undo log，这时FIFO的方式，就显得不适用了，因为第一个事务回滚时间明显比第二个事务回滚时间长很多。

等待图

因为FIFO处理死锁可能不适用，所以数据库普遍采用了wait-for graph（等待图）的方式来进行死锁检测。和超时机制比较，这是一种更为主动的死锁检测方式，InnoDB也采用了这种方式。

等待图要求数据库保存以下两种信息：

（1）锁的信息链表（见图6-5 右）

（2）事务等待链表（见图6-5 左）

通过上述链表可以构造出一张图，而在这个图中存在回路，则代表存在死锁。在等待图中，事务为图中的节点。在图中，事务T1指向事务T2边的定义为：

（1）事务T1等待事务T2所占用的资源

（2）事务之间在等待相同的资源，而事务T1在事务T2之后

发现死锁后，InnoDB会马上回滚一个undo量最小的事务。

锁升级

概念：将当前锁的粒度降低，比如说把行锁升级为表锁，那样子会导致并发性能降低。

InnoDB不是根据每个记录来产生行锁的，而是根据每个事务访问的每个页对锁进行管理的，采用的是位图的方式，因此不管一个事务锁住页中一条还是多条记录，都是用一个锁，其开销通常是一致的。

问题

快照读、当前读指的是什么，有什么区别？

快照读:

• 读取数据的备份，不用加锁，这个数据可能是旧数据.
• 比如说普通的select语句。

当前读:

• 读取的数据最新版本

• 读数据的时候会加上锁，保证其他事务不会并发修改这条记录。

• 如select … lock in share mode

  select … for update

  ​ insert

  ​ update

  ​ delete

在RR级别下，快照读是通过MVVC(多版本控制)和undo log来实现的，当前读是通过加record lock(记录锁)和gap lock(间隙锁)来实现的。

参考

MVCC实现原理

快照读、当前读