跳至主要內容

分页、N+1 与并发锁：控制列表读取和竞争更新

高谷深陵大约 16 分钟约 4848 字
分页、N+1 与并发锁
分页、N+1 与并发锁高清原图

一个商品列表接口可能只返回 20 条商品，却查询了 21 次数据库；翻到较后面的页面，数据库还需要跳过大量前置记录。同一条商品被两个事务修改时，又会出现覆盖更新或锁等待。

这三类问题都能从实际 SQL 形状中辨认：分页决定读取哪一段结果，关联加载决定为了这段结果再发多少查询，并发控制决定多个事务怎样竞争同一行。

分页先确定稳定次序

ORDER BY 必须能够区分每一行

LIMIT 20 只限制返回数量，没有指定“前 20 条”按什么排列。只按非唯一字段排序时，相同值之间的次序也可能改变。通常需要再加唯一主键：

SELECT id, sort_seq
FROM catalog_item
ORDER BY sort_seq ASC, id ASC
LIMIT 2 OFFSET 0;

本例的前五行排序键为：

idsort_seq排序位置
110第 1 行
220第 2 行
320第 3 行，使用 id 区分相同 sort_seq
430第 4 行
540第 5 行

这样一次查询具有确定次序。多次请求之间仍可能发生插入、删除或排序字段更新，要再选择跨页读取方式。PostgreSQL LIMIT/OFFSET

分页参数还需要限制页大小和最大访问范围。Java 计算偏移时先转成 long，避免 int 乘法溢出：

long offset = Math.multiplyExact((long) pageNumber, pageSize);

这里 pageNumber 从 0 开始；如果接口从 1 开始，应在校验正数后先减 1。再检查框架 API 接受的类型和上限，不能把 long 截断成 int 继续执行。

OFFSET 跳过前面的行

第二页使用 OFFSET 2 LIMIT 2，跳过当前结果的前两行。数据库仍要计算需要跳过的那部分；深分页常见的成本来自“读取后丢弃”，即使执行计划已经使用索引。

OFFSET 很适合按页码跳转、数据变化相对可接受的管理列表。调用方如果只需要连续向后滚动，可以考虑使用上次末尾排序键继续读取。

keyset 以末尾键继续

第一页最后一行为 (sort_seq=20, id=2)，下一页写成：

SELECT id, sort_seq
FROM catalog_item
WHERE (sort_seq, id) > (20, 2)
ORDER BY sort_seq ASC, id ASC
LIMIT 2;

它表示“从这个键之后继续”。对应的普通布尔形式是：

WHERE sort_seq > 20
   OR (sort_seq = 20 AND id > 2)

实际应用通过 PreparedStatement 绑定两个值，不拼接外部游标文本。查询如果还有租户、状态或权限条件，第一页和后续页都必须保留。

复合键比较在各数据库中有不同语法支持。降序读取要反转比较方向；混合升降序和 NULL 值需要逐项设计谓词，不能机械地把整个元组换成小于号。PostgreSQL 行比较

游标应携带排序键，并与过滤条件、排序方式和必要的版本信息绑定。对外可以编码并签名，但签名只保护游标内容未被篡改，查询仍须执行当前用户的权限检查。

在真实数据上比较翻页和执行计划

建立环境并完成第一页

下载完整实验 ZIP。PageLab.java 同时使用 JDBC、真实 Hibernate Statistics 和数据库等待视图；CatalogItem.java 与 CatalogLine.java 提供一对多映射。

工程采用 Boot 4.1.1 依赖清单中的 Hibernate 7.4.5.Final、pgJDBC 42.7.13，数据库 PostgreSQL 18.6。Maven 3.9.12 / JDK 25 为默认运行环境，编译目标 Java 17。Boot 受管依赖

在有 Docker Engine、Compose、unzip、openssl 的 Linux Bash 中执行：

test "$(id -u)" -ne 0 || exit 1
docker version
docker compose version
unzip pagination-nplus-locking-lab.zip
cd pagination-nplus-locking

export LAB_DIR="$PWD"
export LAB_CACHE="$LAB_DIR/.m2-cache"
mkdir -p "$LAB_CACHE"
export LAB_DB_PASSWORD="$(openssl rand -hex 24)"
export LAB_APP_PASSWORD="$(openssl rand -hex 24)"
export LAB_DB_URL='jdbc:postgresql://db:5432/pagination_lab'
docker compose -p da10-pagination up -d --wait

数据库进程身份为 999:999；Java 使用宿主普通用户 UID/GID 和数据库角色 lab_app。数据库管理员 lab_owner 负责初始化，应用只具备实验表的 DML 权限。没有宿主端口映射，数据存放于 tmpfs。

init.sh 建立 20,006 条商品，其中前五条各有两条明细；其余记录用于观察深分页扫描。索引为 (sort_seq,id)。另有三条 ready 任务用于 SKIP LOCKED。实验会临时插入、更新并恢复数据，不可对业务库运行。

定义调用函数：

run_lab() {
  docker run --rm --user "$(id -u):$(id -g)" --read-only \
    --network da10-pagination_default --tmpfs /tmp:rw,exec,mode=1777 \
    -v "$LAB_DIR:/src:ro" -v "$LAB_CACHE:/cache" \
    -e LAB_DB_URL -e LAB_APP_PASSWORD -e MAVEN_CONFIG=/tmp/maven \
    maven:3.9.12-eclipse-temurin-25 bash /src/run.sh "$1"
}
run_lab first

源码只读挂载，构建在容器 /tmp 中完成并清理。首次输出：

firstPage=[1, 2] offsetSecond=[3, 4] keysetSecond=[3, 4]

如果数据库没有启动成功，先查看：

docker compose -p da10-pagination logs --tail=80 db

认证失败时核对当前环境变量与数据库首次初始化密码；新 shell 不会自动继承上一个 shell 中 export 的随机值。

两次请求之间插入一行

运行：

run_lab drift
insertBeforePageOffset=[2, 3] keyset=[3, 4] offsetRepeatedId=true
sortKeyChangedKeyset=[3, 1] previouslySeenIdRepeated=true

第一步先读出 [1,2]。另一个已提交写入把 id=99、sort_seq=5 插在所有记录之前：

插入前：1   2 | 3   4   5
插入后：99  1 | 2   3   4   5
              ↑ OFFSET 2 从这里重新开始

OFFSET 第二页出现已读过的 id=2。keyset 仍从 (20,2) 之后查到 [3,4]，不受前方这次插入影响。

随后实验把已经读过的 id=1 的 sort_seq 改为 25。它移动到游标之后，keyset 下一页得到 [3,1]，id=1 也被重复读到。这说明排序字段的可变性必须纳入接口约定。

如果业务需要完整、固定的数据快照，例如财务导出，应设计固定快照、导出任务或合适的事务读取方式。把数据库事务一直保持到用户翻完几十页，会长期持有快照与连接，通常不适合交互页面。Read Committed 与 Repeatable Read 的可见性规则见 PostgreSQL 事务隔离。

用 EXPLAIN 看实际扫描范围

运行：

run_lab plan

它执行两份真实 EXPLAIN (ANALYZE, BUFFERS)，并检查两种查询返回相同页面：

SELECT id FROM catalog_item
ORDER BY sort_seq, id LIMIT 2 OFFSET 10000;

SELECT id FROM catalog_item
WHERE (sort_seq, id) > (10994, 10994)
ORDER BY sort_seq, id LIMIT 2;

这套数据的一次运行中，两者都采用 Index Only Scan。OFFSET 下层扫描交给 Limit 的记录为 10,002 条，keyset 为 2 条。具体耗时和 buffers 会随缓存、硬件、统计信息及数据库维护状态变化。

阅读计划时把顶层返回行数、下层实际行数、loops、索引条件、排序节点和 buffers 连起来看。估算 rows 与 actual rows 差距很大时，还要检查统计信息与查询条件分布。PostgreSQL EXPLAIN

ANALYZE 会真实执行被解释的语句。这里只运行 SELECT；不要在生产库随手对 UPDATE/DELETE 使用 EXPLAIN ANALYZE。

Page、Slice 和 RowBounds 表达不同需求

Spring Data 的 Page 一般需要总数，可能触发 count 查询；Slice 只关心是否还有下一段，常用多取一条记录判断。只需要“下一页”时，没必要让每次请求都计算昂贵总数。Spring Data 分页与限制

总数与内容查询如果在不同快照下执行，页面显示的总数还可能与返回数据暂时不一致。查询有联表、分组或去重时，count SQL 的含义和成本也要单独核对。

MyBatis RowBounds 表达逻辑偏移与数量，普通使用不会自动把 SQL 改成所有数据库通用的 LIMIT/OFFSET。是否发生物理分页，取决于实际 Mapper SQL 和分页插件。观察最终 SQL，不凭方法签名判断数据库只读取了多少行。MyBatis Java API

一页关联数据需要多少次查询

N+1 来自逐个访问关联

实验加载前三个 CatalogItem，随后逐个访问懒加载明细：

List<CatalogItem> items = em.createQuery(
    "select i from CatalogItem i order by i.sortSeq,i.id",
    CatalogItem.class).setMaxResults(3).getResultList();

for (CatalogItem item : items) {
    item.getLines().stream().map(CatalogLine::getSku).toList();
}

一条查询读取父记录，三个集合各增加一条查询，共四次 prepare。数量随父对象数增长，网络往返和数据库执行次数也会增加。实体缓存、批量抓取或已初始化关联可能减少实际次数，所以应测量当前配置而非只按循环次数推断。

运行：

run_lab nplus

首先得到：

nPlusOneStatements=4 twoStepStatements=2 graphEqual=true orderedIds=[1, 2, 3]

计数来自 Hibernate Statistics；两组使用不同 EntityManager，并关闭二级和查询缓存。实验比较完整的 id→明细 SKU 列表，确认减少查询后没有丢关联数据。

先分页主键，再抓取本页关联

通用的两步方法是先取得稳定排序的本页 ID，再按这些 ID 查询完整对象图：

List<Long> pageIds = em.createQuery(
    "select i.id from CatalogItem i order by i.sortSeq,i.id",
    Long.class).setMaxResults(3).getResultList();

List<CatalogItem> items = pageIds.isEmpty() ? List.of() : em.createQuery(
    "select i from CatalogItem i left join fetch i.lines where i.id in :ids",
    CatalogItem.class).setParameter("ids", pageIds).getResultList();

第二次 IN 查询不会天然保持第一步 ID 顺序。工程先按 ID 建立查找表，再遍历 pageIds 组装返回列表。上面的空页分支避免生成无意义或数据库不支持的空 IN；固定数据实验读取的第一页包含三个 ID。

两次查询之间若数据会变，需要决定允许返回稍有变化的结果，还是在合适隔离级别下使用同一事务快照。只把两个调用写在同一个 Java 方法里，并未固定它们的数据版本。

批量 IN 的长度、父记录一页的最大规模、每个父对象关联数量，都需要独立限制。一个父对象如果有几十万条明细，父对象分页无法控制总响应体积。

Hibernate 7.4 的集合分页变化

同一个 nplus 模式接着执行集合 fetch join 与 setMaxResults。Hibernate 7.4.5 在 PostgreSQL 18.6 上生成了带子查询限制的 SQL，结构如下：

SELECT i.id, l.item_id, l.id, l.sku,
       i.quantity, i.sort_seq, i.version
FROM (
    SELECT id, version, quantity, sort_seq
    FROM catalog_item
    ORDER BY sort_seq, id
    OFFSET ? ROWS FETCH FIRST ? ROWS ONLY
) i
LEFT JOIN catalog_line l ON i.id = l.item_id
ORDER BY i.sort_seq, i.id, l.id;

分页先作用于父记录，再连接明细。实验通过 SqlTrace.java 观察真实 StatementInspector SQL，并断言一个语句、三个父对象、完整六条明细：

hibernate74DatabaseFetchPage=true statements=1 parents=3 childRows=6 completeGraph=true

Hibernate 7.4 对支持子查询 limit/offset 的数据库修复了这类集合分页问题。旧版本常见的“抓取全部行再在内存中分页”经验，需要按版本和方言重新判断。Hibernate limits and offsets

仍建议保留保护设置：

hibernate.query.fail_on_pagination_over_collection_fetch=true

该设置拒绝的是需要在内存中限制结果的路径，不是拒绝所有集合 fetch join 分页。在本实验配置上成功返回，正是因为实际分页已进入数据库。

一次 fetch join 也可能返回很多重复父列，多个集合更可能产生笛卡尔积。选择单条子查询抓取、两步查询、批量加载或 DTO 投影时，同时考虑行数、列宽、查询数量和维护复杂度，不以“一条 SQL”作为唯一目标。Hibernate 抓取策略

竞争更新怎样决定谁能成功

用版本条件发现覆盖风险

两个事务先读到 version=7，然后各自修改库存。如果更新条件只有 id，后提交者可能覆盖前一事务的结果。版本谓词把读取时的状态带回更新条件：

UPDATE catalog_item
SET quantity = quantity - 1,
    version = version + 1
WHERE id = 1
  AND version = 7
  AND quantity > 0;

运行：

run_lab optimistic
initialVersion=7 firstUpdateRows=1 staleUpdateRows=0

A、B 分别读取 version=7。A 更新一行并提交，B 使用旧版本更新零行。应用根据影响行数知道原来的前提已经改变，而不是把零行继续当作扣减成功。

更新零行也可能来自库存不足、记录不存在或权限条件不满足，要按业务需要重新读取和分类。纯数量扣减如果不依赖其他已读取字段，可以使用 quantity>0 的单条原子条件更新；版本号用于保护更广的“按旧状态决定新状态”。

JPA 的 @Version 把类似检查组织在实体更新中，详见 JPA 与 Hibernate。原生 SQL 和批量更新则要自己明确版本语义。

需要先读后决策时使用行锁

SELECT ... FOR UPDATE 锁住选中的行，使竞争修改等待当前事务结束。运行：

run_lab nowait
nowaitRejected=true sqlState=55P03 lockAfterRelease=true

A 开启事务并锁住 id=1；B 执行相同查询加 NOWAIT，立即以 55P03 失败。B 回滚失败事务，A 提交释放锁，再次由 B 查询就能获得锁。

普通 SELECT 在 PostgreSQL 的 MVCC 下仍可读取合适快照；“行被锁住”不表示所有读取都会等待。具体冲突取决于行锁模式和操作类型。PostgreSQL 显式锁

事务里不要持锁等待用户输入、长时间远程调用或不受控重试。锁的持续时间由事务完成控制，关闭一个 ResultSet 并不会提交事务。

SKIP LOCKED 用于可以跳过的任务

运行：

run_lab skip-locked
consumerA=1 consumerB=2 differentLockedTasks=true

两个未结束的事务都执行：

SELECT id FROM task
WHERE state = 'ready'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;

A 锁住 id=1，B 跳过它取得 id=2。实验最后回滚两个事务以恢复数据。

真实任务消费者应在同一事务里把选中任务标记为已领取，再提交，并设计租约、失败恢复和重复执行处理。仅锁住一行后立即提交、却不更新任务状态，其他消费者随后仍能领取同一任务。

SKIP LOCKED 有意跳过暂时锁住的行，适合工作队列，不适合要求完整数据的报表或余额核对。NOWAIT 与 SKIP LOCKED 的语法和锁定范围见 PostgreSQL SELECT。

锁等待、死锁和分页异常的判断

用数据库视图确认谁在等待

运行：

run_lab lock-wait
databaseLockWaitObserved=true blockerPidLinked=true releaseCompletedQuery=true

程序在两个独立连接上建立真实锁竞争，第三个连接通过 pg_stat_activity 看到等待类型为 Lock，并通过 pg_blocking_pids 找到阻塞关系。确认后释放 A，B 的查询继续完成。

查看当前活动连接可以使用：

docker compose -p da10-pagination exec -T db \
  psql -X -U lab_owner -d pagination_lab -v ON_ERROR_STOP=1 \
  -c "SELECT pid, state, wait_event_type, wait_event,
             pg_blocking_pids(pid) AS blockers, query
      FROM pg_stat_activity
      WHERE datname = current_database()
        AND usename = 'lab_app';"

自动模式只短暂保持等待，结束后再手工查询可能看不到阻塞，这属于正常时序。生产环境需要相应监控权限，也应避免把完整 SQL 参数等敏感内容直接外发。PostgreSQL 活动与等待统计

应用连接池等待与数据库行锁等待发生在不同层。如果连 PostgreSQL 后端会话都没建立，先查连接获取；已有后端且 wait_event_type=Lock，再分析持锁事务。

超时和重试需要保留原始错误

PostgreSQL 可以在事务中设置等待预算：

BEGIN;
SET LOCAL lock_timeout = '500ms';
SET LOCAL statement_timeout = '3s';
SELECT id FROM catalog_item WHERE id=1 FOR UPDATE;
COMMIT;

这里的数值是演示预算，需要根据真实接口总时限选择。lock_timeout 限制获得锁的等待，statement_timeout 限制语句总执行时间；事务里使用 SET LOCAL，结束后自动恢复。PostgreSQL 客户端连接设置

发生错误时，客户端应先 ROLLBACK，不能把失败后的 COMMIT 输出当成功。原始 SQLState 有助于区分后续动作：

SQLState常见含义处理起点
55P03无法取得锁，例如 NOWAIT 或锁等待超时回滚，判断是否允许稍后重试
40P01数据库检测到死锁回滚整个事务，检查多行锁获取顺序
40001序列化失败在新的事务中重试完整业务计算
57014查询取消或 statement timeout 等查明取消来源与剩余总预算
25P02当前事务已因更早错误失败找到最初错误，先回滚恢复连接

完整代码对应含义见 PostgreSQL 错误代码。重试需要次数上限和退避，还要排除会被重复执行的外部副作用。

锁顺序统一能减少死锁，但不能消除所有业务竞争。索引影响扫描与锁定涉及的行；PostgreSQL 的行锁机制也不能直接用 MySQL InnoDB gap lock 的规则解释。

对照输出与结果规模

症状优先检查下一步
翻页重复或漏行唯一排序键、跨请求数据变化选择 OFFSET/keyset/固定快照的适用方式
深页慢而第一页快Limit 下层实际扫描行数评估 keyset 与复合索引
列表越长 SQL 越多懒加载触发点和实际 prepare 次数按本页 ID 批量抓取，或配置明确抓取计划
join 后页大小异常分页在父子连接之前还是之后查看生成 SQL 与框架版本、方言
更新返回零行版本、数量和业务过滤条件重新读取当前状态，分类冲突
请求停在数据库调用wait_event 与 blockers缩短持锁事务并定位阻塞者

完整运行：

run_lab all

负例均通过捕获预期 SQLState、恢复事务和查询实际结果确认，任意断言失败会非零退出。计划模式打印本机的真实耗时，不应逐字符比对动态数字。

结束实验

docker compose -p da10-pagination down
unset LAB_DB_PASSWORD LAB_APP_PASSWORD LAB_DB_URL LAB_DIR LAB_CACHE
unset -f run_lab

移除本实验容器和网络后，tmpfs 数据被丢弃；源码和 Maven 缓存保留。

权威资料与规范地址

分页查询先查数据库语法与执行计划；ORM 生成方式要结合具体版本，锁错误按数据库 SQLState 和活动视图判断。

资料完整地址
PostgreSQL LIMIT/OFFSEThttps://www.postgresql.org/docs/18/queries-limit.html
PostgreSQL 行比较https://www.postgresql.org/docs/18/functions-comparisons.html
Boot 受管依赖https://docs.spring.io/spring-boot/appendix/dependency-versions/coordinates.html
PostgreSQL 事务隔离https://www.postgresql.org/docs/18/transaction-iso.html
PostgreSQL EXPLAINhttps://www.postgresql.org/docs/18/using-explain.html
Spring Data 分页与限制https://docs.spring.io/spring-data/commons/reference/repositories/query-methods-details.html
MyBatis Java APIhttps://mybatis.org/mybatis-3/java-api.html
Hibernate limits and offsetshttps://docs.hibernate.org/orm/7.4/userguide/html_single/#hql-limit-offset
Hibernate 抓取策略https://docs.hibernate.org/orm/7.4/userguide/html_single/#fetching
PostgreSQL 显式锁https://www.postgresql.org/docs/18/explicit-locking.html
PostgreSQL SELECThttps://www.postgresql.org/docs/18/sql-select.html
PostgreSQL 活动与等待统计https://www.postgresql.org/docs/18/monitoring-stats.html
PostgreSQL 客户端连接设置https://www.postgresql.org/docs/18/runtime-config-client.html
PostgreSQL 错误代码https://www.postgresql.org/docs/18/errcodes-appendix.html
跳到正文