面试题：MySQL排他锁在多表关联写操作中的应用及问题处理

Question

假设存在一个复杂的业务场景，涉及多张表的关联写操作（例如订单表、订单详情表、库存表），使用MySQL排他锁来保障数据一致性。请阐述在这种情况下加锁的策略、可能遇到的死锁问题以及如何预防和解决死锁。

Answer 1

加锁策略

1. 按照固定顺序加锁：为了避免死锁，在涉及多张表的写操作时，应按照固定顺序对表加锁。例如，始终先对订单表加锁，然后订单详情表，最后库存表。这样所有事务都以相同顺序获取锁，减少死锁可能性。
2. 尽量减少锁的范围：对于订单表，若只是针对某一订单记录进行操作，应使用行级锁而不是表级锁。例如，通过SELECT... FOR UPDATE语句，针对特定订单ID进行加锁，如SELECT * FROM orders WHERE order_id = 123 FOR UPDATE;，这能减少并发事务之间的锁冲突。
3. 合理设置锁的粒度：在订单详情表中，如果是对某一批订单详情记录进行操作，可以根据业务情况，先按订单ID进行分组加锁，而不是一次性对所有订单详情记录加锁。例如，先锁定与特定订单相关的订单详情记录，再进行操作。

可能遇到的死锁问题

1. 循环依赖死锁：当两个或多个事务互相等待对方释放锁时会产生死锁。比如事务A持有订单表的锁，等待库存表的锁；而事务B持有库存表的锁，等待订单表的锁，从而形成循环依赖，导致死锁。
2. 锁超时死锁：若事务获取锁的等待时间过长，可能导致锁超时，MySQL会自动回滚其中一个事务来解除死锁。例如，在高并发场景下，一个事务长时间等待获取某一表的锁，达到锁超时时间后，MySQL判定为死锁并进行处理。

预防和解决死锁

1. 预防死锁
   • 优化事务逻辑：尽量将大事务拆分成小事务，减少事务持有锁的时间。例如，将订单创建、订单详情插入和库存更新操作，拆分成多个步骤执行，每个步骤作为一个小事务，执行完后立即释放锁。
   • 设置合理的锁等待超时时间：通过innodb_lock_wait_timeout参数设置合适的锁等待超时时间。一般来说，根据业务场景，可设置为一个适中的值，如50秒。这样可以避免事务长时间等待锁而导致系统资源浪费。
   • 使用乐观锁：在一些读多写少的场景下，可采用乐观锁机制。例如，在库存表中增加一个版本号字段，每次更新库存时，先检查版本号，若版本号一致则进行更新并更新版本号，否则重试操作。这能减少锁的使用，降低死锁概率。
2. 解决死锁
   • 死锁检测与自动回滚：MySQL的InnoDB存储引擎内置了死锁检测机制，当检测到死锁时，会自动选择一个回滚代价最小的事务进行回滚。应用程序应捕获回滚异常，重新执行该事务。
   • 手动干预：在某些复杂场景下，若自动回滚不能满足需求，可以通过查看information_schema.innodb_trx等系统表来获取死锁相关信息，手动分析并决定回滚哪个事务。例如，根据事务的重要性、已执行的操作等因素，选择合适的事务进行回滚。