Geek Answers Handbook

Oracle Transaction Isolation

I have a SaveApp () method that deactivates existing records and inserts a new one.

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

Say in the SalesApp database table, I have 2 records with appID equal to 123;

  • record 1, application 123, inactive
  • record 2, application 123, active

If I call this method SaveApp()in two threads at the same time, the first transaction (let it be called T1 ) will update the existing two records during the second transaction (call it T2 ).

after completing T1 , there will be three entries in this table. however, somehow T2 is not aware of the newly inserted record, the update request in T2 updates only the two previous records and inserts the fourth.

4 , 3- 4- , .

  • 1, 123,
  • 2, 123,
  • 3, appID 123,
  • 4, appID 123,

, ? , .

!

+5
8

, , ? , select for update AppId.

:

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.

:

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.

:

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.

, :

session_2> select * from Child;

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

:

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

2 , 1. . 1

session_1> commit;

Commit complete.

2 :

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

:

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. , Oracle Database Architecture , 23-24. http://www.amazon.com/Expert-Oracle-Database-Architecture-Programming/dp/1430229462/ref=sr_1_2?ie=UTF8&s= & QID = 1282061675 & = 8-2

2. , , AppId . , : , , , , Patrick, , .

+6

, "" db , ( ): http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1249800833250

, Oracle NULL- , "" :

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

:

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');

:

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

ORA-00001: (SAMPLE.UNQ)

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

ORA-00001: (SAMPLE.UNQ)

+4

, . , . , , , , .

:

  • commit update insert.

  •    AppId .

:

:

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.

:

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.

:

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

2 , , 2. 2 , 1 . 1:

session 1 > commit;

Commit complete.

2 , :

1 row updated.

2 , , 1 3.

session 2 > select * from test;

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

2:

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

1 row created.

session 2 > commit;

Commit complete.

( 1):

1 > * ;

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

, update , - . - , . .NET, .

, AppId . AppId 456:

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

0 rows updated.

, .

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

1 row created.

AppId:

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

0 rows updated.

2 5, . 2:

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

1 row created.

session 2 > commit;

Commit complete.

1 :

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.

, Patrick Marchand ( Oracle):

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.

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.

1 . 7 . :

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');

, 2 7, . , , , 1. 2 1 commit rollback:

session 1 > commit;

Commit complete.

2, :

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

. ( update, insert.)

+1

(, AQ), ?

(SELECT FOR UPDATE NOWAIT SELECT FOR UPDATE WAIT)

0

, Oracle, concurrency . , ; Java, synchronise ?

0

@Alex , Oracle, .

, - :

Oracle :

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;

, . , , SELECT FOR UPDATE NOWAIT , .

, , , .

0

, , SERIALIZABLE, , , - .

0

" ".

.

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');

, , "N".

, , ( , ).

0
source

More articles:


All Articles