oracle - atomicity of row locking -

just got ora-00060: deadlock detected while waiting resource.

i have query lock specific columns in rows , wonder how oracle sets lock on rows matched query? atomic or not?

for example have table id , value columns , following query:

select id, value tbl id > 1 , id < 100 update of value 

there several clients execute query. possible deadlock in case?

is possible deadlock in case

it's possible, when 2 or more sessions trying acquire exclusive lock on resource locked each other.

here simple demonstration:

  create table deadlock1(col number);    create table deadlock2(col number);    insert deadlock1(col) values(1);   insert deadlock2(col) values(1); 

session #1

  select *     deadlock1    col = 1      update of col 

session #2

  select *     deadlock2    col = 1      update of col 

session #1

  select *     deadlock2    col = 1      update of col 

session #2

  select *     deadlock1    col = 1      update of col 

attempt acquire lock in session #2 if no commit or rollback has been issued in session #1, result in ora-00060: deadlock detected while waiting resource.

in case, when session#1 executing query , there no commit or rollback, session #2 blocked. there no deadlocking. must else causes deadlock.

oracle creates trace file every time deadlock occurs , places name , path of newly created trace file in alert.log file. so, need inspect content of created trace(*.trc) file identify sessions blocking each other:

*** session id:(362.24645) 2013-09-04 14:46:05.297 *** client id:() 2013-09-04 14:46:05.297 *** service name:(nkrasnov) 2013-09-04 14:46:05.297 *** module name:(pl/sql developer) 2013-09-04 14:46:05.297 *** container id:(3) 2013-09-04 14:46:05.297  deadlock graph:                                           ---------blocker(s)--------  ---------waiter(s)--------- resource name                             process session holds waits  process session holds waits tx-000b0007-00002730-00000000-00000000        138     362     x            142     441           x tx-00050011-00000a51-00000000-00000000        142     441     x            138     362           x  session 362: did 0001-008a-00003be6 session 441: did 0001-008e-0000c55f  session 441: did 0001-008e-0000c55f session 362: did 0001-008a-00003be6  

consider using nowait or starting 11g skip locked option of for update clause.