ORA-000060 Deadlock detected 問題解決

ORA-000060 Deadlock detected

RHEL 4.5
DB : 9.2.0.6
ERP: 11.5.0

Wed Nov 17 16:22:47 2010
ORA-000060: Deadlock detected. More info in file /MIS/misdb/9.2.0/admin/MIS_erpcn/udump/mis_ora_142.trc.

Dump file /MIS/misdb/9.2.0/admin/MIS_erpcn/udump/mis_ora_142.trc
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ORACLE_HOME = /MIS/misdb/9.2.0
System name: Linux
Node name: erpcn
Release: 2.4.21-278-smp
Version: #1 SMP Mon Mar 7 09:17:29 UTC 2005
Machine: i686
Instance name: MIS
Redo thread mounted by this instance: 1
Oracle process number: 49
Unix process pid: 142, image: oracle@erpcn (TNS V1-V3)

*** SESSION ID:(546.33340) 2010-11-17 16:22:46.793
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE FDC.FDC_MOVE_TRANSACTION_INTERFACE SET DELETE_FLAG = 'N' WHERE DELETE_FLAG = 'Y' AND STATUS <> 'I'
----- PL/SQL Call Stack -----
object line object
handle number name
0xb39973d4 64 package body APPS.SB_DELETE_TEMPTABLE
0xb18e8f0c 1 anonymous block
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-00010005-000ebf79 49 546 X 200 537 X
TX-00120013-0006c746 200 537 X 49 546 X
session 546: DID 0001-0031-0000FFCD session 537: DID 0001-00C8-000061E4

[@more@]session 537: DID 0001-00C8-000061E4 session 546: DID 0001-0031-0000FFCD
Rows waited on:
Session 537: obj - rowid = 00040E62 - AABA5iAHCAAAfmpAA2
(dictionary objn - 265826, file - 450, block - 129449, slot - 54)
Session 546: obj - rowid = 00040E62 - AABA5iAHCAAAfmuABV
(dictionary objn - 265826, file - 450, block - 129454, slot - 85)
Information on the OTHER waiting sessions:
Session 537:
pid=200 serial=64359 audsid=60128922 user: 282/FDC
O/S info: user: prodmgr, term: , ospid: 669884, machine: ERP
program: oracle@ERP (TNS V1-V3)
application name: oracle@ERP (TNS V1-V3), hash value=0
Current SQL Statement:
UPDATE "FDC"."FDC_MOVE_TRANSACTION_INTERFACE" "A1" SET "STATUS" = 'I' WHERE "A1"."LINE_ID"=:B1
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
process: 0xab8ec704, call: 0xaeec3b80, xact: 0xae43d200, curses: 0xabb118f8, usrses: 0xabb118f8
----------------------------------------
SO: 0xab8ec704, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=49, calls cur/top: 0xaeec3b80/0xaeff35a4, flag: (0) -
int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 50
last post received-location: kcbzww
last process to post me: ab90fae8 46 0
last post sent: 0 0 16
last post sent-location: ksasnd
last process posted by me: ab8e1ef8 1 6
(latch info) wait_event=0 bits=10
holding 50006554 Parent+children enqueue hash chains level=4
Location from where latch is held: ksqcmi: kslgpl:
Context saved from call: 0
state=busy
recovery area:
Dump of memory from 0xAB8E0F48 to 0xAB8E0F50
AB8E0F40 00000000 00000000
........
client info: 0
application name: SBR0071, hash value=2826633068
action name: Concurrent Request, hash value=1021472160
........

分析:
引數近段時間無大的調整.所出現TX鎖
UPDATE FDC.FDC_MOVE_TRANSACTION_INTERFACE SET DELETE_FLAG = 'N' WHERE DELETE_FLAG = 'Y' AND STATUS <> 'I'
語句是什麼引起?

解決:
1.修改應用SQL先觀察一下.
/*update fdc.fdc_move_transaction_interface
set delete_flag = 'N'
where delete_flag = 'Y' and status <> 'I'; */

--2010年11月18日 Fix
FOR zs IN upa_fdc LOOP
UPDATE fdc.fdc_move_transaction_interface SET delete_flag = 'N'
WHERE LINE_ID=zs.LINE_ID;
END LOOP;