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:
There is a commit happening
between the update and insert.
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 (http://stackoverflow.com/questions/3497065/oracle-transaction-isolation/3498372#3498372):
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.)