sql - Block inserts using for update -

i trying prevent inserts happening using select update in oracle. instance suppose in 1 session (autocommit off, isolation level = serializable) address table contains no rows , in session1:

session1: select * address addressid = 1 update 

now in session2:

session2:insert address (addressid, street, city,zip) values (1, 'main','ny','12345'); commit; 

i have thought blocked. however, i'm finding insert happens. able commit it. in session1 again.

  session1: insert address (addressid, street, city,zip) values (1, 'main','ny','12345') 

this gives integrity constraint error before commit. (not serializable exception have expected).

why happening? using oracle 12c. there couple of unexpected results. first why constraint error in session1 before commit? oracle should not see insert other session. secondly, shouldn't insert in session1 blocked due "for update" select? finally, there way block inserts particular key?

see http://www.oracle.com/technetwork/issue-archive/2010/10-jan/o65asktom-082389.html serializable isolation level.

this degree of isolation comes price, , price following possible error:

error @ line 1: ora-08177: can't serialize access transaction 

you'll message whenever try update row has changed since transaction began. (note oracle tries purely @ row level, may receive ora-08177 error when row you're interested in modifying hasn't been modified. ora-08177 may happen due other row(s) being modified on block contains row.)

in case not update idea same. if query table, not "see" because reads rollback segment. insert different because needs access real data ensure consistency.

as how work around - use named locks. still work although 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; /