#来点儿干货# 今天周日，告警群报了一个死锁异常，简单排查发现是因为批量更新引起间隙锁和关联更新引起死锁，修改关联SQL为通过ID更新，批量更新修改为查询主键后更新，应该能暂时解决问题。刚好借此机会整理一下mysql锁相关知识。

场景一：交叉锁定资源

问题描述 ：两个或多个事务各自持有一部分资源，并尝试获取对方持有的资源，导致无法继续执行的情况。

示例 ：

• 事务A锁定了 table1 中的行，同时事务B锁定了 table2 中的行。
• 事务A尝试锁定 table2 中已被事务B锁定的行，而事务B尝试锁定 table1 中已被事务A锁定的行。

解决方案 ：

• 确保所有事务按照相同的顺序访问资源。
• 实施锁超时机制，让事务在等待锁定资源超过一定时间后回滚并重试。

场景二：长事务

问题描述 ：长时间运行的事务占用资源，导致其他事务无法访问这些资源。

示例 ：

• 事务A执行一个长时间运行的查询或更新操作，持有行锁。
• 同时，其他事务尝试访问被事务A锁定的行。

解决方案 ：

• 尽可能地将长事务拆分成多个短事务。
• 优先执行短事务，让长事务在短事务完成后再执行。

场景三：不合理的锁定粒度

问题描述 ：事务锁定了太多不必要的资源，例如，表级锁定代替行级锁定。

示例 ：

• 事务A在 table1 上执行了一个涉及多行的操作，锁定了整个表。
• 事务B需要更新 table1 中的一行，但由于表被锁定，无法执行。

解决方案 ：

• 使用合适的锁定粒度，例如优先使用行级锁而非表级锁。
• 优化查询，确保只锁定必要的行。

场景四：外键和级联操作

问题描述 ：外键约束和级联更新或删除可能导致多个表之间的锁定冲突。

示例 ：

• 事务A更新了 table1 ，这触发了对 table2 的级联更新。
• 同时，事务B尝试更新 table2 中的相关行。

解决方案 ：

• 重新考虑数据库设计，避免使用级联更新和删除。
• 确保相关更新操作按照一致的顺序执行。

场景五：索引缺失

问题描述 ：没有适当的索引，导致数据库进行全表扫描，增加了锁定范围和时间。

示例 ：

• 事务A对没有索引的列进行搜索，导致全表扫描。
• 事务B需要访问表中的某些行，但由于全表扫描，这些行被锁定。

解决方案 ：

• 为经常用于搜索和连接的列创建索引。
• 定期审查查询性能，确保索引的有效使用。

场景六：多表锁定

问题描述 ：一个事务在多个表上请求锁，而这些表同时也被其他事务请求锁定。

示例 ：

• 事务A锁定了 table1 和 table2 。
• 事务B和事务C分别尝试锁定 table2 和 table1 。

解决方案 ：

• 优化事务逻辑，减少跨表锁定的需求。
• 确保跨表锁定按照固定的顺序执行，以避免死锁。

场景七：锁定超时

问题描述 ：事务等待锁定资源的时间过长，导致系统资源被不必要地占用。

示例 ：

• 事务A等待锁定 table1 中的行，但事务B持有锁并且运行缓慢。

解决方案 ：

• 设置合理的锁定超时时间，超时后事务应回滚并重试或失败。
• 监控系统性能，调整事务逻辑以减少锁定等待时间。

场景八：并发插入

问题描述 ：高并发下的数据插入操作可能会导致锁争用和死锁。

示例 ：

• 多个事务试图同时在 table1 中插入数据。

解决方案 ：

• 使用批量插入减少锁定次数。
• 评估并可能启用 innodb_autoinc_lock_mode 来优化自增锁定。

在处理MySQL死锁问题时，重要的是要分析日志，理解死锁的具体原因，并根据实际情况选择合适的解决策略。通常，这可能涉及到调整事务的大小和顺序、优化索引使用、重新设计数据库结构以及调整应用程序的数据库访问模式。

下一篇整理具体sql场景，和大家一起分享成长！

ps：公司在招聘JAVA和数仓工程师，有看机会的朋友可以私信甩简历。