MySQL锁机制

前言

上篇博客简单讲了MySQL事务隔离级别，当遇到并发问题时就会产生事务的并发问题：脏读、不可重复读、幻读，由于MySQL本身的事务隔离级别就是RR（可重复读），就已经解决了 脏读、不可重复读 问题，幻读 又分为两种情况 快照读 和 当前读，MySQL的MVCC可以解决 快照读情况下的 幻读 问题，而在 当前读 的情况下则需要 共享锁 或 排他锁 和 间隙锁 来解决 幻读 问题。在这里用到MySQL的锁，本篇文章来讲下MySQL的锁机制，了解锁的种类及实现原理。

一、锁的本质

为什么用到锁： 因为数据库是多用户共享资源的，在并发环境下会破坏数据库的一致性，所以需要锁来控制它们。

锁的本质是： 业务场景中存在共享资源，多个进程或线程需要竞争获取并处理共享资源，为了保证公平、可靠、结果正确等业务逻辑，要把并发执行的问题变为串行，串行时引入第三方锁当成谁有权限来操作共享资源的判断依据。

二、锁的实现及分类

开始讲锁之前先简单介绍一下几个 MySQL 常用术语，下面会经常用到：

• DML（data manipulation language）： 它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对 数据库里的数据 进行操作的语言；
• DDL（data definition language）： DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在 定义或改变表（TABLE）的结构 ，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用；
• DCL（Data Control Language）： 是数据库控制功能。是用来 设置或更改数据库用户或角色权限 的语句，包括（grant,deny,revoke等）语句。在默认状态下，只有sysadmin,dbcreator,db_owner或db_securityadmin等人员才有权力执行DCL

数据库锁机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。

MySQL 数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。实际上MySQL的锁在不同的维度上划分是多种多样的，在特地的场景下，发挥不一样的作用，下面来看看锁的分类。

1、操作的粒度

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

存储引擎	表级锁	行级锁	页面锁
MyISAM	√	×	×
InnoDB	√	√	×
MEMORY	√	×	×
BDB	√	×	√

Ⅰ、全局锁

全局锁就是对整个数据库实例加锁，主要被备份工具使用，使用 逻辑方式进行备份（mydumper，mysqldump） 或 物理方式进行备份（percona-xtrabackup） 。MySQL 提供了一个加全局读锁的方法，命令是 Flush tables with read lock ，简称 FTWRL ，保证了数据的一致性，在备份数据库期间，不会因为数据或表结构的更新，而出现备份文件的数据与预期的不一样。

全局锁的加锁解锁：

# 加锁
flush tables with read lock; # 即FTWRL，推荐使用 

# 解锁
unlock tables; 

⑴、全局锁的弊端

加了全局锁之后，整个数据库都是【只读状态】，如果数据库里有很多数据，备份就会花费很多的时间，这样会造成业务停滞，有很大的弊端：

• 备份期间，业务只能读数据，而不能更新数据；
• 由于 FTWRL 需要关闭所有表对象，当这个时候还有长时间的 select 堵塞 FTWRL， 因为FTWRL会释放所有空闲的table缓存，如果有占用者占用某些table缓存，则会等待占用者自己释放这些table缓存，再去关闭表对象，执行命令时容易导致库卡死；
• 如果在主库上备份。那么在备份期间都不能执行写入操作；
• 如果在从库上备份。那么在备份期间，从库不能执行主库同步过来的 binlog，从而造成主从延时。

⑵、解决办法

如果数据库的引擎支持的事务支持 可重复读 的隔离级别，那么在备份数据库之前 先开启事务 ，会先创建 Read View，然后整个事务执行期间都在用这个 Read View，而且由于 MVCC 的支持，备份期间业务依然可以对数据进行更新操作。

官方自带的逻辑备份工具是 mysqldump。当 mysqldump 使用参数 –single-transaction 的时候，导数据之前就会启动一个事务，来确保拿到一致性视图。如果有的表使用了不支持事务的引擎，那么备份就只能通过 FTWRL 方法。这往往是 DBA 要求业务开发人员使用 InnoDB 替代 MyISAM 的原因之一。

关于 FTWRL 想进一步了解可参考以下链接或自行百度：
mysql FTWRL
MySQL FTWRL(flush tables with read lock) 说明

Ⅱ、表级锁

表锁 是 MySQL 中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持。当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣。

MySQL里面表级别的锁有两种：一种是 表锁，一种是 元数据锁（metadata lock，MDL） 。表锁一般是在数据库引擎不支持行锁的时候才会被用到的。默认使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

⑴、表锁

# 隐式上锁（默认，自动加锁自动释放
insert、update、delete 上写锁
# 显式上锁（手动）
lock table tableName read; # 读锁/共享锁
lock table tableName write;# 写锁/排他锁

#解锁
unlock tables; # 客户端断开的时候也会自动释放锁。

# 查看表上加过的锁
show open tables;

• 表级读锁 : 当前表加read锁，当前连接和其他的连接都可以读操作；但是当前连接写操作会报错，其他连接写操作会被阻塞。

• 表级写锁 : 当前表加write锁，当前连接可以对表做读写操作，其他连接对该表所有操作（读写操作）都被阻塞。

表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。

⑵、元数据锁（metadata lock，简称 MDL）

Metadata lock 机制是为了保证数据一致性存在的。当有用户A查询一个表中的数据时，另一个用户B要对这个表结构做变更，删了一列，那么用户A拿到的结果跟表结构就不一致了，就会出现 waiting for table metadata lock。

Metadata lock 加锁过程是系统自动控制，无法直接干预，自动提交模式下，单语句就是一个事务，执行完了，事务也就结束了。

Metadata lock 是server层的锁，表级锁，每执行一条DML、DDL语句时都会申请 Metadata lock ，DML操作需要 Metadata lock 读锁，DDL操作需要 Metadata lock 写锁，读锁和写锁的阻塞关系如下：

• 读锁和写锁之间相互阻塞，即同一个表上的DML和DDL之间互相阻塞。
• 写锁和写锁之间互相阻塞，即两个会话不能对表同时做表定义变更，需要串行操作。

读锁和读锁之间不会产生阻塞。也就是增删改查不会因为 Metadata lock 产生阻塞，可以并发执行，日常工作中大家看到的Metadata lock 之间的锁等待是innodb行锁引起的，和 Metadata lock 无关。

申请 Metadata lock 的操作会形成一个队列，队列中写锁获取优先级高于读锁。一旦出现写锁等待，不但当前操作会被阻塞，同时还会阻塞后续该表的所有操作。事务一旦申请到 Metadata lock 后，直到事务执行完才会将锁释放。（这里有种特殊情况如果事务中包含DDL操作，mysql会在DDL操作语句执行前，隐式提交commit，以保证该DDL语句操作作为一个单独的事务存在，同时也保证元数据排他锁的释放）。

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

注意： 支持事务的InnoDB引擎表和不支持事务的MyISAM引擎表，都会出现Metadata Lock Wait等待现象。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。

MDL 锁一旦发生会对业务造成极大影响，因为后续所有对该表的访问都会被阻塞，造成连接积压。我们日常要尽量避免 MDL 锁阻塞的发生，下面给出几点优化建议可供参考：

• 开启 metadata_locks 表记录 MDL 锁。

• 设置参数 lock_wait_timeout 为较小值，使被阻塞端主动停止。

• 规范使用事务，及时提交事务，避免使用大事务。

• 增强监控告警，及时发现 MDL 锁。

• DDL 操作及备份操作放在业务低峰期执行。

元数据锁可能会导致的问题：MySQL 元数据锁(MDL)。

Ⅲ、行级锁

顾名思义，MySQL的行锁每次操作锁住一行数据，锁定粒度最小，发生锁冲突的概率最低，并发度最高，但开销大，加锁慢，而且会出现死锁。

⑴、行锁的原理

InnoDB行锁是通过对索引数据页上的记录加锁实现的，所以即使访问的不同记录，只要使用的是同一索引项，也可能会出现锁冲突。

⑵、行锁的实现算法

行锁主要实现算法有 3 种：Record Lock、Gap Lock 和 Next-key Lock。

• RecordLock锁（记录锁）：锁定单个行记录的锁，如果表中没有主键和任何一个索引，那InnoDB会使用隐式的主键来进行锁定。（RC、RR隔离级别都支持）

• GapLock锁（间隙锁）：锁定索引记录间隙，确保索引记录的间隙不变，但不包含记录本身。（RR隔离级别支持）

• Next-key Lock 锁（记录锁+间隙锁）：记录锁和间隙锁组合，锁定数据前后范围，并且锁定记录本身。（RR隔离级别支持）

注意： 在RR隔离级别，InnoDB对于行的查询都是采用 Next-Key Lock 的组合锁定算法，但是 在查询的列是唯一索引（包含主键索引）的情况下，Next-key Lock 会降级为 Record Lock ，仅锁住索引本身而非范围」。

对于InnoDB，如果设置 AUTOCOMMIT = 1，如果没有 显式explicit（即你自己写出begin transaction语句），任何一个语句，都是独立的一个事务，每个语句前面隐式（implicit）加了begin transaction，然后随后自动commit。
 
如果你设置AUTOCOMMIT = 0，如果你没有写commit语句，那么你所有的语句都在一个事务里，等着你最后写一个commit去提交不管commit语句出现没有，都会有Redo Log和Undo Log（commit语句的标志会记录在Redo Log里）。

下面具体看下针对不同的sql语句采用的是那种加锁方式：

• 1）select ... from 语句：InnoDB引擎采用MVCC机制实现非阻塞读，所以对于普通的select语句，InnoDB不加锁。

• 2）select ... from lock in share mode语句：追加了共享锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。

• 3）select ... from for update语句：追加了排他锁，InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。

• 4）update ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。

• 5）delete ... where 语句：InnoDB会使用Next-Key Lock锁进行处理，如果扫描发现唯一索引，可以降级为RecordLock锁。

• 6）insert语句：InnoDB会在将要插入的那一行设置一个排他的RecordLock锁。

⑶、两阶段加锁协议

两阶段加锁协议-S2PL（Strict-2PL）：在一个事务里面，分为加锁(lock)阶段和解锁(unlock)阶段,也即所有的lock操作都在unlock操作之前，只有提交(commit)或者回滚(rollback)时才是解锁阶段，

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

事务A持有的两个记录的行锁都是在commit的时候才释放的，事务B的update语句会被阻塞，直到事务A执行commit之后，事务B才能继续执行。

在InnoDB事务中，行锁是在需要的时候才加，但并不是不需要了就立刻释放，而是事务结束时释放。如果事务中需要锁多个行，要把最可能造成锁冲突，最可能影响并发度的语句尽量往后放。

⑷、死锁和死锁检测

死锁：并发系统中出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，会导致几个线程无限等待，称为死锁。

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

事务A在等待事务B释放id=2的行锁，而事务B在等待事务A释放id=1的行锁。事务A和事务B在互相等待对方的资源释放，就是进入了死锁状态。

当出现死锁以后，有两种策略：

• 一种策略是，直接进入等待，直到超时。这个超时时间可以通过参数innodb_lock_wait_timeout来设置。
• 另一种策略是，发起 死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。

在InnoDB中，innodb_lock_wait_timeout的默认值是50s，意味着如果采用第一个策略，当出现死锁以后，第一个被锁住的线程要过50s才会超时退出，然后其他线程才有可能继续执行。对于在线服务来说，这个等待时间往往是无法接受的。

正常情况下还是要采用主动死锁检查策略，而且innodb_deadlock_detect的默认值本身就是on。主动死锁监测在发生死锁的时候，是能够快速发现并进行处理的，但是它有额外负担的。每当一个事务被锁的时候，就要看看它所依赖的线程有没有被别人锁住，如此循环，最后判断是否出现了循环等待，也就是死锁。

参考：MySQL锁详解

Ⅳ、页级锁

页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行锁之间，并发度一般，页级锁和行级锁一样，会发生死锁。应用在 BerkeleyDB存储引擎中，一般很少见，了解一下即可。

2、操作的类型

锁类型按照 操作类型划分 有「读锁（S锁），写锁（X锁）」，其实它们与共享锁，排他锁是一个意思，只是不同叫法而已。

	共享锁（S）	排他锁（X）
共享锁（S）	兼容	互斥
排他锁（X）	互斥	互斥

Ⅰ、共享锁（读锁/S锁）

共享锁（Shared Lock）又称为读锁，简称S锁，是一种行级锁。

共享锁 就是多个事务对于同一数据共享一把锁，都能访问到数据，但是只能读不能修改。

# 加锁方式
select ... from ... lock in share mode;

# 事务提交或回滚释放：
commit;
rollback;

Ⅱ、排他锁（写锁/X锁）

排他锁（EXclusive Lock）又称为写锁，简称X锁，是一种行锁也可以是表锁。

排他锁 就是不能与其他锁并存，即当前写操作没有完成前，会阻断其他写锁和读锁。

注意：其他事务不加锁的读是不会被阻塞的，阻塞的是加锁的读

# 加锁方式:
# innodb引擎默认会在update，delete语句加上 for update
SELECT * FROM student FOR UPDATE; # 排他锁
INSERT INTO student values ...  # 排他锁
DELETE FROM student WHERE ...  # 排他锁
UPDATE student SET ...  # 排他锁

# 事务提交或回滚释放：
commit;
rollback;

Ⅲ、意向锁（表锁）

意向锁（Intention Lock） 简称 I锁，是表级锁的一种，它是由数据库引擎自行维护的，用户自己无需也无法操作意向锁。

意向锁 能够将 检查行锁 的时间复杂度由 O(n) 变成 O(1)，其加锁的具体做法就是，当一个事务想要获取表中某一行的（共享/排他）锁的时候，它会自动尝试给当前表的加上意向（共享/排他）锁，用于防止全表扫描的一种锁。

⑴、意向共享锁（IS锁-表级锁）

意向共享锁（Intention Shared Lock） 简称IS锁，事务想要给某一个数据行加行级共享锁（S锁）之前必须先获取该表的IS锁（表级锁）

⑵、意向排他锁（IX锁-表级锁）

意向排他锁（Intention EXclusive Lock） 简称IX锁，事务想要给某一个数据行加行级排他锁（X锁）之前必须先获取该表的IX锁（表级锁）

Ⅳ、意向锁的兼容互斥性

意向锁之间的兼容互斥性：意向锁之间是互相兼容的。

	意向共享锁（IS）	意向排他锁（IX）
意向共享锁（IS）	兼容	兼容
意向排他锁（IX）	兼容	兼容

意向锁与其他锁兼容互斥性：意向锁与普通的排他锁/共享锁互斥（这的排他锁（X锁）共享锁（S锁）指的都是表锁，意向锁不会与行级的共享锁/排他锁互斥。上面表锁部分讲到的 显式加锁 加的就是排他锁/共享锁）。

	意向共享锁（IS）	意向排他锁（IX）
表级共享锁（S）	兼容	互斥
表级排他锁（X）	互斥	互斥

 

3、操作的性能

从操作的性能可分为乐观锁和悲观锁。乐观锁/悲观锁其实都是概念上的，只是在并发下防止数据被修改的一种加锁形式。

Ⅰ、乐观锁

乐观锁：乐观锁是对于数据冲突保持一种乐观态度，每次读取数据的时都认为其他线程不会修改数据，所以不上锁，只是在数据修改后提交时才通过【版本号机制或者CAS算法】来验证数据是否被其他线程更新。

优点 ：没有加锁和解锁操作，可以提高吞吐量。
 
缺点：乐观锁需要自己实现，且外部系统不受控制。
 
实现方式：有version方式（常用）和CAS算法两种实现方式。

• 1、version方式：一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
   
• 2、CAS算法：乐观锁的核心算法是CAS(Compareand Swap，比较并交换)，它涉及到三个操作数：内存值、预期值、新值，当且仅当预期值和内存值相等时才将内存值修改为新值。这样处理的逻辑是，首先检查某块内存的值是否跟之前我读取时的一样，如不一样则表示期间此内存值已经被别的线程更改过，舍弃本次操作，否则说明期间没有其他线程对此内存值操作，可以把新值设置给此块内存。
  • CAS的核心思想是通过比对内存值与预期值是否一样而判断内存值是否被改过，但这个判断逻辑不严谨，假如内存值原来是A，后来被一条线程改为B，最后又被改成了A，则CAS认为此内存值并没有发生改变，但实际上是有被其他线程改过的，这种情况对依赖过程值的情景的运算结果影响很大。解决的思路是引入版本号，每次变量更新都把版本号加一。

 
应用场景：读多写少。

Ⅱ、悲观锁

悲观锁：对数据的修改抱有悲观态度的一种并发控制方式，悲观的认为当前会话拿到的数据是被修改过的，所以在操作数据之前先加锁。

从广义上来讲，前面提到的行锁、表锁、读锁、写锁、共享锁、排他锁等，这些都属于悲观锁范畴。

优点 ：可以保证数据的独占性和正确性。
 
缺点：每次请求都需要加锁、释放锁，这个过程会降低系统性能。
 
实现方式：依靠数据库提供的锁机制实现。
 
应用场景：写多读少。

---

三、总结

这里对上面锁进行简单总结：

MySQL 各种锁分别在什么情况下会用到，有什么注意事项：

锁	场景	问题
全局锁	全库逻辑备份	加了全局锁之后，整个数据库都是【只读状态】，如果数据库里有很多数据，备份就会花费很多的时间，这样会造成业务停滞。
表锁	当存储引擎不支持行级锁时，使用表锁。 SQL 语句没有匹配到索引时，使用表锁。	表级读锁会阻塞写操作，但是不会阻塞读操作。而写锁则会把读和写操作都阻塞。
元数据锁（MDL）	对表做增删改查时，会加上 MDL 读锁。 对表结构做变更时，会加上 MDL 写锁。	加上 MDL 锁后，后续所有对该表的访问都会被阻塞。
行锁	增删改查匹配到索引时，会使用行级锁。	多个事务在互相等待对方的行锁释放，导致死锁。

规范使用事务，及时提交事务，避免使用大事务，DDL 操作及备份操作放在业务低峰期执行。
 

MySQL 各种锁的显式加锁解锁方式：

锁	显式加锁	显式解锁
全局锁	flush tables with read lock; // 即FTWRL，推荐使用	unlock tables;
表锁	lock table tableName read; # 表读锁/表共享锁 lock table tableName write;# 表写锁/表排他锁	unlock tables; # 客户端断开的时候也会自动释放锁。
行锁	select … from … lock in share mode; # 行共享锁 SELECT * FROM student FOR UPDATE; # 行排他锁	commit; rollback; # 事务提交或回滚释放：

共享锁和排他锁的互斥关系：共享锁与共享锁之前兼容，排他锁不能与其他锁并存。意向锁之间是互相兼容的，意向锁与普通的表级排他锁/表级共享锁互斥（意向锁不会与行级的共享锁/排他锁互斥）。

乐观锁与悲观锁比较：

不同点	乐观锁	悲观锁
实现方式	乐观锁需要自己实现，且外部系统不受控制，没有加锁和解锁操作，可以提高吞吐量。	每次请求都需要加锁、释放锁，这个过程会降低系统性能。单可以保证数据的独占性和正确性。
实现方式	有version方式（常用）和CAS算法两种实现方式。	依靠数据库提供的锁机制实现。
应用场景	读多写少 。	写多读少。

---

参考链接：
MySQL(十三)：小一万字+14张图读懂锁机制
MySQL中锁机制实现原理