deadlock interpretation

[dasdeba]

Oracle version - 9i rel 2
O/S version - Solaris 9

Hi,

I have found a deadlock which happened one day ago. I have pasted that trace file. Now I have to know two things.

1) How can I detect whether the following deadlock is due to row-level locking or transaction layer locking (since I am already one day delay i.e I am trying to find out the reason on 1day after the day lock happened)?

2) I am able to find out the row having rowid 'AAAHzHAAnAAAA7hAAP'. But I am not able to find
out any row having rowid 'AAAH/gAAiAAAByOAA0'. Why? Someone has deleted that row ? A rowid
can have '/' ?

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE SSP_PROGRAMME_SCHEDULE
SET modify_dt=SYSDATE
WHERE SCHD_KEY=:b1
----- PL/SQL Call Stack -----
object line object
handle number name
94f1b1d4 2 STOWNER.TR_SSP_PROGRAMME_SCHEDULE
994a0858 268 package body STOWNER.SSP_PROCESS_SCHEDULE_PKG
994a0858 2549 package body STOWNER.SSP_PROCESS_SCHEDULE_PKG
994a0858 6202 package body STOWNER.SSP_PROCESS_SCHEDULE_PKG
8f90b6d0 1 anonymous block
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-00030007-0001e95b 60 60 X 45 136 X
TX-00060025-0000ff93 45 136 X 60 60 X
session 60: DID 0001-003C-00000002 session 136: DID 0001-002D-00000002
session 136: DID 0001-002D-00000002 session 60: DID 0001-003C-00000002
Rows waited on:
Session 136: obj - rowid = 00007FE0 - AAAH/gAAiAAAByOAA0
(dictionary objn - 32736, file - 34, block - 7310, slot - 52)
Session 60: obj - rowid = 00007CC7 - AAAHzHAAnAAAA7hAAP
(dictionary objn - 31943, file - 39, block - 3809, slot - 15)
Information on the OTHER waiting sessions:
Session 136:
pid=45 serial=1983 audsid=47043 user: 44/
O/S info: user: wsadmin, term: , ospid: 19810, machine: piranha
program: java@piranha (TNS V1-V3)
application name: java@piranha (TNS V1-V3), hash value=0
Current SQL Statement:
DELETE FROM SSP_PROGRAMME_PART
WHERE schd_episode_key = :b1
End of information on OTHER waiting sessions.

Regards

Deba

[marist89]

http://metalink.oracle.com/metalink/...T&p_id=62365.1

[dasdeba]

Thanks for the reply.

Your answer is not clear to me. I have seen the metalink document. But still I am not getting the answers. Would you like to explain pl.

In both the cases (metalink document and in my case) the locks are same . Then why I will not get the row against the above rowid ?

And what about the firts question ?

Regards

Deba

[sambavan]

Check the package STOWNER.SSP_PROCESS_SCHEDULE_PKG to what exactly is happening

Sam

[tamilselvan]

Answer to the 1st Question:
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-00030007-0001e95b 60 60 X 45 136 X
TX-00060025-0000ff93 45 136 X 60 60 X

It is very clear that the sessions (60 and 130) held resources that were needed by other sessions. Check your application whether it is using order of locking the tables are same or not. If not, change it.

Answer to 2nd question:
A rowid cannot have /. The original character might have been overwritten by '/' due to accident.

Tamil

[dasdeba]

Tamil,

thanx for the reply. What you have told me that I know and its is very standard one. I think you have not gone through my questions properly.

q1> deadlock is due to row-level locking or transaction layer locking?
How to understand whether it is row-level locking or transaction
layer locking?

q2> What I have pasted , that is 100% correct. '/' is there in rowid.
Since it is starnge to me , that's why I am asking.

Regards

Deba

[tamilselvan]

It was due to transaction layer lock.
You can see the TX locks on both sessions.

[dasdeba]

Hi Tamil,

you are saying that "It was due to transaction layer lock.
You can see the TX locks on both sessions.". But I have seen a deadlock trace file which contains TX lock and that deadlock was due to row level locking.

If TX is meant for Transaction layer locking , then what indicates in the trace file that the deadlock is due to row level locking.

Deba

[tamilselvan]

Do you see ora-60 error in the beginning of trace file?