Home » RDBMS Server » Server Administration » ORA-00060: Deadlock detected
ORA-00060: Deadlock detected [message #284430] Thu, 29 November 2007 11:44 Go to next message
BlackSwan
Messages: 25050
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 #284432 is a reply to message #284430] Thu, 29 November 2007 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Things that come into mind:
- foreign keys (index, on delete cascade)?
- triggers (multiple same type? possibly with autonomous transaction option? on child table?)

Regards
Michel

[Updated on: Thu, 29 November 2007 11:51]

Report message to a moderator

Re: ORA-00060: Deadlock detected [message #284434 is a reply to message #284430] Thu, 29 November 2007 11:58 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member

SQL> select trigger_name from dba_triggers where owner = 'SECURITY' AND TABLE_NAME = 'DASHBOARD';

no rows selected


SQL> SELECT R_OWNER ,  R_CONSTRAINT_NAME ,  DELETE_RULE  FROM DBA_CONSTRAINTS 
  2  where owner = 'SECURITY' AND TABLE_NAME = 'DASHBOARD' AND CONSTRAINT_TYPE = 'R';

R_OWNER                        R_CONSTRAINT_NAME              DELETE_RU
------------------------------ ------------------------------ ---------
SECURITY                       USERS_PK                       NO ACTION

Been there, done that but looked again, again just to be sure.
Re: ORA-00060: Deadlock detected [message #284437 is a reply to message #284430] Thu, 29 November 2007 12:16 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
I just got two more!
*** 2007-11-29 09:51:11.682
*** SERVICE NAME:(SYS$USERS) 2007-11-29 09:51:11.660
*** SESSION ID:(724.10805) 2007-11-29 09:51:11.660
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
delete from dashboard where owner_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        93     724    SX             75     518    SX   SSX
TM-0001a1e8-00000000        75     518    SX             93     724    SX   SSX
session 724: DID 0001-005D-00007695     session 518: DID 0001-004B-0000AEC1
session 518: DID 0001-004B-0000AEC1     session 724: DID 0001-005D-00007695
Rows waited on:
Session 518: no row
Session 724: no row
Information on the OTHER waiting sessions:
Session 518:
  pid=75 serial=51821 audsid=27122908 user: 718/SECURITY_USER
  O/S info: user: , term: , ospid: 1234, machine: vrx15.abc.com
            program: 
  Current SQL Statement:
  delete from dashboard where owner_user_id = :1
End of information on OTHER waiting sessions.
===================================================

*** 2007-11-29 09:51:24.034
*** SERVICE NAME:(SYS$USERS) 2007-11-29 09:51:24.021
*** SESSION ID:(518.51821) 2007-11-29 09:51:24.021
DEADLOCK DETECTED
[Transaction Deadlock]
Current SQL statement for this session:
delete from dashboard where owner_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        75     518    SX            183     216    SX   SSX
TM-0001a1e8-00000000       183     216    SX             75     518    SX   SSX
session 518: DID 0001-004B-0000AEC1     session 216: DID 0001-00B7-000019A5
session 216: DID 0001-00B7-000019A5     session 518: DID 0001-004B-0000AEC1
Rows waited on:
Session 216: no row
Session 518: no row
Information on the OTHER waiting sessions:
Session 216:
  pid=183 serial=26511 audsid=27123014 user: 718/SECURITY_USER
  O/S info: user: , term: , ospid: 1234, machine: vrx15.abc.com
            program: 
  Current SQL Statement:
  delete from dashboard where owner_user_id = :1
End of information on OTHER waiting sessions.
===================================================

But after staring at these new trace file I see/saw that both sessions are running the same SQL (delete) statement.
I need to go talk to the application developer about if/how the same delete could be issued by two (concurrent) sessions.
Re: ORA-00060: Deadlock detected [message #284438 is a reply to message #284434] Thu, 29 November 2007 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And the opposite: is 'DASHBOARD' parent table (is there table with foreign key on it)? And if this is the case same question: delete rule, trigger...

Regards
Michel
Re: ORA-00060: Deadlock detected [message #284439 is a reply to message #284438] Thu, 29 November 2007 12:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is owner_user_id a unique key or does the delete statement can delete many rows?

Regards
Michel
Re: ORA-00060: Deadlock detected [message #284443 is a reply to message #284430] Thu, 29 November 2007 12:44 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
Developer admits this is new & "complex" code in the application.

SQL> SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE FROM DBA_CONSTRAINTS WHERE OWNER = 'SECURITY' AND TABLE_NAME = 'DASHBOARD';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C0043135                   R
SYS_C0043134                   P
SYS_C0043133                   C

SQL>  SELECT CONSTRAINT_NAME , CONSTRAINT_TYPE, TABLE_NAME  FROM DBA_CONSTRAINTS WHERE OWNER = 'SECURITY' AND R_CONSTRAINT_NAME = 'SYS_C0043134';

CONSTRAINT_NAME                C TABLE_NAME
------------------------------ - ------------------------------
SYS_C0043139                   R USER_DASHBOARD
SYS_C0043142                   R USER_DASHBOARD_SHARED
SYS_C0043144                   R GROUP_DASHBOARD_SHARED
SYS_C0043146                   R ACCOUNT_DASHBOARD_SHARED
SYS_C0043147                   R DASHBOARD_SHARABLE_ALL

So the ball in the developer's court & I'll continue to feed him trace files as they occur.

Thanks Again, Michel
Re: ORA-00060: Deadlock detected [message #284456 is a reply to message #284430] Thu, 29 November 2007 14:11 Go to previous message
Kevin Meade
Messages: 2101
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.
Previous Topic: Help - Long running alter table
Next Topic: Finding Idle Table
Goto Forum:
  


Current Time: Sun Dec 11 08:30:31 CST 2016

Total time taken to generate the page: 0.07401 seconds