Re: strange dead lock question

lsllcm wrote:
> *** 2007-08-10 15:27:04.440
> *** SESSION ID:(26.789) 2007-08-10 15:27:04.439
> DEADLOCK DETECTED ( ORA-00060 )
> 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-00030002-000216dc 20 26 X 16
> 35 S
> TX-00010002-00042f40 16 35 X 13
> 13 X
> TX-00030002-000216dc 13 13 X 20
> 26 X
> session 26: DID 0001-0014-00000004 session 35: DID 0001-0010-00000015
> session 35: DID 0001-0010-00000015 session 13: DID 0001-000D-00000004
> session 13: DID 0001-000D-00000004 session 26: DID 0001-0014-00000004
> Rows waited on:
> Session 35: obj - rowid = 0000ADA2 - AAAK2iABMAAAAAAAAA
> (dictionary objn - 44450, file - 76, block - 0, slot - 0)
> Session 13: obj - rowid = 0000B090 - AAALCQABMAAAGNfAAA
> (dictionary objn - 45200, file - 76, block - 25439, slot - 0)
> Session 26: obj - rowid = 0000AFAC - AAAK+sABNAAAEcMAAT
> (dictionary objn - 44972, file - 77, block - 18188, slot - 19)
> Information on the OTHER waiting sessions:
> Session 35:
> pid=16 serial=34 audsid=403683 user: 84/SPOKANE
> O/S info: user: , term: , ospid: 1234, machine: test02-server
> program:
> Current SQL Statement:
>
>
> The question is session 26 does not hold any locks, but it blocked
> session 35.
>
> The session 35 is update unique index columns

Create the deadlock situation and run this:

SELECT o.object_name, l.lock_type, l.mode_held, l.mode_requested, l.blocking_others
FROM dba_lock l, user_objects o
WHERE l.lock_id1 = o.object_id
AND session_id = 26;

SELECT o.object_name, l.lock_type, l.mode_held, l.mode_requested, l.blocking_others
FROM dba_lock l, user_objects o
WHERE l.lock_id1 = o.object_id
AND session_id = 35;

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org