ORACLE - insert records by multiple user on PK table -

in oracle have table employee(empid,empname) primary key on empid.

two users having insert privileges table user1 , user2.

user1 insert record empid=1 , empname='xyz' , not committed. if user2 trying insert same record empid=1 , empname='xyz' screen hangs till user1 commits or rollback.

is there option insert record both users out hang , user commits second should pk violation error.

thanks, niju jose

in single-user database, user can modify data in database without concern other users modifying same data @ same time. however, in multiuser database, statements within multiple simultaneous transactions can update same data. transactions executing @ same time need produce meaningful , consistent results.

isolation level     dirty read      nonrepeatable read      phantom read ----------------    ------------    ------------------      ------------- read uncommitted    possible        possible                possible read committed      not possible    possible                possible repeatable read     not possible    not possible            possible serializable        not possible    not possible            not possible 

in case, oracle acquires row level lock. insert case simpler, because inserted rows locked, not seen other users because not commited. when user commits, releases locks, so, other users can view these rows, update them, or delete them.

read here data concurrency , consistency. see here more details on insert mechanism

to explain more, reproduce florin's answer here

for example, let tablea(col1 number, col2 number), data within it:

col1  |  col2 1     |  10 2     |  20 3     |  30 

if user john issues @ time1:

update tablea set col2=11 col1=1; 

will lock row1.

at time2 user mark issue

update tablea set col2=22 col1=2; 

the update work, because row 2 not locked.

now table looks in database:

col1  |  col2 1     |  11   --locked john 2     |  22   --locked mark   3     |  30 

for mark table is(he not see changes uncommited)

col1  |  col2 1     |  10    2     |  22    3     |  30 

for john table is:(he not see changes uncommited)

col1  |  col2 1     |  11    2     |  20    3     |  30 

if mark tries @ time3:

update tablea set col2=12 col1=1; 

his session hang until time4 when john issue commit.(rollback unlock rows, changes lost)

table is(in db, @ time4):

col1  |  col2 1     |  11    2     |  22   --locked mark   3     |  30 

immediatley, after john's commit, row1 unlocked , marks's update job:

col1  |  col2 1     |  12   --locked mark   2     |  22   --locked mark   3     |  30 

lets's mark issue rollbak @ time5:

col1  |  col2 1     |  11    2     |  20    3     |  30 

so hang due fact oracle engine waiting commit or rollback user1, before giving definitive response user2.