Re: finding solution 4 this Special DeadLock

From: Yong Huang <yong321_at_yahoo.com>
Date: Tue, 19 Feb 2008 21:21:56 -0800 (PST)
Message-ID: <bd015c19-c985-4183-a099-d47ffa796d7e@q78g2000hsh.googlegroups.com>

On Feb 17, 1:53 am, meysam.khaya..._at_gmail.com wrote:
> Hi again
> i don't think my deadlock graph is easy as seems to solve and
> analyse!!
> i describe my problem again:
> 1) process # 211 hold table in row share mode lock so blocked it
> 2) process # 371 want to hold table in exclusive mode lock but it
> blocked so process # 371 goes to wait mode !
> 3) process # 211 want to hold table in row share mode lock again but
> it blocked by itself so goes to wait mode !
> (as you see it hold this table in row share mode then why again
> needed it and why goes to wait?????)
> 4) process # 371 again want to hold table in exclusive mode so goes to
> wait mode again!!!
>
> actually i think process # 211 blocked table itself and wait for
> itself !!!
> i know concept of DeadLock but i can't understand this deadlock graph
> i found very deadlock article but none of them can't help me to
> understand and analyse it,
>
> this is my deadlock graph in correct display:(changed space character
> with underLine character)
>
> ____________________---------Blocker(s)--------_---------
> Waiter(s)---------
> Resource_Name_______process_session_holds_waits_process_session_holds_waits
> TM-0000aa04-00000000_371______901___________X_____211_____940___________SS
> TM-0000aa04-00000000_211______940_____SS__________371_____901___________X
>
> Best Regards

One contrived case I can come up with involves autonomous transaction, but I'm sure there're other cases where you can reproduce as well. Test in 10.2.0.3.

Session 1:
lock table t in row share mode;
Session 2:
lock table t in exclusive mode;
Session 1:
declare pragma autonomous_transaction;
begin
 lock table t in row share mode;
end;
/

At this time, I get a deadlock and the graph is exactly like yours (I replaced some spaces with _):

Deadlock graph:

                       ---------Blocker(s)--------  ---------

Waiter(s)---------

Resource_Name__________process_session_holds_waits__process_session_holds_waits
TM-000396df-00000000________52____1051___________X_______20____1085__________SS
TM-000396df-00000000________20____1085____SS_____________52____1051___________X

session 1051: DID 0001-0034-0000002C session 1085: DID 0001-0014-00000054
session 1085: DID 0001-0014-00000054 session 1051: DID 0001-0034-0000002C
Rows waited on:
Session 1085: obj - rowid = 0000014F - AAAAFPAABAAAApkAAA   (dictionary objn - 335, file - 1, block - 2660, slot - 0) Session 1051: no row
Information on the OTHER waiting sessions: Session 1085:
  pid=20 serial=974 audsid=12536546 user: 599/YONG   O/S info: user: Owner, term: ROSE, ospid: 652:3940, machine: MSHOME \ROSE
            program: sqlplus.exe
  application name: SQL*Plus, hash value=3669949024   Current SQL Statement:
  LOCK TABLE T IN ROW SHARE MODE
End of information on OTHER waiting sessions. Current SQL statement for this session:
lock table t in exclusive mode

Can you check the application code and see if there's any PL/SQL code, triggers, or as Jonathan said, distributed transactions or parallel executions?

Yong Huang Received on Tue Feb 19 2008 - 23:21:56 CST