华为云 RDS for PostgreSQL 分页导出,为什么总数和明细对不上
导出订单时,程序先查到 1000 笔,生成的文件里却有 1003 笔。如果这期间有人下单,多出来的三笔就可能来自后面的分页查询。在华为云 RDS for PostgreSQL 上,计数和明细查询能看到哪些订单,取决于事务隔离级别,以及这些查询是否使用同一条连接、同一个事务。
这个过程可以用两个数据库连接演示。下面按 PostgreSQL 16 的事务规则编写 SQL,注释标出预期结果。操作时选择测试数据库,打开连接 A 和 B。
用两个连接看清数据何时变化
先在连接 B 中建一张两个连接都能访问的普通表,放入两笔订单。两个连接都开启自动提交,执行 BEGIN 后再进入显式事务。
CREATE TABLE export_snapshot_demo (
id bigint PRIMARY KEY,
amount numeric(12, 2) NOT NULL
);
INSERT INTO export_snapshot_demo VALUES (1, 99.00), (2, 199.00);
在连接 A 中指定 READ COMMITTED,先统计总数,再读第一页,每页两条。
BEGIN ISOLATION LEVEL READ COMMITTED READ ONLY;
SELECT count(*) FROM export_snapshot_demo;
-- 预期:2
SELECT id, amount FROM export_snapshot_demo ORDER BY id LIMIT 2;
-- 预期:id 为 1、2
保持 A 的事务打开,在连接 B 插入第三条记录。确认这条 INSERT 已经提交,再回到 A。
INSERT INTO export_snapshot_demo VALUES (3, 299.00);
连接 A 继续读取下一页:
SELECT id, amount FROM export_snapshot_demo
WHERE id > 2 ORDER BY id LIMIT 2;
-- 预期:读到 id 为 3 的记录
COMMIT;
计数得到 2 条,两页合起来却有 3 条。READ COMMITTED 下,每条查询会取得新的快照:计数时第三笔订单尚未提交,查第二页时已经提交,于是被读了出来。即使查询都放在同一个事务里,这个结果也不会变。按主键翻页同样会遇到这个问题,因为 id > 2 的条件包含了新插入的第三笔订单。
接着在连接 A 中开启可重复读事务,并立即执行计数查询。此时表里已有三条记录。
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT count(*) FROM export_snapshot_demo;
-- 预期:3;这条查询确定本事务使用的快照
SELECT id, amount FROM export_snapshot_demo ORDER BY id LIMIT 2;
在连接 B 插入并提交第四条记录:
INSERT INTO export_snapshot_demo VALUES (4, 399.00);
回到连接 A 继续翻页,会读到第三条记录,看不到刚提交的第四条。结束事务后再查,才能看到全部四条。
SELECT id, amount FROM export_snapshot_demo
WHERE id > 2 ORDER BY id LIMIT 2;
-- 预期:只有 id 为 3 的记录
COMMIT;
SELECT count(*) FROM export_snapshot_demo;
-- 预期:4
这份快照在第一条非事务控制语句执行时确定。比如 10:00 执行 BEGIN,10:01 有新订单提交,10:02 才执行计数查询,这笔订单会被统计进去。因此,导出的业务截止时间也要写进查询条件;用户点击按钮的时间与数据库取得快照的时间之间,可能还隔着排队和连接等待。
把事务放到导出程序里
把这段流程接入应用时,可以让一次导出占用一条连接:从连接池借出连接,开启只读的可重复读事务,完成计数和所有分页查询,提交后再归还。异常路径也要回滚并归还连接。如果现有代码每查一页就借一次连接,需要把连接的借还移到整个读取过程的外层;每页单独提交,也会让后续查询重新取得快照。
有些导出只执行一条普通 SELECT,由驱动分批接收结果。这种情况下,READ COMMITTED 的语句快照会贯穿这条查询,读取过程中提交的新订单不会插进结果。看代码时,可以直接检查数据库实际执行了几条 SELECT:一条查询的分批传输,与循环执行多条分页查询,对快照的要求不同。
同一份快照里,分页顺序仍要固定。示例按唯一的 id 排序,下一页从上页最后一个 id 之后开始。如果按创建时间排序,同一时刻可能有多笔订单,可以再加 id 排出先后,并在下一页条件中同时使用这两个字段。
这条连接也不宜一直占着。导出事务持有快照期间,数据库需要保留它仍可能访问的旧行版本,VACUUM 无法回收这些版本。读完数据就提交,文件压缩、上传等后续工作放到事务结束后执行,可以缩短旧快照的保留时间。
回到最初的 1000 笔和 1003 笔,可以先检查计数与明细的 WHERE 条件是否相同,再看它们用了几条连接、在哪些位置提交事务。把这些位置找出来,通常就能沿着新增订单的提交时间,解释它为什么进入了导出文件。
- 点赞
- 收藏
- 关注作者
评论(0)