Oracle transaction isolation

Question

I have a method SaveApp() which will deactivate the existing records and insert a new one.

void SaveApp(int appID)
{
   begin transaction;
   update;
   insert;
   commit transaction;
}

Let's say in database table SalesApp, I have 2 records with appID equal to 123;

1. record 1, appID 123, inactive
2. record 2, appID 123, active

If I call this method SaveApp() in two threads at same time, the first transaction (let's call it T1) will update the existing two record while the second transaction (let's call it T2) wait.

after T1 finishes, there will be three records in this table now. however, somehow T2 is not aware of the newly inserted record, the update query in T2 only update the previous two records, and insert the forth one.

after these two method call, in database, we will now have 4 records, the 3rd and 4th one both are active which is wrong.

1. record 1, appID 123, inactive
2. record 2, appID 123, inactive
3. record 3, appID 123, active
4. record 4, appID 123, active

Do you know any solution can solve this problem? I have tried using isolation level serializable which does not work.

Thanks!

Answer 1

Do you have another table that holds one row per AppId, enforced via unique or primary key constraint? If so use select for update on the parent table to serialize access per AppId.

Create the tables:

session_1> create table parent (AppId number primary key);

Table created.

session_1> create table child (AppId number not null references Parent(AppId)
  2      , status varchar2(1) not null check (status in ('A', 'I'))
  3      , InsertedAt date not null)
  4  /

Table created.

Insert beginning values:

session_1> insert into Parent values (123);

1 row created.

session_1> insert into child values (123, 'I', sysdate);

1 row created.

session_1> insert into child values (123, 'A', sysdate);

1 row created.

session_1> commit;

Commit complete.

Begin the first transaction:

session_1> select AppId from Parent where AppId = 123 for update;

     APPID
----------
       123

session_1> update Child set Status = 'I' where AppId = 123 and Status = 'A';

1 row updated.

session_1> insert into child values (123, 'A', sysdate);

1 row created.

Prior to commit, in a second session, make sure we are only seeing the first rows:

session_2> select * from Child;

     APPID S INSERTEDAT
---------- - -------------------
       123 I 2010-08-16 18:07:17
       123 A 2010-08-16 18:07:23

Start the second transaction:

session_2> select AppId from Parent where AppId = 123 for update;

Session 2 is now blocked, waiting on session 1. And will not proceed. Commiting session 1 will unblock Session

session_1> commit;

Commit complete.

Session 2 we now see:

     APPID
----------
       123

Complete the second transaction:

session_2> update Child set Status = 'I' where AppId = 123 and Status = 'A';

1 row updated.

session_2> insert into child values (123, 'A', sysdate);

1 row created.

session_2> commit;

Commit complete.

session_2> select * from Child;

     APPID S INSERTEDAT
---------- - -------------------
       123 I 2010-08-16 18:07:17
       123 I 2010-08-16 18:07:23
       123 I 2010-08-16 18:08:08
       123 A 2010-08-16 18:13:51

EDIT Technique cribbed from Expert Oracle Database Architecture second edition by Thomas Kyte, pages 23-24. http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1430229462/ref=sr_1_2?ie=UTF8&s=books&qid=1282061675&sr=8-2

EDIT 2 I would also recommend implementing Patrick Merchand's answer to this question for a constraint that enforces the rule that an AppId can only have one active record. So the final solution would have two parts, this answer for how to do the updates in a way that gets what you want, and Patrick's to make sure that table conforms to the requirements to protect the integrity of the data.