Another Example of a Deadlock (ORA-60)

Problem Details:
I found this error in the grid control looking at one of my systems.
ORA-00060: Deadlock detected. More info in file /REPLACED/replaced_ora_6221.trc

This is my analyze of the problem:
As soon as a ORA-60 is encountered a tracefile is generated, see the alertlog for
details about the tracefile.

The tracefile contains this information:

[Transaction Deadlock]
 
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-000c0019-000b09d6       165    6047     X            608    6202           X
TX-000b0019-000dc685       608    6202     X            165    6047           X
 
session 6047: DID 0001-00A5-00011C25    session 6202: DID 0001-0260-00000638
session 6202: DID 0001-0260-00000638    session 6047: DID 0001-00A5-00011C25
 
Rows waited on:
  Session 6047: obj – rowid = 000060B7 – AAAW7YAAOAAHX/HAAK
  (dictionary objn – 24759, file – 14, block – 1933255, slot – 10)
  Session 6202: obj – rowid = 000060B7 – AAAW7YAADAABEcvAAB
  (dictionary objn – 24759, file – 3, block – 280367, slot – 1)
 
—– Information for the OTHER waiting sessions —–
Session 6202:
  sid: 6202 ser: 36646 audsid: 26514503 user: 313/SIEBEL flags: 0x41
  pid: 608 O/S info: user: oracle, term: UNKNOWN, ospid: 28669
    image: oracle@segotl0119
  client details:
    O/S info: user: vxcn125, term: , ospid: 1234
    machine: SEGOTN10732 program:
  current SQL:
 
UPDATE S_ORG_EXT
SET CUST_STAT_CD = :1
WHERE  ( ( NAME = :2  ) ) AND    ( ( X_DEALER_ID LIKE :3  ) )

The first thing to notice is the text:

[Transaction Deadlock]
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:

And that is fact, most of the ORA-60 are application related and is often fixed
within the application.

So we have two updates of the table s_org_ext, and the reason for receiving this
error is that session 6202 has locked the row with rowid AAAW7YAAOAAHX/HAAK and
wants to lock the row with rowid AAAW7YAADAABEcvAAB. At the same time session 6047
has locked the row with rowid AAAW7YAADAABEcvAAB and wants to lock the row with
rowid AAAW7YAAOAAHX/HAAK.

What can we say about our session receiving t he ORA-60 error ?
If we look further down the trace under PROCESS STATE and client details:

O/S info: user: it-got-2473-argusapp, term: SEGOTN2473, ospid: 8852:3684
machine: VCN\SEGOTN2473 program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
Current Wait Stack:
0: waiting for ‘enq: TX – row lock contention’

We can see that the application used is SQLPLUS and that it is waiting for
‘enq: TX – row lock contention’. So this is a user running an update outside the
“application”.

The “other” session we can’t say much about, we know the sql statement but we do not
know if this is executed from the application or if this is also executed outside the
“application”.

What should our user have done to avoid this situation ?

Since this is an adhoc update he/she could have choosen a better time to execute the update,
perhaps get some more information when is the most appropiate time to run this update.
I also think he should have used what is called pessimistisk locking, by issuing a
select for update nowait for the rows he wants to update, building in functionality to handle
the possible ORA-54 he might receive.

Solution to the problem:
Use pessimistisk locking

References:
Oracle support Notes:
Troubleshooting “ORA-00060 Deadlock Detected” Errors [ID 62365.1]
TX Transaction and Enq: Tx – Row Lock Contention – Example wait scenarios [ID 62354.1]
Books:
Expert Oracle Database Architecture (Tom Kyte)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s