ORA-00600 "deadlock detected while waiting for resource"

最新推荐文章于 2023-02-25 00:40:21 发布

转载最新推荐文章于 2023-02-25 00:40:21 发布 · 556 阅读

本文记录了一次Oracle数据库中出现的死锁情况，详细展示了死锁发生时的Alert Log及Trace文件内容，包括涉及的进程、会话信息以及锁定资源等，并提供了Metalink文档中的解决建议。

Oracle Alert log报错信息：

Thread 1 cannot allocate new log, sequence 7356
Checkpoint not complete
Current log# 2 seq# 7355 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_2_3lqjqcb2_.log
Current log# 2 seq# 7355 mem# 1: /u01/product/oradata/WIND/redo02.log
Thread 1 advanced to log sequence 7356
Current log# 3 seq# 7356 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_3_3lqjqcn6_.log
Current log# 3 seq# 7356 mem# 1: /u01/product/oradata/WIND/redo03.log
Wed Jun 3 11:01:08 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18786.trc.
Wed Jun 3 11:01:09 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18780.trc.
Wed Jun 3 11:01:21 2009
Thread 1 advanced to log sequence 7357
Current log# 1 seq# 7357 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_1_3lqjqbz1_.log
Current log# 1 seq# 7357 mem# 1: /u01/product/oradata/WIND/redo01.log
Wed Jun 3 11:01:26 2009
ORA-00060: Deadlock detected. More info in file /u01/product/admin/wind/udump/wind_ora_18784.trc.
Wed Jun 3 11:01:41 2009
Thread 1 advanced to log sequence 7358
Current log# 2 seq# 7358 mem# 0: /data/flash_recovery_area/WIND/onlinelog/o1_mf_2_3lqjqcb2_.log
Current log# 2 seq# 7358 mem# 1: /u01/product/oradata/WIND/redo02.log
Wed Jun 3 11:02:08 2009
Thread 1 cannot allocate new log, sequence 7359
Checkpoint not complete

Oracle Trace 文件信息：

/u01/product/admin/wind/udump/wind_ora_18772.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /u01/product/oracle
System name:    Linux
Node name:      PDM8_NewDB
Release:        2.6.9-42.ELsmp
Version:        #1 SMP Wed Jul 12 23:32:02 EDT 2006
Machine:        x86_64
Instance name: wind
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 18772, image: oraclewind@PDM8_NewDB

*** 2009-06-03 10:59:05.019
*** SERVICE NAME:(SYS$USERS) 2009-06-03 10:59:04.921
*** SESSION ID:(1069.36453) 2009-06-03 10:59:04.921
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
INSERT INTO ControlBranch(classnameA2A2,updateCountA2,adHocStringIdentifier,
hasArchiveInfoarchiveInfo,classnamekeyC5,idA3C5,branchPointUfid,classnamekeyB5,
idA3B5,classnamekeyD5,idA3D5,oneOffVersionId,classnamekeyA5,idA3A5,sessionOwner,
markForDeleteA2,versionId,viewId,wipState,updateStampA2,createStampA2,modifyStampA2,idA2A2)
VALUES ('wt.vc.ControlBranch',1,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)

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-00020006-0000ee35        34    1069     X             36    1066           S
TX-0009004f-0000f851        36    1066     X             34    1069           S
session 1069: DID 0001-0022-0001F25E    session 1066: DID 0001-0024-00000014
session 1066: DID 0001-0024-00000014    session 1069: DID 0001-0022-0001F25E
Rows waited on:
Session 1066: obj - rowid = 00017071 - AAAXBxABTAAACpzAAA
(dictionary objn - 94321, file - 83, block - 10867, slot - 0)
Session 1069: obj - rowid = 00016FCF - AAAW/PABcAAABIDAAA
(dictionary objn - 94159, file - 92, block - 4611, slot - 0)
Information on the OTHER waiting sessions:
Session 1066:
pid=36 serial=1171 audsid=2795304 user: 40/PDM8
O/S info: user: , term: , ospid: 1234, machine: pdm8ap1
            program:
Current SQL Statement:

INSERT INTO ControlBranch(classnameA2A2,updateCountA2,adHocStringIdentifier,hasArchiveInfoarchiveInfo,classnamekeyC5,idA3C5,branchPointUfid,classnamekeyB5,idA3B5,classnamekeyD5,idA3D5,oneOffVersionId,classnamekeyA5,idA3A5,sessionOwner,markForDeleteA2,versionId,viewId,wipState,updateStampA2,createStampA2,modifyStampA2,idA2A2) VALUES ('wt.vc.ControlBranch',1,:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21)
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
Process global information:
     process: 0x1fe638ea0, call: 0x1f5de63e8, xact: 0x1f8d6ba30, curses: 0x1fe9d2c08, usrses: 0x1fe9d2c08
----------------------------------------
SO: 0x1fe638ea0, type: 2, owner: (nil), flag: INIT/-/-/0x00
(process) Oracle pid=34, calls cur/top: 0x1f5de63e8/0x1f5de63e8, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
(post info) last post received: 0 0 112
              last post received-location: kcbzww
              last process to post me: 1fe63cde0 141 0
              last post sent: 0 0 112
              last post sent-location: kcbzww
              last process posted by me: 1fe63cde0 141 0
    (latch info) wait_event=0 bits=10
      holding    (efd=13) 600096b0 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 0x00000001FE5EDC10 to 0x00000001FE5EDC30
1FE5EDC10 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
    Process Group: DEFAULT, pseudo proc: 0x1fe727b30
    O/S info: user: oracle, term: UNKNOWN, ospid: 18772
    OSD pid info: Unix process pid: 18772, image: oraclewind@PDM8_NewDB
Dump of memory from 0x00000001FE5F3320 to 0x00000001FE5F3528
1FE5F3320 00000016 00000000 F89568D8 00000001 [.........h......]
1FE5F3330 00000007 0003139D F89569B8 00000001 [.........i......]
1FE5F3340 00000007 0003139D F8956A98 00000001 [.........j......]
1FE5F3350 00000007 0003139D F8956B78 00000001 [........xk......]
1FE5F3360 00000007 0003139D F8956C58 00000001 [........Xl......]
1FE5F3370 00000007 0003139D F8956D38 00000001 [........8m......]
1FE5F3380 00000007 0003139D F8956E18 00000001 [.........n......]
1FE5F3390 00000007 0003139D F8956EF8 00000001 [.........n......]
1FE5F33A0 00000007 0003139D F8956FD8 00000001 [.........o......]
1FE5F33B0 00000007 0003139D F89570D0 00000001 [.........p......]
1FE5F33C0 00000007 0003139D F89571B0 00000001 [.........q......]
1FE5F33D0 00000007 0003139D F8957290 00000001 [.........r......]
1FE5F33E0 00000007 0003139D F8957370 00000001 [........ps......]
1FE5F33F0 00000007 0003139D F8957450 00000001 [........Pt......]
1FE5F3400 00000007 0003139D F8957610 00000001 [.........v......]
1FE5F3410 00000007 0003139D F89577D0 00000001 [.........w......]
1FE5F3420 00000007 0003139D F895A930 00000001 [........0.......]
1FE5F3430 00000007 0003139D F8960F58 00000001 [........X.......]
1FE5F3440 00000007 0003139D F8961818 00000001 [................]
1FE5F3450 00000007 0003139D F89618F8 00000001 [................]
1FE5F3460 00000007 0003139D F8961E50 00000001 [........P.......]
1FE5F3470 00000007 0003139D F8961F30 00000001 [........0.......]
1FE5F3480 00000007 0003139D F894E048 00000001 [........H.......]
1FE5F3490 00000007 0003139D F894E128 00000001 [........(.......]
1FE5F34A0 00000007 0003139D F894E680 00000001 [................]
1FE5F34B0 00000007 0003139D F894F9D8 00000001 [................]

Oracle Metalink 信息：

---------------------------------------------------------------------------

Error: ORA 60
Text:   deadlock detected while waiting for resource
-------------------------------------------------------------------------------
Cause: Transactions deadlock one another waiting for resources
Action: Look at the trace file to see the transactions and resources
        involved. Retry if necessary.

*** Important: The notes below are for experienced users - See Note 22080.1

** For CUSTOMER issues with ORA-60 send them:      Note 62365.1
   This details the most common ORA-60 scenarios.

Explanation:
        A deadlock has been detected so your statement has been rolled
        back to allow the other party to continue.

Diagnosis:
        Note the lock TYPES and MODES in the DEADLOCK graph.
   The most common deadlocks involve one of the following:
Lock type Mode Problem
   TX      X   Application row lock deadlock.See below.
   TX        S   Many reasons - see Note 62354.1
   TM     SSX Usually unindex foreign key constraints.

TX requested X mode:
~~~~~~~~~~~~~~~~~~~~~
        This is usually an application design issue in that transactions
        are modifying tables A and B in opposite orders.
        Eg: 1 updates A
            2 updates B
            1 tries to update B but blocks
            2 tries to update A but blocks
            ** Deadlock as neither 1 nor 2 will ever complete.

        a) Has ANYTHING in the schema been changed (Eg: A foreign key INDEX
           may have been dropped) as this could affect locking of tables.

        b) There should be a user trace with the deadlock TX information
           in USER_DUMP_DEST. This will indicate who was involved in the
           deadlock

        c) Is the insert into a clustered table ? If so see Bug 197942

        d) What are MAXTRANS / INITRANS for the tables in the deadlock ?

Articles / Known Issues:
       ORA-60 against UET$ (Fixed 7.2)       Bug 231455
       ORA-60 from ANALYZE ... VALIDATE ... command:
NOTE: It is possible to get this if the dictionary has
been analyzed and has statistics present. Delete
the statistics.

V6: If this occurs on startup it is likely that a file may be missing.
        There should be a trace file produced and an entry in the alert log
        which will tell you the location of the problem file. Either:
a) restore access to the datafiles
OR b) offline the datafiles and drop the tablespace when it
   opens to clean up the data dictionary.
   (obviously you have lost all the data in this case)