数据库死锁

发表于 更新于

死锁是指两个或者多个事务在同一个资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

示例

先用一个示例来演示一下数据库死锁的情况。本示例基于 MySQL 的 InnoDB 存储引擎。

表结构

我们先建一个 wallet 表，表结构如下

1
2
3
4
5
6
7
8
mysql> DESC wallet;
+---------+---------------------+------+-----+---------+-------+
| Field   | Type                | Null | Key | Default | Extra |
+---------+---------------------+------+-----+---------+-------+
| id      | bigint(20) unsigned | NO   | PRI | NULL    |       |
| user_id | bigint(20) unsigned | NO   | UNI | 0       |       |
| balance | bigint(20)          | NO   |     | 0       |       |
+---------+---------------------+------+-----+---------+-------+

开启事务 A，并修改 id 为 1 的记录

1
2
3
4
5
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE wallet SET balance = balance + 100 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)

开启事务 B，并修改 id 为 2 的记录

1
2
3
4
5
6
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> UPDATE wallet SET balance = balance + 100 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

事务 A 修改 id 为 2 的记录

1
mysql> UPDATE wallet SET balance = balance + 100 WHERE id = 2;

此时发现该修改并未马上成功，而是陷入的等待状态，原因是事务 A 此时要锁定 id 为 2 的记录，但该记录已经被事务 B 锁定了。

事务 B 修改 id 为 3 的记录

1
2
mysql> UPDATE wallet SET balance = balance + 100 WHERE id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

此时事务 B 报错，错误原因很明确是发生了死锁问题，通过查询操作也可以发现事务 B 的操作已经回滚，于此同时事务 A 对 id 为 2 的记录的修改已经成功，后续的 COMMIT 操作就不展示了。

如何解决死锁问题

死锁产生的原因

锁的行为和顺序是和存储引擎相关的，同样的顺序执行语句，有些存储引擎会产生死锁，有的则不会。故死锁的产生有两重原因：有些是因为真正的数据冲突，有些则完全是由于存储引擎的实现方式导致的。

死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务性的系统，死锁问题是无法避免的，设计对应的应用程序时必须考虑如何处理死锁。大部分情况只需要重新执行因死锁导致回滚的事务即可。

解决死锁问题

常见的数据库都实现了个中死锁检测和死锁超时机制。

  • 死锁检测：比如上述例子中，检测到死锁问题，数据库立马报错，这种解决方式非常有效，否则死锁会导致非常慢的查询。

  • 超时机制：超时机制就是当查询时间达到锁等待超时的设定后放弃锁的请求。

InnoDB 目前处理死锁的方式是将最少行级排他锁的事务进行回滚。

InnoDB 显式和隐式锁定

InnoDB 采用的时两端锁定协议（two-phase locking protocol）。在事务执行过程中，随时都可以执行锁定，锁只有在执行 COMMIT 或者 ROLLBACK 的时候才会被释放，并且所有的锁是在同一时刻被释放。InnoDB 会根据隔离级别在需要的的时候自动加锁。

InnoDB 也支持通过特定的语句进行显式的锁定，这些语句不属于 SQL 规范。

  • SELECT ... LOCK IN SHARE MODE
  • SELECT ... FOR UPDATE

MySQL 也支持 LOCK TABLES 和 UNLOCK TABLES 语句，这是在服务器层面实现的，跟存储引擎无关，不能用于代替事务处理。