oracle deadlock

Basic operation

su - oracle

　　sqlplus / as sysdba

　　show parameter background

　　show parameter user_dump_dest

　　　　background_dump_dest: path_to_trace_file

　　deadlook error code is: ORA-00060

Refer to dead lock article

      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1528515465282

one example of dead lock

　　

I'm going to hypothesize that there is a unique index on some (at least one) of the columns being 
updated.  

The locks are NOT on a row -- they are due to unique conflicts.  Here is exactly how to simulate 
this:

--------------------- test.sql -------------------------
drop table t;
create table t ( x int primary key );
insert into t values ( 1 );
insert into t values ( 2 );
insert into t values ( 3 );
insert into t values ( 4 );
commit;
update t set x = 5 where x = 1;
REM in another session, run test2.sql
pause
update t set x = 6 where x = 2;
---------------------------------------------------------

-------------------- test2.sql ---------------------------
variable x1 number
variable x2 number
exec :x1 := 6; :x2 := 3;
update t set x = :x1 where x = :x2;
exec :x1 := 5; :x2 := 4;
update t set x = :x1 where x = :x2;
---------------------------------------------------------

Here, session 1 will get the row updated from 1 to 5 -- 5 will be "locked" in the index.

Session 2 will then update 3 to 6 (no conflict, but 6 is "locked" in the index)

Session 2 will then update 4 to 5 -- this'll be a conflict, unique key violation POSSIBLE at this 
point.  Session 2 will block here.

Next, session 1 tries to update 2 to 6 -- that'll be another conflict with session 2, unique key 
violation POSSIBLE at this point.  Session 1 will block and then one of the sessions will get the 
dead lock.

That is when this trace file will be produced:

*** SESSION ID:(8.3883) 2002-08-07 11:09:23.816
DEADLOCK DETECTED
Current SQL statement for this session:
update t set x = :x1 where x = :x2
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-0003003d-000011e2        14       8     X              8       7           S
TX-00020054-0000109e         8       7     X             14       8           S
session 8: DID 0001-000E-00000002   session 7: DID 0001-0008-00000002
session 7: DID 0001-0008-00000002   session 8: DID 0001-000E-00000002
Rows waited on:
Session 7: no row
Session 8: no row
===================================================


So, basically, you have two sessions doing this update (or a similar update) and they are bumping 
into each other with a unique index.  Look for whats unique in this table.

It isn't a row lock issue -- rather, an index key collision that is happening.


Further down in the trace file, you should be seeing something like:


...
Cursor frame allocation dump:
frm: -------- Comment --------  Size  Seg Off
 bind 0: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=48 offset=0
   bfp=01a70280 bln=22 avl=02 flg=05
   value=5     <<<<<=== the bind variable values
 bind 1: dty=2 mxl=22(22) mal=00 scl=00 pre=00 oacflg=03 oacfl2=0 size=0 offset=24      <<<<<=== 
the bind variable values
   bfp=01a70298 bln=22 avl=02 flg=01
   value=4
End of cursor dump
***************** dump of cursor xsc=1a7681c **********************
........

And that should help you ID where the problem is (you'll see the BR_NO and be able to identify the 
ROWS being modified)