racle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1238M/3070M, Ph+PgF:3519M/4967M, VA:554M/2047M
Instance name: orclRedo thread mounted by this instance: 1Oracle process number: 34Windows thread id: 2164, image: ORACLE.EXE (SHAD)
*** 2011-12-02 15:48:13.531
*** SERVICE NAME:(SYS$USERS) 2011-12-02 15:48:13.406
*** SESSION ID:(254.16465) 2011-12-02 15:48:13.406
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
UPDATE office_workflowinstance SET WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-432700003301}',PARENTWORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-ED46-65CD00001DF4}' WHERE WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-432700003301}'
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
TM-00033bb9-00000000 34 254 SX SSX 43 228 SX SSX
TM-00033bb9-00000000 43 228 SX SSX 34 254 SX SSX
session 254: DID 0001-0022-000004F9 session 228: DID 0001-002B-000000B2
session 228: DID 0001-002B-000000B2 session 254: DID 0001-0022-000004F9
Rows waited on:
Session 228: obj - rowid = 00033BB0 - AAAzuwAAFAAG8UeAAA
(dictionary objn - 211888, file - 5, block - 1819934, slot - 0)
Session 254: obj - rowid = 00033B5E - AAAzteAAFAAGfysAAA
(dictionary objn - 211806, file - 5, block - 1703084, slot - 0)
Information on the OTHER waiting sessions:
Session 228:
pid=43 serial=104 audsid=5822812 user: 56/RISENET
O/S info: user: , term: , ospid: 1234, machine: app
program:
Current SQL Statement:
UPDATE office_workflowinstance SET WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}',PARENTWORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-E8B9-91EE0000681A}' WHERE WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}'
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
大家分析一下。更新操作能导致表被挂起,从而导致执行insert into语句挂起(一直执行状态)吗?
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 8 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:1238M/3070M, Ph+PgF:3519M/4967M, VA:554M/2047M
Instance name: orclRedo thread mounted by this instance: 1Oracle process number: 34Windows thread id: 2164, image: ORACLE.EXE (SHAD)
*** 2011-12-02 15:48:13.531
*** SERVICE NAME:(SYS$USERS) 2011-12-02 15:48:13.406
*** SESSION ID:(254.16465) 2011-12-02 15:48:13.406
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
UPDATE office_workflowinstance SET WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-432700003301}',PARENTWORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-ED46-65CD00001DF4}' WHERE WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-432700003301}'
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
TM-00033bb9-00000000 34 254 SX SSX 43 228 SX SSX
TM-00033bb9-00000000 43 228 SX SSX 34 254 SX SSX
session 254: DID 0001-0022-000004F9 session 228: DID 0001-002B-000000B2
session 228: DID 0001-002B-000000B2 session 254: DID 0001-0022-000004F9
Rows waited on:
Session 228: obj - rowid = 00033BB0 - AAAzuwAAFAAG8UeAAA
(dictionary objn - 211888, file - 5, block - 1819934, slot - 0)
Session 254: obj - rowid = 00033B5E - AAAzteAAFAAGfysAAA
(dictionary objn - 211806, file - 5, block - 1703084, slot - 0)
Information on the OTHER waiting sessions:
Session 228:
pid=43 serial=104 audsid=5822812 user: 56/RISENET
O/S info: user: , term: , ospid: 1234, machine: app
program:
Current SQL Statement:
UPDATE office_workflowinstance SET WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}',PARENTWORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-E8B9-91EE0000681A}' WHERE WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}'
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
大家分析一下。更新操作能导致表被挂起,从而导致执行insert into语句挂起(一直执行状态)吗?
检测到死锁。
--------------------
UPDATE office_workflowinstance SET WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}',PARENTWORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-E8B9-91EE0000681A}' WHERE WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}'--------------------
WORKFLOWINSTANCE_GUID 不知道是不是主键?更新主键是数据库大忌。。
或是外键未加索引。。
是主键
如果改成
UPDATE office_workflowinstance SET PARENTWORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-E8B9-91EE0000681A}' WHERE WORKFLOWINSTANCE_GUID='{AC10646E-FFFF-FFFF-FCBE-47730000330B}'这样会不会好点!