Concurrent insertion in mutually exclusive tables in oracle

concurrencyoracle

There are three tables:

create table C(id_row integer primary key)

create TABLE A(col_a1,col_a2,col_a3,..., id_row integer primary key, constraint fk_a_c foreign key (id_row)  references C(id_row))

create TABLE B(col_b1,col_b2,col_b3,..., id_row integer primary key, constraint fk_b_c foreign key (id_row)  references C(id_row))

Table C is populated with a number of values.
I have to write an application that will allow its users to insert in table A or B an id_row already present in table C, constrained by the fact that a single id_row can be present at most in only one of A and B.

The application I have to write will establish a connection to the database for each user and start a new transaction for every id_row to be inserted. The isolation level must be READ COMMITTED.

The procedure I would write to implement mutually exclusive inserts would be this:

 select * from table c where id_row =in_id_row for update; --lock the row in the parent table
 check id_row is not already in a or b, if it is raise exception;
 proceed to insert the row in a or b.

Does anybody know other approaches to achieve this behavior without using 'select for update' or 'lock table'?

Best Answer

One way is to add discriminator column, something like 

create table c (id_c int not null, val char(1))
create index idx_c1 on c(id_c,val);
alter table c add constraint chk_val check(val in ('a','b'));


alter table c add constraint c_pk primary key (id_c) using index idx_c1;
alter table c add constraint c_uq unique (id_c,val) using index idx_c1;


create table a(id_a int not null primary key, 
val char(1) default 'a' not null ,
constraint fk_t foreign key(id_a,val)  references c (id_c,val));

alter table a add constraint chk_val_a check (val ='a');

Similar for table (but check constraint should b check(val='b').

Then detail record can be in table a or b, but not in both. Overhead is small thanks to the fact Oracle allows multiple constraint supported by the same index.

Related Question