MySQL：锁总结

锁的类型

• 数据库锁设计的初衷是处理并发问题。作为多用户共享的资源，当出现并发访问时，数据库需要合理的控制资源的访问规则，而锁就是用来实现这些访问规则的重要数据。
• 根据加锁的范围，MySQL里面的锁大致可以分为全局锁、表锁和行锁三类。

全局锁

是什么？

顾名思义，全局锁就是对整个数据库实例加锁。

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

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

特点：

• 对整个库加锁
• 开销和加锁时间介于表级锁和行级锁之间；
• 锁定粒度介于表级锁和行级锁之间，并发度一般
• 会出现死锁；

为啥需要全局锁

以前有一种做法，是通过FTWRL确保不会有其他线程对数据库更新，然后对整个库做备份。注意，在备份过程中整个库完全处于只读状态。

但是让整个库都只读，听上去就很危险：

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

看起来加全局锁不太好。但是细想一下，备份为什么要加锁呢？我们来看下不加锁有什么后果。

假设你要维护一个购买系统，关注的是用户账户余额表和用户课程表。

• 现在发起一个逻辑备份。假设在备份期间，有一个用户，购买了一门课程，业务逻辑里就要扣掉他的余额，然后往已购课程里面加上一门课。

• 如果时间顺序上是先备份余额表u_account，然后用户购买，然后备份课程表u_course，会怎么样呢？如下图：

• 可以看到，用户A里面的数据状态是“账户余额没扣，但是用户课程表多了一门课”。
  

• 如果备份数据表的顺序反过来，那么和上面就相反了。

也就是说，不加锁的话，备份系统备份得到的库不是一个逻辑时间点，这个视图是逻辑不一致的。

说到视图，我们马上就会想起事务隔离。在事务隔离中，其中是有一个方法能够拿到一致性视图的：在可重复读隔离级别下开启一个事务

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

那有了这个功能，为什么还要FTWRL呢？

• 一致性读是好，但前提是引擎要支持这个隔离级别。比如，对于MyISAM这种不支持事务的引擎，如果备份过程中有更新，总能取到最新的数据，那么就破坏了备份的一致性。这是，我们就需要使用FTWRL命令了
• 所以，single-transaction方法只适用于所有的表使用事务引擎的库。如果有的表使用了不支持事务的引擎，那么备份就只能使用FTWRL命令。这也是为什么要用InnoDB而不是MyISAM的原因之一。

既然要全库只读，那为什么不使用set global readonly=true 的方式呢

确实 readonly 方式也可以让全库进入只读状态，但还是会建议你用 FTWRL 方式，主要有两个原因：

• 第一个原因：在有些系统中，readonly的值会被用来做其他逻辑，比如用来判断一个库是主库还是从库。因此，修改global变量的方式影响面更大，不建议使用。
• 第二个原因：在异常处理机制上有差异
  • 如果执行FTWRL命令之后由于客户端发送异常断开，那么MySQL会自动释放这个全局锁，整个库可以回到正常更新的状态
  • 而将整个库设置为readonly之后，如果客户端发生异常，则数据库会一直readonly状态。这样会导致整个库长时间出于不可写的状态。

业务的更新不只是增删改数据，还可能是加字段等修改表结构的操作。一个库被全局锁上之后，你要对里面任何一个表做加字段的操作，都是会被锁住的。

但是，即使没有被全局锁住，加字段也不是一帆风顺的。因为你可能会碰到表级别锁

表级锁

MySQL里面表级别的锁有三种：

• 表锁
• 元数据锁（meta data lock）
• 表的意向锁

表锁

MyISAM默认是表锁

分类

表共享读锁

• 表共享读锁（read lock），也叫共享锁（shared lock）：
  • 一旦某个客户端给表加了共享读锁，针对同一个表，（无论是自己还是其他客户端）可以同时读但是不能同时写（select）
  • 即：读锁会阻塞写操作，不会阻塞读操作
  • Lock Tables xxx Read：这是加表级共享锁

表共享写锁

• 表共享写锁（write lock），也叫排他锁（exclusive lock）：
  • 当前操作没完成之前，只有本客户端可以进行读写，但是会阻塞其他客户端读和写操作（update、insert、delete）
  • 即：写锁会阻塞读和写操作
  • Lock Tables xxx Write：这是加表级独占锁

特点

• 对整张表加锁
• 开销小， 加锁快
• 锁粒度大，发生锁冲突概率大，并发性低
• 不会出现死锁

如何上锁

（1）隐式上锁（默认，自动加锁自动释放）

select //上读锁
insert、update、delete //上写锁

（2）显式上锁（手动）

lock table tableName read;//读锁
lock table tableName write;//写锁

（3）解锁（手动）#

unlock tables;//所有锁表

使用建议

表锁一般是在数据库引擎不支持行锁的时候才会被用到的。如果你发现你的应用程序里有lock tables 这样的语句，你需要追查一下，比较可能的情况是：

• 要么是你的系统现在还在用 MyISAM 这类不支持事务的引擎，那要安排升级换引擎；
• 要么是你的引擎升级了，但是代码还没升级。将lock tables 和 unlock tables 改成 begin 和 commit，问题就解决了。

元数据锁

元数据就是描述数据的数据，这里可以理解为表的结构。

• 它不需要显示使用，在访问一个表的时候被自动加上。
• MDL的作用是，保证读写的正确性。可以想象一下，如果一个查询正在遍历一个表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结构就更表结构对应不上了，这肯定是不行的。

因此，在MySQL5.5版本中引入了MDL，当对一个表做增删改的时候，加MDL读锁；当要对表结构变更时，加MDL表锁。

• 读锁之前不互斥，因此可以由多个线程同时对一张表增删改查
• 读写锁之间、写锁之间是互斥的，用来保证变更表操作的安全性。

意向锁

为什么要有意向锁呢？

• 有这么一个场景，当一个事务想给另一张表加上表锁，其前提是没有其他任何事务已经锁定了这张表的任意一行数据
• 也就是说，需要去全表扫描，看是否有哪一行数据被其他事务锁定了，但是这非常低效
• 因此引入了，意向锁相当于一个标识，表示是否有其他事务锁定该表的其他行数据。
  • 当有事务给表的数据行加了共享锁或者排他锁，同时会给表设置一个标识，代表已经有行锁了
  • 其他事务不需要逐行判断有没有行锁可能跟表锁冲突了，直接读这个标识就可以确定自己该不该加表锁，这个标识就是意向锁。
    主要目的是提高加表锁的概率

意向锁是一种表锁，锁定的粒度是整张表，分为意向共享锁和意向排他锁两类。

意向共享锁

• 对整个表加共享锁之前，需要先获取意向共享锁。由语句select…lock in share mode添加。

意向排他锁

• 对整个表加排他锁之前，需要先获取到意向排他锁。由insert,update,delete,select…for update添加。

意向锁与其他锁的兼容互斥情况如下图所示，简单来说就是，意向锁之间是兼容的，意向排他锁和除了意向锁之外的锁都是互斥的，排他锁（X）与任何锁都是互斥的。

关于使用：MySQL里是如何加表锁呢？

MySQL中的表锁，其实是极为鸡肋的一个东西，几乎一般很少会用到

表锁可以用下面手动语法加

• Lock Tables xxx Read：这是加表级共享锁
• Lock Tables xxx Write：这是加表级独占锁

作用：

• 与FTWL类似，可以用unlock tables主动释放锁，也可以在客户端断开的时候自动释放
• 需要注意，lock tables语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。
  • 举个例子，如果在某个线程A中执行lock tables t1 read, t2 write;这个语句，则其他线程写t1、读写t2的语句都会被阻塞
  • 同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操作。连写t1都不允许，自然也不能访问其他表

说明：

• 在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。
• 其实一般来讲，几乎没人会用这两个语法去加表锁，所以才说表锁特别的鸡肋。
  • 对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整张表的影响还是太大

还有另外两种情况会加表锁

• 如果有事务在表里执行增删改操作，那么会在行级加独占锁，还会在表级加一个意向独占锁
• 如果有事务在表里执行查询操作，那么会在表级加一个意向共享锁

其实平时我们操作数据库，比较常见的两种表锁，是更新和操作操作加的意向独占锁和意向共享锁。但是这个意向独占锁和意向共享锁，平时把它们当做透明的就可以了，因为这两种意向锁不会互斥。

为什么呢？

• 因为假设有一个事务要在表里更新id=10的一行数据，在表上加了一个意向独占锁，此时另外一个事务要在表里面更新id=20的一行数据，也会在表上加一个意向独占锁。这两把锁会是互斥的吗？
• 很明显不是。因为他们俩更新的都是表里不同的数据。也就是说意向锁根本不会互斥。
• 同样，假设一个事务要更新表里的数据，在表级加了一个意向独占锁，另一个事务要读取表中的数据，在表级加了一个意向共享锁，它们是互斥的吗？
• 很明显也不是。意向独占锁和意向共享锁之间也不是互斥的。

所以说，表级的意向锁压根就没有什么作用。

但是，手动加表级共享锁和独占锁，以及更新和查询的时候自动表表级别加的意向共享锁和意向独占锁，它们之间反而是有一定的互斥关系。如下：

• 如果你手动加了表级独占锁（LOCK TABLES xxx WRITE），那么此时任何人都不能执行更新操作了。
• 如果你手动加了表级共享锁（LOCK TABLES xxx WRITE），那么此时任何人也不能执行更新操作了。因为更新就要加意向独占锁，跟手动加的表级共享锁，是不吃的

也就是说，如果手动加了表级共享锁或者独占锁，此时会阻塞调其他事务的一些正常的读写操作，跟他们自动加的意向锁都是互斥的。

实际开发中，根本不会手动加表级锁，所以一般来说读写操作自动加的表级意向锁，互相之间绝对不会互斥。

一般来讲，都是对同一行数据的更新操作加的行级独占锁是互斥，跟读操作都是不互斥的，读操作默认都是走MVCC机制读快照版本的

行级锁

MySQL的行锁是在引擎层由各个引擎自己实现的。但不是所有的引擎都支持行锁，比如MyISAM就不支持行锁。

• 不支持行锁就意味着并发控制只能使用表锁，对于这种引擎的表，同一张表上任何时刻只能由一个更新在执行，这就会影响到业务的并发度。
• InnoDB支持行锁，这也是MyISAM 被 InnoDB 替代的重要原因之一。

行锁

什么是行锁？

• 行锁，就是针对数据表中行记录的锁。比如事务A更新了一行，而这时候事务B也要更新同一行，则必须等待事务A的操作完成之后才能进行更新
• 行锁是锁住单个记录的锁，防止其他事务对其update、delete的操作，在RR和RC隔离级别中都支持。
• 行锁是通过锁住索引来实现的
• 在多个事务并发更新数据的时候，都是要在行级别加独占锁的，这就是行锁。

分类

共享锁

• 读锁（read lock），也叫共享锁（shared lock）
  • 允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
  • 一个事务给一个数据行加共享锁时，必须先获得表的 意向共享锁（IS）

排他锁

• 写锁（write lock），也叫排他锁（exclusive lock）
  • 允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁（只允许获取写锁的线程操作，其他线程的任何操作都不能进行）
  • 一个事务给一个数据行加排他锁时，必须先获得该表的意向排它锁（IX）

特点

1. 对一行数据加锁
2. 开销大
3. 加锁慢
4. 会出现死锁
5. 锁粒度小，发生锁冲突概率最低，并发性高
6. 行锁是通过对索引上的索引项加锁来实现的

行锁的三种算法

MySQL的InnoDB存储引擎支持三种行锁算法：

• record lock（记录锁）：单个记录上的锁
• gap lock（间隙锁）：锁定一个范围，但是不包含记录本身
• next-key lock（record lock + gap lock）：锁定一个范围，并且锁定记录本身

行锁的注意点

• 只有通过索引条件检索数据时，InnoDB才会使用行锁，否则会使用表锁(索引失效，行锁变表锁)
• 即使是访问不同行的记录，如果使用的是相同的索引键，会发生锁冲突
• 如果数据表建有多个索引时，可以通过不同的索引锁定不同的行

页锁

开销、加锁时间和锁粒度介于表锁和行锁之间，会出现死锁，并发处理能力一般（此锁不做多介绍）

事务并发问题如何解决

• 脏写：对读取操作加排他锁（让事务变成串行操作）
• 脏读：隔离级别为Read uncommitted
• 不可重复读：使用Next-Key Lock算法来避免
• 幻读：间隙锁（Gap Lock）解决

如何排查锁？

表锁

（1）查看表锁情况

show open tables;

（2）表锁分析

1. table_locks_waited
   出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次值加1），此值高说明存在着较严重的表级锁争用情况
2. table_locks_immediate
   产生表级锁定次数，不是可以立即获取锁的查询次数，每立即获取锁加1

show status like 'table%';

行锁

行锁分析

show status like 'innodb_row_lock%';

1. innodb_row_lock_current_waits //当前正在等待锁定的数量
2. innodb_row_lock_time //从系统启动到现在锁定总时间长度
3. innodb_row_lock_time_avg //每次等待所花平均时间
4. innodb_row_lock_time_max //从系统启动到现在等待最长的一次所花时间
5. innodb_row_lock_waits //系统启动后到现在总共等待的次数

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2. 合理设计索引，尽量缩小锁的范围
3. 尽可能较少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽可能低级别事务隔离