面试题：在复杂的 MySQL 事务场景下，如何避免死锁并进行有效的性能调优？

资源竞争：多个事务都试图获取对方已持有的资源，形成循环等待。例如事务 A 持有资源 R1 并请求资源 R2，而事务 B 持有资源 R2 并请求资源 R1。
锁顺序不一致：不同事务以不同顺序获取锁，例如事务 T1 先获取锁 L1 再获取 L2，事务 T2 先获取 L2 再获取 L1，当并发执行时可能导致死锁。
长时间持有锁：某个事务长时间持有锁，导致其他事务等待，增加死锁风险。

innodb_lock_wait_timeout：
- 该参数设置 InnoDB 事务等待行锁的超时时间（单位为秒）。可以适当调小此值，例如设置为 50（默认值为 50）。这样当事务等待锁时间过长时，会自动回滚，避免长时间等待造成死锁。
- 优点是能快速释放等待资源，减少死锁可能性。缺点是可能导致一些正常业务因等待锁时间不足而回滚。
innodb_deadlock_detect：
- 该参数控制 InnoDB 是否启用死锁自动检测。默认值为 ON，开启时 InnoDB 会自动检测死锁并回滚一个事务来打破死锁。可以根据实际情况考虑设置为 OFF，如果业务场景中死锁发生概率极低，关闭此检测可减少检测带来的性能开销。但如果关闭，死锁发生时不会自动处理，需手动干预。

读未提交（Read Uncommitted）：
- 此隔离级别下，事务可以读取其他事务未提交的数据。虽然可以减少锁等待时间，提高并发性能，但会出现脏读问题。适用于对数据一致性要求不高，且希望最大程度提高并发的场景，如一些统计分析场景。
读已提交（Read Committed）：
- 事务只能读取已提交的数据，避免了脏读。在这种隔离级别下，InnoDB 使用行级锁，相比更高隔离级别，锁的粒度更小，减少了锁争用，从而降低死锁风险。大多数业务场景可以使用此隔离级别，能在数据一致性和并发性能之间取得较好平衡。
可重复读（Repeatable Read）：
- 这是 MySQL 的默认隔离级别。在一个事务内多次读取同一数据，结果是一致的，避免了脏读和不可重复读。InnoDB 在可重复读级别下使用 MVCC（多版本并发控制），减少了锁的使用，提高并发性能。但在某些情况下（如间隙锁的使用），仍可能出现死锁，不过相比串行化隔离级别，死锁概率较低。
串行化（Serializable）：
- 最高隔离级别，事务串行执行，不存在并发问题，也就不会产生死锁。但性能最低，因为所有事务都排队执行，只有在对数据一致性要求极高，且并发量极小的场景下才使用。

合理使用索引：
- 为经常作为查询条件和连接条件的列添加索引。例如在多表关联更新中，对关联字段添加索引，可减少全表扫描，加快查询速度，从而减少事务持有锁的时间，降低死锁风险。如在 UPDATE table1 JOIN table2 ON table1.id = table2.id SET table1.column = 'value' 中，为 table1.id 和 table2.id 添加索引。
减少锁的粒度：
- 尽量使用行级锁而不是表级锁。例如在更新操作中，通过条件限制只更新必要的行，而不是对整个表加锁。如 UPDATE users SET status = 'active' WHERE user_id = 1 比 UPDATE users SET status = 'active' 更好，前者只锁定 user_id = 1 的行，而后者会锁定整个 users 表。
按相同顺序访问资源：
- 在多个事务中，确保按相同顺序获取锁和访问资源。例如所有事务都先获取表 A 的锁，再获取表 B 的锁，这样可以避免因锁顺序不一致导致的死锁。

查看错误日志：
- MySQL 的错误日志（通常为 error.log）会记录死锁相关信息。当死锁发生时，日志中会包含死锁发生的时间、涉及的事务、锁信息等。例如：
```
[InnoDB] 2023 - 10 - 01 12:00:00.123456, deadlock detected, dumping detailed information:
*** (1) TRANSACTION:
TRANSACTION 123456, ACTIVE 10 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
```
可以通过分析这些信息了解死锁发生的具体场景，如事务执行的操作、等待的锁等。
使用 SHOW ENGINE INNODB STATUS：
- 执行 SHOW ENGINE INNODB STATUS 命令，会返回详细的 InnoDB 引擎状态信息，其中包括死锁相关内容。在输出结果中，LATEST DETECTED DEADLOCK 部分详细记录了死锁的信息，包括两个事务的详细情况，如事务 ID、执行的 SQL 语句、锁的类型和对象等。通过分析这些信息，可以明确死锁产生的原因，如锁顺序、资源竞争等，并针对性地进行优化。
事务跟踪：
- 可以通过开启慢查询日志（slow_query_log）和设置 long_query_time 来记录执行时间较长的事务。结合死锁发生时间，查看慢查询日志中对应事务的 SQL 语句，分析其执行过程中是否存在长时间持有锁、锁争用等问题，从而进一步排查死锁原因。

星途面试题库