MySQL--死锁的原因及解决方法

一时失言乱红尘 2023-10-02 21:36 12阅读 0赞

原文网址：[MySQL--死锁的原因及解决方法\_IT利刃出鞘的博客-CSDN博客][MySQL--_IT_-CSDN]

## 简介 ##

**说明**

本文介绍MySQL死锁的原因及解决方法。

InnoDB中，除单个SQL组成的事务外，锁是逐步获得的，所以可能发生死锁。

**不会死锁的情况**

MyISAM表不会出现死锁（Deadlock Free） 。

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

## 导致死锁的场景 ##

### **先申请共享锁后申请排它锁（同一张表）** ###

在事务中，更新记录时应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁，更新时再申请排他锁。因为当申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁。

下面的例子中，先申请共享锁，更新时再申请排他锁，造成死锁。

<table> 
 <tbody> 
 <tr> 
 <td>session_1</td> 
 <td>session_2</td> 
 </tr> 
 <tr> 
 <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> 
 <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> 当前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></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> 当前session对锁定的记录进行更新操作，等待锁： mysql&gt; update actor set last_name = 'MONROE T' where actor_id = 178; 等待 </td> 
 <td></td> 
 </tr> 
 <tr> 
 <td></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> 获得锁后，可以成功更新： 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访问两个表的顺序不同，发生死锁的机会就非常高！但如果以相同的顺序来访问，死锁就可以避免。

上边只是部分内容，为便于维护，本文已迁移到此地址：[MySQL-死锁的原因及解决方法 - 自学精灵][MySQL-_ -]

[MySQL--_IT_-CSDN]: https://knife.blog.csdn.net/article/details/127113231
[MySQL-_ -]: https://learn.skyofit.com/archives/641