PURPOSE
-------

This article explains the most common scenarios for Deadlocks and how
to avoid them.

 
SCOPE & APPLICATION
-------------------

This article is intended for Oracle Support Analysts , Oracle Consultants,
Database Administrators and Application Developers. 

Deadlocks
---------
 Deadlocks normally occur when two or more sessions are both holding 
and requesting the same set of resources, this bulletin is intended to
discuss two of the most common causes for deadlocks; row-level locks
and block-level locks.

Row Level Locks

Very easy to produce and detect, suppose that you have 2 sessions 
accessing the same table Dept:

session 9  :  Update dept set Dname='SUPPORT' where deptno=10;
session 10 :  Update dept set dname='CONSULTING' where deptno=20;

Session 10 : Update dept set  Loc='ORLANDO' where deptno=10; 
session 10 will hang waiting on deptno=10 row to be released by session 1

Session 9: update dept set loc='QQQ' where deptno=20; 
      ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

So the last update of session 1 caused a deadlock and a trace file was
generated in the user_dump_dest:

DEADLOCK DETECTED
Current SQL statement for this session:
update dept set loc='QQQ' where deptno=20
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-0003000c-00000708        10       9     X             11      10           X
TX-00020007-000006df        11      10     X             10       9           X
session 9: DID 0001-000A-00000004	session 10: DID 0001-000B-00000002
session 10: DID 0001-000B-00000002	session 9: DID 0001-000A-00000004
Rows waited on:
Session 10: obj - rowid = 000051F1 - AAAFHxAAFAAAAAwAAA
Session  9: obj - rowid = 000051F1 - AAAFHxAAFAAAAAwAAB
================================================================================

The above graph shows that the two sessions 9 & 10 are both holding and requesting
the same set of resources which are the two rows of Dept table.
If the deadlock is due to row-level locks being obtained in different 
order then this section of the trace file indicates the exact rows that 
each session is waiting to lock for themselves. 



If you check v$lock you will find the following locks:

ADDR     KADDR        SID TY     ID1     ID2   LMODE REQUEST   CTIME   BLOCK
-------- -------- ------- -- ------- ------- ------- ------- ------- -------
04BD8DE8 04BD8EB8       9 TX  196620    1800       6       0   20637       1
03052480 03052494       9 TM   20977       0       3       0   20637       0
04BD8DE8 04BD8EB8      10 TX  131079    1759       6       0   20594       0
030524F4 03052508      10 TM   20977       0       3       0   20594       0
02F4CB00 02F4CB10      10 TX  196620    1800       0       6   20567       0

The two TM table locks above are holding the same table DEPT, these locks are 
always aquired even if there is no integrity constraints on table Dept at all,
the following query shows how to get the object name :

SQL> select object_name from dba_objects
  2  where object_id=20977;

OBJECT_NAME
--------------------------------------------------------------------------------
DEPT

To avoid the above locking scenario the application developers has to maintain 
certain order in updating a specific table or set of tables.



Block Level Locks

Everybody knows that Oracle was the first database to implement row-level locking
instead of page locking or block locking, but in real life we still encounter
page locking in very rare cases.

Application logic is not the only cause for Deadlocks ,Database Design is also 
responsible for that sometimes, it might be related to the way transaction layer 
storage parameters were set for the database objects.

Any database block has a transaction layer and a data layer, a good design will 
take into consideration both the expected number of records to be accommodated 
in one block and the maximum number of concurrent transactions accessing this block.
If the data portion was increased by accommodating more records then this will
decrease the possibility of having the required number of concurrent transactions 
that might access a certain block and some of the transactions have to wait until 
others are completed , in this case a transaction layer lock will occur and ora-60
is likely to be reported.

Two parameters are responsible about that , INITRANS which is the initial number of 
concurrent transactions that access one block and MAXTRANS which is the maximum 
number of concurrent transactions that may access the same block. For example, 
if a table is very large and only a small number of users simultaneoulsy access 
the table, the chances of multiple concurrent transactions requiring access
to the same data block is low.  Therefore, INITRANS can be set low, especially
if space is an issue with the database.

Alternatively, assume that a small table is usually accessed by many users at the
same time.  In this case, you might consider preallocating transaction entry
space by using a high INITRANS and allowing a higher MAXTRANS so that no users 
have to wait to access certain hot blocks.

Indexes also should be well designed since it might lead into ora-60 problems
specially when doing concurent deletes.

To produce a Transaction layer lock you can follow the following steps:

 create table dept10 (Deptno  number(4),dname varchar2(10),Loc varchar2(10))
 initrans 1 maxtrans 1;
 inser into dept10 select * from dept;
 commit;
 
Session 10 : Update emp set comm=9999;
session 9  : update dept10 set loc='Orlando' where deptno=10;
session 10 : update dept10 set loc='Orlando' where deptno=20;  -- hangs
session 9  : delete emp;  -- hangs  and session 10 will report ora-60

session 10 above hangs though it is updating a different row, the
Transaction Layer contention caused a block-level lock instead of row-level 
lock in this example and led to a Deadlock situation.

  select * from v$lock;

ADDR     KADDR        SID TY     ID1     ID2   LMODE REQUEST   CTIME   BLOCK
-------- -------- ------- -- ------- ------- ------- ------- ------- -------
04BD8DE8 04BD8EB8       9 TX  262152    1831       6       0     307       0
030524F4 03052508       9 TM   20983       0       3       0     307       0
03052398 030523AC       9 TM   20976       0       3       0     271       0
02F4CB00 02F4CB10       9 TX   65536    1937       0       6     271       0
04BD8DE8 04BD8EB8      10 TX   65536    1937       6       0     341       1
03052480 03052494      10 TM   20976       0       3       0     341       0

where Dept10  object_id =20983 and Emp object_id=20976
Note that the Deadlock graph in the generated trace file is similar to 
the graph shown eralier in this bulletin.
.
RELATED DOCUMENTS
-----------------
Note:285270.1 The Performance Impact of Deadlock Detection
Note:62365.1  What to do with "ORA-60 Deadlock Detected" Errors
Note:115467.1 ORA-60 DEADLOCK DETECTED ON CONCURRENT INSERTS
Bug:616548    ORA-60 NOT WRITTEN TO ALERT FILE
 Fixed in 8.1.6
Bug:1249920   SUPPORTABILITY: IMPROVE DEADLOCK DIAGNOSTICS
 Fixed in 9.2
Bug:1328999   ORA-60 may be incorrectly signalled using global transactions
 Fixed in  8.1.7  Sub-Component: XA
 Details:            
  Multiple branches in a global transaction may signal a false deadlock
  between each other. This typically shows up as an ORA-60 between a
  DX lock and an 'enqueue hash chains' latch .
Bug:1372087 ORA-60 WITH ONE RESOURCE ON DEADLOCK GRAPH
 Fixed in 7.3.4.5 Sub-Component: XA

返回页首