Deadlock ORA-60

Problem Details:
A colleague is testing to move an application out of one database.
It involves a lot of deletes and other dml to clean the existing database
from data. During one of these tests he ran into ORA-60

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:

DEADLOCK DETECTED ( ORA-00060 )
[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-003d002e-00003cad        86      35     X             78      34           S
TX-00430000-000000d2        78      34     X             81      86           S
TX-001c0006-0000090c        81      86     X            122     134           S
TX-00200010-00003629       122     134     X            116      81           S
TX-00390020-000005af       116      81     X             86      35           S
session 35: DID 0001-0056-000006B2    session 34: DID 0001-004E-000006B4
session 34: DID 0001-004E-000006B4    session 86: DID 0001-0051-00000661
session 86: DID 0001-0051-00000661    session 134: DID 0001-007A-000000AA
session 134: DID 0001-007A-000000AA    session 81: DID 0001-0074-000001C0
session 81: DID 0001-0074-000001C0    session 35: DID 0001-0056-000006B2
Rows waited on:
Session 34: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 86: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 134: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 81: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 35: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Information on the OTHER waiting sessions:
Session 34:
  sid: 34 ser: 4018 audsid: 37231477 user: 47368/DBS
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x8)
  pid: 78 O/S info: user: oracle, term: UNKNOWN, ospid: 21635
    image: oracle@segotl0152
  O/S info: user: dbs, term: unknown, ospid: 1234, machine: vgz11
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  DELETE FROM VT_DRAWING WHERE OBJ_ID = 6953456 AND OBJ_TYPE = ‘O’
Session 86:
  sid: 86 ser: 1550 audsid: 37231486 user: 47368/DBS
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x8)
  pid: 81 O/S info: user: oracle, term: UNKNOWN, ospid: 21716
    image: oracle@segotl0152
  O/S info: user: dbs, term: unknown, ospid: 1234, machine: vgz11
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  DELETE FROM VT_DRAWING WHERE OBJ_ID = 7063151 AND OBJ_TYPE = ‘O’
Session 134:
  sid: 134 ser: 5346 audsid: 37231589 user: 47368/DBS
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x8)
  pid: 122 O/S info: user: oracle, term: UNKNOWN, ospid: 22475
    image: oracle@segotl0152
  O/S info: user: dbs, term: unknown, ospid: 1234, machine: vgz11
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  DELETE FROM VT_DRAWING WHERE OBJ_ID = 6989107 AND OBJ_TYPE = ‘O’
Session 81:
  sid: 81 ser: 2953 audsid: 37231582 user: 47368/DBS
    flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
    flags2: (0x8)
  pid: 116 O/S info: user: oracle, term: UNKNOWN, ospid: 22427
    image: oracle@segotl0152
  O/S info: user: dbs, term: unknown, ospid: 1234, machine: vgz11
            program: JDBC Thin Client
  application name: JDBC Thin Client, hash value=2546894660
  Current SQL Statement:
  DELETE FROM VT_DRAWING WHERE OBJ_ID = 7006874 AND OBJ_TYPE = ‘O’
End of information on OTHER waiting sessions.
Current SQL statement for this session:
DELETE FROM VT_DRAWING WHERE OBJ_ID = 7041351 AND OBJ_TYPE = ‘O’

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.

We can see that we have several sessions executing the same sql:
DELETE FROM VT_DRAWING WHERE OBJ_ID = 6953456 AND OBJ_TYPE = ‘O’

the only difference is the actual obj_id value, so all sessions
deletes different row(s) from VT_DRAWING.

The locking info shows that we are waiting to accuire a Shared lock:
                       ———Blocker(s)——–  ———Waiter(s)———
Resource Name          process session holds waits  process session holds waits
TX-003d002e-00003cad        86      35     X             78      34           S
TX-00430000-000000d2        78      34     X             81      86           S
TX-001c0006-0000090c        81      86     X            122     134           S
TX-00200010-00003629       122     134     X            116      81           S
TX-00390020-000005af       116      81     X             86      35           S

We acan also see which objectblock etc that are involved:

Rows waited on:
Session 34: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 86: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 134: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 81: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)
Session 35: obj – rowid = 0000A8BD – AAAKi9AAAAAAAAAAAA
  (dictionary objn – 43197, file – 0, block – 0, slot – 0)

The object is 43197, the object can be found by running:
select OWNER,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_ID=’43197′

OWNER                   OBJECT_NAME         OBJECT_TYPE
—————- ————–  ———–
<REPLACED>          IDX1_VT_DRAWING INDEX

So this is an index.

So what causes this error.  How does Oracle keep track of sessions modifying a row ?
Every block has a list of transactions modifying it, it is handled by something called Interest Transaction List ITL.
ITL consists of slots that exists in the block header. A minimum of initrans slots are always available and
a maximum of maxtrans is possible, it depends on the amount of free space in the block.
We can create more ITL slots if we have available space in the block.

If more ITL slots are needed and no free space is available ORA-60 will occour.
The best way to seure space for ITL slots are to increase initrans, but it comes to a cost of
wasted space in the block. Another way is to increase pctfree, pctfree reserves space for future
updates in the block, by reserving more space for updates there is a chance that ITL slots can
use this space as well. But it is no guarantee, it depends on how many updates are done.
The only way to guarantee ITL slots is to increase initrans.

The ITL lists contains information about which transaction modifies which row and also
reference to undo data.
The actual transaction table is stored in the rollback segment header block.

So in this case we have several sessions deleting rows, this also triggers an update of the index
IDX1_VT_DRAWING, but we do not have enough space in the block to handle 5 sessions. Therefore we
got ORA-60.

Solution to the problem:
Increase initrans to a higher value or rebuild table with a higher pctfree.

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]

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