MySQL的锁

太过爱你忘了你带给我的痛 2023-10-08 18:51 2阅读 0赞

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

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

## 一.全局锁 ##

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

要使用全局锁，则要执行这条命令：

flush tables with read lock

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

如果要释放全局锁，则要执行这条命令：

unlock tables

常见的使用场景

做全库逻辑备份，也就是把整库每个表都 select 出来存成文本。

## 二.表级锁 ##

MySQL 里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。这其中还有意向锁和AUTO-INC 锁的概念，这里一并列举一下。

### 1.表锁 ###

//表级别的共享锁，也就是读锁；
    lock tables table_name read;
    
    //表级别的独占锁，也就是写锁；
    lock tables table_name write;

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

### 2.MDL锁 ###

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

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

对一张表进行 CRUD 操作时，加的是 MDL 读锁；  
对一张表做结构变更操作的时候，加的是 MDL 写锁；

> **问题1:MDL 不需要显示调用，那它是在什么时候释放的?**
> 
> MDL 是在事务提交后才会释放，这意味着事务执行期间，MDL 是一直持有的。

> **问题2:为什么线程因为申请不到 MDL 写锁，而导致后续的申请读锁的查询操作也会被阻塞？**
> 
> 这是因为申请 MDL 锁的操作会形成一个队列，队列中**写锁获取优先级高于读锁**，一旦出现 MDL 写锁等待，会阻塞后续该表的所有 CRUD 操作。
> 
> 所以为了能安全的对表结构进行变更，在对表结构变更前，先要看看数据库中的长事务，是否有事务已经对表加上了 MDL 读锁，如果可以考虑 kill 掉这个长事务，然后再做表结构的变更。

### 3.意向锁 ###

在使用 InnoDB 引擎的表里对某些记录加上**共享锁**之前，需要先在表级别加上一个**意向共享锁**，在使用 InnoDB 引擎的表里对某些记录加上**独占锁**之前，需要先在表级别加上一个**意向独占锁。**

**意向共享锁和意向独占锁是表级锁，不会和行级的共享锁和独占锁发生冲突，而且意向锁之间也不会发生冲突，只会和共享表锁（*lock tables ... read*）和独占表锁（*lock tables ... write*）发生冲突。**

表锁和行锁是满足读读共享、读写互斥、写写互斥的。

select 对表加共享锁和独占锁：

//先在表上加上意向共享锁，然后对读取的记录加共享锁
    select ... lock in share mode;
    
    //先表上加上意向独占锁，然后对读取的记录加独占锁
    select ... for update;

如果没有「意向锁」，那么加「独占表锁」时，就需要遍历表里所有记录，查看是否有记录存在独占锁，这样效率会很慢。

那么有了「意向锁」，由于在对记录加独占锁前，先会加上表级别的意向独占锁，那么在加「独占表锁」时，直接查该表是否有意向独占锁，如果有就意味着表里已经有记录被加了独占锁，这样就不用去遍历表里的记录。

所以，**意向锁的目的是为了快速判断表里是否有记录被加锁**

### **4.**AUTO-INC 锁 ###

表里的主键通常都会设置成自增的，这是通过对主键字段声明 `AUTO_INCREMENT` 属性实现的。

之后可以在插入数据时，可以不指定主键的值，数据库会自动给主键赋值递增的值，这主要是通过 **AUTO-INC 锁**实现的。

AUTO-INC 锁是特殊的表锁机制，锁**不是再一个事务提交后才释放，而是再执行完插入语句后就会立即释放**。

**在插入数据时，会加一个表级别的 AUTO-INC 锁**，然后为被 `AUTO_INCREMENT` 修饰的字段赋值递增的值，等插入语句执行完成后，才会把 AUTO-INC 锁释放掉。

那么，一个事务在持有 AUTO-INC 锁的过程中，其他事务的如果要向该表插入语句都会被阻塞，从而保证插入数据时，被 `AUTO_INCREMENT` 修饰的字段的值是连续递增的。

AUTO-INC锁可以使用innodb\_autoinc\_lock\_mode变量来配置自增锁的算法。innodb\_autoinc\_lock\_mode变量可以选择三种值如下表：

<table> 
 <thead> 
  <tr> 
   <th>innodb_autoinc_lock_mode</th> 
   <th>变量含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0</td> 
   <td>传统锁模式</td> 
  </tr> 
  <tr> 
   <td>1</td> 
   <td>连续锁模式</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td><strong>交错锁模式(MySQL8默认)</strong></td> 
  </tr> 
 </tbody> 
</table>

#### 传统锁模式 ####

在传统锁模式下，所有的"INSERT-LIKE"语句为AUTO\_INCREMENT列使用表级AUTO\_INC锁。**一个事务的"INSERT-LIKE"语句在语句执行结束后释放AUTO\_INC表级锁，而不是在事务结束后释放**。这样做的目的是为了保证这个"INSERT-LIKE"语句的自增列的值是连续的。

#### 连续锁模式 ####

连续锁模式对于“Simple inserts”不会使用表级锁，而是使用一个轻量级锁来生成自增值，因为InnoDB可以提前直到插入多少行数据。自增值生成阶段使用轻量级互斥锁来生成所有的值，而不是一直加锁直到插入完成。但是如果其他事务持有AUTO\_INC锁，那么“Simple Inserts”类语句也需要等待其他事务完成才能使用轻量级锁来生成所有的自增值。

连续锁模式对于“bulk inserts”类语句使用AUTO\_INC表级锁直到语句完成。使用表级AUTO\_INC锁的语句：INSERT ... SELECT、REPLACE ... SELECT、LOAD DATA 。

当innodb\_autoinc\_lock\_mode=1时，在语句复制格式下(BINLOG\_FORMAT=STATEMENT)，BINLOG中没有记录主库执行过程中获取到的所有自增值及其对应行的信息，要保证"Bulk insert"操作主从复制数据一致就必须保证语句在主库和从库执行时获取到相同自增值，而因此只能通过控制“获取连续自增值”的方式来实现，同时为避免受其他事务插入操作影响，就必须在表级别加锁且保证持有锁至语句结束。

在行复制格式下(BINLOG\_FORMAT=ROW)，主库BINLOG中保存有记录的所有列信息包括自增列值，因此无需通过AUTO-INC锁来保证主从数据一致。在MySQL 8.0版本前，参数BINLOG\_FORMAT的默认值为STATEMENT，参数innodb\_autoinc\_lock\_mode的默认值为1。在MySQL 8.0版本后，参数BINLOG\_FORMAT的默认值被调整为ROW格式，参数innodb\_autoinc\_lock\_mode的默认值为2。

#### 交错锁模式 ####

所有的“INSERT-LIKE”语句都不使用表级锁，而是使用轻量级互斥锁。

交错锁模式速度快、可扩展性高，但是对于基于语句复制会有问题，只能使用基于ROW复制。

之所以称为交错模式是因为并发插入场景下自增值的分配大概率是交替这来的，时刻1事务1获得自增值，时刻2事务2获得自增值，以此类推。

如果一个插入数据的事务回滚，那么为这个事务生成的自增值就会丢失，因为自增值不会被回滚。

回滚造成的自增值丢失InnoDB是不会被重用，所以不能依赖InnoDB自增列是连续值。

## 三.行锁 ##

对于mysql数据库来说，InnoDB 引擎是支持行级锁的，而 MyISAM 引擎并不支持行级锁。

行级锁的类型主要有三类：

*  Record Lock，记录锁，也就是仅仅把一条记录锁上；
 *  Gap Lock，间隙锁，锁定一个范围，但是不包含记录本身；
 *  Next-Key Lock：Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

### 1.Record Lock ###

Record Lock 称为记录锁，锁住的是一条记录。而且记录锁是有 S 锁和 X 锁之分的：

*  当一个事务对一条记录加了 S 型记录锁后，其他事务也可以继续对该记录加 S 型记录锁（S 型与 S 锁兼容），但是不可以对该记录加 X 型记录锁（S 型与 X 锁不兼容）;
 *  当一个事务对一条记录加了 X 型记录锁后，其他事务既不可以对该记录加 S 型记录锁（S 型与 X 锁不兼容），也不可以对该记录加 X 型记录锁（X 型与 X 锁不兼容）。

### 2.Gap Lock ###

Gap Lock 称为间隙锁，只存在于可重复读隔离级别，目的是为了解决可重复读隔离级别下幻读的现象。

间隙锁虽然存在 X 型间隙锁和 S 型间隙锁，但是并没有什么区别，**间隙锁之间是兼容的，即两个事务可以同时持有包含共同间隙范围的间隙锁，并不存在互斥关系，因为间隙锁的目的是防止插入幻影记录而提出的**。

#### 间隙锁是在普通索引的范围之间，如果开启事务，进行独占后，在找到下一个索引值之前的范围都会被进行锁定，无法进行insert操作。 ####

### 3.Next-Key Lock ###

Next-Key Lock 称为临键锁，是 Record Lock + Gap Lock 的组合，锁定一个范围，并且锁定记录本身。

next-key lock 即能保护该记录，又能阻止其他事务将新纪录插入到被保护记录前面的间隙中。

next-key lock 是包含间隙锁+记录锁的，如果一个事务获取了 X 型的 next-key lock，那么另外一个事务在获取相同范围的 X 型的 next-key lock 时，是会被阻塞的。