MySQL（三）、锁

短命女 2022-11-27 01:53 142阅读 0赞

**目录**

简介

锁级别

全局锁

表级锁

页级锁

行级锁

InnoDB锁

共享锁S

排他锁X

意向锁

记录锁

间隙锁

Next-Key Lock

插入意向锁

自增锁

扩展

死锁

活锁

悲观锁

乐观锁

并发控制（MVCC）

--------------------

**MySQL系列：**

[MySQL（一）、InnoDB索引原理及优化][MySQL_InnoDB]

[MySQL（二）、事务][MySQL]

--------------------

# 简介 #

锁是计算机用以协调多个进程间并发访问同一共享资源的一种机制。MySQL中为了保证数据访问的一致性与有效性等功能，实现了锁机制，MySQL中的锁是在服务器层或者存储引擎层实现的。

# 锁级别 #

**MySQL引擎默认的锁级别**：

*  **MyISAM**：表级锁；
 *  **Memory**：表级锁；
 *  **InnoDB**：支持行级锁和表级锁 ，默认为行级锁。
 *  **BDB**：页面锁或表级锁，默认为页面锁。 （MySQL5.1之后弃用）

（在后面的文章中会详细对比MySQL的常用引擎，下面对MySQL支持的锁进行介绍）

## 全局锁 ##

对整个数据库实例加锁，加锁后整个库处于只读状态的时候，之后其他线程的以下语句会被阻塞：DML（增删改）、DDL、事务提交。不会影响DQL（查询请求），从而获取一致性视图，保证数据的完整性。

**全局锁**：[https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html][https_dev.mysql.com_doc_refman_8.0_en_lock-instance-for-backup.html]

全局锁命令：

-- 加全局锁 （FTWRL）
    flush tables with read lock;
    
    -- 释放全局锁
    unlock tables;
    或断开加全局锁的会话连接

错误信息：*\[Error Code: 1223, SQL State: HY000\]  Can't execute the query because you have a conflicting read lock *

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70][]

全局锁的场景是全库备份，但前面也介绍了，全局锁的时候库是只读状态。此时如果在主库加全局锁进行备份，业务就会停摆，如果在从库加全局锁，就无法同步主库的操作导致主从延迟。其实在实际应用中我们可以对不同的存储引擎使用不同的策略备份。

**使用mysqldump工具备份**：

*  **InnoDB引擎**：利用MVCC提供一致性视图，开启一个RR级别的事务，保证备份过程中数据可以正常更新，dump对应参数为：*--single-transaction*；
 *  **MyISAM引擎**：利用FTWRL加全局锁，dump对应参数为：*--lock-all-tables*；

## 表级锁 ##

**表级锁（Table-level Locking）**：表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分 MySQL 引擎支持；

*  **优点**：开销小，加锁快；不会出现死锁；
 *  **缺点**：锁定粒度大，发出锁冲突的概率最高，并发度最低。

**表级锁**： [https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html][https_dev.mysql.com_doc_refman_8.0_en_lock-tables.html]

表锁命令：

-- 加表锁
    lock tables … read/write;
    
    -- 释放表锁
    unlock tables;
    或断开加表锁的会话连接

错误信息：*\[Error Code: 1099, SQL State: HY000\]  Table 'csdn' was locked with a READ lock and can't be updated*

表读锁：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1][]

表写锁：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 2][]

**元数据锁（Metadata Locking）**：MySQL5.5之后新增，MDL也是表级别的锁，不需要显示的使用，当对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。

*  读锁之间不互斥，可以有多个线程同时对一张表增删改查；
 *  读写锁之间、写锁之间是互斥的，用来保证表结构变更操作的安全性。如果有两个线程要同时给一个表加字段，其中一个要阻塞等待另一个执行完才能开始执行。

## 页级锁 ##

**页级锁（Page-level Locking）**：锁定表中某些行集合（称做页），被锁定的行只对锁定最初的线程是可行。如果另外一个线程想要向这些行写数据，它必须等到锁被释放。不过其他页的行仍然可以使用。在MySQL5.1之前BDB引擎默认页级锁，之后被弃用。

## 行级锁 ##

**行级锁（Row-level Locking）**：基于索引数据结构实现，是 MySQL 中锁定粒度最细的一种锁，只有线程当前使用的行被锁定，其他行对于其他线程都是可用的；InnoDB引擎默认的锁级别。

*  **优点**：发生锁冲突几率低，并发高
 *  **缺点**：开销大，加锁慢；会出现死锁的情况；

行级锁包括共享锁、排他锁和更新锁。在下面的内容中具体介绍。

> **InnoDB行级锁是通过锁索引记录实现的，如果更新的列没建索引会锁住整个表。**

# InnoDB锁 #

**InnoDB锁**：[https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html][https_dev.mysql.com_doc_refman_8.0_en_innodb-locking.html]

## 共享锁S ##

**共享锁（Shared Locks）**：简称S锁，也被称为读锁。读锁允许多个连接可以同一时刻并发的读取同一资源,互不干扰。

语法：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

## 排他锁X ##

**排他锁（Exclusive Locks）**：简称X锁，也叫独占锁、写锁。一个独占锁会阻塞其他的写锁或读锁，保证同一时刻只有一个连接可以写入数据，同时防止其他用户对这个数据的读写。执行数据更新命令，即INSERT、UPDATE 或DELETE 命令时，MySQL 会自动使用独占锁。但当对象上有其它锁存在时，无法对其加独占锁。独占锁一直到事务结束才能被释放。

语法：

SELECT * FROM table_name WHERE ... FOR UPDATE

## 意向锁 ##

**意向锁（Intention Locks）**：提前声明一个意向，并获取表级别的意向锁（共享意向锁 IS 或排他意向锁 IX），如果获取成功，则稍后将要或正在(才被允许)，对该表的某些行加锁(S或X)了。（除了 LOCK TABLES ... WRITE,会锁住表中所有行，其他场景意向锁实际不锁住任何行)。

**意向锁包含**：

*  **意向共享锁（Intention Shared Lock）**：简称IS锁，事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁.
 *  **意向独占锁（Intention Exclusive Lock）**：简称IX锁，事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁.

**意向锁协议**：在事务能够获取表中行上的共享锁之前，它必须首先获取表上的IS锁或更强的锁。 在事务能够获取表中的行上的独占锁之前，它必须首先获取表上的IX锁。

**InnoDB锁兼容性列表**

> 共享锁和排他锁都是标准的行级锁，意向锁是表级锁。  
> 意向锁是比X、S更弱的锁，存在一种预判的意义！先获取更弱的IX、IS锁，如果获取失败就不必再花费更大的开销获取更强的X、S锁。

## 记录锁 ##

**记录锁（Record Locks）**：最简单的行锁，行锁是加在索引上的，如果查询命令不走索引时，这条语句会锁住所有记录也就是锁表，如果语句的执行能够执行某一个字段的索引，那么仅会锁住满足 where 的行(Record Lock)。

## 间隙锁 ##

**间隙锁（Gap Locks）**：锁定索引记录之间的间隙(\[2\])，或者锁定一个索引记录之前的间隙(\[1\])，或者锁定一个索引记录之后的间隙(\[3\])。

**例**：

![format_png][]

如图\[1\]、\[2\]、\[3\]部分。一般作用于我们的范围筛选查询> 、< 、between......

例如， *SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE;* 阻止其他事务将值3插入到列 userId 中。因为该范围内所有现有值之间的间隙都是锁定的。

> 对于使用唯一索引来搜索唯一行的语句 select a from ，不产生间隙锁定。(不包含组合唯一索引，也就是说 gapLock 不作用于单列唯一索引）。

例如，如果id列有唯一的索引，下面的语句只对id值为100的行使用索引记录锁，其他会话是否在前一个间隙中插入行并不重要:

*SELECT \* FROM t1 WHERE id = 100;* 如果id没有索引或具有非唯一索引，则语句将锁定前面的间隙。

> 间隙可以跨越单个索引值、多个索引值(如上图2,3)，甚至是空的；
> 
> 间隙锁是性能和并发性之间权衡的一种折衷，用于某些特定的事务隔离级别，如RC级别（RC级别：REPEATABLE READ，我司为了减少死锁，关闭了gap锁，使用RR级别）；
> 
> 在重叠的间隙中（或者说重叠的行记录）中允许gap共存；

例如，同一个 gap 中，允许一个事务持有 gap X-Lock（gap 写锁\\排他锁)，同时另一个事务在这个 gap 中持有(gap 写锁\\排他锁)；

## Next-Key Lock ##

记录锁与与间隙锁的结合。

例：存在一个查询匹配 b=3 的行(b上有个非唯一索引)，那么所谓 NextLock 就是：在b=3 的行加了 RecordLock 并且使用 GapLock 锁定了 b=3 之前（“之前”：索引排序）的所有行记录。

> ** innodb 中默认隔离级别(RR)下，next key Lock 自动开启；**

## 插入意向锁 ##

**插入意向锁（Insert Intention Locks）**：是一种间隙锁，在行执行 INSERT 之前的插入操作设置。如果多个事务 INSERT 到同一个索引间隙之间，但没有在同一位置上插入，则不会产生任何的冲突。假设有值为4和7的索引记录，现在有两事务分别尝试插入值为 5 和 6 的记录，在获得插入行的排他锁之前，都使用插入意向锁锁住 4 和 7 之间的间隙，但两者之间并不会相互阻塞，因为这两行并不冲突。

插入意向锁只会和 间隙或者 Next-key 锁冲突，正如上面所说，间隙锁作用就是防止其他事务插入记录造成幻读，正是由于在执行 INSERT 语句时需要加插入意向锁，而插入意向锁和间隙锁冲突，从而阻止了插入操作的执行。

**不同类型的锁之间的兼容如下表所示**：

<table style="width:666px;"> 
 <thead> 
  <tr> 
   <th>&nbsp;</th> 
   <th>RECORED</th> 
   <th>GAP</th> 
   <th>NEXT-KEY</th> 
   <th>II GAP（插入意向锁）</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>RECORED</td> 
   <td>&nbsp;</td> 
   <td>兼容</td> 
   <td>&nbsp;</td> 
   <td>兼容</td> 
  </tr> 
  <tr> 
   <td>GAP</td> 
   <td>兼容</td> 
   <td>兼容</td> 
   <td>兼容</td> 
   <td>兼容</td> 
  </tr> 
  <tr> 
   <td>NEXT-KEY</td> 
   <td>&nbsp;</td> 
   <td>兼容</td> 
   <td>&nbsp;</td> 
   <td>兼容</td> 
  </tr> 
  <tr> 
   <td>II GAP</td> 
   <td>兼容</td> 
   <td>&nbsp;</td> 
   <td>&nbsp;</td> 
   <td>兼容</td> 
  </tr> 
 </tbody> 
</table>

## 自增锁 ##

**自增锁（Auto-inc Locks）**：是一种特殊的表级别锁，专门针对事务插入AUTO\_INCREMENT类型的列。如果一个事务正在往表中插入记录，所有其他事务的插入必须等待，以便第一个事务插入的行，是连续的主键值。

**站内摘来的一张时序图，帮助理解MySQL的事务和锁：**

![format_png 1][]

# 扩展 #

## 死锁 ##

**死锁（Dead Lock）**：当并发系统中不同线程出现循环资源依赖，涉及的线程都在等待别的线程释放资源时，就会导致这几个线程都进入无限等待的状态，称为死锁。

**产生死锁的4个必要条件**：

*  互斥条件：一个资源每次只能被一个线程使用
 *  请求与保持条件：一个线程因请求资源而阻塞时，对已获得的资源保持不放
 *  不剥夺条件：进程已经获得的资源，在未使用完之前，不能强行剥夺
 *  循环等待条件：若干线程之间形成一种头尾相接的循环等待资源关系

**死锁检测**：

对于死锁，我们可以通过参数 *innodb\_lock\_wait\_timeout*** **根据实际业务场景来设置超时时间，InnoDB引擎默认值是50s。  
发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数 *innodb\_deadlock\_detect* 设置为 on，表示开启这个逻辑（默认是开启状态）。

*wait-for graph* 算法来主动进行死锁检测：innodb 还提供了* wait-for graph* 算法来主动进行死锁检测，每当加锁请求无法立即满足需要并进入等待时，*wait-for graph *算法都会被触发。

**避免死锁的方法：**

1.  如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低发生死锁的可能性；
2.  在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
3.  对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率。

**如何解决热点行更新导致的性能问题？**

1.  如果你能确保这个业务一定不会出现死锁，可以临时把死锁检测关闭掉。一般不建议采用；
2.  控制并发度，对应相同行的更新，在进入引擎之前排队。这样在InnoDB内部就不会有大量的死锁检测工作了；
3.  将热更新的行数据拆分成逻辑上的多行来减少锁冲突，但是业务复杂度可能会大大提高；

> MyISAM 中是不会产生死锁的，因为 MyISAM 总是一次性获得所需的全部锁，要么全部满足，要么全部等待。

## 活锁 ##

**活锁、死锁本质上是一样的：都是多个线程任务没有任何进展**。原因是在获取共享资源时，并发多线程或多进程声明资源占用(即加锁)的顺序有冲突，死锁是加不上就死等（多线程都处于阻塞状态）；活锁是加不上就放开已获得的资源重试，这种情况下线程并没有阻塞所以是活的状态，但是只是在做无用功（每次重试都是失败的），其实多核活锁不太常见（资源分配充足）。

例：资源A和B，进程P1和P2

start：
    P1 lock A
    P2 lock B
    P1 lock B fail context switch
    P2 lock A fail context switch
    P1 release A
    P2 release B
    goto start    #活锁，不断尝试，做无用功

## 悲观锁 ##

顾名思义，就是很悲观，它对于数据被外界修改持保守态度，认为数据随时会修改，所以整个数据处理中需要将数据加锁。悲观锁一般都是依靠关系数据库提供的锁机制，事实上关系数据库中的行锁，表锁，读写锁都是悲观锁。

## 乐观锁 ##

乐观锁一般是指用户自己实现的一种锁机制。乐观锁是指操作数据库时(更新操作)，想法很乐观，认为这次的操作不会导致冲突，在操作数据时，并不进行任何其他的特殊处理（也就是不加锁），而在进行更新后，再去判断是否有冲突了。乐观锁适用于多读的应用类型，这样可以提高吞吐量。

**常见乐观锁实现方式：**

*  **版本号机制：**一般是在数据表中加上一个数据版本号version字段，表示数据被修改的次数，当数据被修改时，version值会加一。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
 *  **CAS算法：**比较与交换（compare and swap），是一种很有名的无锁算法。无锁编程，即不使用锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。CAS算法涉及到三个操作值：
    
     *  需要读写的内存值 V
     *  进行比较的值 A
     *  拟写入的新值 B

当且仅当 V 的值等于 A时，CAS通过原子方式用新值B来更新V的值，否则不会执行任何操作（比较和替换是一个原子操作）。一般情况下是一个自旋操作，即不断的重试。

> **注意：乐观锁只能用于本系统控制，无法阻止外系统更新；**

## 并发控制（MVCC） ##

**MVCC (multiple-version-concurrency-control）**：行级锁的变种， 在普通读情况下避免了加锁操作，因此开销更低。虽然实现不同，但通常都是实现非阻塞读，对于写操作只锁定必要的行。

*  **一致性读 （就是读取快照）**select \* from table ....
 *  **当前读（就是读取实际的持久化的数据），**特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。 select \* from table where ... lock in share mode; select \* from table where ... for update; insert; update ; delete;

> 注意：select ...... from table ...... （没有额外加锁后缀）使用MVCC，保证了读快照(MySQL 称为 consistent read)，所谓一致性读或者读快照就是读取当前事务开始之前的数据快照，在这个事务开始之后的更新不会被读到。

**InnoDB的 MVCC 通常是通过在每行数据后边保存两个隐藏的列来实现**（其实是三列，第三列是用于事务回滚）**，**一个保存了行的创建版本号，另一个保存了行的更新版本号（上一次被更新数据的版本号） 这个版本号是每个事务的版本号，递增的。这样保证了 InnoDB对读操作不需要加锁也能保证正确读取数据。

**在MySQL 默认的 Repeatable Read 隔离级别下， MVCC 的具体操作**：

*  **Select**（快照读，所谓读快照就是读取当前事务之前的数据）**：**
    
    a.InnoDB 只 select 查找版本号早于当前版本号的数据行，这样保证了读取的数据要么是在这个事务开始之前就已经 commit 了的（早于当前版本号），要么是在这个事务自身中执行创建操作的数据（等于当前版本号）。
    
    b.查找行的更新版本号要么未定义，要么大于当前的版本号(为了保证事务可以读到老数据)，这样保证了事务读取到在当前事务开始之后未被更新的数据。
    
    注意： 这里的 select 不能有 for update、lock in share 语句。 总之要只返回满足以下条件的行数据，达到了快照读的效果：

(行创建版本号< =当前版本号 && (行更新版本号==null or 行更新版本号>当前版本号 ) )

*  **Insert：**InnoDB为这个事务中新插入的行，保存当前事务版本号的行作为行的行创建版本号。
 *  **Delete：**InnoDB 为每一个删除的行保存当前事务版本号，作为行的删除标记。
 *  **Update：**将存在两条数据，保持当前版本号作为更新后的数据的新增版本号，同时保存当前版本号作为老数据行的更新版本号。

当前版本号—写—>新数据行创建版本号 && 当前版本号—写—>老数据更新版本号();

希望本文对你有帮助，请点个赞鼓励一下作者吧~ 谢谢！

[MySQL_InnoDB]: https://blog.csdn.net/wsdc0521/article/details/107927730
[MySQL]: https://blog.csdn.net/wsdc0521/article/details/107938849
[https_dev.mysql.com_doc_refman_8.0_en_lock-instance-for-backup.html]: https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70]: /images/20221124/924be0f6603047019f57bffceee59e8d.png
[https_dev.mysql.com_doc_refman_8.0_en_lock-tables.html]: https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 1]: /images/20221124/a334e47de2b94824bda03ab035f8885f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3dzZGMwNTIx_size_16_color_FFFFFF_t_70 2]: /images/20221124/f3fc46be9d5e48039ce0d038a2015cc2.png
[https_dev.mysql.com_doc_refman_8.0_en_innodb-locking.html]: https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html
[format_png]: /images/20221124/fefcaf59db974f4fb29e4ce2e02a1060.png
[format_png 1]: /images/20221124/945b6edda0d440c6b1e194ada9220fd1.png