MySQL锁机制漫谈(一)

前言

数据库锁定机制是数据库为了保证数据的一致性而使各种共享资源在并发访问时变的有序的一种规则。MySQL数据库的各种存储引擎使用了三种的锁定机制：行级锁定、页级锁定、表级锁定。

• 行级锁定
  行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定粒度最小的。由于锁定粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力，从而提高一些需要高并发应用系统的整体性能。虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁。

• 表级锁定
  和行级锁定相反，表级别的锁定是MySQL 各存储引擎中最大粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

• 页级锁定
  页级锁定是MySQL 中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁。

在MySQL 数据库中，使用表级锁定的主要是MyISAM，Memory，CSV 等一些非事务性存储引擎，而使用行级锁定的主要是Innodb 存储引擎和NDB Cluster 存储引擎，页级锁定主要是BerkeleyDB 存储引擎的锁定方式。

下面，我们主要针对MySQL中的表级锁定和行级锁定进行具体的实现原理分析。

锁定机制分析

表级锁定

MySQL本身就提供表级锁定支持。MySQL的表级锁定主要分为两种：一种是读锁定，一种是写锁定。在MySQL中，主要通过4个队列来维护这两种锁定：两个存放当前正在锁定的读和写锁定信息；另外两个存放等待中的读写锁定信息。这四个队列信息如下：

• Current read-lock queue (lock->read)
• Pending read-lock queue (lock->read_wait)
• Current write-lock queue (lock->write)
• Pending write-lock queue (lock->write_wait)

当前持有读锁的所有线程的相关信息都能够在Current read-lock queue 中找到，队列中的信息按照获取到锁的时间依序存放。而正在等待锁定资源的线程的信息则存放在Pending read-lock queue 里面，剩下的写锁队列也按照上面相同规则来存放信息。

读锁定

一个新的客户端线程在请求获得表的读锁定时需要满足连个条件：

• 请求锁定的资源当前没有被写锁定
• 写锁定等待队列（Pending write-lock queue）中没有更高优先级的写锁定等待

如果满足了上面两个条件之后，该请求会被立即通过，并将相关的信息存入Current read-lockqueue 中，而如果上面两个条件中任何一个没有满足，都会被迫进入等待队列Pending read-lock queue中等待资源的释放。

写锁定

行级锁定

MySQL本身没有实现行级锁定，InnoDB是目前事务型存储引擎中使用最为广泛的，我们下面以InnoDB为例来分析行级锁的实现机制。

概念

InnoDB在锁定机制的实现过程中为了让行级锁定和表级锁定共存， Innodb使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种。
Innodb 的行级锁定同样分为两种类型，共享锁和排他锁：

• 共享锁（S Lock），允许事务读一行数据。
• 派他锁（X Lock），允许事务删除或更新一行数据

如果一个事务T1已经获得了行r的共享所锁，那么事务T2是可以立即获得行r上的共享锁，因为读取行为并不会该百年行r的数据，这种情况称为锁兼容。但如果事务T3想要获得行r的排它锁，则必须等待事务T1、T2先释放行r上的共享锁-这种情况称为锁不兼容。
注：S Lock与X Lock都是行锁，兼容是指对同一记录锁的兼容性情况。
同时，由于InnoDB存储引擎支持多粒度的锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为支持在不同粒度上的加锁行为，InnoDB支持另外一种加锁方式，即所谓的意向锁（Intention Lock）。意向锁是将锁定的对象分为多个层次，意向锁是的事务可以在更细的粒度上进行加锁操作。
意向锁怎么使用呢，很简单：

SELECT ...... IN SHARE MODE;//设置一个Intention Share Lock
SELECT ......FOR UPDATE;//设置一个Intention Xtra Lock

意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。所以我们可以总结出Innodb 的锁定模式实际上可以分为四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），它们的兼容关系如下：

	IS	IX	S	X
IS	兼容	兼容	兼容	不兼容
IX	兼容	兼容	不兼容	不兼容
S	兼容	不兼容	兼容	不兼容
X	不兼容	不兼容	不兼容	不兼容

InnoDB存储引擎对意向锁的设计比较简单，它的意向锁就是表级别的锁。
但是到这里，我们还是没能明白意向锁存在的意义，看了一下mysql开发者文档，原文如下：

Thus, intention locks do not block anything except full table requests (for example, LOCK TABLES ... WRITE). The main purpose of IX and IS locks is to show that someone is locking a row, or going to lock a row in the table.
即意向锁只会阻塞全表锁的请求。IX与IS的主要目的在于告诉你，这张表里有行锁存在或有意向对某行加锁。

那意向锁是要解决一个什么问题呢？表锁与行锁共存的问题。考虑这样一种情况，事务A对row1加了一个读锁，其他事务对该行只能读不能写。然后事务B要对整个表加一个写锁。此时事务B首先需要判断该表是否可以加表锁，这个步骤就是：

• step1，判断表是否被其他事务加上了表锁。
• step2，判断表中每一行是否都有行锁。
  我们需要注意的就是step2，每一行的去检查效率太低了，这会需要遍历整个表。此时意向锁可以派上用场了。在意向锁存在的情况下，事务A想要对某一行加行锁，必须先申请表的意向锁，成功后再申请加行锁。
  此时，以上的表锁与行锁共存问题步骤会编程如下：
• step1同上。
• step2，先判断该表上是否有意向锁，如果存在意向锁，说明表中可能某行加了行锁，因此事务B的写锁会被直接阻塞，而不需要再次扫表验证。效率大大提高。

行锁算法

InnoDB存储引擎有三种行锁的算法，分别是：

• Record Lock

单个行记录上的锁。Record Lock总是会去锁住索引记录，如果InnoDB存储引擎在表建立的时候没有设置任何一个索引，那么此时InnoDB存储引擎会创建一个隐式的聚集索引并使用该索引记录进行锁定。在RC隔离级别下一般加的都是该类型的记录锁（但唯一二级索引上的duplicate key检查除外，总是加LOCK_ORDINARY类型的锁）

• Gap Lock

间隙锁，锁定一个范围，但不包含记录本身。间隙锁是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。称之为间隙锁是因为Query 执行过程中通过过范围查找的，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。在使用唯一索引搜索唯一记录时不需要显示声明间隙锁。一般在RR隔离级别下会使用到GAP锁。InnoDB中的间隙锁时‘完全非排他的’-这意味着InnoDB中的间隙锁仅仅会阻止其他事物在间隙中插入记录，但并不会阻止不同事物在相同的间隙上加锁。
间隙锁可以显示的关闭。你可以通过切换到RC隔离级别，或者开启选项innodb_locks_unsafe_for_binlog来避免GAP锁。这时候只有在检查外键约束或者duplicate key检查时才会使用到GAP LOCK。间隙锁是性能与并发下的折中方案。

• Next-Key Lock

Gap Lock+Record Lock，在索引记录上加记录锁以及索引记录前的间隙上加间隙锁。在Next-Key Lock算法下，如果一个会话在记录R上获得了一个共享锁或者排他锁，则此时另一个会话就不能以索引的顺序在该索引前的间隙上插入记录。假设一个索引有值 10, 11, 13, 20，则该索引上会覆盖的next-key锁会包含一下区间。对于最后一个区间，next-key lock会锁住从索引中最大的记录到无穷大的区间。

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

默认情况下，MySQL在RR隔离级别下使用 next-lock算法进行查询，索引扫描。该算法可以解决在RR隔离级别下出现的幻读问题。所谓幻读就是一个事务内执行相同的查询，会看到不同的行记录。在RR隔离级别下这是不允许的。
注：当查询所有唯一索引列时，InnoDB存储引擎会对Next-Key Lock进行优化，会将锁降级为Record Lock，即仅锁住索引本身，而非一个范围。
我们通过一个例子对 Next-Key Lock算法进行验证说明：

首先我们先创建一个表t;

CREATE TABLE t(a INT,b INT,PRIMARY KEY (a),KEY (b))engine=innodb CHARSET=utf8;
INSERT INTO t SELECT 1,1;
INSERT INTO t SELECT 3,1;
INSERT INTO t SELECT 5,3;
INSERT INTO t SELECT 7,6;
INSERT INTO t SELECT 10,8;

如果事务T1执行该SQL语句：

SELECT * FROM t WHERE b=3 FOR UPDATE;

该语句通过索引列b进行查询，因此会使用传统的Next-Key Lock技术加锁。同时由于表中存在两个索引，需分别进行锁定。对于聚集索引，仅对列a等于5的索引加上Record Lock。而对于辅助索引b，加上的是Next-Key Lock，锁定范围(1,3).同时需要注意，InnoDB存储引擎还会对辅助索引的下一个键值加上Gap Lock，即还有一个（3，6）的锁。此时其他事务插入任何b列值在（1，6）都是会被阻塞。

• Insert intention locks

插入意向锁是一种由INSERT操作设置的间隙锁。该锁表明了执行插入操作的意向，因此多个事务在相同的索引间隙中执行插入操作，并且也不是在同一个位置上插入，则事务之间不需要互相等待。
举个例子，两个事物分别尝试插入值5，6。在获取插入行的排他锁之前，两个事务都会获取（4,7]之间的插入意向锁。由于不存在冲突，它们不会互相阻塞。

• AUTO-INC Locks

AUTO-INC锁是一个特殊的表级锁，当一个事务尝试在带有AUTO_INCREMENT列的表上进行插入操作，该锁会被获取。在最简单的情况下，一个事务正在向标中插入数据，任何其他想要进行插入操作的事务必须等待，只有这样第一个插入行的事务才能得到连续的primary key值。
innodb_autoinc_lock_mode 配置项可以控制AUTO-INC Locks算法的开启。

幻读问题

我们先看一下Phantom Problem的定义：

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

在InnoDB存储引擎中，对于INSERT操作，会检查插入的下一个记录是否被锁定，若以锁定，则无法插入。
InnoDB采用Next-Key Lock算法来避免Phantom Problem。InnoDB存储引擎默认事务隔离级别是REPEATABLE READ，在该隔离级别下采用Next-Key Lock来加锁。在事务隔离级别READ COMMITED下，采用Record Lock。

Problem

通过索引实现锁定的方式还存在其他几个较大的性能问题：

• 当Query 无法利用索引的时候，Innodb 会放弃使用行级别锁定而改用表级别的锁定，造成并发性能的降低；
• 当Quuery 使用的索引并不包含所有过滤条件的时候，数据检索使用到的索引键所只想的数据可能有部分并不属于该Query 的结果集的行列，但是也会被锁定，因为间隙锁锁定的是一个范围，而不是具体的索引键；
• 当Query 在使用索引定位数据的时候，如果使用的索引键一样但访问的数据行不同的时候（索引只是过滤条件的一部分），一样会被锁定

InnoDB行锁优化建议

Innodb 存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM 的表级锁定的。当系统并发量较高的时候Innodb 的整体性能和MyISAM 相比就会有比较明显的优势了。但是，Innodb 的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb 的整体性能表现不仅不能比MyISAM 高，甚至可能会更差。因此我们有如下建议：

• 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb 因为无法通过索引键加锁而升级为表级锁定；
• 合理设计索引，让Innodb 在索引键上面加锁的时候尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query 的执行；
• 尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录
• 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
• 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL 因为实现事务隔离级别所带来的附加成本；

参考

• MySQL性能调优与架构设计
• MySQL技术内幕
• mysql dev manual