如何在无“丢失更新”时正确归档数据？能否仅用SQL实现？

当然可以不用PL/SQL游标，仅用纯SQL就能解决这个归档时的丢失更新问题。核心思路是先锁定要归档的目标行，再基于唯一行标识（ROWID）执行插入和删除操作，避免后续新插入的行被误删。

问题分析 #

你遇到的核心问题是：先执行INSERT INTO ... SELECT归档数据，再执行DELETE时，其他会话可能已经插入了符合删除条件的新行，导致这些新行被误删。这是因为DELETE的条件（如id=2）会匹配所有符合条件的行，包括归档操作后新插入的行。

纯SQL解决方案 #

我们可以通过临时表存储要归档行的唯一标识（ROWID），结合行锁来实现安全归档：

步骤1：创建临时表（仅需创建一次） #

临时表仅在当前会话可见，用于存储要归档行的ROWID：

CREATE GLOBAL TEMPORARY TABLE temp_arch_rids (rid ROWID) ON COMMIT DELETE ROWS;

步骤2：执行归档操作（单事务内完成） #

在同一个事务中依次执行以下SQL：

-- 1. 锁定要归档的行，并存储它们的ROWID
INSERT INTO temp_arch_rids
SELECT ROWID FROM authority WHERE id=2 FOR UPDATE NOWAIT;

-- 2. 从原表读取锁定的行，插入到归档表
INSERT INTO authority_arch
SELECT a.* 
FROM authority a 
JOIN temp_arch_rids t ON a.ROWID = t.rid;

-- 3. 删除原表中已归档的行（仅删除锁定的那些行）
DELETE FROM authority a 
WHERE EXISTS (
    SELECT 1 FROM temp_arch_rids t 
    WHERE a.ROWID = t.rid
);

-- 提交事务，释放行锁并清空临时表
COMMIT;

方案原理 #

• 行锁保护：SELECT ... FOR UPDATE NOWAIT会锁定所有符合id=2的现有行，防止其他会话修改或删除这些行；同时，如果其他会话尝试修改这些锁定行，会立即返回错误（NOWAIT参数），避免阻塞。
• 精准定位行：通过ROWID（Oracle中唯一标识行的物理地址）来定位要归档和删除的行，确保只操作一开始锁定的那些行，不会误删后续新插入的id=2的行。
• 临时表隔离：临时表仅在当前会话有效，不会与其他会话的归档操作产生冲突，无需担心数据污染。

效果验证 #

按照你的测试场景：

1. 会话1执行上述归档SQL的前两步（插入临时表、插入归档表）；
2. 会话2插入insert into authority(2, 'random_key6');并提交；
3. 会话1执行删除和提交；
4. 最终查询authority表时，id=2, key='random_key6'的行会保留，符合你的期望结果。

替代简化方案（单查询+事务） #

如果不想创建临时表，也可以在同一个事务中先锁定行，再利用Oracle的隔离级别特性实现，操作同样安全：

-- 开启事务
BEGIN TRANSACTION;

-- 先锁定目标行（这一步会阻止其他会话修改这些行，且当前事务后续仅能看到锁定时的数据集）
SELECT * FROM authority WHERE id=2 FOR UPDATE NOWAIT;

-- 归档仅锁定的行
INSERT INTO authority_arch
SELECT * FROM authority WHERE id=2;

-- 删除仅锁定的行（此时不会包含其他会话后续插入的新行）
DELETE FROM authority WHERE id=2;

-- 提交事务
COMMIT;

注：这个方案依赖Oracle的读已提交隔离级别，在SELECT FOR UPDATE之后，其他会话插入的新行不会被当前事务的INSERT和DELETE语句识别到，逻辑同样可靠，但临时表方案的可读性和扩展性会更好一些。

内容的提问来源于stack exchange，提问作者Z. Anton