MySQL高级之锁机制详解（五）

一、概述

1. 什么是锁

锁是计算机协调多个进程或线程并发访问某一资源的机制

• 在数据库中，除传统的计算资源（CPU、RAM、I/O）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所在有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

2. 为什么要使用锁

• 数据库锁定机制简单来说，就是数据库为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则。对于任何一种数据库来说都需要有相应的锁定机制。
• 当多个用户并发地存取数据时，在数据库中就可能会产生多个事务同时操作同一行数据的情况，若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据的一致性。

二、锁的分类

MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别。

1. 按锁的操作类型分类

• 共享锁：又称读锁，S锁。即会阻塞其他事务修改表数据，但是不影响其他事务读。
• 排他锁：又称写锁，X锁。会阻塞其他事务读和修改表数据。

2. 按锁的操作粒度分类

• 表锁：为单个表加锁。
• 行锁：对行数据行进行加锁。
• 页锁：介于表锁和行锁之间，一次锁定相邻的一组记录。
• 全局锁：对整个数据库实例加锁。

三、MySQL各种锁详解

MySQL里有非常多锁的概念，比如：乐观锁、悲观锁、行锁、表锁、Gap锁（间隙锁）、MDL锁（元数据锁）、意向锁、读锁、写锁、共享锁、排它锁。这些锁并不是在一个维度上来说的，下面将就其中主要的锁进行详解。

1. 共享锁与排他锁（读锁和写锁）

1.1 共享锁

• 含义

  共享锁又叫做读锁或S锁，其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。

• 用法

  --查询语句后加上LOCK IN SHARE MODE给表加行读锁
  SELECT ... LOCK IN SHARE MODE;
  
  --使用 lock table给表加表读锁
  lock table <表名> read;
  

  Mysql会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。

1.2 排他锁

• 含义

  排他锁又称写锁或X锁，如果事务对数据加上排他锁后，则其他事务不能再对该数据加任何类型的锁。获得排他锁的事务既能读数据，又能修改数据。

• 用法

  --查询语句后加上FOR UPDATE给表加行写锁
  SELECT ... FOR UPDATE;
  
  --或使用 lock table给表加表写锁
  lock table <表名> write;
  

  Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。

1.3 二者关系

读锁和写锁的加锁关系如下，Y 表示可以共存，X 表示互斥

	读锁	写锁
读锁	Y	X
写锁	X	X

2. 表锁、行锁和页锁

除了表锁、行锁和页锁外，MySQL还有一种粒度更大的锁叫全局锁。

2.1 全局锁

• 含义

  全局锁就对整个数据库实例加锁，加锁后整个实例就处于只读状态，后续的MDL的写语句，DDL语句，已经更新操作的事务提交语句都将被阻塞。

• 用法

  --加全局锁
  flush tables with read lock;
  
  --释放全局锁(执行完还需要断开加锁session的连接)
  unlock tables;
  

• 使用场景

  典型的使用场景是做全库的逻辑备份，对所有的表进行锁定，从而获取一致性视图，保证数据的完整性

2.2 表锁

• 含义

  直接锁定整张表。该锁定机制最大的特点是实现逻辑非常简单，获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。但是会出现锁定资源争用的概率提高，影响性能。

• 用法

  --加读或写锁
  lock table <表名> read(write);
  
  --释放锁
  unlock tables;
  

  read表示读锁，write表示写锁。当给表加上读锁时，当前线程只能访问该表，不能访问其余表，所有线程只能对该表进行读操作，不能修改。当给表加上写锁时，当前线程只能访问或修改该表，不能访问其它表，其余线程不能访问或修改该表。

• 使用场景

  使用表级锁定的主要是MyISAM，MEMORY，CSV等一些非事务性存储引擎。

2.3 行锁

• 含义

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

• 用法

  --行读锁
  SELECT ... LOCK IN SHARE MODE;
  
  --行写锁
  SELECT ... FOR UPDATE;
  

  行锁不需要显示释放，当事务被提交时，该事务中加的行锁就会自动被释放。

• 使用场景

  使用行级锁定的主要是InnoDB存储引擎。

2.4 页锁

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

2.5 这几种锁的区别比较

总的来说，MySQL这3种锁的特性可大致归纳如下：

• 表锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
• 行锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
• 页锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从锁的角度来说，表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。

3. 乐观锁和悲观锁

3.1 乐观锁

• 含义

  乐观锁总是假设不会发生冲突，因此读取资源的时候不加锁，只有在更新的时候判断在整个事务期间是否有其他事务更新这个数据。如果没有其他事务更新这个数据那么本次更新成功，如果有其他事务更新本条数据，那么更新失败。乐观锁无需像悲观锁那样维护锁资源，做加锁阻塞等操作，因此更加轻量化。

• 实现

  乐观锁的实现可以使用数据库的版本号或基于redis实现

  • 数据库版本号
    • 1）给每条数据都加上一个 version 字段，表示版本号
    • 2）开启事务，先读取数据，包含当前数据的版本号version1
    • 3）更新数据的时候比较version1和数据库里面的版本号是否一致，即执行update t set version = version + 1 where version = version1，如果执行成功表示版本号没有变化，数据没有被修改。否则就会更新失败，表示数据已经被修改了。
  • 基于redis（利用redis的事务机制以及watch指令）

• 使用场景

  适合读多写少，高并发场景。

3.2 悲观锁

悲观锁总是假设会发生冲突，因此在读取数据时候就将数据加上锁，这样保证同时只有一个线程能更改数据。前面介绍的表锁、行锁等都是悲观锁。悲观锁适合写多读少，并发量较小的场景。

4. 元数据MDL锁（metadata lock）

MDL锁是一种表级锁，MDL锁不需要显示使用。MDL 锁是用来避免数据操作与表结构变更的冲突，当你执行一查询语句时，这个时候另一个线程在删除表中的一个字段，那么两者就发生冲突了，因此MySQL在5.5版本以后加上了MDL锁。当对一个表做增删查改时会加MDL读锁，当对一个表做结构变更时会加MDL写锁。读锁相互兼容，读锁与写锁不能兼容。

5. InnoDB中常见的锁

5.1 意向锁(Intention Locks)

意向锁是一个表级别的锁，InnoDB为了支持多粒度锁机制，允许行级锁与表级锁共存，因此引入了意向锁。意向锁是指，未来的某个时刻，事务可能要加共享/排它锁了，先提前声明一个意向。意向锁是Innodb自动加的，不需要用户干预。

• 分类

  • 意向共享锁：事务有意向对表中的某些行加共享S锁；
  • 意向排它锁：事务有意向对表中的某些行加排它X锁。

• 意向锁与共享排它锁的兼容互斥关系

  	共享锁	排他锁	意向共享锁	意向排他锁
  共享锁	兼容	互斥	兼容	互斥
  排他锁	互斥	互斥	互斥	互斥
  意向共享锁	兼容	互斥	兼容	兼容
  意向排他锁	互斥	互斥	兼容	兼容

  可以看到意向锁之间是互相兼容的，排他锁与其余锁是完全互斥的

5.2 记录锁(Record Locks)

• 顾名思义，记录锁就是为某行记录加锁，它封锁该行的索引记录。

• 例如

  --给id=1的数据加记录锁，id为主键
  SELECT * FROM table WHERE id = 1 FOR UPDATE;
  

  需要注意id 列必须为唯一索引列或主键列，同时查询或更新语句必须为精准匹配（=），不能为 >、<、like等，否则上述语句加的锁就会变成临键锁（下面会讲解）。注意，如果在唯一索引上查询或更新不存在的记录时会加间隙锁。

5.3 间隙锁(Gap Locks)

是Innodb存储引擎在可重复读情况下为了解决幻读问题时引入的锁机制。它封锁索引记录中的间隔，或者第一条索引记录之前的范围，又或者最后一条索引记录之后的范围。例如

select * from lock_example where id between 8 and 15 for update;

这个SQL会封锁区间（8，15），防止其他事务插入id位于该范围的数据，导致幻读的存在。如果把事务的隔离级别降级为读提交(Read Committed, RC)，间隙锁则会自动失效。

• 具体间隙锁的案例可以参考如下链接

  Mysql加锁过程详解（9）-innodb下的记录锁，间隙锁，next-key锁

5.4 临键锁(Next-key Locks)

可以理解为一种特殊的间隙锁。它是记录锁+间隙锁，即锁定一个范围，并且锁定记录本身。它可以解决幻读的问题。 每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁。

• 例如表t1，其中id为主键，col1为普通索引

  id	col1
  1	5
  4	15
  7	20

  因为临键锁只与非唯一索引有关，所以该表中潜在的临键锁有：

  • (负无穷,5]
  • (5,15]
  • (15,20]
  • (20,正无穷]

  在事务A执行如下命令

  -- 根据非唯一索引列 UPDATE 某条记录
  UPDATE table SET col1 = 16 WHERE col1 = 15;
  -- 或根据非唯一索引列 锁住某条记录
  SELECT * FROM table WHERE col1 = 15 FOR UPDATE;
  

  之后在事务B中执行命令

  INSERT INTO t1 VALUES(5, 18);
  
  

  会进入阻塞等待，因为事务A拥有区间(5,20)的临键锁。

5.5 插入意向锁

是一种间隙锁，非意向锁。它在插入操作时产生。多个事务，在同一个索引，同一个范围区间插入记录时，如果插入的位置不冲突，不会阻塞彼此。假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

• 普通的间隙锁不允许在（上一条记录，本记录）范围内插入数据
• 插入意向锁允许 在（上一条记录，本记录）范围内插入数据

插入意向锁的作用是为了提高并发插入的性能。

5.6 自增锁

自增锁是一种特殊的表级别锁，专门针对事务插入AUTO_INCREMENT类型的列。最简单的情况，如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。例如表t1有两列，id和col1，其中id为自增主键。

• 事务A执行插入操作，未提交

  insert into t1(col1) values(xxx);
  
  

• 事务B执行插入操作

  insert into t1(col1) values(yyy);
  
  

  此时事务B的插入操作会被阻塞，直到事务A提交释放自增锁后才能执行。