CODEWITHSUNDEEP
sqloracle-databaselocking
dan b
dan b

Reputation: 1182

Block inserts using for update

I am trying to prevent inserts from happening using select for update in oracle. For instance suppose in one session (autocommit is off, isolation level = serializable) the Address table contains no rows and I do in SESSION1:

SESSION1: select * from Address where addressID = 1 for update

Now in SESSION2: 

SESSION2:insert into Address (addressID, street, city,zip) values (1, 'main','ny','12345'); commit;

I would have thought that is blocked. However, I'm finding that the insert happens. I am able to commit it. Then in SESSION1 again.

  SESSION1: insert into Address (addressID, street, city,zip) values (1, 'main','ny','12345')

This gives an integrity constraint error even before a commit. (not as serializable exception as I would have expected).

Why is this happening? I am using oracle 12c. There are a couple of unexpected results. First why do I get a constraint error in Session1 even before the commit? Oracle should not see the insert from the other Session. Secondly, shouldn't the insert in Session1 be blocked due to the "for update" select? Finally, is there a way to block inserts for a particular key?

Upvotes: 0

Views: 710

Answers (1)

jva
jva

Reputation: 2805

See http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o65asktom-082389.html about SERIALIZABLE isolation level.

this degree of isolation comes with a price, and that price is the following possible error: 

ERROR at line 1: ORA-08177: can't serialize access for this transaction

You'll get this message whenever you try to update a row that has changed since your transaction began. (Note that Oracle tries to do this purely at the row level, but you may receive an ORA-08177 error even when the row you're interested in modifying hasn't been modified. The ORA-08177 may happen due to some other row(s) being modified on the block that contains your row.)

In your case it is not exactly update but the idea is the same. If you query the table, it will not "see" it because it reads from rollback segment. But insert is different because it needs to access the real data to ensure consistency.

As for how to work around it - use named locks. They still work although they shouldn't :)

Alter session set isolation_level=serializable; 

declare
  v_lockhandle varchar2(128);
  v_result number;
begin
  dbms_lock.allocate_unique('table_name'||'id'
                           ,v_lockhandle);

  v_result := dbms_lock.request(v_lockhandle
                               ,release_on_commit => true
                               ,lockmode => 6 -- exclusive
                               ,timeout => 0);

  dbms_output.put_line('result: '||v_result);
end;
/

Upvotes: 2

Related Questions