面试题：MySQL 死锁概率评估指标与优化策略

Question

详细说明从哪些关键指标去评估 MySQL 死锁概率，以及针对这些指标，在数据库设计、事务管理等方面可以采取哪些优化措施来降低死锁概率。

Answer 1

评估 MySQL 死锁概率的关键指标

1. 锁争用情况
   • 指标：查看 SHOW STATUS LIKE 'innodb_row_lock%'; 中 Innodb_row_lock_current_waits（当前正在等待锁的数量）、Innodb_row_lock_time（等待锁的总时长）、Innodb_row_lock_time_avg（每次等待锁的平均时长）、Innodb_row_lock_waits（等待锁的总次数）等指标。高数值意味着锁争用激烈，死锁概率增大。
   • 原因：当多个事务同时请求相同资源的锁时，就会产生锁争用。争用越激烈，不同事务获取锁的顺序越容易形成环路，从而导致死锁。
2. 事务复杂度
   • 指标：事务中涉及的 SQL 语句数量、表数量、操作类型（如插入、更新、删除）等。复杂事务通常包含更多步骤和锁请求，增加死锁风险。
   • 原因：复杂事务在执行过程中需要获取多个锁，这些锁的获取顺序如果在不同事务中不一致，就容易形成死锁。
3. 索引使用情况
   • 指标：通过 EXPLAIN 语句分析查询是否使用了合适的索引。若查询未使用索引，可能导致全表扫描，从而锁定更多的行，增加死锁概率。
   • 原因：全表扫描会锁定大量数据行，多个事务同时进行全表扫描操作时，更容易出现锁冲突和死锁。
4. 并发事务数量
   • 指标：监控系统中同时活跃的事务数量。并发事务越多，锁资源竞争越激烈，死锁可能性越大。
   • 原因：并发事务多意味着更多的锁请求和释放操作，在资源有限的情况下，锁获取顺序更容易混乱，引发死锁。

降低死锁概率的优化措施

数据库设计方面

1. 优化索引
   • 做法：分析业务查询，为经常用于 WHERE 子句、连接条件的列创建合适的索引。避免冗余索引和不必要的索引，减少索引维护成本。
   • 原理：合适的索引能使查询定位更准确，减少锁的范围，降低锁争用，从而减少死锁发生的可能性。
2. 合理设计表结构
   • 做法：避免大表，对大表进行水平或垂直拆分。例如，按时间范围对日志表进行水平拆分；将不常用的大字段单独拆分到另一张表。
   • 原理：拆分表能减少单个事务锁定的数据量，降低锁争用的程度，减少死锁概率。
3. 控制事务粒度
   • 做法：将大事务拆分成多个小事务，确保每个事务只处理必要的操作。例如，在电商下单场景中，将库存扣减、订单生成、支付等操作拆分成不同事务按顺序执行。
   • 原理：小事务执行时间短，持有锁的时间也短，能减少锁争用时间，降低死锁风险。

事务管理方面

1. 设定合理的事务隔离级别
   • 做法：根据业务需求选择合适的隔离级别，一般情况下，默认的 REPEATABLE READ 能满足多数业务。如果业务对一致性要求不高，可考虑使用 READ COMMITTED 隔离级别。
   • 原理：不同隔离级别对锁的使用方式不同，选择合适的隔离级别可以在保证数据一致性的前提下，减少锁的使用，降低死锁概率。
2. 按照相同顺序访问资源
   • 做法：在所有事务中，确保对多个表或行的访问顺序一致。例如，在涉及多个表的事务中，始终先操作表 A，再操作表 B。
   • 原理：统一资源访问顺序可避免因锁获取顺序不一致而导致的死锁。
3. 设置合理的锁等待超时时间
   • 做法：通过 innodb_lock_wait_timeout 参数设置合适的锁等待超时时间，如设置为 50（秒）。
   • 原理：当事务等待锁的时间超过该值时，自动回滚，避免事务长时间等待导致死锁情况一直存在，提高系统的可用性。
4. 死锁检测与自动回滚
   • 做法：MySQL 内置了死锁检测机制，当检测到死锁时，会自动选择回滚一个事务来解除死锁。开发人员应妥善处理事务回滚后的业务逻辑，确保数据一致性。
   • 原理：自动回滚死锁事务，防止死锁导致系统阻塞，尽快恢复系统正常运行。