CODEWITHSUNDEEP
sqloracle-database
Z. Anton
Z. Anton

Reputation: 75

Correctly archiving data without "lost update"

How can one correctly archive data without "lost update"?

Here is what I want to do:

INSERT INTO
SELECT FOR UPDATE

DELETE SELETED rows.

But the syntax FOR UPDATE is not supported in INSERT INTO ... SELECT...

Can one solve the problem using just SQL without Cursor on PL/SQL?

Example

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 session 

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 session

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

in result have:

select * from authority

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

but i want delete ONLY selected rows 

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

As a solution i use:

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;

Upvotes: 3

Views: 235

Answers (3)

Chris Saxon
Chris Saxon

Reputation: 9865

The for update clause doesn't help you here. This only locks the rows you query, stopping others updating/deleting them. Any new rows added are not locked! So the delete will always process the new rows.

To overcome this, there's a couple of basic approaches you can take:

  • Insert & delete the rows as they existed at a particular point in time
  • Save a list of all the rows you want to archive. Then insert+delete using this list, rather than the table itself.

You can do the first using flashback query. Get the database's SCN with dbms_flashback.get_system_change_number. Then use "as of scn" to get the rows as they existed at this time:

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;
/

You can use a temporary table for the second method. Or you could use bulk collection to fetch them into an array. And forall to insert+delete:

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;
/

Upvotes: 1

ewramner
ewramner

Reputation: 6278

You should be able to do something like:

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;

You could also use a temporary table for temp_keys, then you don't have to drop it every time.

EDIT: With the new information you added you can skip the select for update. Just keep track of the ids you have copied for the following delete. Create a (possibly temporary) table with ids, do the insert, do the delete and you are done.

Upvotes: 1

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

In Oracle 12c and above, you could achieve this using In-Database Archiving. Enabling in-database archiving on the table results in the addition of a system generated hidden column called ORA_ARCHIVE_STATE

It uses a concept of "mark for delete", so the data remains present in the table, but is not visible to the application.

Firstly, enable in-database archiving for your table.

ALTER TABLE yourtable ROW ARCHIVAL;

So, a hidden column called ORA_ARCHIVE_STATE is created for the table which you could check using user_tab_cols

Now, make your rows invisible to other applications/sessions,

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

Now, You may delete these rows whenever you like using the condition.

Refer In-Database Archiving in Oracle Database 12c Release 1 (12.1)

Upvotes: 1

Related Questions