DBA Notes: 2011/09/15

Cheng Li

How to resolve Oracle Deadlock

From alert log, we are reported for ORA-000060: Deadlock detected. By diggering into trace file we can see following information marked in RED.

*** 2011-09-15 15:37:08.529

*** SESSION ID:(14.60398) 2011-09-15 15:37:08.264 DEADLOCK DETECTED Current SQL statement for this session:

UPDATE PS_BU_ITEMS_INV SET    LAST_ORDER = :1, LAST_ORDER_DATE = :2, QTY_AVAILABLE = QTY_AVAILABLE - :3, QTY_RESERVED = QTY_RESERVED + :4, DT_TIMESTAMP = SYS

DATE WHERE  BUSINESS_UNIT = :5 AND INV_ITEM_ID = :6 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-000d0038-0001b99b         9      14     X             44      91           X

TX-000e005c-0001baea        44      91     X              9      14           X

session 14: DID 0001-0009-00000002      session 91: DID 0001-002C-00000002

session 91: DID 0001-002C-00000002      session 14: DID 0001-0009-00000002

Rows waited on:

Session 91: obj - rowid = 00001336 - AAABM2AAZAAAIJ5AAY Session 14: obj - rowid = 00001336 - AAABM2AA3AAAHQdAAU ===================================================

Oracle comments about deadlock:

http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14220/consist.htm#sthref2043

As I review the description from blog of secooler:

http://space.itpub.net/519536/viewspace-611729

And following article: http://oracle-error.blogspot.com/2008/10/ora-00060-deadlock-detected-while_20.html

Oracle automatically detects deadlocks and resolves them by rolling back one of the transactions/statements involved in the deadlock, thus releasing one set of resources/data locked by that transaction. The session that is rolled back will observe Oracle error: ORA-00060: deadlock detected while waiting for resource. Oracle will also produce detailed information in a trace file under database's UDUMP directory.

In the affected session, the rolled back statement needs to be re-executed once the resources are available

Way to detect:

When the wait event is experienced, issue the following complex query:

Select s.sid SID,
s.serial# Serial#,
l.type type,
' ' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p
where s.sid = l.sid and
s.username <> ' ' and
s.paddr = p.addr and
l.type <> 'TM' and
(l.type <> 'TX' or l.type = 'TX' and l.lmode <> 6)
union
select s.sid SID,
s.serial# Serial#,
l.type type,
object_name object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, sys.dba_objects o
where s.sid = l.sid and
o.object_id = l.id1 and
l.type = 'TM' and
s.username <> ' ' and
s.paddr = p.addr
union
select s.sid SID,
s.serial# Serial#,
l.type type,
'(Rollback='||rtrim(r.name)||')' object_name,
lmode held,
request request
from v$lock l, v$session s, v$process p, v$rollname r
where s.sid = l.sid and
l.type = 'TX' and
l.lmode = 6 and
trunc(l.id1/65536) = r.usn and
s.username <> ' ' and
s.paddr = p.addr
order by 5, 6
/

The output of the query will look something like this:

SID SERIAL# TY OBJECT_NAM HELD REQUEST
----- ------- -- ---------- ---------- --------
36 8428 TX 0 4
36 8428 TM TAB1 3 0
52 29592 TM TAB1 3 0
52 29592 TX (Rollback=RBS1_6) 6 0

CONCLUSION:
Dead locks do occur in most of the applications and dead locks can be avoided by properly designing the transactions and applications by keeping other transactions and applications in mind. ITL waits and dead locks related to ITL waits can be avoided by setting of INITRANS and MAXTRANS properly. Dead locks during the transactions on bitmap indexed tables can be avoided by performing heavy transactions with no bitmap indexes and after completing the transactions rebuild the bitmap indexes.