【GaussDB】讨论下for update游标加锁的设计

背景

在 Oracle 中有一种常见用法：通过存储过程有条件地更新一张或多张表的数据。由于存储过程可能被并发调用，需要加锁防止并发执行。做法是定义一个游标，在查询语句末尾加上 FOR UPDATE，然后 OPEN 这个游标即实现加锁。至于后续的数据更新操作，在存储过程里还是外面都无所谓——反正数据已经锁住了，更新完提交即可：

DECLARE
    v_id NUMBER := 123; 
    CURSOR c IS SELECT * FROM t1 WHERE id = v_id FOR UPDATE;
    r t1%ROWTYPE;
BEGIN
    OPEN c;
    FETCH c INTO r;
    IF c%FOUND THEN
        UPDATE t1 SET status = 'DONE' WHERE CURRENT OF c;
    END IF;
    CLOSE c;
    COMMIT;
END;
/

在没接触其他数据库之前，Oracle 这种用法看上去似乎天经地义。但之前我测试 openGauss 和 GaussDB 时，发现它们在 OPEN 一个 FOR UPDATE 游标时并不会加锁，必须 FETCH 才能加锁，行为与 Oracle 不一致。这会导致迁移过来的代码锁不住数据，并发时产生问题。

当时我给openGauss社区提了个 issue：https://gitee.com/opengauss/openGauss-server/issues/I9HDIA

社区很快改了（第一版有问题，后面的 PR 修复了）。但 GaussDB 则一直没改，所以实际项目迁移到GaussDB时，这一块加锁只能由客户开发人员自己改用其他方式实现。

加锁的本质

任何数据库对特定行加锁都要先找到数据再加锁，因此必然会发生扫描行为（只是不需要获取数据）；而不加 FOR UPDATE 时，OPEN 游标只需记录一个快照即可。

当时我并没有仔细去思考这个问题，只是简单认为 Oracle 和 GaussDB 加锁时机不一致。但后来客户给了一段代码问怎么改，深入思考后，发现了一个值得讨论的细节。

用例

以下为精简逻辑的用例：

-- 建表与初始数据
CREATE TABLE t1(id NUMBER, context VARCHAR2(100));
INSERT INTO t1 VALUES (1, 'a');
COMMIT;

-- 加锁存储过程
CREATE PROCEDURE func_lock(i_id NUMBER) IS
    CURSOR c IS SELECT * FROM t1 WHERE id = i_id FOR UPDATE;
BEGIN
    OPEN c;
    CLOSE c;
END;
/

-- 下面这段可能并发执行
DECLARE
    l_id     NUMBER;
    l_context VARCHAR(100);
BEGIN
    l_id := 1;
    func_lock(l_id);
    -- 如果上一步没锁住，下面获取的数据就不稳定，导致执行结果错误
    SELECT context INTO l_context FROM t1 WHERE id = l_id;
    UPDATE t1 SET context = l_context || 'a' WHERE id = l_id;
    COMMIT;
END;
/

Oracle 的"不对称"设计

在非自动提交的情况下，Oracle OPEN 一个 FOR UPDATE 游标其实做了两个动作：

建立快照：基于 OPEN 的时间点和数据可见性建立数据快照，后续获取游标数据时不会因表数据变化而变化；
加锁：给快照中的数据加上行锁，避免其他会话修改。

然而在 CLOSE 这个游标时，快照释放了，锁并没有释放！

开发人员正是利用了这个特殊行为：在存储过程里打开并关闭游标来获取锁（不关心数据长什么样，不需要结果集，所以不用 FETCH），然后再对锁定的数据进行修改。（有时候锁的表和更新的表不一样，应用可能会设计专门的锁表，但相同点都是锁的阶段只要锁，不要数据。）

简单来说：Oracle OPEN 干了两件事，CLOSE 只干了一件事，并没有和 OPEN 对应！ 如果 CLOSE 也干两件事（释放锁），问题反而更大了。

而 PG/Gauss 系原本就是快照归快照，锁归锁，各自处理，逻辑清晰干净。只是我们习惯了 Oracle 的行为，认为 Oracle 就是事实上的标准；但有了所谓"学院派"设计的产品后，回过头来看 Oracle 的设计，反而会觉得 PG/Gauss 似乎更合理。

PostgreSQL 的行为

PG 文档中对此行为有明确描述：

https://pg.center/docs/18/sql-declare.html

If the cursor's query includes FOR UPDATE or FOR SHARE, then returned rows are locked at the time they are first fetched, in the same way as for a regular SELECT command with these options.

如果游标的查询包含 FOR UPDATE 或 FOR SHARE，那么返回的行会像带有这些选项的常规 SELECT 命令那样，在首次取出时被锁定。

实际测试

1. 数据准备

CREATE TABLE test_lock (id int PRIMARY KEY, value text);
INSERT INTO test_lock VALUES (1, 'initial');

2. 会话 A（使用游标的会话）

BEGIN;

DO $$
DECLARE
    cur CURSOR FOR SELECT * FROM test_lock WHERE id = 1 FOR UPDATE;
    rec RECORD;
BEGIN
    RAISE NOTICE '[A] 游标已声明，准备 OPEN ...';
    OPEN cur;   -- 仅打开游标，不取数据
    RAISE NOTICE '[A] 游标已 OPEN，但尚未 FETCH。'
              || '接下来 pause 30 秒，请去会话 B 执行 UPDATE。';
    PERFORM pg_sleep(30);  -- 等待 30 秒，期间让会话 B 尝试更新

    RAISE NOTICE '[A] 现在执行第一次 FETCH ...';
    FETCH cur INTO rec;
    RAISE NOTICE '[A] FETCH 完成，获取到 id=%, value=%', rec.id, rec.value;

    -- 再 pause 10 秒，让会话 B 观察后续行为
    PERFORM pg_sleep(10);

    CLOSE cur;
    RAISE NOTICE '[A] 游标已关闭。';
END $$;

COMMIT;

3. 会话 B（尝试更新同一行）

在会话 A 执行到第一个 pg_sleep(30) 期间，快速执行：

UPDATE test_lock SET value = 'updated_by_B' WHERE id = 1;

结论：可以明显观察到——OPEN 游标时未加锁，FETCH 游标时才加锁。

总结

行为	Oracle	PostgreSQL /GaussDB/ openGauss更新前	openGauss更新后
`OPEN FOR UPDATE` 游标时加锁	✅	❌	✅
`FETCH` 时加锁	✅	✅	✅
`CLOSE` 游标时释放锁	❌（事务结束才释放）	❌（事务结束才释放）	❌（事务结束才释放）
`OPEN` 时建立快照	✅	✅	✅

国产数据库为了做 Oracle 兼容性，可以学 Oracle 一样在打开游标时就加锁；但应时刻保持清醒——哪些设计是好的，哪些设计是有问题的。兼容不等于照搬，理解背后的原理才能做出正确的技术决策。