业务任务每分钟写表时查询变慢，如何避免锁阻塞？

嘿，咱们来解决你遇到的这个问题——每分钟运行的写任务执行时，查询会因为锁竞争变慢。我在生产环境里处理过类似的情况，给你分享几个经过验证的实用方案：

1. 读写分离架构 #

• 把数据库拆成主库（负责写操作）和从库（负责读操作）。所有写请求都路由到主库，查询请求则转到从库。这样主库上的写事务完全不会阻塞读操作。需要注意的是主从之间可能存在几秒的数据延迟，如果你的业务能接受这个延迟，这是最直接的解决办法。
• 可以用MyCat、ShardingSphere这类中间件自动处理路由，也可以在应用层直接实现读写分离的逻辑判断。

2. 优化事务与锁策略 #

• 缩小事务范围：别把无关操作（比如日志记录、远程API调用）塞进写事务里。写完表就尽快提交事务，减少锁的持有时间。比如原来的事务包含写表+发送通知，把发送通知的步骤移到事务外面。
• 用行级锁代替表级锁：确保你的写操作是通过主键或唯一索引定位行。比如别用UPDATE table SET ... WHERE status = 1（如果status没有索引，会触发全表扫描并加表锁），给status加索引，或者结合主键来缩小更新范围。
• 避免长事务：如果你的分钟级任务是批量写数据，把数据分成小批次，每处理一批就提交一次。比如别一次性写1000条再提交，改成每写100条就commit，这样锁的持有时间短了，竞争也会减少。

3. 调整数据库隔离级别 #

• 大多数数据库默认是可重复读（REPEATABLE READ）隔离级别，这个级别下查询可能会等待锁，或者使用的快照会有延迟。如果你的业务能接受不可重复读（也就是连续两次查询可能得到不同结果），可以把隔离级别降到读已提交（READ COMMITTED）。这个级别下查询会用最新的快照，不会被写锁阻塞（前提是数据库支持MVCC，比如InnoDB、PostgreSQL）。
• 调整前一定要充分测试——有些业务逻辑可能依赖可重复读，得确认这个 trade-off 是业务能接受的。

4. 使用快照读/读视图 #

• 对于支持MVCC的数据库（比如MySQL InnoDB、PostgreSQL），可以显式用快照读来避免锁竞争：
  • 在MySQL里，用START TRANSACTION WITH CONSISTENT SNAPSHOT;开启一个只读事务，再执行查询。查询会基于事务启动时的快照，完全不会被正在进行的写操作阻塞。
  • 在PostgreSQL里，先执行SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;再查询，同样是基于一致快照读取，不用等锁。

5. 分表/分区策略 #

• 如果单表数据量很大，哪怕是行级锁也会有竞争，而且查询可能因为扫描大量数据变慢。可以按时间（你的任务每分钟运行，时间维度很合适）或业务维度给表分表/分区。比如按天分区，写任务只操作当天的分区，查询历史数据时访问旧分区，两者完全不会互相干扰。
• 按业务ID分表也可行，把大表拆成多个小表，写和查询分别对应不同的分片，锁竞争自然就没了。

6. 优化查询语句 #

• 确保所有查询都走索引，避免全表扫描。全表扫描可能触发间隙锁（比如InnoDB），或者占用大量资源导致写和查询都变慢。比如查询用create_time作为条件，就给create_time加索引。另外别用SELECT *，只查需要的字段，减少数据传输和资源消耗。

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