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)