携手创作，共同成长！这是我参与「掘金日新计划 · 8 月更文挑战」的第5天，点击查看活动详情
Mysql Deadlock
最近项目中突然遇到 MySQL 的死锁问题，日志为：Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction。突然联想到有次面试面试官让我手写 MySQL 死锁的代码。今天借此机会总结一下死锁的问题。
死锁代码
create database test;
use test;
CREATE TABLE `test` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`status` tinyint DEFAULT '0' COMMENT '0：未发布；1：已发布',
`create_time` timestamp NOT NULL DEFAULT current_timestamp() COMMENT '创建时间',
`update_time` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp() COMMENT '更新时间',
PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='测试数据库';
-- session1
begin;
update test set status=1 where id=1;
update test set status=1 where id=2;
commit;
-- session2
begin;
update test set status=1 where id=2;
update test set status=1 where id=1;
begin;
-- 错误日志
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
下图显示以上代码执行结果，标记结果也可以看出 MySQL 的默认事务隔离级别。可重复读即 session2 中不会读到session1 中未提交的数据。
下图可以看到 Mysql 死锁的日志。即 session1 在等待 session2 释放锁；session2 在等待 session1 释放锁，最终就会出现死锁。
死锁分析
-- mysql 查询死锁的方法
show engine innodb status;
-- 需要关心的日志如下
LATEST DETECTED DEADLOCK
------------------------
2022-08-13 23:38:55 0x16e39b000
*** (1) TRANSACTION:
TRANSACTION 3349, ACTIVE 185 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10, OS thread handle 6159429632, query id 25 localhost root updating
update test set status=1 where id=2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test`.`test` trx id 3349 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000000d15; asc ;;
2: len 7; hex 020000010a03a3; asc ;;
3: len 1; hex 81; asc ;;
4: len 4; hex 62f7c4bf; asc b ;;
5: len 4; hex 62f7c4d6; asc b ;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test`.`test` trx id 3349 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000002; asc ;;
1: len 6; hex 000000000d16; asc ;;
2: len 7; hex 01000001140519; asc ;;
3: len 1; hex 81; asc ;;
4: len 4; hex 62f7c4bf; asc b ;;
5: len 4; hex 62f7c517; asc b ;;
*** (2) TRANSACTION:
TRANSACTION 3350, ACTIVE 120 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 6160543744, query id 26 localhost root updating
update test set status=1 where id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test`.`test` trx id 3350 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000002; asc ;;
1: len 6; hex 000000000d16; asc ;;
2: len 7; hex 01000001140519; asc ;;
3: len 1; hex 81; asc ;;
4: len 4; hex 62f7c4bf; asc b ;;
5: len 4; hex 62f7c517; asc b ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 7 page no 4 n bits 72 index PRIMARY of table `test`.`test` trx id 3350 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 8; hex 8000000000000001; asc ;;
1: len 6; hex 000000000d15; asc ;;
2: len 7; hex 020000010a03a3; asc ;;
3: len 1; hex 81; asc ;;
4: len 4; hex 62f7c4bf; asc b ;;
5: len 4; hex 62f7c4d6; asc b ;;
*** WE ROLL BACK TRANSACTION (2)
......
......
上面展示了整个死锁发生的日志。只要搜索对应的 SQL 就可以收集到关键信息，下面简单分析下死锁的整个过程。
- session1 开启事务，并获取 id=1 数据的锁；
- session2 开启事务，并获取 id=2 数据的锁；
- session1 开启事务，尝试获取 id=2 数据的锁，并等待；
- session2 开启事务，尝试获取 id=1 数据的锁，并等待；
- 检测到死锁的出现，Mysql 回滚 session2 session1 并记录日志。
解决方案
对于 InnoDB 存储引擎来说，死锁的发生有很多种情况，总结来说就是在不同的事务中同时修改同一资源所造成的。
那么避免死锁的方式有：
- 通过表锁来减少死锁产生的概率（效率低）
- 多个程序尽量约定以相同的顺序访问表 （类似队列）
- 在一个事务中尽可能做到一次锁定所有所需的数据 （逻辑复杂）
Spring Deadlock
上文分析了 SQL 语句中死锁的发生条件及解决方法，那么在日常 Coding 的时候底层的数据库操作都交给了 ORM(Mybatis) 或者 Spring 。日常工作中如何解决死锁？
其实死锁的发生的条件大同小异，对于 Spring 来说我们一定要注意事务的粒度。也就是要注意@Transactional注解的使用。尤其是要熟悉事务的传播机制，不要将所有的代码都包裹在一个事务被运行，在真正需要更新数据的时候才使用锁、事务。
Transactional propagation
- REQUIRED 支持当前事务，如果当前没有事务，就新建一个事务
- SUPPORTS 支持当前事务，如果没有当前事务，就以非事务方式执行
- MANDATORY 支持当前事务，如果当前没有事务，就抛出异常
- REQUIRES_NEW 新建事务，如果存在当前事务，把当前事务挂起
- NOT_SUPPORTED 以非事务方式执行操作，如果当前存在事务，就把当前事务挂起
- NEVER 以非事务方式执行，如果当前存在事务，则抛出异常
- NESTED 新建事务，依赖于上级事务，如果失败则一同回滚；
注意： spring事务中，默认是只有运行时异常（RuntimeException及其子类）才会回滚，而且使用的默认传播性是REQUIRED。可以对着英文的注释一同去理解。
public enum Propagation {
/**
* Support a current transaction, create a new one if none exists.
* Analogous to EJB transaction attribute of the same name.
* <p>This is the default setting of a transaction annotation.
*/
REQUIRED(TransactionDefinition.PROPAGATION_REQUIRED),
/**
* Support a current transaction, execute non-transactionally if none exists.
* Analogous to EJB transaction attribute of the same name.
* <p>Note: For transaction managers with transaction synchronization,
* {@code SUPPORTS} is slightly different from no transaction at all,
* as it defines a transaction scope that synchronization will apply for.
* As a consequence, the same resources (JDBC Connection, Hibernate Session, etc)
* will be shared for the entire specified scope. Note that this depends on
* the actual synchronization configuration of the transaction manager.
* @see org.springframework.transaction.support.AbstractPlatformTransactionManager#setTransactionSynchronization
*/
SUPPORTS(TransactionDefinition.PROPAGATION_SUPPORTS),
/**
* Support a current transaction, throw an exception if none exists.
* Analogous to EJB transaction attribute of the same name.
*/
MANDATORY(TransactionDefinition.PROPAGATION_MANDATORY),
/**
* Create a new transaction, and suspend the current transaction if one exists.
* Analogous to the EJB transaction attribute of the same name.
* <p><b>NOTE:</b> Actual transaction suspension will not work out-of-the-box
* on all transaction managers. This in particular applies to
* {@link org.springframework.transaction.jta.JtaTransactionManager},
* which requires the {@code javax.transaction.TransactionManager} to be
* made available to it (which is server-specific in standard Java EE).
* @see org.springframework.transaction.jta.JtaTransactionManager#setTransactionManager
*/
REQUIRES_NEW(TransactionDefinition.PROPAGATION_REQUIRES_NEW),
/**
* Execute non-transactionally, suspend the current transaction if one exists.
* Analogous to EJB transaction attribute of the same name.
* <p><b>NOTE:</b> Actual transaction suspension will not work out-of-the-box
* on all transaction managers. This in particular applies to
* {@link org.springframework.transaction.jta.JtaTransactionManager},
* which requires the {@code javax.transaction.TransactionManager} to be
* made available to it (which is server-specific in standard Java EE).
* @see org.springframework.transaction.jta.JtaTransactionManager#setTransactionManager
*/
NOT_SUPPORTED(TransactionDefinition.PROPAGATION_NOT_SUPPORTED),
/**
* Execute non-transactionally, throw an exception if a transaction exists.
* Analogous to EJB transaction attribute of the same name.
*/
NEVER(TransactionDefinition.PROPAGATION_NEVER),
/**
* Execute within a nested transaction if a current transaction exists,
* behave like {@code REQUIRED} otherwise. There is no analogous feature in EJB.
* <p>Note: Actual creation of a nested transaction will only work on specific
* transaction managers. Out of the box, this only applies to the JDBC
* DataSourceTransactionManager. Some JTA providers might support nested
* transactions as well.
* @see org.springframework.jdbc.datasource.DataSourceTransactionManager
*/
NESTED(TransactionDefinition.PROPAGATION_NESTED);
private final int value;
Propagation(int value) {
this.value = value;
}
public int value() {
return this.value;
}
}
