面试题：MySQL死锁分析与解决

Question

假设在一个MySQL数据库中有两个事务T1和T2，T1先对表A的记录1加排他锁，然后尝试对表B的记录2加排他锁；T2先对表B的记录2加排他锁，然后尝试对表A的记录1加排他锁，这种情况会导致死锁。请描述如何在MySQL中检测到这种死锁，并提出至少两种避免死锁的方案。

Answer 1

检测死锁

MySQL 内置了死锁检测机制，当 InnoDB 存储引擎检测到死锁时，会自动选择一个事务进行回滚（牺牲掉这个事务），以打破死锁。InnoDB 通过等待图（wait-for graph）算法来检测死锁，它会监控事务之间的锁等待关系，当发现循环等待时，就判定发生了死锁。

避免死锁的方案

1. 按照相同顺序访问资源： 所有事务都按照固定的顺序访问资源，例如先访问表 A 的记录 1，再访问表 B 的记录 2。这样可以避免循环等待的情况。比如在业务代码中，规定所有涉及表 A 和表 B 的操作，都先对表 A 进行操作，再对表 B 进行操作。
2. 设置合理的锁超时时间： 为事务设置合理的锁等待超时时间。如果一个事务等待锁的时间超过了设定的超时时间，就会自动放弃等待并回滚。可以通过 innodb_lock_wait_timeout 参数来设置 InnoDB 存储引擎的锁等待超时时间（默认值为 50 秒）。例如，若将其设置为 10 秒，当某个事务等待锁超过 10 秒，就会回滚，从而打破可能的死锁。
3. 使用乐观锁： 乐观锁假设在大多数情况下不会发生冲突，不使用数据库的锁机制，而是通过版本号（version）或者时间戳（timestamp）来控制并发。每次更新数据时，将版本号加 1，事务在提交时，检查版本号是否与开始时一致，如果一致则提交成功，否则回滚事务重新执行。例如表结构中添加一个 version 字段，初始值为 1，更新语句如下：

UPDATE your_table
SET column1 = 'new_value', version = version + 1
WHERE id = 1 AND version = 1;

4. 减少锁的持有时间： 尽量缩短事务持有锁的时间，在获取锁后尽快完成相关操作并释放锁。例如，将大事务拆分成多个小事务，每个小事务只包含必要的操作，这样可以减少锁的竞争时间，降低死锁发生的概率。