ORA-00060

CD-ORACLE

环境：redhat 6.5 + oracle 11.2.0.4

告警日志当中出现死锁关键字：

Thu Aug 18 14:18:10 2016
ORA-00060: Deadlock detected. More info in file /oracle/app/diag/rdbms/migdb/MIGDB/trace/MIGDB_ora_16931.trc.
Thu Aug 18 14:18:46 2016

希望刘大帮忙给分析分析相关的trace文件。

Liu Maclean(刘相兵

FYI

Trace file /oracle/app/diag/rdbms/migdb/MIGDB/trace/MIGDB_ora_16943.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /oracle/app/product/11.2.0/db_1
System name:        Linux
Node name:        sunline09
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine:        x86_64
Instance name: MIGDB
Redo thread mounted by this instance: 1
Oracle process number: 283
Unix process pid: 16943, image: oracle@sunline09


*** 2016-08-18 14:32:54.305
*** SESSION ID:(3850.6574) 2016-08-18 14:32:54.305
*** CLIENT ID:() 2016-08-18 14:32:54.305
*** SERVICE NAME:(SYS$USERS) 2016-08-18 14:32:54.305
*** MODULE NAME:(JDBC Thin Client) 2016-08-18 14:32:54.305
*** ACTION NAME:() 2016-08-18 14:32:54.305



*** 2016-08-18 14:32:54.305
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

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-002c001b-0000ad2e       283    3850     X            304    2285           S
TX-00040002-000114b9       304    2285     X            283    3850           S

session 3850: DID 0001-011B-00000432        session 2285: DID 0001-0130-0000026A
session 2285: DID 0001-0130-0000026A        session 3850: DID 0001-011B-00000432

Rows waited on:
  Session 3850: no row
  Session 2285: obj - rowid = 00020E45 - AAAjVTAAFAAMnARAAA
  (dictionary objn - 134725, file - 5, block - 3305489, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 2285:
  sid: 2285 ser: 8821 audsid: 8249363 user: 85/V7MIG flags: 0x8000045
  pid: 304 O/S info: user: oracle, term: UNKNOWN, ospid: 16987
    image: oracle@sunline09
  client details:
    O/S info: user: v7mig, term: unknown, ospid: 1234
    machine: sunline08 program: JDBC Thin Client
    application name: JDBC Thin Client, hash value=2546894660
  current SQL:
  INSERT INTO KDPA_KEHUZH VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 )

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=121zd9y9b7vgr) -----
INSERT INTO KDPA_KEHUZH VALUES (:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 )
----- PL/SQL Stack -----
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
0x403868638       352  procedure V7MIG.PRCJBXINX

Liu Maclean(刘相兵

KDPA_KEHUZH 上是否有唯一索引？
(:B1 ,:B2 ,:B3 ,:B4 ,:B5 ,:B6 ,:B7 ,:B8 ,:B9 ,:B10 ,:B11 ,:B12 ,:B13 ,:B14 ,:B15 ,:B16 ,:B17 ,:B18 ,:B19 ,:B20 ,:B21 ,:B22 ,:B23 ,:B24 ,:B25 ,:B26 ,:B27 ,:B28 ,:B29 ,:B30 ,:B31 ,:B32 ,:B33 ,:B34 ,:B35 ,:B36 ,:B37 ,:B38 ,:B39 ,:B40 ,:B41 ,:B42 ,:B43 ,:B44 ,:B45 )

2个session执行的SQL 绑定变量可能一样 导致索引tx row lock ，如果有唯一索引 把唯一索引干掉试试

CD-ORACLE

上面存在两条索引，一条为UNIQUE INDEX "V7MIG"."KDPA_KEHUZH_IDX1"，另一条为INDEX "V7MIG"."KDPA_KEHUZH_IDX2"，我这边先去试一下，然后再给回复。