如何避免MySQL死锁？周期性数据更新引发的表锁问题求解

问题根源 #

核心问题在于全量更新操作持有表锁时间过长，加上大事务的存在，导致实时查询/插入请求无法正常访问数据库。下面是几个直接可行的优化方向：

1. 从全量更新改为增量同步 #

不要每次都更新整张表，只同步外部API中发生变化的数据：

• 给业务表添加last_sync_time或version字段，每次从API拉取数据时，只对比那些更新时间晚于上次同步时间的记录，或者版本号不一致的记录。
• 如果API支持增量拉取（比如按时间戳返回更新数据），直接用API的增量接口，避免拉取全量数据。
• 这样每次更新的行数大幅减少，行级锁的持有时间变短，不会阻塞整个表的访问。

2. 拆分大事务，缩小锁持有时间 #

把原来的单一大事务拆分成多个小事务：

• 比如每更新100条记录就提交一次事务，而不是等所有更新完成再提交。
• 确保// UPDATE ALL DATA代码块里，每完成一批数据的更新就立即提交事务，不要让事务长时间处于打开状态。
• 特别注意：不要在事务中包含API调用等耗时操作，只把数据库操作放在事务里，进一步缩短锁的持有时间。

3. 避免表锁，强制使用行级锁 #

如果使用的是InnoDB引擎，全量更新语句（比如UPDATE user_permission SET ...不带WHERE条件）会触发表锁，改成带条件的行级更新：

• 即使要更新所有数据，也可以分批次按userid范围更新，比如UPDATE user_permission SET ... WHERE userid BETWEEN ? AND ?，每次处理一部分数据，避免锁全表。
• 检查数据库的事务隔离级别，尽量使用READ COMMITTED，这个级别下InnoDB会更快释放锁，减少锁竞争。

4. 用缓存+读写分离隔离实时流量 #

针对用户连接的实时场景，做流量隔离：

• 缓存层：把用户权限数据缓存到Redis中，用户连接时优先查缓存。同步数据时，更新数据库后同时更新缓存（或设置缓存过期），这样绝大多数实时请求不需要访问数据库。
• 读写分离：把实时查询请求引导到数据库的只读副本，主库只负责处理同步更新操作，彻底避免读写锁冲突。

5. 优化任务调度逻辑 #

调整任务触发时机，避免更新操作占用所有时间：

• 不要用固定5秒间隔调度，改成更新完成后再调度下一次执行。比如每次更新结束后，计算距离下一分钟开始的时间，再调度下一次任务，确保两次更新之间有足够的时间窗口让实时业务操作执行。
• 如果是单实例部署，确保任务串行执行即可；多实例部署的话，可以用分布式锁避免重复触发更新。

内容的提问来源于stack exchange，提问作者Lara