| ORA-00060: Deadlock detected [message #284430] |
Thu, 29 November 2007 11:44  |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
I've solved ORA-00060 in the past but this one has me stumped.
*** 2007-11-28 15:18:32.553
*** SERVICE NAME:(SYS$USERS) 2007-11-28 15:18:32.544
*** SESSION ID:(610.22538) 2007-11-28 15:18:32.544
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
delete from users where user_id = :1
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-0001a1e7-00000000 49 610 SX 47 205 SX SSX
TM-00010d30-00000000 47 205 SX 49 610 SX SSX
session 610: DID 0001-0031-0000C82E session 205: DID 0001-002F-0000A0B5
session 205: DID 0001-002F-0000A0B5 session 610: DID 0001-0031-0000C82E
Rows waited on:
Session 205: no row
Session 610: obj - rowid = 0001A1E3 - AAAaHjAAFAACiQIAAA
(dictionary objn - 106979, file - 5, block - 664584, slot - 0)
Information on the OTHER waiting sessions:
Session 205:
pid=47 serial=28726 audsid=27106578 user: 718/SECURITY_USER
O/S info: user: , term: , ospid: 1234, machine: vrx12.abc.com
program:
Current SQL Statement:
delete from dashboard where owner_user_id = :1
End of information on OTHER waiting sessions.
===================================================
In previous cases the trace file has always contained 2 SQL statements.
The above extract is from the 3rd ORA-00060 this week.
Each has contained the single SQL statement - "delete from dashboard where owner_user_id = :1".
Complicating my search for clues is the fact that I don't know the actual value of the bind variable being used &
when I query the DASHBOARD table using the ROWID in the trace file I get the following results:
SQL> select * from security.dashboard where rowid = chartorowid('AAAaHjAAFAACiQIAAA');
no rows selected
SQL>
I acknowledge we have an application problem.
I've been asked to assist with fault isolation & correction.
AFAIK, these errors occur when an application user does something, but we don't know who or what exactly causes this error.
Any clues, ideas, suggestions on how to capture more details would be MOST welcomed.
Thanks In Advance
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: ORA-00060: Deadlock detected [message #284456 is a reply to message #284430] |
Thu, 29 November 2007 14:11  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
I am guessing a bit here, but I think the dump is telling you most of what you need to know. In particular the deadlock graph has good info.
It says there are two sessions blocking each other. It tells you in each case which two sessions these are, and it is showing you the sql each session has just executed.
If I had to guess, I'd guess that the application is looping through a list of users and deleting them one at a time. However, the same code is running on two different sessions and the order of users being deleted is not the same.
If so, a simple fix would be to simple use and order by or otherwise equivelant mechanism to sort the list of users you are deleting, so that they are always deleted in the same order. There are still some situations where you might have deadlock, but this should fix 99% of what you are seeing... if this is the problem.
An alternative and also easy fix would be to have the code acquire a user defined lock (dbms_lock), before you delete any rows, named after the user you are trying to delete data for. This will cause your delete processes to serialize based on the user. Then only one process at a time will be allowed to delete data for any single user. If your developer has not used dbms_lock before, have him/her read up on it first. Not hard to use.
Good luck, Kevin.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
