Re: archiving records

From: Michel Cadot <micadot{at}altern{dot}org>
Date: Mon, 28 Aug 2006 18:41:22 +0200
Message-ID: <44f31cb3$0$980$626a54ce@news.free.fr>

"Daud" <daud11_at_hotmail.com> a écrit dans le message de news: 1156760226.440981.312160_at_i42g2000cwa.googlegroups.com...
| >
| > Yes, *exactly*, 'assume'! *NO* guarantee whatsoever
| > as
| > a) you don't lock the table
| > b) you break up your *logical* transaction into multiple *physical*
| > transactions
| > Basic violations against the ACID concept.
| >
| > Please do not top post.
| >
|
| What do you mean? I don't understand. Why the table has to be locked?
| Those rows with colA = 'ABC' will not be updated. No rows with
| colA='ABC' will be inserted. So, why lock the whole table?
|

What Sybrand said is unless you are alone on the database you cannot guarantee a correct result with your procedure. Here's a small exemple with just one line in the table:

Session 1:

SQL> select id from mytab;

        ID

---

         1

1 row selected.

SQL> begin

  2     INSERT INTO MYTAB_ARC
  3     SELECT * FROM MYTAB WHERE ROWNUM < 2;
  4
  5     dbms_lock.sleep (60);
  6
  7     DELETE FROM MYTAB WHERE ROWNUM < 2;
  8
  9     COMMIT;

 10
 11 end;
 12 /

PL/SQL procedure successfully completed.

During the execution of this, I executed the following in a second session:

SQL> insert into mytab values(0, 'a', 'a', 'a');

1 row created.

SQL> commit;

Commit complete.

SQL> select id from mytab_arc;

no rows selected

SQL> select id from mytab;

        ID

---

         0
         1

2 rows selected.

Well, nothing strange, I inserted a new row and as the first session does not commit yet I see the 2 rows in MYTAB and nothing in MYTAB_ARC. Now go back to the first session that has now ended its PL/SQL block:

SQL> select id from mytab_arc;

        ID

---

         1

1 row selected.

SQL> select id from mytab;

        ID

---

         1

1 row selected.

Well well well, we lost the new inserted row and are about to archive to same one! It goes worst when you repeat the same process with multiple loops.

Regards
Michel Cadot Received on Mon Aug 28 2006 - 11:41:22 CDT