MySQL-锁总结

锁

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

lock和latch

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

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

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

006tNc79gy1g4cmob7jx6j31eu0fg475.jpg

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

![image-20190624224824680]()

锁的类型

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

共享锁、排他锁

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

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

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

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

image-20190624225847173

从表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这四个值，那么该索引可能被锁定的区间为：

image-20190728200240969

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

image-20190728200422526

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

image-20190728200453193

当查询的索引是唯一索引的时候，InnoDB会将临键锁优化成记录锁，从而提高并发。这时候，将不再由间隙锁避免幻读的问题，但是试验了下，即使优化成记录锁，也不会有幻读的问题，其实是因为MVCC，在可重复读的情况下，SELECT操作只会查找行版本号小于当前事务版本号的记录，其他事务（事务开启时间比当前事务晚）新插入的记录版本号不满足条件，就不会查出来。

对于辅助索引，当执行类似select * from z where b = 3 for update;加锁语句时，会加上临键锁，并且下一个键值的范围也会加上间隙锁。

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

image-20190803232019608

image-20190803231957959

在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所示：

006tNc79gy1g4cndxw7kpj31690u07ou.jpg

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

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

![image-20190624233058036]()

插入意向锁

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

插入意向锁，是间隙锁(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。首先看下自增长的插入分类，如下图：

image-20190722221544377

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

image-20190722221852110

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

参考：http://blog.itpub.net/15498/v...

外键与锁

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

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

image-20190722230035134

image-20190722230121265

MVCC多版本

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

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

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

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

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

在事务隔离界别read committed 和 repeatable read（InnoDB默认的事务隔离界别）下，InnoDB使用非锁定一致性读。然而，对于快照数据的定义却不相同。对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据（如果没有被锁定，则读取行的最新数据；如果行锁定了，则读取该行的最新一个快照）。而在repeatable read事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。

MVCC的优缺点

MVCC在大多数情况下代替了行锁，实现了对读的非阻塞，读不加锁，读写不冲突。缺点是每行记录都需要额外的存储空间，需要做更多的行维护和检查工作。注意写写不能并行。

MVCC的实现原理

undo log

undo log是为回滚而用，具体内容就是copy事务前的数据库内容（行）到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         将数据写到磁盘
6         提交事务

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

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

redo log

redo log就是保存执行的SQL语句到一个指定的Log文件，当MySQL执行recovery（修复）时重新执行redo log记录的SQL操作即可。当客户端执行每条SQL（更新语句）时，redo log会被首先写入log buffer；当客户端执行COMMIT命令时，log buffer中的内容会被视情况刷新到磁盘。redo log在磁盘上作为一个独立的文件存在，即Innodb的log文件。

Innodb中的隐藏列

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

一行记录的结构如下：

MVCC工作过程

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

SELECT

InnoDB 会根据两个条件来检查每行记录：

• InnoDB只查找版本(DB_TRX_ID)早于当前事务版本的数据行（行的系统版本号<=事务的系统版本号,这样可以确保数据行要么是在开始之前已经存在了，要么是事务自身插入或修改过的）
• 行的删除版本号(DB_ROLL_PTR)要么未定义（未更新过），要么大于当前事务版本号（在当前事务开始之后更新的）。这样可以确保事务读取到的行，在事务开始之前未被删除。

INSERT

InnoDB为新插入的每一行保存当前系统版本号作为行版本号

DELETE

InnoDB为删除的每一行保存当前的系统版本号作为行删除标识

UPDATE

innodb为插入一行新纪录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标示。

MVCC插入示例

image-20190803133625261

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

初始状态

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

事务1更改该行的值

image-20190803134019783

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

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

事务2更改该行的值

image-20190803134329790

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

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

事务提交

当事务正常提交时，InnoDB只需要更改事务状态为COMMIT即可，不需要做其他额外的工作，而回滚则复杂一点，需要根据回滚指针找出事务修改前的版本，并且恢复。如果事务影响的行非常多，回滚则可能变得效率不高。

一致性非锁定读(见共享锁、排他锁)

在某些情况下，用户需要显式的对数据库读取操作进行加锁以保证数据逻辑的一致性。而这要求数据库支持加锁语句。InnoDB存储引擎对于SELECT语句支持两种一致性的锁定度操作：

• select … for update 共享锁(S锁, share locks)。其他事务可以读取数据，但不能对该数据进行修改，直到所有的共享锁被释放。

​ 如果事务对某行数据加上共享锁之后，可进行读写操作；其他事务可以对该数据加共享锁，但不能加排他锁，且只能读数据，不能修改数据。

• select … lock in share mode

​ 如果事务对数据加上排他锁之后，则其他事务不能对该数据加任何的锁。获取排他锁的事务既能读取数据，也能修改数据。

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

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

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

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

参考：https://blog.csdn.net/u012099...

锁问题

脏读

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

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

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

示例：

image-20190804114458983

不可重复读

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

示例：

image-20190804114524677

丢失更新

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

丢失更新的实例：

image-20190804120327921

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

解决办法的示例：

image-20190804120415922

阻塞

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

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

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

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

死锁

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

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

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

等待图

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

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

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

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

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

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

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

image-20190804134158816

image-20190804134632126

发现死锁后，InnoDB会马上回滚一个事务。

锁升级

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

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

image-20190804140229826