MySQL--锁

红太狼 2023-01-07 11:29 151阅读 0赞

原文网址：[MySQL--锁\_IT利刃出鞘的博客-CSDN博客][MySQL--_IT_-CSDN]

# 其他网址 #

> [MySQL InnoDB 锁 · 语雀][MySQL InnoDB _ _]  
> [MySQL学习之——锁(行锁、表锁、页锁、乐观锁、悲观锁等)\_一个手艺人-CSDN博客][MySQL_-CSDN]

# **悲观锁与乐观锁** #

**其他网址**

> [数据库乐观锁和悲观锁的理解和实现（转载&总结）\_数据库\_王震1024的专栏-CSDN博客][1024_-CSDN]  
> [使用mysql乐观锁解决并发问题 - 小卖铺的老爷爷 - 博客园][mysql_ - _ -]  
> [MySQL中的悲观锁与乐观锁][MySQL]

<table> 
 <tbody> 
 <tr> 
 <td> 项 </td> 
 <td> 悲观锁 </td> 
 <td> 乐观锁 </td> 
 </tr> 
 <tr> 
 <td> 名词解释 </td> 
 <td> &nbsp; &nbsp; &nbsp; &nbsp; 假设数据肯定会冲突，所以在数据开始读取的时候就把数据锁住。 </td> 
 <td> &nbsp; &nbsp; &nbsp; &nbsp; 认为数据一般情况下不会造成冲突，在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测，如果发现冲突了，则让用户返回错误的信息。让用户决定如何去做。 </td> 
 </tr> 
 <tr> 
 <td> 实现方法 </td> 
 <td> &nbsp; &nbsp; &nbsp; &nbsp; 大多数情况下依靠数据库的锁机制实现 &nbsp; &nbsp; &nbsp; &nbsp; 一般使用 select ...for update 对所选择的数据进行加锁处理，例如select * from account where name=”Max”&nbsp;for update，&nbsp;这条sql 语句锁定了account 表中所有符合检索条件（name=”Max”）的记录。本次事务提交之前（事务提交时会释放事务过程中的锁），外界无法修改这些记录。 </td> 
 <td> &nbsp; &nbsp; &nbsp; &nbsp;大多数基于数据版本（Version）记录机制实现。&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;可通过给表加一个版本号或时间戳字段实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。提交更新的时候，判断当前版本信息与第一次取出来的版本值大小，如果数据库表当前版本号与第一次取出来的version值相等，则更新，否则认为是过期数据，拒绝更新。可以让用户重新操作。 </td> 
 </tr> 
 </tbody> 
</table>

**乐观锁举例**

> 举例：商品goods表中有一个字段status，status为1代表商品未被下单，status为2代表商品已经被下单，对某个商品下单时必须确保该商品status为1。假设商品的id为1。
> 
> 下单操作包括3步骤：
> 
> 1.查询出商品信息
> 
> select (status,status,version) from t\_goods where id=\#\{id\}
> 
> 2.根据商品信息生成订单
> 
> 3.修改商品status为2
> 
>     update t_goods set status=2,version=version+1 where id=#{id} and version=#{version};

# 锁的粒度 #

**其他网址**

> [MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解\_数据库\_hsd2012的专栏-CSDN博客][MySQL_MyISAM_InnoDB_hsd2012_-CSDN]  
> [MyISAM表锁 - 一束光 - 博客园][MyISAM_ - _ -]

> [MySQL 表锁和行锁机制][MySQL 1]

## 简介 ##

**不同的存储引擎的锁机制**

> <table> 
> <tbody> 
> <tr> 
> <td style="width:171px;">存储引擎</td> 
> <td style="width:260px;">支持的锁</td> 
> <td>说明</td> 
> </tr> 
> <tr> 
> <td style="width:171px;">InnoDB</td> 
> <td style="width:260px;">表级锁、行级锁（默认）。</td> 
> <td>InnoDB行级锁基于索引实现。若查询字段无索引或索引失效，则使用表锁。</td> 
> </tr> 
> <tr> 
> <td style="width:171px;">MyISAM</td> 
> <td style="width:260px;">表级锁</td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:171px;">MEMORY</td> 
> <td style="width:260px;">表级锁</td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:171px;">BDB</td> 
> <td style="width:260px;">表级锁、页面锁</td> 
> <td></td> 
> </tr> 
> </tbody> 
> </table>

> <table> 
> <tbody> 
> <tr> 
> <td style="width:56.25pt;"> 锁类型 </td> 
> <td style="width:143px;"> 开销（加锁速度） </td> 
> <td style="width:84px;"> 死锁 </td> 
> <td style="width:296px;"> 锁定粒度（并发性） </td> 
> <td> 适用场景 </td> 
> </tr> 
> <tr> 
> <td style="width:56.25pt;"> 表级锁 </td> 
> <td style="width:143px;"> 开销小（加锁快） </td> 
> <td style="width:84px;"> 不会死锁 </td> 
> <td style="width:296px;"> 最大，发生锁冲突的概率最大（并发性低） </td> 
> <td> 场景1：读多写少； 场景2：写特别多。若用行锁，会导致事务执行效率低，可能造成其他事务长时间锁等待和锁冲突。 </td> 
> </tr> 
> <tr> 
> <td style="width:56.25pt;"> 行级锁 </td> 
> <td style="width:143px;"> 开销大（加锁慢） </td> 
> <td style="width:84px;"> 会死锁 </td> 
> <td style="width:296px;"> 最小，发生锁冲突的概率最低（并发性最高） </td> 
> <td> 并发量大。 </td> 
> </tr> 
> <tr> 
> <td style="width:56.25pt;"> 页面锁 </td> 
> <td style="width:143px;"> 居中 </td> 
> <td style="width:84px;"> 会死锁 </td> 
> <td style="width:296px;"> 居中，并发一般 </td> 
> <td> </td> 
> </tr> 
> </tbody> 
> </table>

## 行锁 ##

**其他网址**

> [\[MySQL\] 行级锁SELECT ... LOCK IN SHARE MODE 和 SELECT ... FOR UPDATE\_数据库\_列苗-CSDN博客][MySQL_ _SELECT ... LOCK IN SHARE MODE _ SELECT ... FOR UPDATE_-CSDN]
> 
> 《MySQL技术内幕 InnoDB存储引擎 第2版》=> 第6章 锁

**说明**

> 行锁只在InnoDB中。有两种锁：共享锁（也称为：读锁）、独占锁（也称为：写锁）。
> 
> InnoDB实现了以下两种类型的行锁。意向锁是InnoDB自动加的，不需用户干预。
> 
> * 共享锁（S）：允许获得共享锁的事务去读一行，阻止其他事务获得相同数据集的排他锁。
> * 排他锁（X）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享锁和排他锁。另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
> 
> * 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。（Intention Share）
> * 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。（Intention Exclusive）
> 
> 上述锁模式的兼容情况具体如下表所示。如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。
> 
> <table style="width:581px;"> 
> <tbody> 
> <tr> 
> <td> </td> 
> <td>X</td> 
> <td>IX</td> 
> <td>S</td> 
> <td>IS</td> 
> </tr> 
> <tr> 
> <td>X</td> 
> <td>冲突</td> 
> <td>冲突</td> 
> <td>冲突</td> 
> <td>冲突</td> 
> </tr> 
> <tr> 
> <td>IX</td> 
> <td>冲突</td> 
> <td>兼容</td> 
> <td>冲突</td> 
> <td>兼容</td> 
> </tr> 
> <tr> 
> <td>S</td> 
> <td>冲突</td> 
> <td>冲突</td> 
> <td>兼容</td> 
> <td>兼容</td> 
> </tr> 
> <tr> 
> <td>IS</td> 
> <td>冲突</td> 
> <td>兼容</td> 
> <td>兼容</td> 
> <td>兼容</td> 
> </tr> 
> </tbody> 
> </table>

**相同点**

> <table> 
> <tbody> 
> <tr> 
> <td style="width:82.5pt;"> 项 </td> 
> <td style="width:557px;"> 共享锁（也称为：读锁） </td> 
> <td>独占锁（也称为：写锁）</td> 
> </tr> 
> <tr> 
> <td style="width:82.5pt;"> 加锁方式 </td> 
> <td colspan="2"> 自动加锁。 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁； 对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁： &nbsp; &nbsp; 共享锁：select * from tableName where ... + lock in share more &nbsp; &nbsp; 排他锁：select * from tableName where ... + for update </td> 
> </tr> 
> <tr> 
> <td style="width:82.5pt;"> 事务 </td> 
> <td colspan="2"> 必须在事务中使用。 </td> 
> </tr> 
> </tbody> 
> </table>

**不同点**

> <table> 
> <tbody> 
> <tr> 
> <td style="width:106px;"> 项 </td> 
> <td> 共享锁（也称为：读锁） </td> 
> <td> 独占锁（也称为：写锁） </td> 
> </tr> 
> <tr> 
> <td style="width:106px;">加锁语句</td> 
> <td>select ... lock in share mode</td> 
> <td>select ... for update</td> 
> </tr> 
> <tr> 
> <td style="width:106px;"> 定义 </td> 
> <td> &nbsp; &nbsp; &nbsp; &nbsp; 在读取的行上设置一个共享锁。 &nbsp; &nbsp; &nbsp; &nbsp; 这共享锁允许其它session读数据但不允许修改它（否则锁等待超时）。 &nbsp; &nbsp; &nbsp; &nbsp; 行读取的是最新的数据，如果它被其它事务使用中而没有提交，读锁将被阻塞直到那个事务结束。 官方文档： SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify them. The rows read are the latest available, so if they belong to another transaction that has not yet committed, the read blocks until that transaction ends. &nbsp; </td> 
> <td> &nbsp; &nbsp; &nbsp; &nbsp; 在读取行上设置一个排他锁。 &nbsp; &nbsp; &nbsp; &nbsp; 阻止其他session读写行数据。 官方文档： SELECT ... FOR UPDATE sets an exclusive lock on the rows read. An exclusive lock prevents other sessions from accessing the rows for reading or writing. </td> 
> </tr> 
> <tr> 
> <td style="width:106px;"> 锁类型 </td> 
> <td> 对记录加共享锁。 在当前事务内，可再加X锁。 其他session可以读取这些记录，也可以继续添加S锁，不能加X锁。 其他session无法修改这些记录，修改时会阻塞，直到这加锁的事务结束(否则直接锁等待超时) </td> 
> <td> 对记录加排它锁。 其他session不能加S锁或者X锁。 其他session不能锁定读，可以快照读。 快照读：纯select语句，不带IS锁或者IX锁。读取修改行之前的数据版本,会出现脏读，也称为非锁定读，因为不需要等待被访问行的锁的释放。非锁定读的方式极大提高了数据库的并发性。在InnoDB存储引擎中，这是默认的读取方式。 </td> 
> </tr> 
> <tr> 
> <td style="width:106px;"> 不同点 </td> 
> <td> 1. 会造成死锁。 2. 适用于本事务中不会修改数据的情况。 3. 只锁覆盖索引。如果要用lock in share mode必须得绕过覆盖索引的优化，在查询字段中加入索引中不存在的字段。 4. 两个会话可以都用select ... lock in share mode锁定某行（某个间隙锁范围），可以读取，但不能修改（修改时会阻塞）。 说明： 1. 如果两个事务同时获取某行数据共享锁，事务A更新该行发生阻塞，事务B提交之后，事务A更新成功。 2. 如果两个事务同时获取某行数据共享锁，事务A更新该行发生阻塞，事务B更新也会发生阻塞，事务B提交之后，事务A更新失败。 </td> 
> <td> 1. 不会造成死锁。 2. 适用于本事务中会修改数据的情况。 3. 执行 for update 时， 系统会认为你接下来要更新数据，因此会顺便给主键索引上。 4. 两个会话不能都用select ... for update锁定某行（某个间隙锁范围）,对话A用了select ... for update锁定某行（某个间隙锁范围）后，对话B再用select ... for update锁定这行（这个间隙锁范围）会直接阻塞（select ...for update锁定其他行（其他间隙范围）不会阻塞，可以读取到。）。 说明： 若事务A使用select ... for update锁定某行（某个间隙锁范围），则事务B修改这行（这个间隙锁范围）会阻塞，事务A提交后，事务B更新成功。 </td> 
> </tr> 
> </tbody> 
> </table>

**注意事项**

> 未加索引时，两种行锁情况为（使用表锁）：  
> \- 事务1获取某行数据共享锁，其他事务可以获取不同行数据的共享锁，不可以获取不同行数据的排他锁  
> \- 事务1获取某行数据排他锁，其他事务不可以获取不同行数据的共享锁、排他锁
> 
> 加索引后，两种行锁为（使用行锁）：  
> \- 事务1获取某行数据共享锁，其他事务可以获取不同行数据的排他锁  
> \- 事务1获取某行数据排他锁，其他事务可以获取不同行数据的共享锁、排他锁

**实现原理**

> 有一个先进先出的队列,队列中存放的都是所有锁，包括共享锁 和 排它锁。如下FIFO队列示意图, 按照从左到右的先进先出顺序存放各种锁：
> 
> 列头 < ①Read Lock == ②Read Lock < ③Write Lock < ④Read Lock < 列尾
> 
> 读锁① 和 读锁② 中间用等号，表示这两个锁可以同时进行数据查询。其余的小于号表示 右边的锁等待小于号左边的锁。
> 
> 上图队列中一共有4个锁，分别为读锁①、读锁②、写锁③、读锁④。并且每个锁都分别对用有1个事务进行获取，即有4个事务。
> 
> 事务1(读锁)
> 
> select * from trans where id=2 lock in share mode;
> 
> 事务2(读锁)
> 
> select * from trans where id=2 lock in share mode;
> 
> 事务3(写锁)
> 
> update trans set xxx=10 where id=2; -- 加上写锁（独占锁）
> 或
> select * from trans where id=2 for update; -- 加上独占锁。
> 
> 事务4(读锁)
> 
> select * from trans where id=2 lock in share mode;

> **请求锁顺序**
> 
> 事务获得①读锁。  
> 事务2尝试获取读锁②，发现前面已经有①锁并且是一个读锁，这时候获取成功,并可以读取到共享锁正在读取的数据。  
> 事务3尝试用获取写锁③，但发现前面已经有两个写锁，所以等待直至读锁释放掉。  
> 事务4常使用获取 读锁④，发现前面的是写锁③，所以等待事务3释放写锁③,而写锁③正在等待前面两个 读锁①、读锁②，所以事务4间接在等待 最前面两个读锁。

## 表锁 ##

**说明**

> 表锁有两种锁：共享锁（也称为：读锁）、独占锁（也称为：写锁）。

**相同点**

> <table> 
> <tbody> 
> <tr> 
> <td style="width:110px;"> 项 </td> 
> <td style="width:683px;">共享锁（也称为：读锁）</td> 
> <td style="width:424px;">独占锁（也称为：写锁）</td> 
> </tr> 
> <tr> 
> <td style="width:110px;"> 加锁方式 </td> 
> <td colspan="2"> 自动加锁。 查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显式加锁： &nbsp; &nbsp; 共享锁：lock table tableName read; &nbsp; &nbsp; 独占锁：lock table tableName write; </td> 
> </tr> 
> </tbody> 
> </table>

**MyISAM表锁**

**InnoDB表锁**

> **表锁生效**
> 
> 用 LOCK TABLES对InnoDB表加锁时要注意，要将AUTOCOMMIT设为0，否则MySQL不会给表加锁。
> 
> 详解：LOCK TABLES虽然可以给InnoDB加表级锁，但表锁不是由InnoDB管理的，而是由其上一层──MySQL Server负责的，仅当autocommit=0、innodb\_table\_locks=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，MySQL Server也才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
> 
> **表锁释放**
> 
> 事务结束前，不要用UNLOCK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK并不能释放用LOCK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁。正确的方式见如下语句：  
> 例如，如果需要写表t1并从表t读，可以按如下做：
> 
> SET AUTOCOMMIT=0;  
> LOCK TABLES t1 WRITE, t2 READ, ...;  
> \[do something with tables t1 and t2 here\];  
> COMMIT;  
> UNLOCK TABLES;

# 间隙锁(Gap Locks) #

**其他网址**

> [MySQL InnoDB 锁 · 语雀][MySQL InnoDB _ _ 1]

**定义**

> 当 SQL 执行按照索引进行数据的检索时，查询条件的数据不存在，这时 SQL 语句加上的锁即为 Gap locks，**锁住数据不存在的区间（左开右开）。**
> 
>  **存在间隙锁的隔离级别：重复读、序列化。**（因为幻读是在 RR 事务通过临键锁和 MVCC 解决的，而**临键锁=间隙锁+记录锁**，所以间隙锁在 RR及Serial 事务隔离级别存在。）

**演示案例**

> ![69003609866d469b63b8ab1eaf7a2cc1.png][]

> **测试**
> 
> `SELECT * FROM t2 WHERE id>4 AND id <6 FOR UPDATE;` 这条查询语句不能命中数据，它会锁住 (4, 7\] 这个区间。
> 
> 语句
> 
> -- 事务A执行
> BEGIN;
> 
> SELECT * FROM t2 WHERE id>4 AND id <6 FOR UPDATE;
> -- 或者
> SELECT * FROM t2 WHERE id=6 FOR UPDATE;
> 
> ROLLBACK;
> 
> -- 事务B执行
> INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');
> INSERT INTO `t2` (`id`, `name`) VALUES (6, '6');
> 
> 结果
> 
> ![a8947c1d79c2f9357c3202f3474bb2a0.png][]

# 记录锁(Record Locks) #

**定义**

> 当 SQL 执行按照唯一性（Primary key、Unique key）索引进行数据的检索时，查询条件等值匹配且查询的数据是存在，这时 SQL 语句加上的锁即为记录锁 Record Locks，**锁住具体的索引项**。

**示例**

> ![9f653fc1e18b7bb1639c11718940ab3d.png][]

**测试**

> **语句**
> 
>     -- 事务A执行
>     BEGIN;
>     
>     SELECT * FROM t2 WHERE id=4 FOR UPDATE;
>     
>     ROLLBACK;
>     
>     
>     -- 事务B执行
>     SELECT * FROM t2 WHERE id=7 FOR UPDATE;
>     SELECT * FROM t2 WHERE id=4 FOR UPDATE;
> 
> **结果**
> 
> ![c2859573682929f207bce3a1dbe82c0f.png][]
> 
> 事务A执行 SELECT \* FROM t2 WHERE id=4 FOR UPDATE; 把 id=4 的数据行锁住。

# **临键锁(Next-Key Locks)** #

**其他网址**

> [MySQL InnoDB 锁 · 语雀][MySQL InnoDB _ _ 2]

**简介**

> 临键锁（Next-key Locks）：InnoDB 默认的行锁算法。 **临键锁=间隙锁+记录锁**
> 
> **为什么 InnoDB 选择临键锁作为行锁的默认算法？**
> 
> 1.防止幻读。当我们把下一个区间也锁住的时候，这个时候我们要新增数据，就会被锁住，这样就可以防止幻读。

**定义**

> 当 SQL 执行按照索引进行数据的检索时，若查询条件为范围查找（between and、<、>等）或等值查找并有数据命中时， SQL 语句加上的锁为 Next-key locks，**锁住索引的记录 +** **区间（左开右闭）****。**

**演示案例**

> 演示之前，先看一下 t2 表的结构和数据内容。
> 
> 结构：
> 
> ![39f33af1b468496d8d2999c510ce6ced.png][]
> 
> 数据：
> 
> ![2a2b5c7b8904687902f11fcefff33a30.png][]
> 
> InnoDB 引擎会将表中的数据划分为：(-∞, 1\] (1, 4\] (4, 7\] (7, 10\] (10, +∞)：
> 
> ![20210118162942712.png][]
> 
> 若执行SELECT \* FROM t2 WHERE id>5 AND id<9 FOR UPDATE; 会锁住这两个区间：(4,7\], (7,10\]。

> **测试**
> 
> 语句：
> 
> -- 事务A执行
> BEGIN;
> 
> SELECT * FROM t2 WHERE id>5 AND id<9 FOR UPDATE;
> 
> ROLLBACK
> 
> -- 事务B执行
> BEGIN;
> 
> SELECT * FROM t2 WHERE id=4 FOR UPDATE; -- 可以执行
> SELECT * FROM t2 WHERE id=7 FOR UPDATE; -- 锁住
> SELECT * FROM t2 WHERE id=10 FOR UPDATE; -- 锁住
> INSERT INTO `t2` (`id`, `name`) VALUES (9, '9'); -- 锁住
> 
> 结果：
> 
> ![3e288a67569f96c5f807a71e63cb9eb1.png][]

# 死锁(Deadlock) #

**其他网址**

> [MySQL学习之——锁(行锁、表锁、页锁、乐观锁、悲观锁等)\_一个手艺人-CSDN博客][MySQL_-CSDN]

## 简介 ##

**不会死锁的情况**

> MyISAM表不会出现死锁（Deadlock Free） 。
> 
> **原因**
> 
> 用LOCK TABLES给表显式加表锁时，必须同时取得所有涉及到表的锁，并且MySQL不支持锁升级。也就是说，在执行LOCK TABLES后，只能访问显式加锁的这些表，不能访问未加锁的表；同时，如果加的是读锁，那么只能执行查询操作，而不能执行更新操作。其实，在自动加锁的情况下也基本如此，MyISAM总是一次获得SQL语句所需要的全部锁。

**会死锁的情况**

> InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，这就决定了在InnoDB中发生死锁是可能的。

## 死锁示例与避免 ##

**例：共享锁导致死锁（同一张表）**

> 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁，因为当用户申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。
> 
> 下面的例子中，先申请共享锁，更新时再申请排他锁，造成死锁。
> 
> <table> 
> <tbody> 
> <tr> 
> <td style="width:476px;">session_1</td> 
> <td>session_2</td> 
> </tr> 
> <tr> 
> <td style="width:476px;"> mysql&gt; set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql&gt; select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | LISA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | MONROE&nbsp;&nbsp;&nbsp; | +----------+------------+-----------+ 1 row in set (0.00 sec) </td> 
> <td> mysql&gt; set autocommit = 0; Query OK, 0 rows affected (0.00 sec) mysql&gt; select actor_id,first_name,last_name from actor where actor_id = 178; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | LISA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | MONROE&nbsp;&nbsp;&nbsp; | +----------+------------+-----------+ 1 row in set (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:476px;"> 当前session对actor_id=178的记录加共享锁： mysql&gt; select actor_id,first_name,last_name from actor where actor_id = 178&nbsp;lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | LISA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | MONROE&nbsp;&nbsp;&nbsp; | +----------+------------+-----------+ 1 row in set (0.01 sec) </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:476px;"></td> 
> <td> 其他session仍然可以查询记录，并也可以对该记录加共享锁： mysql&gt; select actor_id,first_name,last_name from actor where actor_id = 178&nbsp;lock in share mode; +----------+------------+-----------+ | actor_id | first_name | last_name | +----------+------------+-----------+ | 178&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | LISA&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | MONROE&nbsp;&nbsp;&nbsp; | +----------+------------+-----------+ 1 row in set (0.01 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:476px;"> 当前session对锁定的记录进行更新操作，等待锁： mysql&gt; update actor set last_name = 'MONROE T' where actor_id = 178; 等待 </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:476px;"></td> 
> <td> 其他session也对该记录进行更新操作，则会导致死锁退出： mysql&gt; update actor set last_name = 'MONROE T' where actor_id = 178; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction </td> 
> </tr> 
> <tr> 
> <td style="width:476px;"> 获得锁后，可以成功更新： mysql&gt; update actor set last_name = 'MONROE T' where actor_id = 178; Query OK, 1 row affected (17.67 sec) Rows matched: 1&nbsp; Changed: 1&nbsp; Warnings: 0 </td> 
> <td></td> 
> </tr> 
> </tbody> 
> </table>

**例：访问两个表的顺序不同，发生死锁**

> 如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会。 
> 下面的例子中，由于两个session访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。
> 
> <table> 
> <tbody> 
> <tr> 
> <td style="width:489px;">session_1</td> 
> <td>session_2</td> 
> </tr> 
> <tr> 
> <td style="width:489px;"> mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.00 sec) </td> 
> <td> mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:489px;"> mysql&gt; select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | PENELOPE&nbsp;&nbsp; | GUINESS&nbsp;&nbsp; | +------------+-----------+ 1 row in set (0.00 sec) </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:489px;"></td> 
> <td> mysql&gt; insert into country (country_id,country) values(110,'Test'); Query OK, 1 row affected (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:489px;"> mysql&gt;&nbsp; insert into country (country_id,country) values(110,'Test'); 等待 </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:489px;"></td> 
> <td> mysql&gt; select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | PENELOPE&nbsp;&nbsp; | GUINESS&nbsp;&nbsp; | +------------+-----------+ 1 row in set (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:489px;"> 发生死锁 mysql&gt;&nbsp; insert into country (country_id,country) values(110,'Test'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction </td> 
> <td></td> 
> </tr> 
> </tbody> 
> </table>

**例：表数据操作顺序不一致造成死锁**

> 程序以批量方式处理数据的时候，如果事先对数据排序，保证每个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能。 
> 下面的例子中，处理数据的顺序不同，导致死锁。
> 
> <table> 
> <tbody> 
> <tr> 
> <td style="width:493px;">session_1</td> 
> <td>session_2</td> 
> </tr> 
> <tr> 
> <td style="width:493px;"> mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.00 sec) </td> 
> <td> mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:493px;"> mysql&gt; select first_name,last_name from actor where actor_id = 1 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | PENELOPE&nbsp;&nbsp; | GUINESS&nbsp;&nbsp; | +------------+-----------+ 1 row in set (0.00 sec) </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:493px;"></td> 
> <td> mysql&gt; select first_name,last_name from actor where actor_id = 3 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | ED&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | CHASE&nbsp;&nbsp;&nbsp;&nbsp; | +------------+-----------+ 1 row in set (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:493px;"> mysql&gt; select first_name,last_name from actor where actor_id = 3 for update; 等待 </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:493px;"></td> 
> <td> mysql&gt; select first_name,last_name from actor where actor_id = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction </td> 
> </tr> 
> <tr> 
> <td style="width:493px;"> mysql&gt; select first_name,last_name from actor where actor_id = 3 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | ED&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | CHASE&nbsp;&nbsp;&nbsp;&nbsp; | +------------+-----------+ 1 row in set (4.71 sec) </td> 
> <td></td> 
> </tr> 
> </tbody> 
> </table>

**例：重复读隔离级别导致死锁（同一张表）**

> 在REPEATABLE-READ隔离级别下，如果两个线程同时对相同条件记录用SELECT...FOR UPDATE加排他锁，在没有符合该条件记录情况下，两个线程都会加锁成功。程序发现记录尚不存在，就试图插入一条新记录，如果两个线程都这么做，就会出现死锁。这种情况下，将隔离级别改成READ COMMITTED，就可避免问题。
> 
> 下面的例子中，隔离级别为重复读，导致死锁。
> 
> <table> 
> <tbody> 
> <tr> 
> <td style="width:498px;">session_1</td> 
> <td>session_2</td> 
> </tr> 
> <tr> 
> <td style="width:498px;"> mysql&gt; select @@tx_isolation; +-----------------+ | @@tx_isolation&nbsp; | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql&gt; set autocommit = 0; Query OK, 0 rows affected (0.00 sec) </td> 
> <td> mysql&gt; select @@tx_isolation; +-----------------+ | @@tx_isolation&nbsp; | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec) mysql&gt; set autocommit = 0; Query OK, 0 rows affected (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:498px;"> 当前session对不存在的记录加for update的锁： mysql&gt; select actor_id,first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec) </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:498px;"></td> 
> <td> 其他session也可以对不存在的记录加for update的锁： mysql&gt; select actor_id,first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:498px;"> 因为其他session也对该记录加了锁，所以当前的插入会等待： mysql&gt; insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); 等待 </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:498px;"></td> 
> <td> 因为其他session已经对记录进行了更新，这时候再插入记录就会提示死锁并退出： mysql&gt; insert into actor (actor_id, first_name , last_name) values(201,'Lisa','Tom'); ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction </td> 
> </tr> 
> <tr> 
> <td style="width:498px;"> 由于其他session已经退出，当前session可以获得锁并成功插入记录： mysql&gt; insert into actor (actor_id , first_name , last_name) values(201,'Lisa','Tom'); Query OK, 1 row affected (13.35 sec) </td> 
> <td></td> 
> </tr> 
> </tbody> 
> </table>

**例：提交读隔离级别导致死锁（同一张表）**

> 隔离级别为READ COMMITTED时，如果两个线程都先执行SELECT...FOR UPDATE，判断是否存在符合条件的记录，如果没有，就插入记录。此时，只有一个线程能插入成功，另一个线程会出现锁等待，当第1个线程提交后，第2个线程会因主键重出错，但虽然这个线程出错了，却会获得一个排他锁！这时如果有第3个线程又来申请排他锁，也会出现死锁。
> 
> 对于这种情况，可以直接做插入操作，然后再捕获主键重异常，或者在遇到主键重错误时，总是执行ROLLBACK释放获得的排他锁。
> 
> <table> 
> <tbody> 
> <tr> 
> <td style="width:412px;">session_1</td> 
> <td style="width:450px;">session_2</td> 
> <td>session_3</td> 
> </tr> 
> <tr> 
> <td style="width:412px;"> mysql&gt; select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.01 sec) </td> 
> <td style="width:450px;"> mysql&gt; select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.01 sec) </td> 
> <td> mysql&gt; select @@tx_isolation; +----------------+ | @@tx_isolation | +----------------+ | READ-COMMITTED | +----------------+ 1 row in set (0.00 sec) mysql&gt; set autocommit=0; Query OK, 0 rows affected (0.01 sec) </td> 
> </tr> 
> <tr> 
> <td style="width:412px;"> Session_1获得for update的共享锁： mysql&gt; select actor_id, first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec) </td> 
> <td style="width:450px;"> 由于记录不存在，session_2也可以获得for update的共享锁： mysql&gt; select actor_id, first_name,last_name from actor where actor_id = 201 for update; Empty set (0.00 sec) </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:412px;"> Session_1可以成功插入记录： mysql&gt; insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); Query OK, 1 row affected (0.00 sec) </td> 
> <td style="width:450px;"></td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:412px;"></td> 
> <td style="width:450px;"> Session_2插入申请等待获得锁： mysql&gt; insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); 等待 </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:412px;"> Session_1成功提交： mysql&gt; commit; Query OK, 0 rows affected (0.04 sec) </td> 
> <td style="width:450px;"></td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:412px;"></td> 
> <td style="width:450px;"> Session_2获得锁，发现插入记录主键重，这个时候抛出了异常，但是并没有释放共享锁： mysql&gt; insert into actor (actor_id,first_name,last_name) values(201,'Lisa','Tom'); ERROR 1062 (23000): Duplicate entry '201' for key 'PRIMARY' </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:412px;"></td> 
> <td style="width:450px;"></td> 
> <td> Session_3申请获得共享锁，因为session_2已经锁定该记录，所以session_3需要等待： mysql&gt; select actor_id, first_name,last_name from actor where actor_id = 201 for update; 等待 </td> 
> </tr> 
> <tr> 
> <td style="width:412px;"></td> 
> <td style="width:450px;"> 这个时候，如果session_2直接对记录进行更新操作，则会抛出死锁的异常： mysql&gt; update actor set last_name='Lan' where actor_id = 201; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction </td> 
> <td></td> 
> </tr> 
> <tr> 
> <td style="width:412px;"></td> 
> <td style="width:450px;"></td> 
> <td> Session_2释放锁后，session_3获得锁： mysql&gt; select first_name, last_name from actor where actor_id = 201 for update; +------------+-----------+ | first_name | last_name | +------------+-----------+ | Lisa&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | Tom&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; | +------------+-----------+ 1 row in set (31.12 sec) </td> 
> </tr> 
> </tbody> 
> </table>

## 死锁排查 ##

如果出现死锁，可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因。返回结果中包括死锁相关事务的详细信息，如引发死锁的SQL语句，事务已经获得的锁，正在等待什么锁，以及被回滚的事务等。据此可以分析死锁产生的原因和改进措施。

下面是一段SHOW INNODB STATUS输出的样例：

mysql> show innodb status \G
    …….
    ------------------------
    LATEST DETECTED DEADLOCK
    ------------------------
    070710 14:05:16
    *** (1) TRANSACTION:
    TRANSACTION 0 117470078, ACTIVE 117 sec, process no 1468, OS thread id 1197328736 inserting
    mysql tables in use 1, locked 1
    LOCK WAIT 5 lock struct(s), heap size 1216
    MySQL thread id 7521657, query id 673468054 localhost root update
    insert into country (country_id,country) values(110,'Test')
    ………
    *** (2) TRANSACTION:
    TRANSACTION 0 117470079, ACTIVE 39 sec, process no 1468, OS thread id 1164048736 starting index read, thread declared inside InnoDB 500
    mysql tables in use 1, locked 1
    4 lock struct(s), heap size 1216, undo log entries 1
    MySQL thread id 7521664, query id 673468058 localhost root statistics
    select first_name,last_name from actor where actor_id = 1 for update
    *** (2) HOLDS THE LOCK(S):
    ………
    *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
    ………
    *** WE ROLL BACK TRANSACTION (1)
    ……

[MySQL--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/112756515
[MySQL InnoDB _ _]: https://www.yuque.com/yinjianwei/vyrvkf/ei0mep#meu35
[MySQL_-CSDN]: https://blog.csdn.net/mysteryhaohao/article/details/51669741
[1024_-CSDN]: https://blog.csdn.net/woshiyeguiren/article/details/80277475
[mysql_ - _ -]: https://www.cnblogs.com/laoyeye/p/8097684.html
[MySQL]: https://juejin.cn/post/6844904063776063496
[MySQL_MyISAM_InnoDB_hsd2012_-CSDN]: https://blog.csdn.net/hsd2012/article/details/51112009
[MyISAM_ - _ -]: https://www.cnblogs.com/Alight/p/3941293.html
[MySQL 1]: https://juejin.cn/post/6844903549168517127#heading-6
[MySQL_ _SELECT ... LOCK IN SHARE MODE _ SELECT ... FOR UPDATE_-CSDN]: https://blog.csdn.net/u012099869/article/details/52778728
[MySQL InnoDB _ _ 1]: https://www.yuque.com/yinjianwei/vyrvkf/ei0mep#j5MAz
[69003609866d469b63b8ab1eaf7a2cc1.png]: /images/20221119/4f4693b1035c42cbb35658df0675a3f0.png
[a8947c1d79c2f9357c3202f3474bb2a0.png]: /images/20221119/4e6aec2acc8742d7918b2fb15adbb36a.png
[9f653fc1e18b7bb1639c11718940ab3d.png]: https://img-blog.csdnimg.cn/img_convert/9f653fc1e18b7bb1639c11718940ab3d.png
[c2859573682929f207bce3a1dbe82c0f.png]: https://img-blog.csdnimg.cn/img_convert/c2859573682929f207bce3a1dbe82c0f.png
[MySQL InnoDB _ _ 2]: https://www.yuque.com/yinjianwei/vyrvkf/ei0mep#gZI3T
[39f33af1b468496d8d2999c510ce6ced.png]: https://img-blog.csdnimg.cn/img_convert/39f33af1b468496d8d2999c510ce6ced.png
[2a2b5c7b8904687902f11fcefff33a30.png]: https://img-blog.csdnimg.cn/img_convert/2a2b5c7b8904687902f11fcefff33a30.png
[20210118162942712.png]: https://img-blog.csdnimg.cn/20210118162942712.png
[3e288a67569f96c5f807a71e63cb9eb1.png]: https://img-blog.csdnimg.cn/img_convert/3e288a67569f96c5f807a71e63cb9eb1.png