MySQL的“锁”事一览（一）

1、锁设计背景

数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问的时候，数据库需要合理地控制资源的访问规则。而锁就是用来实现这些访问规则的重要数据结构。

2、锁的分类

根据加锁的范围，MySQL 里面的锁大致可以分成全局锁、表级锁和行锁三类。

2.1、全局锁

2.1.1、定义

全局锁就是对整个数据库实例加锁。

2.1.2、实现

MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock (FTWRL)。

当你需要让整个库处于只读状态的时候，可以使用这个命令，之后其他线程的以下语句会被阻塞：数据更新语句（数据的增删改）、数据定义语句（包括建表、修改表结构等）和更新类事务的提交语句。

2.1.3、应用场景

2.1.3.1、做全库逻辑备份。

问题：在备份过程中整个库完全处于只读状态。

优化：官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数–single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。而由于 MVCC 的支持，这个过程中数据是可以正常更新的。

局限：single-transaction 方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。

探索：要全库只读，是否可用set global readonly=true 的方式呢？

不建议。原因有二：

一是，在有些系统中，readonly 的值会被用来做其他逻辑，比如用来判断一个库是主库还是备库。因此，修改 global 变量的方式影响面更大，不建议使用。

二是，在异常处理机制上有差异。如果执行 FTWRL 命令之后由于客户端发生异常断开，那么 MySQL 会自动释放这个全局锁，整个库回到可以正常更新的状态。而将整个库设置为 readonly 之后，如果客户端发生异常，则数据库就会一直保持 readonly 状态，这样会导致整个库长时间处于不可写状态，风险较高。

2.2、表级锁

2.2.1、表锁

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

举个例子, 如果在某个线程 A 中执行 lock tables t1 read, t2 write; 这个语句，则其他线程写 t1、读写 t2 的语句都会被阻塞。同时，线程 A 在执行 unlock tables 之前，也只能执行读 t1、读写 t2 的操作。连写 t1 都不允许，自然也不能访问其他表。

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

2.2.2、元数据锁（meta data lock，MDL)

2.2.2.1、技术背景

在MySQL中，DDL是不属于事务范畴的，如果事务和DDL并行执行，操作相关联的表的话，会出现各种意想不到问题，如事务特性被破坏、binlog顺序错乱等，为了解决类似这些问题，MySQL在5.5.3引入了MDL锁(Metadata Locking)。

2.2.2.2、定义

Meta Data Lock 简称MDL，是在MySQL server层使用的一种表级别锁，并不是InnoDB引擎中实现的。使用时不需要显式声明，在访问一个表的时候会被自动加上。

MDL 的作用是，保证读写的正确性。

2.2.2.3、特性

当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

读锁之间不互斥，因此你可以有多个线程同时对一张表增删改查。

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

元数据锁的兼容关系如下：

兼容性	MDL 读锁	MDL 写锁
MDL 读锁	兼容	不兼容
MDL 写锁	不兼容	不兼容

2.2.2.4、metedata lock的生命周期有多长？

表上的metadata lock的生命周期从事务中的第一条涉及自身的语句开始，到整个事务结束而结束。

查询锁等待超时时间：

属性	默认值（单位秒）	说明
innodb_lock_wait_timeout	50	行锁等待超时时间，默认为50秒
lock_wait_timeout	31536000	metadata lock锁等待超时时间，默认为1年

所以，如果要降低metadata lock的锁等待时间，最好要及时提交事务，同时尽量避免大事务。

2.2.3 自增列锁（AUTO-INC Locks）

2.2.3.1、定义

AUTO-INC锁是一种特殊的表级锁，当表使用了AUTO_INCREMENT列时，插入数据时需要获取AUTO-INC锁。AUTO-INC锁是作用范围是语句级别，也就是说当执行完成插入语句后，哪怕整个事务还没结束，AUTO-INC锁也会被释放。因此会出现：一个事务在持有AUTO-INC锁进行插入操作时，其他事务的插入操作就会被阻塞，以此来保证自增值是连续的。

2.2.3.2、问题

使用AUTO-INC Locks会出现这样的问题：如果一个插入语句执行过长（比如insert … select大数据量插入），会导致后面的插入语句阻塞时间久，整体性能降低。

2.2.3.3、解决方案

所以MySQL InnoDB引擎还会采用另一种轻量级锁（互斥量）的方式，在执行插入语句之前先获取该轻量级锁，生成AUTO_INCREMENT的值后就释放锁，不需要等到插入语句执行完成后才释放。这种方式会大大提高AUTO_INCREMENT值插入的性能，但是也会带来的问题是——并发时事务的自增列值是不连续的，主从复制时可能是不安全的。

使用innodb_autoinc_lock_mode系统变量可以控制选择哪一种锁来为AUTO_INCREMENT赋值

• innodb_autoinc_lock_mode=0:统一使用AUTO-INC 锁
• innodb_autoinc_lock_mode=2:统一使用轻量级锁
• innodb_autoinc_lock_mode=1：插入记录数确定时，采用轻量级锁；不确定时使用AUTO-INC 锁

2.2.4 意向锁 （Intention Locks）

2.2.4.1、背景

假设有这样的一种场景：我们想对某张表加X锁，此时就必须先保证表中的记录都没有被加S锁和X锁。那么该如何去检测呢？

可以采用循环遍历每一条记录有没有被上锁，这种方式明显效率太低了。

所以InnoDB设计了另一种特殊的表级锁——意向锁。使用它是为了表在后续被加上X锁或者S锁时，能快速判断表记录之前是否有被加锁，从而避免通过遍历的方式一个个去检测行锁的存在。

2.2.4.2、意向共享锁（IS）和意向排它锁(IX)

意向锁也分为意向共享锁（IS）和意向排它锁(IX)。

• 意向共享锁（IS）：当事务准备给表记录加S锁时，需要先对表加上IS锁
• 意向排它锁 (IX) ：当事务准备给表记录加X锁时，需要先对表加上IX锁

表级别锁的兼容性如下：

兼容性	S锁	IS锁	X锁	IX锁
S锁	兼容	兼容	不兼容	不兼容
IS锁	兼容	兼容	不兼容	兼容
X锁	不兼容	不兼容	不兼容	不兼容
IX锁	不兼容	兼容	不兼容	兼容

其中，IS锁和IX锁、IS锁和IS锁、IX锁和IX锁之间都是兼容的。这个如何理解呢？

刚刚有提到，意向锁是为了可以快速判断表记录是否被加了锁，方便判断事务是否可以对表加锁。这就意味着，不管有事务对表记录中加了S锁，还是加了X锁，只需要加上对应的IS锁和IX锁就好了，不需要关心其他事务加的是IS锁还是IX锁。

也就是说，IS锁和IX锁只是为了后续对表加S锁或者X锁时才起作用。

• IS锁不兼容表级X锁，兼容表级S锁。意思是表中记录加了S锁的，只允许对表整体加S锁
• IX锁不兼容表级X锁和S锁。表中记录加了X锁的，不只允许对表整体加S锁和X锁

2.3、行级锁

表级锁是对整个表进行加锁，行级锁就是以行为单位进行加锁的机制。

2.3.1 Record Locks

记录锁。但Record Lock锁的都是索引的记录，作用于聚簇索引或者二级索引之上。即使一个表没有定义索引，InnoDB也会自动创建一个隐藏的聚簇索引并使用该索引进行记录锁定，所以Record Lock也称为索引记录锁。

2.3.2 Gap Locks

Gap Lock直译过来就是间隙锁。间隙锁的引入是作为记录锁的补充。我们知道MySQL在可重复读RR隔离级别下，是可以解决大部分幻读问题的。

幻读:指一个事务在前后两次查询同一个范围的时候，后一次查询看到了前一次查询没有看到的行。

• RR级别下，事务中如果是使用快照读（也称一致性读）的，如：普通的select查询，会利用MVCC的一致性视图方案来避免幻读。
• RR级别下，事务中如果是使用当前读的，如：加锁的select语句和更新语句（更新数据都是先读后写的，此时的【读】，必须读当前的值，故称为“当前读”）。 只能用加锁的方案来避免幻读。

间隙锁之间是没有冲突的，与间隙锁产生冲突的是：向间隙中间插入数据的操作。间隙锁的作用只是为了防止幻读问题。

2.3.3 Next-Key Locks

Next-Key Lock 就是Record Lock+Gap Lock，锁住行记录，以及中间的空隙。

性能问题

间隙锁和 next-key lock 的引入，在为了解决RR隔离级别下出现幻读的问题。但同时由于锁住更大的范围，在一定程度上影响了并发性能。

解决方案

虽然RR是MySQL默认的隔离级别，但是很多线上业务系统都会选择使用RC读提交作为默认的隔离级别，同时将binlog_format设置为row。因为RC级别是允许幻读情况发生的，所以绝大部分场景下RC是不会采用间隙锁的方式（外键场景可能会使用），binlog_format设置为row则是为了防止可能出现数据和日志不一致的问题。

2.3.4、插入意向锁（Insert Intention Locks ）

介绍间隙锁的时候，我们知道，在某个索引区间如（1,5）加上间隙锁后，是无法插入id=3和id=4的数据，除非该间隙锁被释放。
当两个事务分别执行插入id=3和id=4的记录时，会在区间上加插入意向锁且锁状态是等待状态（is_waiting=true），等到间隙锁释放时，将插入意向锁状态is_waiting=false，唤醒两个插入的事务，且这两个事务之间是不阻塞的。

• 插入意向锁是在INSERT插入操作时设置的一种特殊间隙锁 ，注意它并不属于意向锁而是属于间隙锁。
• 插入意向锁之间互不排斥，当多个事务在同一区间插入记录时，只要记录本身（主键索引、唯一索引）不发生冲突，那么事务之间也不会阻塞等待。

3、表级锁和行级锁的区别

• 表级锁：优点在于加锁开销小，速度快，但锁的粒度粗，缺点是并发性能低。
• 行级锁：相对开销较大，速度较慢，但锁的粒度细，并发性能更高，更适合OLTP的场景。

MySQL 的行级锁是在引擎层由各个引擎自己来实现的。行级锁也是 InnoDB引擎对比传统的MyISAM引擎的一大优势特性。

4、metadata lock和行锁有什么区别？

metadata lock是表级锁，所有的dml操作都会在表上加一个metadata读锁；所有的ddl操作都会在表上加一个metadata写锁。读锁和写锁的阻塞关系如下：

1. 1. 读锁和写锁之间相互阻塞，即同一个表上的dml和ddl之间互相阻塞。
   2. 写锁和写锁之间互相阻塞，即两个session不能对表同时做表定义变更，需要串行操作。
   3. 读锁和读锁之间不会产生阻塞。也就是增删改查不会因为metadata lock产生阻塞，可以并发执行，日常工作中看到的dml之间的锁等待是innodb行锁引起的，和metadata lock无关。

innodb行锁分类和metadata lock很类似，也主要分为读锁和写锁，或者叫共享锁和排他锁，读写锁之间阻塞关系也一致。二者最重要的区别一个是表锁，一个是行锁，且行锁中的读写操作对应在metadata lock中都属于读锁。

5、死锁

5.1、定义

死锁是指不同事务之间每个事务都持有其他事务需要获取的锁资源，导致事务无法继续进行的情况。因为事务都在等待资源变得可用，但都不会释放它持有的锁。也就是当不同线程并发执行出现资源依赖循环，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

5.2、MySQL 锁问题实践

设计开发阶段:

1. 表设计要避免使用myisam存储引擎，改用innodb引擎；
2. 为SQL创建合适的索引，避免多个单列索引执行出错；
3. 避免大事务，长事务，复杂事务导致事务在数据库中的运行时间加长。

管理运维阶段：

1. 在业务低峰期执行上述操作，比如创建删除索引；
2. 在结构变更前，观察数据库中是否存在长时间运行的SQL，未提交的事务；
3. 结构变更期间，监控数据库的线程状态是否存在lock wait。

6、如何安全地给小表加字段？

首先我们要解决长事务，事务不提交，就会一直占着 MDL 锁。在 MySQL 的 information_schema 库的 innodb_trx 表中，你可以查到当前执行中的事务。如果你要做 DDL 变更的表刚好有长事务在执行，要考虑先暂停 DDL，或者 kill 掉这个长事务。

其次，在 alter table 语句里面设定等待时间，如果在这个指定的等待时间里面能够拿到 MDL 写锁最好，拿不到也不要阻塞后面的业务语句，先放弃。之后开发人员或者 DBA 再通过重试命令重复这个过程。

ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ... 

MariaDB 已经合并了 AliSQL 的这个功能，所以这两个开源分支目前都支持 DDL NOWAIT/WAIT n 这个语法。