从 oracle 跟踪文件中查找死锁错误的原因
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17358088/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding cause of deadlock error from oracle trace file
提问by shashikanthb
I have been getting this "ora-00060 deadlock detected while waiting for resource" error often now in my application when multiple users are using the application. I have got the trace file from the oracle Admin, but need help in reading it. Below is bits of data from the trace file, which i hope would help in locating the cause.
当多个用户正在使用该应用程序时，我的应用程序中经常出现“在等待资源时检测到 ora-00060 死锁”错误。我从 oracle Admin 那里得到了跟踪文件，但在阅读它时需要帮助。以下是跟踪文件中的一些数据，我希望这些数据有助于查找原因。
*** 2013-06-25 09:37:35.324
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
TM-000151a2-00000000 210 72 SX SSX 208 24 SX SSX
TM-000151a2-00000000 208 24 SX SSX 210 72 SX SSX
session 72: DID 0001-00D2-000000C6 session 24: DID 0001-00D0-00000043
session 24: DID 0001-00D0-00000043 session 72: DID 0001-00D2-000000C6
Rows waited on:
Session 72: no row
Session 24: no row
----- Information for the OTHER waiting sessions -----
Session 24:
sid: 24 ser: 45245 audsid: 31660323 user: 90/USER
flags: (0x45) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 208 O/S info: user: zgrid, term: UNKNOWN, ospid: 2439
image: [email protected]
client details:
O/S info: user: , term: , ospid: 1234
machine: xyz.local program:
current SQL:
delete from EMPLOYEE where EMP_ID=:1
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=dyfg1wd8xa9qt) -----
delete from EMPLOYEE where EMP_ID=:1
===================================================
I would appreciate if some one can tell me what the "Deadlock graph::" is saying. Also the rows waited on section says no rows.
如果有人能告诉我“死锁图::”在说什么，我将不胜感激。等待的行部分也说没有行。
I also read in some blogs that "sqltxt" section from the trace file can suggest the cause. Below is the query i see in that section.
我还在一些博客中读到跟踪文件中的“sqltxt”部分可以暗示原因。下面是我在该部分看到的查询。
select /*+ all_rows */ count(1) from "USERS"."EMPLOYEE_SALARY" where EMPSAL_EMP_ID=:1
The employee_salary table has foreignkey constraint on EMPSAL_EMP_ID column.
employee_salary 表在 EMPSAL_EMP_ID 列上有外键约束。
The sql hint says "all_rows", so does it mean that this table gets table level lock when deleting records from employee table? i dont have an index on the foreign key column currently. Would adding an index on this column help?
sql提示说“all_rows”，那么是不是表示这个表在从employee表中删除记录时得到了表级锁？我目前在外键列上没有索引。在此列上添加索引有帮助吗？
Kindly post, in case any more information is need.
请张贴，以防万一需要更多信息。
Thanks
谢谢
回答by ThinkJet
First of all, selectstatement never lock anything in Oracle, just uses last available consistent version of data. It's not a case for select ... for updatewhich locks data like updatesince Oracle 9i, but there are no for updateclause in the query from question.
首先，selectOracle 中的语句从不锁定任何内容，只使用最后可用的一致版本的数据。这不是自 Oracle 9i 以来select ... for update锁定数据的情况update，但for update问题中的查询中没有子句。
Resource Name process session holds waits process session holds waits
TM-000151a2-00000000 210 72 SX SSX 208 24 SX SSX
Session #72 holds table-level lock (TM) with "Row Exclusive" type (SX) and want to acquire "Share Row Exclusive" (SSX) lock on same table. This session blocked by Session #24 which already holds table-level lock of a same type (SX) and waits while SSX lock would be available.
会话 #72 持有具有“行独占”类型 (SX) 的表级锁 (TM)，并希望在同一表上获取“共享行独占”(SSX) 锁。此会话已被会话 #24 阻止，该会话已持有相同类型 (SX) 的表级锁，并在 SSX 锁可用时等待。
Resource Name process session holds waits process session holds waits
TM-000151a2-00000000 208 24 SX SSX 210 72 SX SSX
This (second row) demonstrates exactly same situation, but in opposite direction: Session #24 waits for SSX lock become available, but blocked by Session #72 which already holds SX lock on same table.
这（第二行）演示了完全相同的情况，但方向相反：会话 #24 等待 SSX 锁变得可用，但被会话 #72 阻止，该会话已经在同一个表上持有 SX 锁。
So, Sessions #24 and Session #72 blocks each other: deadlock happens.
因此，会话 #24 和会话 #72 相互阻塞：发生死锁。
Both lock types (SX and SSX) are table-level locks.
To understand the situation I recommend to read this articleby Franck Pachot.
两种锁类型（SX 和 SSX）都是表级锁。
要了解这种情况，我建议阅读Franck Pachot 的这篇文章。
Below is citation from this article, which directly relevant to your situation(note that SSX and SRX abbreviations are equivalent):
以下是本文的引文，与您的情况直接相关（请注意，SSX 和 SRX 缩写是等效的）：
Referential integrity also acquires TM locks. For example, the common issue with unindexed foreign keys leads to S locks on child table when you issue a delete, or update on the key, on the parent table. This is because without an index, Oracle has no single lower level resource to lock in order to prevent a concurrent insert that can violate the referential integrity.
When the foreign key columns are the leading columns in a regular index, then the first index entry with the parent value can be used as a single resource and locked with a row level TX lock.
And what if referential integrity has an on delete cascade? In addition to the S mode, there is the intention to update rows in the child table, as with Row X (RX) mode. This is where the share row exclusive (SRX) occurs: S+RX=SRX.
参照完整性也需要 TM 锁。例如，当您对父表的键发出删除或更新时，未索引外键的常见问题会导致子表上的 S 锁。这是因为如果没有索引，Oracle 就没有单个较低级别的资源要锁定，以防止可能违反参照完整性的并发插入。
当外键列是常规索引中的前导列时，具有父值的第一个索引条目可以用作单个资源并使用行级 TX 锁进行锁定。
如果参照完整性有一个删除级联呢？除了 S 模式之外，还有更新子表中的行的意图，就像 Row X (RX) 模式一样。这是共享行独占 (SRX) 发生的地方：S+RX=SRX。
So, most probable variant is that Session #72 and Session #24 deletes some rows in EMPLOYEEtable at same time, and there are on delete cascadeconstraint for EMPSAL_EMP_IDin conjunction with absence of index on EMPLOYEE_SALARYtable in which EMPSAL_EMP_IDcolumn listed first.
因此，最可能的变体是会话#72 和会话#24 同时删除EMPLOYEE表中的某些行，并且on delete cascade对于首先列出的表中EMPSAL_EMP_ID没有索引的情况存在约束。EMPLOYEE_SALARYEMPSAL_EMP_ID
