CODEWITHSUNDEEP
sqloracle-database
Бычков Алексей
Бычков Алексей

Reputation: 1

How to lock oracle table on WHERE condition?

I have a table INFO_TABLE with 3 fields (id, param1, param2) in my ORACLE database

I use this table in the following way

`1) SELECT ID FROM INFO_TABLE WHERE param1 = ? param2 = ?` <br>
2) IF ROW EXISTS, THEN I USE IT <br>
3) if row does not exist i execute this query : 
INSERT INTO INFO VALUES (INFO_SEQ.NEXTVAL, 'val1', 'val2' and return the generated id for this row

The problem is that when i do this in concurrent mode there is a chance to insert a duplicate value pair of ('val1', 'val2') into the table.

Is there any way to some how lock the table on WHERE predicate to avoid this?

I dont want to lock the whole table for this operation and I cant use unique constraints.
I need something like SELECT FOR UPDATE (this one doesnt work because there is no row to lock yet) query

Upvotes: 0

Views: 304

Answers (2)

sstan
sstan

Reputation: 36493

I completely agree with the other answer that, one way or another, you will need a unique constraint to ensure data integrity when faced with concurrent inserts.

However, if you don't want to deal with the exceptions in your Java code, you can wrap the insert logic inside a PL/SQL procedure, and have it deal with the exception instead.

Assuming you have properly defined a unique constraint on param1 and param2, then you can use a procedure that looks like this:

create or replace procedure get_info_table_id_for(param1Value in varchar2, param2Value in varchar2, IdValue out number)
as
begin
  begin
    select Id into IdValue
    from info_table
    where param1 = param1Value
    and param2 = param2Value;
  exception
    when no_data_found then
      IdValue := null;
  end;

  if IdValue is null then
    IdValue := info_seq.nextval;

    begin
      insert into info_table (Id, param1, param2) values (IdValue, param1Value, param2Value);
    exception
      when dup_val_on_index then -- concurrent insert, reselect the row.
        select Id into IdValue
        from info_table
        where param1 = param1Value
        and param2 = param2Value;
    end;
  end if;
end get_info_table_id_for;
/

The unique constraint violation exception is dealt with in the when dup_val_on_index then block.

Upvotes: 0

David Aldridge
David Aldridge

Reputation: 52386

You add a unique constraint on ('val1', 'val2'), and this will prevent duplicates from being entered. An ORA-0001 error will be raised if you do.

If you can't do this, then you do not have a way of preventing duplicates from being entered.

Upvotes: 4

Related Questions