多端同步为什么会漏记录：用 SQLite 验证游标和删除事件

为什么第二页会漏掉一条待办变更

鸿蒙开发实践中心的「云同步待办清单：鸿蒙端云一体实战」提到了从全量轮询改成增量拉取。这里把这个思路缩小成一个 SQLite 内存实验：两个设备同步待办变更，每页只取一条。本文不改动案例源码，也未部署云资源。

如果游标只记 changed_at，第一页取走同一秒的第一条变更后，下一页用 changed_at > ? 会跳过同一秒的第二条。把唯一的变更 ID 加入排序与游标，能解决已有数据的并列问题：WHERE (changed_at, change_id) > (?, ?) ORDER BY changed_at, change_id。两个字段都不能为空，且客户端保存的是实际处理完的最后一条，不是客户端当前时间。

复合游标也挡不住迟到写入

复合游标扫过 (100, "change-b") 后，如果补写了 (100, "change-0")，它仍排在游标前。按业务时间分页不等于按可见的变更顺序同步。下面用同一张追加事件表验证两个问题，再对比按服务端顺序 seq 拉取。deleted=1 是删除事件，不是从结果集里过滤掉一行。

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as connection:
    connection.execute("""CREATE TABLE changes (
        seq INTEGER PRIMARY KEY, changed_at INTEGER NOT NULL,
        change_id TEXT NOT NULL UNIQUE, task_id TEXT NOT NULL,
        deleted INTEGER NOT NULL CHECK (deleted IN (0, 1))
    )""")
    append_sql = """INSERT INTO changes
        (changed_at, change_id, task_id, deleted) VALUES (?, ?, ?, ?)"""
    with connection:
        connection.executemany(append_sql, [
            (100, "change-a", "task-a", 0),
            (100, "change-b", "task-b", 0),
        ])

    first_page = connection.execute("""SELECT changed_at, change_id
        FROM changes ORDER BY changed_at, change_id LIMIT 1""").fetchone()
    assert first_page == (100, "change-a")
    naive = connection.execute("SELECT change_id FROM changes WHERE changed_at > ?",
                               (first_page[0],)).fetchall()
    assert naive == []
    composite_sql = """SELECT changed_at, change_id FROM changes
        WHERE (changed_at, change_id) > (?, ?) ORDER BY changed_at, change_id LIMIT 1"""
    second_page = connection.execute(composite_sql, first_page).fetchone()
    assert second_page == (100, "change-b")

    with connection:
        connection.executemany(append_sql, [
            (100, "change-0", "task-late", 0),
            (101, "change-delete", "task-b", 1),
        ])
    assert connection.execute(composite_sql, second_page).fetchone() == (101, "change-delete")

    pull_sql = """SELECT seq, task_id, deleted FROM changes
        WHERE seq > ? ORDER BY seq LIMIT 1"""
    local_tasks = {"task-a", "task-b"}
    cursor = 2
    late_event = connection.execute(pull_sql, (cursor,)).fetchone()
    assert late_event == (3, "task-late", 0)
    cursor, task_id, deleted = late_event
    local_tasks.add(task_id)
    delete_event = connection.execute(pull_sql, (cursor,)).fetchone()
    assert delete_event == (4, "task-b", 1)
    cursor, task_id, deleted = delete_event
    local_tasks.discard(task_id)
    assert local_tasks == {"task-a", "task-late"}
    assert connection.execute(pull_sql, (cursor,)).fetchone() is None

print("8 项断言通过")

落地时检查什么

• 服务端排序：示例是 SQLite 单写入者、追加事件且不删除历史的模型。迁移到并发事务数据库时，不能把任何自增 ID 都当成提交顺序：如果小号事务晚于大号提交，客户端照样可能越过它。使用能保证发布顺序的变更日志或数据库变更流位点。
• 客户端提交：把本页变更和新游标放在同一个本地事务。中断后按旧游标重拉，增改按版本或事件去重，删除按业务键幂等应用。删除事件保留时间必须覆盖增量同步窗口；过期游标改走全量重建。
• 数据范围：服务端根据已认证身份限定可读的事件，不接受客户端指定任意用户。演示只覆盖单用户分页和删除，不涉及并发写冲突、鉴权或全量快照初始化。

实验使用 Python 标准库的 sqlite3，全部为合成数据。参考：SQLite 官方文档《Row Values》的滚动窗口查询、《Isolation In SQLite》的写入隔离，以及上述鸿蒙公开实践案例。AI 辅助起草和整理，示例在本地独立运行验证。