无需"lost update"即可正确归档数据

Question

如何在不"丢失更新"的情况下正确存档数据？

这是我想做的：

INSERT INTO
SELECT FOR UPDATE
DELETE SELETED rows.

但语法FOR UPDATE在INSERT INTO ... SELECT...中不受支持

是否可以在PL/SQL上使用没有光标的SQL来解决问题？

---

例

create table authority(id number, key varchar2(128));
create table authority_arch(id number, key varchar2(128));
insert into authority(1, 'random_key1');
insert into authority(1, 'random_key2');
insert into authority(1, 'random_key3');
insert into authority(2, 'random_key4');
insert into authority(2, 'random_key5');
commit;

1 节课

insert into authority_arch
select * from authority where id=2;
-- in this moment 2 session make insert! 'Lose rows' in next delete
delete from authority where id=2;

2 节

insert into authority(2, 'random_key6', sysdate+1);
commit;

结果有：

select * from authority

id  |  key
-----------
1   |   random_key1
1   |   random_key2
1   |   random_key3

但我只想删除选定的行

id  |  key
-----------
1   |   random_key1
1   |   random_key2
1   |   random_key3
2   |   random_key6

作为解决方案，我使用：

for rec in (select rowid as rid, a.* from authority a where id=2 FOR UPDATE nowait) loop
insert into authority_arch values(rec.id, rec.key);
delete from authority where rowid=rec.rid;
end loop;

Answer 1

在 Oracle 12c 及更高版本中，您可以使用In-Database Archiving来实现此目的。 对表启用数据库内存档会导致添加一个名为ORA_ARCHIVE_STATE的系统生成的隐藏列

它使用"标记为删除"的概念，因此数据仍存在于表中，但对应用程序不可见。

首先，为表启用数据库内存档。

ALTER TABLE yourtable ROW ARCHIVAL;

因此，为表创建了一个名为ORA_ARCHIVE_STATE的隐藏列，您可以使用user_tab_cols

现在，使您的行对其他应用程序/会话不可见，

UPDATE yourtable
SET    ORA_ARCHIVE_STATE = '1'
WHERE  id  BETWEEN 1 AND 10000;
COMMIT;

现在，您可以随时使用该条件删除这些行。

请参阅 Oracle 数据库 12c 第 1 版 (12.1( 中的数据库内归档

Answer 2

你应该能够执行以下操作：

create table temp_keys as (select pk from yourtable where condition);
select pk from yourtable where pk in (select pk from temp_keys) for update;
insert into archivetable (columnlist)
select columnlist from yourtable
where pk in (select pk from temp_keys);
commit;
drop table temp_keys;

您也可以使用临时表进行temp_keys，这样您不必每次都删除它。

编辑：使用您添加的新信息，您可以跳过选择更新。只需跟踪您为以下删除复制的 ID。创建一个带有 id 的(可能是临时的(表，执行插入，执行删除，然后就完成了。

Answer 3

for update子句在这里对你没有帮助。这只会锁定您查询的行，阻止其他人更新/删除它们。添加的任何新行都不会锁定！因此，删除将始终处理新行。

为了克服这个问题，您可以采取几种基本方法：

• 插入和删除在特定时间点存在的行
• 保存要存档的所有行的列表。然后使用此列表而不是表本身插入+删除。

您可以使用闪回查询执行第一个操作。获取数据库的 SCN，其中包含 dbms_flashback.get_system_change_number。然后使用"as of scn"获取此时存在的行：

declare
insert_time pls_integer;
begin
insert_time := dbms_flashback.get_system_change_number;
insert into authority_arch
select * from authority as of scn insert_time
where  id = 2;
dbms_lock.sleep(10); -- wait to allow insert in session 2
delete authority
where  ( id, key ) in (
select id, key from authority as of scn insert_time
where  id = 2 
);
end;
/

对于第二种方法，可以使用临时表。或者，您可以使用批量收集将它们提取到数组中。并且要插入+删除：

declare
type auth_rec is table of authority%rowtype index by binary_integer;
arch_recs auth_rec ;
begin
select * 
bulk collect into arch_recs
from   authority
where  id = 2;
forall i in arch_recs.first .. arch_recs.last
insert into authority_arch values arch_recs(i);
dbms_lock.sleep(10); -- wait to allow insert in session 2
forall i in arch_recs.first .. arch_recs.last
delete authority
where  id = arch_recs(i).id
and    key = arch_recs(i).key;
end;
/