Printable View
Shows that the statement causing the deadlock (and the one 'rolled back') was an insert:
INSERT INTO Label_sequences (sequence_number, date)
VALUES (:B1, SYSDATE)
Why would an insert create a DEADLOCK? Could it be firing a trigger maybe ... but then the statement in the trigger would be the deadlock statement and not this one ... no?
Hi
Post the trace file here.
Are the inserts being done parallely
regards
Hrishy
you have pk/uk constraints? and mulitple sessions trying to do dml on the table/tables in a package/anonymous code? then u have every possibility of getting deadlock.
rgds
abhay
Quote:
Originally Posted by hrishy
I don't have the source code, but its an OAS application, running Oracle Forms: 2 web servers, balancing the load.
Here's the top of the trace file - do you need all of it?
****************************************************
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/app/oracle/product/10.2.0
System name: HP-UX
Node name: kcmsdbd1
Release: B.11.11
Version: U
Machine: 9000/800
Instance name: ccopiat
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 5881, image: oracle@kcmsdbd1
*** 2007-09-03 05:56:39.647
*** SERVICE NAME:(SYS$USERS) 2007-09-03 05:56:39.616
*** SESSION ID:(276.11101) 2007-09-03 05:56:39.616
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO LABEL_SEQUENCES (SEQUENCE_NUMBER,DATE_CREATED) VALUES (:B1 ,SYSDATE)
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-00080004-0000081f 36 276 X 36 276 S
session 276: DID 0001-0024-00000245 session 276: DID 0001-0024-00000245
Rows waited on:
Session 276: obj - rowid = 00000000 - D/////AACAAABNmAAA
(dictionary objn - 0, file - 2, block - 4966, slot - 0)
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: c00000002f3ecf20, call: c00000001bae2d78, xact: c00000002de59190, curses: c00000002ea0c4b0, usrses: c00000002ea0c4b0
----------------------------------------
SO: c00000002f3ecf20, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
(process) Oracle pid=36, calls cur/top: c00000001bae2d78/c000000018b4e4c0, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 108 0 4
last post received-location: kslpsr
last process to post me: c00000002f3de100 1 6
last post sent: 0 0 24
last post sent-location: ksasnd
last process posted by me: c00000002f3de100 1 6
(latch info) wait_event=0 bits=10
holding (efd=23) c000000010612410 Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Context saved from call: 0
state=busy, wlstate=free
recovery area:
Dump of memory from 0xC00000002F3B20F0 to 0xC00000002F3B2110
C00000002F3B20F0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
Process Group: DEFAULT, pseudo proc: c00000002e8a4f90
O/S info: user: oracle, term: UNKNOWN, ospid: 5881
OSD pid info: Unix process pid: 5881, image: oracle@kcmsdbd1
Dump of memory from 0xC00000002F3B9920 to 0xC00000002F3B9B28
C00000002F3B9920 00000010 00000000 C0000000 188A7A38 [..............z8]
C00000002F3B9930 00000010 0003139D C0000000 18B4E4C0 [................]
C00000002F3B9940 00000003 0003139D C0000000 2E54BB40 [.............T.@]
C00000002F3B9950 0000000B 0003139D C0000000 2EA0C4B0 [................]
C00000002F3B9960 00000004 00031291 C0000000 2D43BA40 [............-C.@]
C00000002F3B9970 00000007 0003139D C0000000 2D43BB20 [............-C. ]
C00000002F3B9980 00000007 0003139D C0000000 2D43BC00 [............-C..]
C00000002F3B9990 00000007 0003139D C0000000 2D43BCE0 [............-C..]
C00000002F3B99A0 00000007 0003139D C0000000 2D43C698 [............-C..]
C00000002F3B99B0 00000007 0003139D C0000000 2D43C778 [............-C.x]
C00000002F3B99C0 00000007 0003139D C0000000 2D43D9F0 [............-C..]
C00000002F3B99D0 00000007 0003139D C0000000 2D43DAD0 [............-C..]
C00000002F3B99E0 00000007 0003139D C0000000 2D43E028 [............-C.(]
C00000002F3B99F0 00000007 0003139D C0000000 2D43E1E8 [............-C..]
C00000002F3B9A00 00000007 0003139D C0000000 2D43E488 [............-C..]
C00000002F3B9A10 00000007 0003139D C0000000 2D43FFD8 [............-C..]
C00000002F3B9A20 00000007 0003139D 00000000 00000000 [................]
C00000002F3B9A30 00000000 00000000 00000000 00000000 [................]
Repeat 14 times
C00000002F3B9B20 00000000 00000000 [........]
(FOB) flags=2 fib=c00000002d6e1b90 incno=0 pending i/o cnt=0
fname=/u04/oradata/ccopiat/undotbs_01.dbf
fno=2 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e3c60 incno=0 pending i/o cnt=0
fname=/u10/oradata/ccopiat/clinicopia_invt.dbf
fno=11 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e4000 incno=0 pending i/o cnt=0
fname=/u04/oradata/ccopiat/temp_01.dbf
fno=201 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e3508 incno=0 pending i/o cnt=0
fname=/u11/oradata/ccopiat/clinicopia_data_02.dbf
fno=9 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e2a28 incno=0 pending i/o cnt=0
fname=/u13/oradata/ccopiat/clinicopia_jrnl_02.dbf
fno=6 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e2dc8 incno=0 pending i/o cnt=0
fname=/u06/oradata/ccopiat/clinicopia_jrnl_01.dbf
fno=7 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e3168 incno=0 pending i/o cnt=0
fname=/u12/oradata/ccopiat/clinicopia_data_01.dbf
fno=8 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e1438 incno=0 pending i/o cnt=0
fname=/backups/oradata/ccopiat/control03.ctl
fno=2 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e1098 incno=0 pending i/o cnt=0
fname=/u02/oradata/ccopiat/control02.ctl
fno=1 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e0cf8 incno=0 pending i/o cnt=0
fname=/u01/oradata/ccopiat/control01.ctl
fno=0 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e38c0 incno=0 pending i/o cnt=0
fname=/u02/oradata/ccopiat/clinicopia_indx_01.dbf
fno=10 lblksz=4 fsiz=7798248
(FOB) flags=2 fib=c00000002d6e17d8 incno=0 pending i/o cnt=0
fname=/u08/oradata/ccopiat/system_01.dbf
fno=1 lblksz=4 fsiz=7798248
----------------------------------------
SO: c00000002ea0c4b0, type: 4, owner: c00000002f3ecf20, flag: INIT/-/-/0x00
(session) sid: 276 trans: c00000002de59190, creator: c00000002f3ecf20, flag: (8100041) USR/- BSY/-/-/-/-/-
DID: 0001-0024-00000245, short-term DID: 0000-0000-00000000
txn branch: 0000000000000000
oct: 0, prv: 0, sql: 0000000000000000, psql: c000000029d88638, user: 45/
O/S info: user: SYSTEM, term: USKCM-SAPP11, ospid: 888:8028, machine: APTUIT\USKCM-SAPP11
program: frmweb.exe
application name: frmweb.exe, hash value=0
last wait for 'enq: TX - row lock contention' blocking sess=0xc00000002ea0c4b0 seq=4245 wait_time=2944690 seconds since wait started=2
name|mode=54580004, usn<<16 | slot=80004, sequence=81f
Dumping Session Wait History
for 'enq: TX - row lock contention' count=1 wait_time=2944690
name|mode=54580004, usn<<16 | slot=80004, sequence=81f
for 'db file sequential read' count=1 wait_time=58
file#=2, block#=1366, blocks=1
for 'db file sequential read' count=1 wait_time=24
file#=8, block#=89ca, blocks=1
for 'db file sequential read' count=1 wait_time=47
file#=8, block#=89e1, blocks=1
for 'db file sequential read' count=1 wait_time=16786
file#=a, block#=8471, blocks=1
for 'db file sequential read' count=1 wait_time=19437
file#=a, block#=8480, blocks=1
for 'db file sequential read' count=1 wait_time=10440
file#=a, block#=8465, blocks=1
for 'db file sequential read' count=1 wait_time=60360
file#=a, block#=8476, blocks=1
for 'db file sequential read' count=1 wait_time=12820
file#=a, block#=8473, blocks=1
for 'db file sequential read' count=1 wait_time=13968
file#=a, block#=846d, blocks=1
temporary object counter: 2