如何找出导致Oracle死锁的原因 [英] How to find out the cause of an Oracle deadlock

问题描述

当与多个用户一起测试我的应用程序时，我遇到了ORA-00060死锁错误.我不确定如何确定此错误的原因，因此，如果有人可以帮助我，将不胜感激.

When testing my application with multiple users, I have encountered an ORA-00060 deadlock error. I'm not sure how to determine the cause of this error, so if anyone could help me it would be greatly appreciated.

我查看了为此事件生成的跟踪文件，它向我显示了导致此问题的查询:

I looked in the trace file generated for this event and it shows me which query caused this:

UPDATE TABLE_A SET CK=CK+1 WHERE A_ID=(
    SELECT A_ID FROM TABLE_B WHERE SOME_COLUMN=:SOMECOLUMN
)

它也产生了这个:

[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-0002a675-00000000        51     200    SX   SSX       52      22    SX   SSX
TM-0002a675-00000000        52      22    SX   SSX       51     200    SX   SSX

session 200: DID 0001-0033-0000014A session 22: DID 0001-0034-0000005A 
session 22: DID 0001-0034-0000005A  session 200: DID 0001-0033-0000014A 

Rows waited on:
  Session 200: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
  (dictionary objn - 173745, file - 20, block - 339, slot - 0)
  Session 22: obj - rowid = 0002A6B1 - AAAqaxAAUAAAAFTAAA
  (dictionary objn - 173745, file - 20, block - 339, slot - 0)

如何使用以上信息来找出导致问题的原因? TABLE_A或TABLE_B上都没有任何未索引的外键(我相信这通常是导致这种类型错误的原因).

How do I use the above information to find out what caused the problem? There aren't any unindexed foreign keys on either TABLE_A or TABLE_B (which I believe is commonly the cause of this type of error).

当我运行此查询时:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE 
FROM DBA_OBJECTS 
WHERE OBJECT_ID = 173745;

我在完全不同的表上获得了主键的名称！这可能是导致死锁的线索吗?

I get the name of a primary key on a completely different table! Could this be a clue as to why the deadlock is happening?

推荐答案

因为涉及的排队是'TM'排队，并且由于持有锁的模式是'SX'而等待锁的模式是我可以绝对肯定地告诉您"SSX"，这是由于外键关系导致引用表(子表)列未建立索引.如果您寻找未索引的外键并添加索引，则此问题应会消失.

Because the enqueue involved is a 'TM' enqueue, and because the mode the lock is being held is 'SX' and the mode the lock is waited on is 'SSX', I can tell you, with absolute certainty, that this is due to a foreign key relationship, where the referring table (child table) column is not indexed. If you look for unindexed foreign keys and add indexes, this problem should disappear.

希望有帮助....

P.S.我的演示文稿了解和解释死锁，或如何处理ORA-00060"可在OakTable网站上下载，网址为 http://www.oaktable.net .在贡献的文件"部分中查找.

P.S. My presentation, "Understanding and Interpreting Deadlocks, or, What to do with an ORA-00060", is available for download at the OakTable site, http://www.oaktable.net. Look in the "Contributed Files" section.

这篇关于如何找出导致Oracle死锁的原因的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！