面试题：在复杂分布式系统中MySQL行级锁与表级锁如何协同应用

Question

在一个涉及多个MySQL数据库实例的复杂分布式系统中，业务操作既包含大量单条数据的高频读写，又有批量数据的修改，此时应如何合理地协同应用行级锁和表级锁，以确保数据的一致性和系统的高性能，需要考虑分布式事务、网络延迟等因素。

Answer 1

1. 单条数据高频读写场景
   • 行级锁的应用：对于单条数据的高频读操作，一般无需加锁，因为MySQL的MVCC（多版本并发控制）机制可保证读一致性。而对于高频写操作，使用行级锁。例如，在更新一条用户信息记录时，通过SELECT ... FOR UPDATE语句锁定特定行。这可以减少锁的粒度，降低对其他行数据操作的影响，提高并发性能。
   • 分布式事务考量：在分布式系统中，若该单条数据的读写操作涉及分布式事务，可使用XA协议。在获取行级锁时，要确保所有参与事务的数据库实例都能成功获取相应行锁，以保证数据一致性。如在跨多个MySQL实例更新用户账户信息（可能分库存储）时，各实例都要获取对应行锁。
   • 网络延迟应对：由于网络延迟可能导致获取行锁超时等问题，需设置合理的锁等待超时时间。若获取锁超时，应用程序应根据业务逻辑决定是重试获取锁还是进行其他处理，如记录日志并向用户返回友好提示。
2. 批量数据修改场景
   • 表级锁与行级锁选择：
     • 少量数据批量修改：若批量修改的数据量较少且分布较为离散，仍可使用行级锁。例如，批量更新10条分布在不同区域的订单状态，可通过多次SELECT ... FOR UPDATE获取每行锁，然后依次更新。
     • 大量数据批量修改：当批量修改大量连续或关联性强的数据时，表级锁更为合适。比如要对某个月内所有订单进行状态更新，此时获取表级锁可避免获取大量行锁带来的性能开销和死锁风险。可使用LOCK TABLES语句锁定表，完成批量修改后再UNLOCK TABLES。
   • 分布式事务考量：对于涉及多个数据库实例的批量数据修改分布式事务，在获取表级锁时，要遵循两阶段提交（2PC）或三阶段提交（3PC）协议。先在所有参与实例尝试获取表级锁，若都成功则进行数据修改操作，若有一个实例获取锁失败，则全部回滚。
   • 网络延迟应对：在使用表级锁时，由于锁的粒度大，网络延迟可能会对系统性能产生更大影响。除设置合理的锁等待超时时间外，可考虑采用异步处理方式。即先将批量修改任务放入消息队列，由后台服务逐步获取表级锁并执行修改操作，这样可避免前端请求长时间等待。
3. 锁的优化与死锁预防
   • 优化：无论是行级锁还是表级锁，都要尽量缩短锁的持有时间。在获取锁后尽快完成数据操作并释放锁。例如，在处理分布式事务时，将复杂的业务逻辑拆分成多个小步骤，在获取锁的阶段只进行必要的数据修改操作。
   • 死锁预防：
     • 按顺序获取锁：在分布式系统中，对多个数据库实例的锁获取制定统一顺序。比如按照数据库实例的ID从小到大获取锁，避免循环等待导致死锁。
     • 死锁检测与自动回滚：MySQL自身有死锁检测机制，当检测到死锁时，会自动回滚一个事务。应用程序应能正确处理这种回滚情况，进行必要的重试或其他处理逻辑。