mysql死锁日志解读

MySQL死锁日志解读

基础概念

MySQL死锁是指两个或多个事务互相等待对方释放资源，导致所有事务都无法继续执行的现象。MySQL通过检测并回滚其中一个事务来解决死锁。

类型

基于锁的死锁：事务之间因为获取锁的顺序不同而产生死锁。
基于等待图的死锁：事务之间形成一个循环等待链，导致死锁。

应用场景

死锁常见于高并发的数据库操作中，例如银行转账、库存管理等需要同时更新多个记录的场景。

死锁日志示例

假设我们有以下两个事务：

-- 事务1
START TRANSACTION;
UPDATE table1 SET amount = amount - 100 WHERE id = 1;
UPDATE table2 SET amount = amount + 100 WHERE id = 2;

-- 事务2
START TRANSACTION;
UPDATE table2 SET amount = amount - 100 WHERE id = 2;
UPDATE table1 SET amount = amount + 100 WHERE id = 1;

如果事务1先获取了table1的锁，事务2先获取了table2的锁，两个事务就会陷入死锁。

死锁日志解读

MySQL的死锁日志通常包含以下信息：

LATEST DETECTED DEADLOCK
========================
2023-04-01 12:34:56 0x7f9b8c0b4700
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1234, OS thread handle 0x7f9b8c0b4700, query id 123456 localhost user updating
UPDATE table1 SET amount = amount - 100 WHERE id = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index `PRIMARY` of table `test`.`table2` trx id 123456 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000002; asc     ;;
 2: len 7; hex 0000000000000064; asc     d;;

*** (2) TRANSACTION:
TRANSACTION 123457, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1235, OS thread handle 0x7f9b8c0b4701, query id 123457 localhost user updating
UPDATE table2 SET amount = amount - 100 WHERE id = 2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 3 n bits 72 index `PRIMARY` of table `test`.`table1` trx id 123457 lock_mode X locks rec but not gap
Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000000001; asc     ;;
 2: len 7; hex 0000000000000064; asc     d;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index `PRIMARY` of table `test`.`table2` trx id 123457 lock_mode X waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000002; asc     ;;
 1: len 6; hex 000000000002; asc     ;;
 2: len 7; hex 0000000000000064; asc     d;;

*** WE ROLL BACK TRANSACTION (2)

解决方法

优化事务顺序：确保所有事务按照相同的顺序获取锁，避免循环等待。
减少事务范围：尽量缩小事务的范围，减少锁的持有时间。
使用超时机制：设置事务超时时间，超过时间自动回滚。

示例代码

以下是一个优化后的示例代码，确保事务按照相同的顺序获取锁：

-- 事务1
START TRANSACTION;
UPDATE table1 SET amount = amount - 100 WHERE id = 1;
UPDATE table2 SET amount = amount + 100 WHERE id = 2;
COMMIT;

-- 事务2
START TRANSACTION;
UPDATE table1 SET amount = amount + 100 WHERE id = 1;
UPDATE table2 SET amount = amount - 100 WHERE id = 2;
COMMIT;

通过这种方式，可以有效避免死锁的发生。