MySQL技术内幕InnoDB存储引擎 学习笔记 第六章 锁

本文主要是介绍MySQL技术内幕InnoDB存储引擎 学习笔记 第六章 锁，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

锁是数据库系统区别于文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。

InnoDB引擎会对表数据上锁以提供数据的完整性和一致性，除此之外，还会对数据库内部其他多个地方使用锁，从而保证对多种不同资源提供并发访问，如增删改LRU列表中的元素。

不同数据库和引擎使用的锁机制的实现可能完全不同。对MyISAM来说，其锁是表锁，并发读没有问题，但并发插入性能较差，如果插入是在底部的情况，MyISAM还是可以有一定并发操作。对于SQL server，在2005版之前都是页锁，相对于MyISAM的表锁性能有所提高，到2005版本开始支持乐观并发和悲观并发，在乐观并发下开始支持行级锁，但其实现方式与MySQL完全不同。

InnoDB引擎锁的实现与Oracle非常类似，提供一致性读、行级锁支持，行级锁没有相关的开销，可同时得到并发性和一致性。

InnoDB中实现了两种行级锁：
1.共享锁（S Lock）：允许事务读一行数据。
2.排他锁（X Lock）：允许事务删除或更新一行数据。

锁兼容：一个事务已经获取了行r的共享锁，另外的事务可以立即获得行r的共享锁，因为读取没有改变行r的数据。此时如果有事务想获得行r的排他锁，则必须等待事务释放行r上的共享锁，此种情况称为锁不兼容。

InnoDB引擎支持多粒度锁定，允许行级锁和表级锁同时存在。InnoDB支持一种额外的锁方式，称为意向锁，意向锁是表级别的锁，设计目的是表明某个事务正在某一行上持有锁，或者准备去持有锁，有两种意向锁：
1.意向共享锁（IS Lock）：事务想要获取一个表中某几行的共享锁。
2.意向排他锁（IX Lock）：事务想要获取一个表中某几行的排他锁。

事务在请求S锁和X锁前，需要先获得对应的意向锁。

查看当前请求锁的信息：


在InnoDB Plugin之前，只能通过SHOW FULL PROCESSLIST、SHOW ENGINE INNODB STATUS等命令查看当前数据库请求，再判断当前事务中锁的情况。在新版本中，information_schema架构下添加了innodb_trx、innodb_locks、innodb_lock_waits三张表，可更简单地监控当前事务并分析可能存在的锁问题。首先看innodb_trx表中的字段：
1.trx_id：InnoDB引擎内部唯一的事务ID。
2.trx_state：当前事务状态。
3.trx_started：事务的开始时间。
4.trx_requested_lock_id：等待事务的锁ID，如果trx_state状态为LOCK WAIT，则该值代表当前事务等待的锁资源ID，该锁资源正在被其他事务占用。如trx_state不是LOCK WAIT，则该值为NULL。
5.trx_wait_started：事务等待开始的时间。
6.trx_weight：事务的权重，反映了一个事务修改和锁住的行数。发生死锁需要回滚时，InnoDB引擎会选择该值最小的事务进行回滚。
7.trx_mysql_thread_id：MySQL中线程id。
8.trx_query：事务运行的SQL语句，该值实际使用时有时会显示为NULL。

innodb_trx表只能显示当前运行的事务，不能判断锁的情况，使用innodb_locks表查看锁，该表中字段如下：
1.lock_id：锁ID。
2.lock_trx_id：事务ID。
3.lock_mode：锁的模式。
4.lock_type：锁的类型，表锁还是行锁。
5.lock_table：加锁的表。
6.lock_index：锁的索引。
7.lock_space：表空间ID。
8.lock_page：被锁住的页的数量，表锁时此值为NULL。
9.lock_rec：被锁住的行的数量，表锁时此值为NULL。
10.lock_data：被锁住的行的主键值，表锁时此值为NULL。此值不是可靠的值，当进行范围查找时，lock_data可能只是第一行的主键值。如果当前资源被锁住，且缓冲池中该被锁住资源所在的页被替换出内存，此时再查看innodb_locks表，该值会显示为NULL。

查出了每张表上的锁情况，就能判断锁的等待情况了，如果事务量太大，还是不容易判断，此时可通过innodb_lock_waits表查看等待情况，该表由以下字段组成：
1.requesting_trx_id：申请锁资源的事务ID。
2.requesting_lock_id：申请的锁的ID。
3.blockint_trx_id：当前占用此锁的事务ID。
4.blocking_lock_id：当前被占用的锁的ID。

可执行以下联合查询直观地看详细信息：

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query
FROM information_schema.innodb_lock_waits w 
INNER JOIN information_schema.innodb_trx b 
ON b.trx_id = w.blocking_trx_id 
INNER JOIN information_schema.innodb_trx r 
ON r.trx_id = w.requesting_trx_id;

INNER JOIN等于JOIN，含义为选取两表的共有部分。运行结果如下：


一致性的非锁定行读指InnoDB通过行多版本控制的方式读取请求时间点时的数据库中行的数据。如果读取的行正执行DELETE、UPDATE操作，此时读操作不会等待行上锁的释放，而是会去读取行的一个快照数据：

之所以称其为非锁定读，是因为不需要等待要访问的行上的X锁的释放。快照数据是该行之前版本的数据，这是通过Undo段实现的，而Undo段原本用来在事务中回滚数据，因此快照数据本身没有开销。读快照数据不需要上锁，因为没有必要对历史的数据进行修改。

非锁定性读提高了数据读取的并发性，在InnoDB引擎的默认设置下，这是默认的读取方式，但在不同事务隔离级别下，读取的方式不同，并不是每个事务隔离级别下读取都是一致性读，即使都是使用一致性读，对于快照数据的定义也不同。

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

在Read Committed和Repeatable Read（InnoDB引擎默认事务隔离级别）下，InnoDB使用非锁定的一致性读，但它们对于快照的定义不同，Read Committed事务隔离级别下，非一致性读总是读取被锁定行的最新一份快照数据，在Repeatable Read事务隔离级别下，非一致性读总是读取事务开始时的行数据版本。

假如有两个事务，A事务如下：

此时事务A已开始，并读取了id为1的行数据，但事务并没有结束，此时在事务B中做以下修改：

同样事务B也未提交，此时id为1的行上加了一个X锁，如果此时再在事务A中读取id为1的行，如果InnoDB引擎的事务隔离级别现在是Read Committed或Repeatable Read，会使用非锁定的一致性读读取快照内容，结果如下（假设这段时间只有这两个事务，此时会只有一个版本的快照数据）：

如果此时事务B提交了事务：

如果此时事务A再读取id为1的数据，不同事务隔离级别下结果就不同了：
1.如果是Read Committed事务隔离级别：

它总是会读取行的最新版本，如果行被锁定了，则读取最新的快照，因此会读不到id为1的行数据：

2.如果是Repeatable Read事务隔离级别：

它总是会读取事务开始时的行数据，因此此时A应该还能读取到id为1的行：

从时间角度展现上述示例：

对于Read Committed事务隔离级别，它违反了ACID中的隔离性。

默认，InnoDB引擎的SELECT操作使用一致性非锁定读，但某些情况下需要对读取操作进行加锁：
1.SELECT … FOR UPDATE：对读取的行记录加一个X锁，其他事务想在这些行上加任何锁都会被阻塞。
2.SELECT … LOCK IN SHARE MODE：对读取的行记录加一个S锁，其他事务可以向被锁定的记录加S锁，但会阻塞加X锁的事务。

以上两条语句在事务中使用时，事务提交了，锁也就释放了。

对于一致性非锁定读，即使读取的行已经被使用SELECT … FOR UPDATE语句加锁了，也是可以进行读取的。

InnoDB引擎对于每个含有自增长值的表都有一个自增长计数器，得到计数器的值：

SELECT MAX(auto_inc_col)
FROM tableName
FOR UPDATE;

插入操作会将这个计数器值加1赋予自增长列，实现方式为AUTO-INC Locking，这种实现方式采用一种特殊的表锁机制，为提高插入性能，锁不是在一个事务结束后才释放，而是完成对自增长值插入的SQL后立即释放。这种方式对于有自增长的列的并发插入性能较差，需要等待前一个插入的完成，对于INSERT … SELECT这种大数据量的插入，会令其他插入操作被阻塞。

从MySQL 5.1.22开始，InnoDB引擎提供了一种轻量级互斥量的自增长实现机制，大大提高了自增长值插入的性能，此版本开始，InnoDB引擎提供了参数innodb_autoinc_lock_mode，默认值为1，以下是自增长列的几种插入情况：
1.INSERT-like指所有的插入语句，如INSERT、REPLACE、INSERT … SELECT、REPLACE … SELECT、LOAD DATA等。
2.Simple inserts指能在插入前确认插入行数的语句，如INSERT、REPLACE等，但不包括INSERT … ON DUPLICATE KEY UPDATE（MySQL特有语句，当insert的记录的主键在表中冲突时，执行Update）这类SQL语句。
3.Bulk inserts指在插入前不能确定插入行数的语句，如INSERT … SELECT、REPLACE … SELECT、LOAD DATA语句。
4.Mixed-mode inserts指插入中有一部分值是自增长的，如INSERT语句同时插入多行数据时，有一部分自增列值给出、一部分为NULL值或INSERT … ON DUPLICATE KEY UPDATE时。

参数innodb_auoinc_lock_mode的可选值：
1.0是5.1.22版本之前自增长的实现方式，即通过表锁的AUTO-INC Locking方式。
2.1是参数默认值，对于Simple inserts，该值使用互斥量对内存中的计数器进行累加操作，对于Bulk inserts，还是使用传统的AUTO-INC Locking方式。此时，不考虑回滚操作，自增值的增长还是连续的。这种情况下，如果先使用了AUTO-INC Locking方式产生自增长的值，在该INSERT语句还未结束时，Simple inserts的操作还是会等待AUTO-INC Locking的释放。
3.2是令所有INSERT-like的自增长值的产生都是通过互斥量，这是性能最高的方式，但会带来一些问题，由于并发插入的存在，每次插入时，自增长的值可能不是连续的，如果主从复制时，使用的是Statement-Base Replication（主从库使用相同的SQL语句），可能会由于并发使得主从库的自增列值不同，此时应使用Row-Base Replication（主从库会做相同更改，binlog中记录的是行的改变而非SQL语句）。

MyISAM使用的是表锁，自增长不用考虑并发插入问题。

InnoDB引擎中，自增长的列必须是索引列，如果是联合索引，则必须是联合索引的第一个列，如果是第二个列则会报错，MyISAM没有这个问题：

CREATE TABLE tab (a    INT   auto_increment,b    INT,KEY(b, a)
) ENGINE = InnoDB;

运行它：

对于外键列，如果没有显式对这个列加索引，InnoDB引擎会自动对其添加一个索引，这样可以避免表锁。

对外键列的插入或更新，需要先查询父表中的记录，即SELECT父表，对于父表的SELECT，不使用一致性非锁定读，这样会发生数据不一致的问题，它会使用SELECT … LOCK IN SHARE MODE，对父表加一个S锁，如果此时父表上已经加X锁了，子表上的操作会被阻塞：

上图情况下，两个事务都没有COMMIT或ROLLBACK，此时事务B会被阻塞，因为事务A在父表的id=3的行上加了一个X锁，而事务B需要在父表上id=3的行加一个S锁，事务B会被阻塞。如果事务B访问父表时使用的是一致性非锁定读，在InnoDB的默认事务隔离级别Repeatable Read情况下，会读到父表中有id为3的行，可以进行插入操作，而事务A提交后，父表中就没有了id为3的记录，会出现父子表不一致的情况。如果此时查询innodb_locks表：


InnoDB引擎有三种行锁算法设计：
1.Record Lock：单个行记录上的锁。
2.Gap Lock：间隙锁，锁定一个范围，不包含记录本身。
3.Next-Key Lock：相当于1与2一起使用，可锁定一个范围，且锁定记录本身。是结合了Gap Lock和Record Lock的一种锁定算法，对于不同SQL查询语句，可能设置共享的Next-Key Lock和排他的Next-Key Lock。

Record Lock总是会锁定索引记录，如果InnoDB引擎表建立时没有设置索引，会使用InnoDB引擎隐式主键进行锁定。

演示Next-Key Lock，先创建表：

CREATE TABLE nkl (a    INT,PRIMARY KEY(a)
) ENGINE = InnoDB;

向表中插入以下数据：

BEGIN;INSERT INTO nkl 
SELECT 1;INSERT INTO nkl 
SELECT 2;INSERT INTO nkl 
SELECT 3;INSERT INTO nkl 
SELECT 4;INSERT INTO nkl 
SELECT 7;INSERT INTO nkl 
SELECT 8;COMMIT;

接着执行以下两个事务：

在以上情况下，事务B无论插入的是5还是6都会被锁定，因为在Next-Key Lock算法下，Gap Lock锁定的是(-∞, 6)区间内所有数值，Record Lock锁定6，此时插入9是可以的。而对于单个值的索引查询，不需要用到Gap Lock，此时Next-Key Lock优化为Record Lock：

上例演示过程是在InnoDB默认事务隔离级别下进行的，即在Repeatable Read模式下Next-Key Lock算法是默认的行记录锁定算法。

锁可能带来以下三种问题：
1.丢失更新：多用户同时修改一条记录时，可能会丢失更新，即用户的更新操作被另一个用户的更新操作覆盖了，比如以下情况：
（1）事务1查询一行数据，放入本地内存，显示给用户1。
（2）事务2查询同一行数据，放入本地内存，显示给用户2。
（3）用户1修改这行记录，更新数据库并提交。
（4）用户2修改这行记录，更新数据库并提交。此时用户1的修改被丢失了。

要避免丢失更新，需要让事务变成串行操作，可在第（1）步给记录加一个排他锁（下图的for update子句），此时事务2需要等待（1）（3）步完成才能进行第（2）步查询数据：

2.脏读：脏数据和脏页不同，脏页指在缓冲池中已被修改，但还没被刷新到磁盘的页，即内存中的页和磁盘中的页中数据不一致（当然在数据刷到磁盘前，重做日志已被写入），而脏数据指在缓冲池中被修改但还没被事务提交的数据。

读脏页不会影响数据一致性，因为有效数据在脏页中，这样异步同步数据还能带来性能的提高。而读脏数据意味着一个事务读到了另一个事务中未提交的数据，违反了数据库的隔离性。

事务隔离级别Read Uncommitted才会发生脏读。

3.不可重复读：指在同一个事务内多次读同一数据时，在两次读的数据的间隔，由于另一个事务的修改，导致两次读到的数据不同。

事务隔离级别Read Committed会导致不可重复读。一般不可重复读的问题是可接受的，SQL server和Oracle数据库的默认隔离级别都是Read Committed。

InnoDB引擎通过使用Next-Key Lock算法避免不可重复读的问题，MySQL官方文档将不可重复读定义为Phantom Problem（幻读），在此算法下，对于索引的扫描锁住的不仅是扫描到的索引，还锁住这些索引覆盖的范围，因此对于这个范围内的插入都是不允许的，这就避免了另外的事务在这个范围内插入数据导致的不可重复读问题，InnoDB默认事务隔离级别下采用Next-Key Lock算法，避免了不可重复读问题。

由于不同锁之间的兼容性关系，有些时候一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。

InnoDB引擎中，参数innodb_lock_wait_timeout用来控制阻塞等待的最大时间（默认50秒，动态参数，可运行时调整），参数innodb_rollback_on_timeout用来设定等待阻塞超时后对进行中的事务是否进行回滚（默认OFF，不回滚，静态参数）。

默认，InnoDB不对阻塞超时的事务进行回滚，而是提交，可能会产生错误，以下是一个例子：

在事务A中对上表加一个Next-Key Lock，锁住a小于4的所有记录，4也被锁住：


在事务B中，进行插入操作：

可见事务B并没有执行完，但已经执行的部分被提交。

经典死锁情况：

如上图，InnoDB检测到了死锁，大多数的死锁都可以被检测到，不需要人为干预。上例中事务B发生死锁后进行了回滚，释放了锁，事务A才得到了资源。InnoDB引擎不会回滚大部分的错误异常，但死锁除外，因此出现死锁异常时，不需要对事务再次回滚。

Oracle数据库产生死锁的常见原因是没有对外键添加索引，而InnoDB引擎会自动对表中外键列添加索引，删除此索引时会抛出异常。

锁升级指将锁的范围扩大，如将行锁升级为页锁，将页锁升级为表锁。有些数据库设计认为锁是一个稀缺资源，想避免维护锁的开销，可将锁升级，SQL server就是这样做的，它会在合适的时候自动将行、键或分页级锁升级为更粗粒度的锁，保护了系统资源，防止系统使用太多内存维护锁，一定程度上提高了效率，但会带来并发性能的降低。

在SQL server 2005之后，新支持了行锁，但其设计与InnoDB引擎完全不同，以下情况仍可能发生锁升级：
1.一句单独的SQL语句在一个对象上持有的锁数量超过阈值（默认5000），如果是不同对象，则不会发生锁升级。
2.锁资源占用的内存超过了激活内存的40%时。

InnoDB引擎没有锁升级，对它来说1个锁和1000000个锁是一样的，都没有开销，这点与Oracle数据库类似。InnoDB记录锁是以位图表示的，0表示未加锁，1表示加锁，基本没有开销。

这篇关于MySQL技术内幕InnoDB存储引擎 学习笔记 第六章 锁的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---