定时SQL查询遗漏提交中记录，寻求替代延迟查询的优化方案

问题背景 #

每隔5分钟执行一次查询，提取调度任务执行前5分钟内创建/更新的记录，偶尔会遗漏处于提交过程中但未完全完成的记录。目前临时方案是将查询延迟几分钟，希望找到更优解决办法。

数据示例 #

查询示例 #

select *
from exampleTable
where recordDate between '2022-09-28 16:15:00' and '2022-09-28 16:20:00';

问题现象 #

2022-09-28 16:20:00调度执行的查询未返回任何记录；2022-09-28 16:25:00的下一次执行返回了订单#2的所有记录，但订单#1被完全遗漏。

可行优化方案 #

• 调整事务隔离级别：如果使用MySQL、PostgreSQL等数据库，可将查询的事务隔离级别设置为READ COMMITTED及以上（如REPEATABLE READ），确保查询仅读取已提交的事务数据。注意，更高隔离级别可能带来性能损耗，需结合业务场景权衡。
• 引入动态延迟窗口：不固定延迟时长，而是将查询时间范围的结束点设为「调度执行时间减去30秒-1分钟的缓冲窗口」。例如16:20执行的查询，时间范围改为16:15:00到16:19:30，给未完成事务留出提交时间。同时后续查询需覆盖前一周期的缓冲窗口+当前周期，通过唯一主键去重避免重复处理。
• 跟踪事务提交时间：在表中新增commitTimestamp字段，记录事务实际提交时间（而非事务开始时的recordDate），查询时基于该字段过滤，从根源上规避提交中事务的问题。此方案需修改表结构和写入逻辑。
• 数据库层面事务等待（部分数据库支持）：PostgreSQL可通过pg_stat_activity视图监控涉及目标表的活跃事务，等待其完成后再执行查询；MySQL可通过SHOW ENGINE INNODB STATUS查看事务状态，但这类方案需较高权限，且实现复杂，不建议作为常规方案。
• 幂等性+补漏机制：在数据处理环节加入幂等逻辑（如记录已处理的主键ID），同时定期执行补漏任务（如每日凌晨），扫描过去一段时间内可能遗漏的记录，作为兜底保障数据完整性。

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