MySQL技术内幕-InnoDB存储引擎-第六章、锁

锁

MySQL数据库InnoDB引擎行级锁锁定范围详解

• 最大程度利用并发
• 确保每个用户能一致的读和修改数据

一、什么是锁？

锁用于管理对共享资源的并发访问。InnoDB支持行锁
InnoDB其他多个地方也使用了锁，比如操作LRU列表，删除、添加、移动LRU列表中的元素。
数据库使用锁就是为了支持对共享资源进行并发访问，提供数据的完整性和一致性。

不同数据库的锁的实现可能完全不同。
MyISAM锁锁表锁，并发情况下，读没有问题，但是在并发插入的时候性能就差一点。

二、lock和latch

latch(mutex互斥锁、rwlock读写锁)

latch一般称为闩(shuan)锁，是一种轻量级的锁，因为其要求锁定的时间非常短，如果持续时间长，性能会非常差，没有死锁检查机制。
InnoDB中的latch分类：

• mutex(互斥锁)
• rwlock(读写锁)

lock

lock的对象是事务，用来锁定的是数据库中的对象，如表、页、行。

并且lock的对象仅仅在事务commit或者rollback后进行释放(不同事务隔离级别释放的时间可能不同)。有死锁机制。

可以使用show engine innodb mutex。
结果的参数说明：

三、InnoDB存储引擎中的锁

1、锁的类型

两个标准的行级锁：

• 共享锁：允许事务读一行数据
• 排他锁：允许事务删除或更新一行数据

共享锁和排他锁不能同时有，排他锁和排他锁不能同时有。

InnoDB支持多粒度加锁，这种锁定允许事务在行和表级别上的锁同时存在，为了支持不同粒度上进行加锁，InnoDB支持意向锁。
意向锁将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

如果要对页上的记录进行上X锁，那么分别需要对数据库、表、页上意向锁IX，最后对这条记录上X锁。其中任何一部分导致等待，那么该操作需要等待粗粒度锁的完成，比如需要对表进行了S表锁，之后事务需要在该表加IX锁，那么事务需要等待表锁操作的完成。

意向锁为表级别的锁，设计的目的主要是为了在一个事务中揭示下一行被请求的锁类型，其支持两种意向锁：

• 意向共享锁(IS Lock)，事务想要获得一张表中某几行的共享锁
• 意向排他锁(IX Lock)，事务想要获得一张表某几行的排他锁

表级意向锁和行级锁的兼容性如下：

可以通过show engine innodb status;命令来查看当前锁请求的信息。

RECORD LOCKS space id 30 page no 3 bits 72 index ‘PRIMARY’ of table ‘test’.‘t’ trx id 48B89BD lock_mode X locks rec but not gap.表示锁住的资源，locks rec but not gap表示锁住的锁一个索引，不是一个范围。

在InnoDB1.0之前，使用命令SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS等来查看当前数据库中锁的请求，从InnoDB1.0开始，在INFORMATION_SCHEMA架构下添加了表INNODB_TRX、INNODB_LOCKS、INNODB_LOCK_WAITS，通过这三张表可以更简单监控当前事务并分析可能存在的锁问题。

INNODB_TRX的字段定义：

上面的表只能显示当前运行的InnoDB事务，并不能直接判断锁的一些情况，如果需要查看锁，还需要访问表INNODB_LOCKS，该字段组成如表6-6所示：

lock_data这个值并非是“可信”的值。例如用户运行一个范围查找的时候，lock_data可能只返回第一行的主键值。

INNODB_LOCK_WAITS可以直观反映当前事务的等待。表INNODB_LOCK_WAITS由4个字段组成。

从上面可以直观看到哪个事务阻塞了另外一个事务。

2、一致性非锁定读(使用MVCC，当行上正在执行其他操作，可以读取行的快照，不需等待)

一致性的非锁定读指的是InnoDB存储引擎通过行多版本控制的方法来读取当前执行时间数据库中行的数据。

如果读取的行正在执行DELETE或者UPDATE操作，那么这个时候读取操作不回因此去等待行上锁的释放，相反的，InnoDB存储引擎会去读取行的一个快照数据。

快照数据指的是之前版本的数据，该实现是通过undo段来完成，而undo用来在事务中回滚数据。
非锁定读大大提高了数据库的并发性，快照数据其实就是当前行数据的历史版本，每行记录可能有多个版本即多个快照数据，一般称这种技术为行多版本技术。由此带来的并发控制称为多版本并发控制。

在可重复读和已提交读隔离级别下，InnoDB使用非锁定的一致性读，但是他们对于快照的定义不同，在READ COMMITTED事务隔离级别下，对于快照数据，非一致性读总是读取被锁定行的最新一份快照数据，而在REPEATABLE READ事务隔离级别下，对于快照数据，非一致性读总是读取事务开始时的行数据版本。
一个是看到的是最新的版本数据，一个看到的是事务开启时候的版本数据。

3、一致性锁定读(select…for update、select … in share mode)

前面讲了select操作使用了一致性非锁定读，但是在某些情况下，用户需要对数据库读取操作进行加锁以保证数据逻辑的一致性。
InnoDB对select语句支持两种一致性的锁定读操作：

• select … for update（对锁定的行加X锁）
• select … lock in share mode（对锁定的行加S锁）

对于一致性非锁定读，即使读取的行已经被执行了select … for update，也是可以进行读取的，此外select … for update、select … lock in share mode必须在一个事务中，当事务提交，锁被释放，因此必须开启事务，如begin、start transaction或者set autocommit = 0;

4、自增长与锁

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

• select MAX(auto_inc_col) from t for update;

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

虽然AUTO-INC Locking从一定程度提高了并发插入的效率，但是还是存在一些性能问题：

• 对于自增长列的并发插入性能比较差，事务必须等待前一个插入的完成
• 对于INSERT…SELECT的大数据量的插入会影响插入的性能，因为另一个事务中的插入会阻塞

从MySQL5.1.22开始，InnoDB提高了轻量级互斥的自增长实现机制。大大提高了自增长值插入的性能。InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式。默认值为1。

对自增长的分类

在InnoDB存储引擎中，自增长值必须是索引，同时必须是索引的第一个列。如果不是，MySQL会抛出异常，MyISAM不会有这个问题

5、外键和锁

前面介绍了外键，外键主要用于引用完整性的约束检查，在InnoDB存储引擎中，对于一个外键列，如果没有显式对这个列加索引，InnoDB存储引擎会自动对其加1个索引，用户必须自己手动添加，这也导致了Oracle数据库中可能产生死锁。

四、锁的算法

1、行锁的3种算法(Next-Key Lock在索引唯一时，InnoDB会将Next-Key Lcok降级为Record Lock)

间隙锁只会阻止insert语句，record锁可能select也会阻止。

InnoDB种3种行锁的算法，分别是：

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

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

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

• （-无穷，10]、(10,11]、(11,13]、(13,20]、(20,+无穷)

除了next-key locking，还有previous-key locking技术，同样上述的索引10，11，13，20,如果使用previous-key locking，那么可以锁定的区间为：

• (-∞，10)、[10，11)、[11，13)、[13，20)、[20，+∞)

然而，当查询的索引有唯一属性的时候，InnoDB会将Next-Key Lcok进行优化，降级为Record Lock。即锁住索引本身而不是范围。如下面，并没有锁定(2，5)这个范围，而是降级为了Record Lock。
下面是Gap Lock降级为Record Lock的例子。

如果查询的是辅助索引

• select * from z where b = 3 for update;

很明显，这个SQL语句通过索引列b进行查询，因此其使用传统的Next-Key Locking技术加锁，并且由于有两个索引，其需要分别进行锁定，对于聚集索引，仅需要在a=5上加Record Lock，对于辅助索引，加上Next-Key Lock，锁定的范围是(1,3)，特别注意的是InnoDB还会对下一个键值加上gap Lock,即还有一个辅助索引范围(3,6)的锁。

对于下面的三条语句：

上面这三条都会被阻塞。

上面的3条不会被阻塞，会立即执行。

Gap Lock的作用是为了组织多个事务将记录插入到同一个范围内，而这个会导致Phantom Problem(幻读)问题的产生。比如在上面的例子中，会话A用户锁定了b=3的记录，如果没有Gap Lock锁定(3,6)，那么用户可以插入索引b列为3的记录，这回导致会话A中的用户再次执行同样查询时会返回不同的记录，导致幻读问题。

用户可以通过下面的方式关闭Gap Lock：

• 将事务的隔离级别改成已提交读
• 将参数innodb_locks_unsafe_for_binlog设置为1.

在InnoDB中，对于Insert操作，会检查插入记录的下一条记录谁都被锁定，如果已经被锁定，不允许插入。比如说锁定b=3，那么(1,3)范围不可以插入会阻塞，因为下一个记录是3.

2、解决Phantom Problem(幻读)

在默认的事务隔离级别下，可重复读下，InnoDB使用Next-Key Locking机制来避免幻读。或者使用可串行化隔离级别。
可串行化感觉就是一个开启两个事务，如果一个查了，另外一个事务就不能做insert操作，因为可能会导致幻读，再比如一个事务只要insert了，另外一个事务就不能查,会阻塞

幻读是指在同一事务下，连续执行两次同样的SQL可能会导致不同的结果，第二次的SQL语句可能会返回之前不存在的行。

show create table t5;
+-------+---------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                            |
+-------+---------------------------------------------------------------------------------------------------------+
| t5    | CREATE TABLE `t5` (
  `id` int(11) DEFAULT NULL,
  KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+----------------------------------------------------------------

幻读例子：

时间	Session A	Session B	备注
1	beigin;select id from t5 where id>4; //结果输出一行，id为7
2		begin;insert into t5 values(8);
3	select id from t5 where id>4; // 结果输出一行，id为7		这里事务Session B没有提交，不管是Read COMMITTED还是REPEATABLE READ都是不可见的
4		commit
5	select id from t5 where id>4; //结果输出一行，id为7		当前的事务隔离级别是REOEATABLE，InnoDB通过MVCC机制提供一致性非锁定读，故当前事务仍然不可见Session B事务
6	update t5 set id=10 where id=8;		诡异现象出现了，这里update的语句竟然可以看到Session B的提交
7	select id from t5 where id>4; //会看到两行。		Session A再次查询的时候就可以查看新的update的值，当然这里是合理的，因为在一个事务中，应该能看到本事务的修改。
8	commit

在已提交读隔离级别下，采用的是Record
Lock的方式加锁，而在可重复读下使用的是Next-Key Lock方式加锁。

可串行化如果开启两个事务，一个事务比如想插入一个数据，必须等待另外一个事务commit或者rollback。这样的化，并发量很低。在可重复读下，使用for update也是使用的是Next-Key Lock但是只是会锁住一部分Gap，不会锁住全部，而可串行化会锁住全部。

五、锁问题

通过锁机制可以实现事务的隔离性要求，使得事务可以并发工作。但是却带来了潜在的问题，锁智慧带来三种问题，如果解决三种情况的发生，就不会产生并发异常。

1、脏读

脏数据：事务对缓冲池中行记录的修改，并没有被提交。

如果读到了脏数据，即一个事务可以读到另外一个事务未提交的数据，违反了数据库的隔离性。

脏读发生的事务隔离级别是READ UNCOMMITTED，目前大部分数据库都至少设置为READ COMMITTED。InnoDB默认的隔离级别是READ REPEATABLE。

2、不可重复读

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

一般来说，不可重复读是可以接受的，因为其读到的是已经提交的数据，本身不会带来什么问题。

在InnoDB存储引中，使用Next-Key Lock算法来避免不可重复读的问题即幻读问题。在Next-Key Lock下，对于索引的扫描，不仅是锁住了扫描到的索引，而且锁住了索引之间的范围。因此在这个范围内的插入都是不允许的。

3、丢失更新

简单的说就是一个事务的更新操作会被另一个事务的更新操作覆盖。从而导致数据的不一致。如：

• 事务T1将行记录r更新为v1，但是事务T1没有提交
• 此时事务T2将行记录r更新为v2，事务T2未提交
• 事务T1提交
• 事务T2提交

在当前数据库的任何隔离级别下，都不会出现丢失更新问题，这是因为，即使READ UNCOMMITTED的事务隔离级别，对于行的DML操作，需要对行或者其他粗粒度级别的对象加锁，因此上面的第二个步骤中，事务T2并不难对行记录r进行更新操作，其会被阻塞，直到事务T1提交。

下面的情况会出现丢失更新：

• 事务T1查询一行数据，放入本地内存，并显示给一个终端用户User1。
• 事务T2也查询该行数据，并将取得的数据显示给终端用户User2。
• User1修改这行数据，更新数据库并提交
• User2修改这行数据，更新数据库并提交

如果用户User1的修改更新操作丢失了，可能导致严重问题。设想银行发生丢失更新现在，如果一个用户账号中有10000元人民币，第一次转9000，第二次转1元，第一次转的9000因为网络和数据的原因，需要等待，如果两笔操作都成功了，用户的账号余款是9999元，第一次转的9000没得到更新。但是在转账的另外一个账户收到了9000.

将操作变成串行化，对用户读取的记录加上排他X锁。

六、阻塞

因为不同锁之间的兼容性问题，有些时刻一个事务中的锁要等待另外一个事务中的锁释放他占用的资源，这就是阻塞。

• 在InnoDB中，参数innodb_lock_wait_timeout用来控制等待的时间，默认50s

• innodb_rollback_on_timeout用来设定是否在等待超时时对进行中的事务进行回滚操作。

• innodb_lock_wait_timeout是动态的，可以在MySQL运行的时候对其进行调整

• set @@innodb_lock_wait_timeout=60

innodb_lock_wait_timeout是静态的，不可以在启动的时候进行修改。

• set @@innodb_rollback_on_timeout=on;

需要牢记的是，在默认情况下InnoDB存储引擎不会回滚超时引发的错误异常，其实InnoDB存储引擎在大部分情况下都不会对异常进行回滚，如在一个会话中执行如下语句：

会话B中插入记录5是可以的，但是插入3的时候，因为会话A中Next-Key Lock算法的关系，需要等待会话中事务释放这个资源，所以等待后产生了超时，但是再进行select的时候会发现，5这个记录依然存在，但是事务B没进行commit和rollback操作，这是非常危险的情况，因此用户必须判断是佛呀需要commit还是rollback。

七、死锁

1、死锁的概念(解决：先超时的先回滚、)

死锁指的是两个或者两个以上的事务在执行过程中，因争夺锁资源而造成的一种互相等待的现象。

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

• innodb_lock_wait_timeout用来设置超时的时间。

如果使用超时，那么是先来的事务先回滚，但是如果超时的事务权重比较大。如事务操作更新了很多行，占用了很多的undo log，这时采用FIFO的方式就不是很合适，因为回滚这个事务的时间相对另外一个事务所占的时间可能会很多。

因此，除了使用超时，当前数据库普遍使用了wait-for-graph的方式来进行死锁检查。wait-for-graph要求数据库保存下面两种信息：

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

通过上述链表可以构造一张图，而在这个图中如果存在回路，就代表存在死锁，因此资源之间发生相互等待。

可以看到t1、t2发信啊了回路，因此存在死锁，通过上面的介绍，可以看出wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断是否存在回路，如果存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最少的事务。

2、死锁概率

略

3、死锁的例子

死锁只存在于并发的情况下。死锁的经典情况A等待B、B等待A。

InnoDB存储引擎并不会回滚大部分的错误异常，但是死锁除外。发现死锁后，InnoDB存储引擎会马上回滚一个事务。

八、锁升级

锁升级指的是将当前锁的粒度降低，举例来说，数据库可以把一个表的1000个行锁升级为一个页锁，或者将页锁升级为表锁。

一下情况会发生锁升级：

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