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

Yesterday I created a test case to reproduce the problem described. Today I found that the test case was flawed. I did not understand the problem, as such, I believe the answer I gave yesterday is is incorrect.

There are two possible issues:

1. There is a commit happening between the update and insert.

2. This is only an issue for new AppIds.

Test case:

Create the test table and insert two rows:

session 1 > create table test (TestId number primary key
  2             , AppId number not null
  3             , Status varchar2(8) not null 
  4                 check (Status in ('inactive', 'active'))
  5  );

Table created.

session 1 > insert into test values (1, 123, 'inactive');

1 row created.

session 1 > insert into test values (2, 123, 'active');

1 row created.

session 1 > commit;

Commit complete.

Begin first transaction:

session 1 > update test set status = 'inactive'
  2         where AppId = 123 and status = 'active';

1 row updated.

session 1 > insert into test values (3, 123, 'active');

1 row created.

Begin second transaction:

session 2 > update test set status = 'inactive'
  2         where AppId = 123 and status = 'active';

Now session 2 is blocked, waiting to get a row lock on row 2. Session 2 can not proceed until the transaction in session 1 either commits or rollsback. Commit session 1:

session 1 > commit;

Commit complete.

Now session 2 is unblocked and we see:

1 row updated.

When session 2 was unblocked, the update statement restarted, saw the changes in session 1, and updated row 3.

session 2 > select * from test;

    TESTID      APPID STATUS
---------- ---------- --------
         1        123 inactive
         2        123 inactive
         3        123 inactive

Complete the transaction in session 2:

session 2 > insert into test values (4, 123, 'active');

1 row created.

session 2 > commit;

Commit complete.

Check the results (using session 1):

session 1 > select * from test;

    TESTID      APPID STATUS
---------- ---------- --------
         1        123 inactive
         2        123 inactive
         3        123 inactive
         4        123 active

The only way for the two updates to not block each other is for there to be a commit or rollback between one and the other. There may be an implicit commit hidden somewhere in the software stack you are using. I don't know enough about .NET to advise on tracking that down.

However, the same problem will happen if AppId is brand new to the table. Test using a new AppId of 456:

session 1 > update test set status = 'inactive'
  2         where AppId = 456 and status = 'active';

0 rows updated.

No locks are taken because no rows are written to.

session 1 > insert into test values (5, 456, 'active');

1 row created.

Start second transaction for the same new AppId:

session 2 > update test set status = 'inactive'
  2          where AppId = 456 and status = 'active';

0 rows updated.

Session 2 does not see row 5, so it will not attempt to aquire a lock on it. Continue session 2:

session 2 > insert into test values (6, 456, 'active');

1 row created.

session 2 > commit;

Commit complete.

Commit session 1 and view results:

session 1 > commit;

Commit complete.

session 1 > select * from test;

    TESTID      APPID STATUS
---------- ---------- --------
         1        123 inactive
         2        123 inactive
         3        123 inactive
         4        123 active
         5        456 active
         6        456 active

6 rows selected.

To fix, use the function based index from Patrick Marchand (Oracle transaction isolation):

session 1 > delete from test where AppId = 456;

2 rows deleted.

session 1 > create unique index test_u
  2         on test (case when status = 'active' then AppId else null end);

Index created.

Start first transaction of new AppId:

session 1 > update test set status = 'inactive'
  2         where AppId = 789 and status = 'active';

0 rows updated.

session 1 > insert into test values (7, 789, 'active');

1 row created.

Again session 1 does not take any locks with the update. There is a write lock on row 7. Start the second transaction:

session 2 > update test set status = 'inactive'
  2         where AppId = 789 and status = 'active';

0 rows updated.

session 2 > insert into test values (8, 789, 'active');

Again, session 2 does not see row 7, so it does not attempt to take a lock on it. BUT the insert is trying to write to the same slot on the function based index, and blocks on the write lock held by session 1. Session 2 will now wait for session 1 to commit or rollback:

session 1 > commit;

Commit complete.

And is session 2 we see:

insert into test values (8, 789, 'active')
*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.TEST_U) violated

At which point your client could retry the entire transaction. (Both the update and the insert.)

Answer 2

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.

Answer 3

If you want to make sure you never can have more than one "active" records in the db for a given id, here's a cool (credit goes here): http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1249800833250

It takes advantage of the fact that Oracle doesn't store completely NULL index entries and will guarantee that a particular id can't have more than one "active" record:

drop table test
/

create table test (a number(10), b varchar2(10))
/

CREATE UNIQUE INDEX unq ON test (CASE WHEN b = 'INACTIVE' then NULL ELSE a END)
/

these inserts work fine:

insert into test (a, b) values(1, 'INACTIVE');
insert into test (a, b) values(1, 'INACTIVE');
insert into test (a, b) values(1, 'INACTIVE');
insert into test (a, b) values(1, 'ACTIVE');
insert into test (a, b) values(2, 'INACTIVE');
insert into test (a, b) values(2, 'INACTIVE');
insert into test (a, b) values(2, 'INACTIVE');
insert into test (a, b) values(2, 'ACTIVE');

these inserts fail:

insert into test values(1, 'ACTIVE');

ORA-00001: unique constraint (SAMPLE.UNQ) violated

insert into test values(2, 'ACTIVE');

ORA-00001: unique constraint (SAMPLE.UNQ) violated

Answer 4

"the 3rd and 4th one both are active which is wrong."

A simple unique index can prevent that at the database level.

create table rec (id number primary key, app_id number, status varchar2(1));
create unique index rec_uk_ix on rec (app_id, case when status = 'N' then id end);
insert into rec values (1,123,'N');
insert into rec values (2,123,'N');
insert into rec values (3,123,'N');
insert into rec values (4,123,'Y');
insert into rec values (5,123,'Y');

The unique index ensures that there can only by one record for any app with a status other than 'N'.

Obviously the application has to catch the error and know what to do with it (re-try or inform the user the data has changed).

Answer 5

I'm not entirely sure, but I think that if you set both transactions to SERIALIZABLE you would get an error in the second one so that you would know that something was wrong.

Answer 6

@Alex is correct, it's not really an Oracle issue, it's an application issue.

Perhaps something like this might work for you:

Put your Oracle transaction in a stored procedure, and perform it this way:

BEGIN
  LOOP
    BEGIN
      SELECT * 
        FROM SaleApp
       WHERE appID = 123
         AND status = 'ACTIVE'
         FOR UPDATE NOWAIT;
      EXIT;
    EXCEPTION
      WHEN OTHERS THEN
        IF SQLCODE = -54 THEN
          NULL;
        ELSE
          RAISE error
        END IF;
    END IF;
  END LOOP;
  UPDATE ....
  INSERT ....
  COMMIT;
END;

The idea here is that the first transaction to grab and lock the current active record gets to complete. Any other transactions that try to lock that record will fail on the SELECT FOR UPDATE NOWAIT, and loop until they succeed.

Depending on how long it takes to perform a typical transaction, you may want to sleep within the exception handler before retrying the select.

Answer 7

Can you push the updates onto a queue (AQ maybe) so that they're executed sequentially?

Another option might be to lock the records in question (SELECT FOR UPDATE NOWAIT or SELECT FOR UPDATE WAIT)

Answer 8

Seems like it isn't really an Oracle issue, it's a concurrency issue in your application. Not sure what language this is; if it's Java can you just synchronise the method?