简介

数据库锁设计的初衷是处理并发问题。当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。根据加锁的范围，MySQL里面的锁大致可以分成全局锁、表级锁、页级锁和行锁四类。这些锁都属于悲观锁。

MySQL还有一个显著的特点就是不同的存储引擎支持不同的锁机制。比如，MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）；BDB存储引擎采用的是页面锁（page-level locking），但也支持表级锁；InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，InnoDB默认情况下是采用行级锁。

很难笼统地说哪种锁更好，只能就具体应用场景的特点来说哪种锁更合适。仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用。

全局锁

全局锁就是对整个数据库实例加锁。MySQL提供了一个加全局读锁的方法，命令是 Flush tables with read lock(FTWRL)。当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

全局锁的典型使用场景是，做全库逻辑备份。也就是把整库每个表都select出来存成文本。

  • 如果你在主库上备份，那么在备份期间都不能执行更新，业务基本上就得停摆；
  • 如果你在从库上备份，那么备份期间从库不能执行主库同步过来的binlog，会导致主从延迟。

你可能会想到一致性视图来解决这个问题从而不锁全库。

其实官方自带的逻辑备份工具mysqldump使用参数–single-transaction导数据，就会启动一个事务来确保拿到一致性视图。而由于MVCC的支持，这个过程中数据是可以正常更新的。但前提是引擎要支持这个隔离级别。比如MyISAM就无法支持。所以如果有的表使用了不支持事务的引擎，那么备份就只能通过FTWRL方法。

这时，可能又会有疑问了，既然要全库只读，为什么不使用set global readonly=true的方式呢？

  1. 在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此不建议使用。
  2. 在异常处理机制上有差异。执行FTWRL命令之后由于客户端发生异常断开，那么MySQL会自动释放这个全局锁，整个库回到可以正常更新的状态。而这种方式则会让数据库一直保持只读方式，风险更高。

表级锁

表级锁，注意不是表锁。MySQL中的表级锁有两类：表锁和元数据锁（metadata lock, MDL）。

表锁

表锁的语法是lock tables ... read/write。与FTWRL类似，可以用unlock tables主动释放锁，也会在客户端断开时自动释放。需要注意，lock tables语句除了会限制别的线程的读写外，也会限制本线程接下来的操作对象。

表锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。

  • 当一个线程（会话）获得对一张表的写锁后，只有持有锁的线程可以对表进行查询或者更新；其他线程的读写操作都会等待，直到锁被释放为止。
  • 当一个线程（会话）获得对一张表的读锁后，持有读锁的线程可以查询锁定表的记录，但是更新或者访问其他表都会提示错误，即不能查询没有锁定的表；同时，另外一个线程可以查询被锁定表的记录，但是更新就会出现锁等待。

在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB一般不使用表锁来控制并发，毕竟锁住整个表的影响面还是太大。

元数据锁

MDL不需要显式使用，在访问一个表的时候会被自动加上。MDL的作用是，保证读写的正确性。MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。

  • MDL读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。
  • MDL读写锁之间、MDL写锁之间是互斥的，用来保证变更表结构操作的安全性。因此，如果有两个线程要同时给一个表加字段，其中一个要等另一个执行完才能开始执行。

同样的，事务中的MDL锁，也是在语句执行开始时申请，语句结束后同样并不会马上释放，而会等到整个事务提交后再释放。

行锁

页级锁接触不多，不作阐述，介绍完了全局锁和表级锁，这里重点整理一下InnoDB中默认使用的行锁。MySQL的行锁不是所有的引擎都支持的，使用表锁意味着同一张表上任何时刻只能有一个更新在执行！行锁就是针对数据表中行记录的锁，锁粒度最小。搞清楚行锁，先从两阶段锁说起。

两阶段锁

先看一个执行流程的例子，假设有一张简单表t，主键id，有数值字段k：

事务A 事务B
begin;
update t set k=k+1 where id=1;
update t set k=k+1 where id=2;
begin;
update t set k=k+2 where id=1;
commit;

在上面的执行流程中，事务B的更新语句执行时会出现什么现象呢？结论取决于事务A在执行完两条update语句后，持有哪些锁，以及在什么时候释放。实际上，事务B的更新会被阻塞，直到事务A提交之后才会继续执行。

也就是说，事务A持有的两个记录的行锁，都是在commit之后才释放的。即：在InnoDB事务中，行锁时在需要的时候才加上的，但并不是不需要了就立即释放，而是等事务结束了才释放。这就是两阶段锁协议。

从这个协议中能得到一个结论：如果一个事务中需要锁多个行，要把最可能造成锁冲突、最困难影响并发度的锁尽量往后放。这样一旦发生了锁冲突，可以尽可能地减少锁等待时间，提高并发度。

死锁

与表锁不同，行锁的粒度更细，并发度更高，但是是有可能产生死锁的。看一个例子

事务A 事务B
begin;
update t set k=k+1 where id=1;		 begin;
update t set k=k+1 where id=2;
update t set k=k+1 where id=2;
update t set k=k+1 where id=1;

例子中事务A等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁，于是进入死锁状态。当出现死锁之后，有两种策略：

  • 等待超时。InnoDB是有超时机制的，可通过参数innodb_lock_wait_timeout设置，默认50s。
  • 发起死锁检测，主动回滚死锁链中的某一个事务。将参数innodb_deadlock_detect设置为on（默认），表示开启这个逻辑。

一般正常情况下采用第二个策略，主动检测死锁。因为一旦发生死锁，这种策略能快速发现并进行处理。超时机制默认50s才能退出，也就是说有50s时间无法继续执行，而innodb_lock_wait_timeout值又无法设置一个很小的值，很小的值会带来误伤。

另外，通过SHOW STATUS LIKE 'innodb_row_lock%';可以获取InnoDB行锁的争用情况用作分析。如果发现锁争用比较严重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高，还可以通过设置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等，并分析锁争用的原因。

行锁模式

InnoDB实现了两种类型的行锁，共享锁（读锁、S锁）和排他锁（写锁、X锁）。

  • S锁：允许一个事务去读被加锁的数据集，阻止其他事务获得相同数据集的X锁。如果事务A对数据行i加上S锁，则事务A可以读i，但是不能修改i；其他事务只能再对i加S锁，不能加X锁，直到事务A释放了S锁。也就是保证了其他事务在A释放S锁之前，只能读i，不能修改i。
  • X锁：允许获取X锁的事务更新数据，阻止其他事务获取相同数据集的S锁和X锁。如果事务A对数据行加上了X锁，则只有事务A可以读i或者写i，其他事务不能再对i加S锁或者X锁，直到事务A释放。

这里对于X锁要注意一点，排它锁实际上是阻止其他事务不能对已加锁的相同数据集再加锁，但是其他事务里的普通查询是可以的。因为普通查询不需要锁！这里再次强调一下，select语句默认不会加上任何锁，加X锁用select…for update，加S锁用select…lock in share mode。

对于UPDATE/DELETE/INSERT语句，InnoDB会自动给涉及数据集加X锁，用SELECT…LOCK IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT… FOR UPDATE方式获得排他锁。

行锁的实现

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应的数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则InnoDB将使用表锁！

在实际应用中，需要特别注意InnoDB行锁的这一特点，不然的话会导致大量的锁冲突从而影响并发性能。

  • 在不通过索引条件查询的时候，InnoDB无法使用行锁，只能使用表锁。
  • 由于InnoDB的行锁是针对索引添加，所以当访问不同的行记录，但是使用的是相同的索引键时，是会出现锁冲突的。
  • 当有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行。
  • 不论是使用主键索引、唯一索引还是普通索引，InnoDB都会使用行锁来对数据加锁。
  • 及时在条件中使用了索引字段，但是是否使用索引键锁数据是由MySQL通过不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，那就不会使用索引。这种情况下InnoDB也会使用表锁。因此在检查锁冲突的时候，别忘了检查SQL执行计划。

意向锁

InnoDB为了允许表锁和行锁共存，实现多粒度锁机制，内部还使用了一种表级锁–意向锁（Intension Lock），意向锁也有两种：

  • 意向共享锁（IS锁）：事务打算给数据行加共享锁，事务在给一个数据行加S锁之前必须先获得该表的IS锁。
  • 意向排他锁（IX锁）：事务打算给数据行加排他锁，事务在给一个数据行加X锁之前必须先获得改表的IX锁。

InnoDB行锁与意向锁兼容性一览表：

X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 冲突
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需要用户干预。

间隙锁与Next-Key锁

这两种锁的引入其实是为了解决幻读问题，那么我们先从幻读问题开始看起。

幻读

幻读指的是一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。这里，需要对“幻读”做一个说明：

  • 在可重复读隔离级别下，普通的查询是快照读，是不会看到别的事务插入的数据的。因此，幻读在“当前读”下才会出现。
  • 幻读仅专指“新插入的行”。

为了说明幻读问题，我们还是用一个例子来说明，假设又一张表t，初始化如下：

1
2
3
4
5
6
7
8
9
CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

假设有三个事务，执行流程如下所示：

时刻 事务A 事务B 事务C
T1 begin;
select * from t where d=5 for update;//Q1
update t set d=100 where d=5;
T2 update t set d=5 where id=0;
update t set c=5 where id=0
T3 select * from t where d=5 for update;//Q2
T4 insert into t values(1,1,5);
update t set c=5 where id=1;
T5 select * from t where d=5 for update;//Q3
T6 commit;

T1时刻，事务A先执行查询，是一个当前读，加X锁，并且d字段没有索引，因此会锁住t表所有的行，所以事务B执行第一个update语句会被锁住，需要等到T6时刻事务A提交以后才能继续执行。这样对于id=0这一行，在数据库里最终结果还是(0,5,5)。根据我们已经介绍的锁的知识，目前得出在binlog里，执行序列是这样的：

1
2
3
4
5
6
7
insert into t values(1,1,5);    //(1,1,5)
update t set c=5 where id=1;    //(1,5,5)

update t set d=100 where d=5;   //所有d=5的行，d改成100

update t set d=5 where id=0;    //(0,0,5)
update t set c=5 where id=0;    //(0,5,5)

为什么事务C的T4时刻日志在最前面？不清楚的可以回顾一下MySQL系列第2篇日志系统相关知识。

可以看到，按照日志顺序，id=0这一行的最终结果是(0,5,5)，这没有什么问题，问题是id=1这一行的数据最终是多少呢？根据binlog执行顺序可以得出，id=1这一行最终是(1,5,100)。T3时刻给所有的行加锁时，id=1的行还不存在，不存在也就加不上锁，也就是说，即使把所有的记录都加上锁，还是阻止不了新插入的记录，这就是幻读。而实际上，如果你在数据库中模拟执行的话，你会发现id=1这一行最终是(1,5,5)。换句话说InnoDB实际上还是在T4时刻阻止了事务C新插入记录的操作，解决了幻读问题，否则id=1这一行查询出的结果将会和以上分析的结果一致为(1,5,100)。那InnoDB是怎么解决幻读的呢？

上面的binlog执行序列根据之前已经介绍过的锁的知识得出，但是很可惜，它仍旧是错误的（可重复读隔离级别下），因为还缺少了接下来的知识点–间隙锁，而间隙锁的引入正是InnoDB解决幻读的重要环节。

间隙锁

现在我们知道了，产生幻读的原因是行锁只能锁住行，但是新插入记录这个动作，要更新的是记录之间的“间隙”。因此，为了解决幻读InnoDB只好引入新的锁，也就是间隙锁（Gap Lock）。顾名思义，间隙锁，锁的就是两个行之间的间隙，比如本节的表t初始化插入了6条记录，这就产生了7个间隙。

这样，当事务A在T1时刻执行select * from t where d=5 for update的时候，就不知给数据库中已有的6条记录加上了锁，同时还加了7个间隙锁，以此确保无法插入新的记录，因此在事务A提交事务释放锁之前，事务C在T4时刻的插入实际上是被阻塞了的。事务B和C都是在事务A提交之后才会继续执行！

至此得出的结论：数据行可以加上锁的实体，数据行之间的间隙也可以加上锁的实体。

但是间隙锁跟我们之前讲过的锁都不不太一样。比如行锁，无论是行锁的读锁或写锁，跟它有冲突关系的是“另外一个锁”。但是跟间隙锁存在冲突关系的，是往被锁住的这个间隙中插入一条记录操作。间隙锁之间是不存在冲突关系的。为了更好的理解，再用表t举个例子，看下面的执行流程：

事务A 事务B
begin;
select * from t where c=7 lock in share mode;
begin;
select * from t where c=7 for update;

这里的事务B并不会被阻塞。表t中没有c=7的行，事务A加的间隙锁(5,10)区间，同样事务B加的间隙锁也是(5,10)区间。他们有共同的目标：保护这个间隙，不允许插入值。因此他们是不冲突的。

Next-key Lock

间隙锁和行锁合称为next-key lock，每个next-key lock为前开后闭的区间。在表t中，如果用select * from t for update;要把整个表所有记录锁起来，就形成了7个next-key lock，分别是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]

为什么是+supremum而不是+∞？因为next-key lock是一个前开后闭区间，而+∞是一个开区间，所以在实现上，InnoDB给每个索引加了一个不存在的最大值suprenum（不是supernum）来满足next-key lock“后闭”的条件。

因此，也可以说间隙锁合next-key lock的引入，解决了幻读的问题。

间隙锁死锁

看一个因间隙锁形成死锁的例子，同样适用初始化后的表t：

事务A 事务B
begin;
select * from t where id=9 for update;
begin;
select * from t where id=9 for update;
insert into t values(9,9,9);
[blocked]
insert into t values(9,9,9);
[ERROR:Deadlock found!]
  1. 事务A实行当前读，由于id=9行不存在，因此加上间隙锁(5,10)
  2. 事务B执行当前读，同样加上间隙锁(5,10)
  3. 事务B尝试插入，与事务A的间隙锁冲突，进入等待
  4. 事务A尝试插入，与事务B的间隙锁冲突，进入等待
  5. 至此，两个事务互相等待，形成死锁。然后InnoDB死锁检测马上发现了这对死锁关系，让事务A的插入语句报错返回了。

至此间隙锁内容整理完了，最后补充一点，间隙锁是在可重复读隔离级别下才会生效的。所以，如果把隔离级别设置为读提交的话，就没有间隙锁了。

小结

关于全局锁

  • 典型应用场景–全局逻辑备份
  • FTWRL比set global readonly=true更适合做全库只读备份

关于表级锁

  • 表级锁有表锁合MDL两类，表锁的锁定粒度大
  • 表锁共享读锁（S）之间是兼容的，但共享读锁（S）与排他写锁（X）之间，以及排他写锁（X）之间是互斥的，也就是说读和写是串行的
  • MyISAM使用表锁，在一定条件下，MyISAM允许查询和插入并发执行，拓展：MyISAM默认的锁调度机制是写优先
  • 意向锁也是一种表级锁，InnoDB中使用，不需要用户干预

关于行锁

  • InnoDB默认使用行锁，行锁粒度小，并发度高同时锁冲突的几率也高，会形成死锁
  • 分S锁和X锁，读写串行，与意向锁一起使用实现多粒度锁机制
  • 锁在有需要时才添加，事务提交之后才释放 – 两阶段锁概念
  • InnoDB的行锁是基于索引实现的，如果不通过索引访问数据，InnoDB会将扫描到的行都加锁
  • 可重复读级别下，引入间隙锁解决幻读，间隙锁和行锁合称next-key lock
  • 间隙锁不同于其他锁，与之冲突的是往间隙中插入数据的这个操作，它也会导致死锁