ORA-00060 (DEADLOCKS)

ORA-00060 (DEADLOCKS)

A deadlock occurs when a session wants a resource held by another session, but that session also wants a resource held by the first session.

Below is a snippet of an actual production database trace file showing deadlock:

 

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-00090008-0012afb1 165 501 X 178 461 X

TX-0010002f-000530d1 178 461 X 165 501 X

Rows waited on:

Session 461: obj - rowid = 0000D663 - AAANZjAAFAAABypAAV

(dictionary objn - 54883, file - 5, block - 7337, slot - 21)

Session 501: obj - rowid = 0000D2F9 - AAANL5AAFAAAA80AAG

(dictionary objn - 54009, file - 5, block - 3892, slot - 6)

Information on the OTHER waiting sessions:

Session 461:

pid=178 serial=24604 audsid=420036396 user: 56/HRMS

O/S info: user: HRMS, term: TC2HRMS2, ospid: 3072:2676, machine:

===================================================

 

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-00090008-0012afb1    165  501     X    178   461   X

TX-0010002f-000530d1    178  461     X    165   501   X

This shows who was holding each lock, and who was waiting for each lock.

The columns in the graph indicate:

Resource Name =   Lock name being held / waited for.

process            =  V$PROCESS.PID of the Blocking / Waiting session

session            =  V$SESSION.SID of the Blocking / Waiting session

holds               =  Mode the lock is held in

waits               =  Mode the lock is requested in

 

So in this example:

SID 501 holds TX-00090008-0012afb1 in X mode

and wants TX-0010002f-000530d1 in X mode

SID 461 holds TX-0010002f-000530d1 in X mode

and wants TX-00090008-0012afb1 in X mode

 

Rows waited on:

Session 461: obj - rowid = 0000D663 - AAANZjAAFAAABypAAV

(dictionary objn - 54883, file - 5, block - 7337, slot - 21)

Session 501: obj - rowid = 0000D2F9 - AAANL5AAFAAAA80AAG

(dictionary objn - 54009, file - 5, block - 3892, slot - 6)

 

To get the objects:

 

SELECT owner, object_name, object_type FROM dba_objects WHERE object_id in (54883, 54009);

owner       object_name             object_type

----------  ------------------      ---------------

HRMS        ASSET_ALIAS             TABLE

HRMS        EQUITY_INFO             TABLE

Normally, deadlocks occur on the same object.  In this example, we are trying to access 2 different tables owned by HRMS schema but resulting to a deadlock.

 

SELECT owner, object_name, object_type FROM dba_objects WHERE object_id in (54883, 54009);

owner object_name object_type

HRMS ASSET_ALIAS TABLE

HRMS EQUITY_INFO TABLE

 

The deadlock was due to a "SELECT FOR UPDATE" on 2 different tables:

 

SELECT ASSET_CODE, EQUITY_TYPE, EQUITY_SHRS_OUTSTANDING, EQUITY_ADR_FLAG, EQUITY_TRANSFER_AGENT, EQUITY_CM_UNSPECIFIED, EQUITY_CM_CLASS_A, EQUITY_CM_CLASS_B, EQUITY_PR_CONV, EQUITY_PR_CUMM, EQUITY_PR_PART, EQUITY_CONV_RATIO, EQUITY_CTRY_OF_INC, EQUITY_AUDIT_PARENT, EQUITY_AUDIT_CHILD FROM EQUITY_INFO WHERE (ASSET_CODE =:1) FOR UPDATE

SELECT ALIAS_SEQ, ALIAS_IDENTIFIER, IDENTIFIER_CODE, AA_BEG_DATE, COUNTRY_CODE, AA_END_DATE, ASSET_CODE, AA_MEMO, AA_USER_STAMP,

AA_PORT_STAMP, AA_DATE_STAMP FROM ASSET_ALIAS WHERE (ALIAS_SEQ =:1) FOR UPDATE

Lets try to check its dependencies to other tables.

 

SQL> select table_name, R_CONSTRAINT_NAME from dba_constraints where table_name in ('ASSET_ALIAS','EQUITY_INFO') and constraint_type = 'R';

TABLE_NAME              R_CONSTRAINT_NAME

--------------------    ------------------------------

ASSET_ALIAS             XPK_ASSET

ASSET_ALIAS             XPKCOUNTRY_CODE

ASSET_ALIAS             XPKIDENTIFIER_TYPE

EQUITY_INFO             XPK_ASSET

 

As we can see, there is a referantial constraints XPK_ASSET used by both ASSET_ALIAS and EQUITY_INFO tables.

SQL> select table_name, constraint_name from dba_constraints where constraint_name = 'XPK_ASSET';

TABLE_NAME                    CONSTRAINT_NAME

----------------------------  ------------------------------

ASSET                         XPK_ASSET

Use the scipt belows to get all the referencing tables of ASSET table.

Select acc.table_name REFERENCING_TABLE_NAME, acc.column_name REFERENCING_COLUMN_NAME from all_constraints ac1,all_constraints ac2,all_cons_columns acc where ac1.constraint_type = 'P' and ac1.table_name = 'ASSET'and ac2.r_constraint_name = ac1.constraint_name

and ac2.constraint_name = acc.constraint_name;

Query results to 84 rows referencing to ASSET table.

At this point, what a production DBA can do is to inform the Application Designers and Developers to review the current application design.

The above deadlock example occurs because the application which issues the update statements has no strict ordering of the rows it updates.  The strict ordering of the updates ensures that a deadly embrace cannot occur. Note that the deadlock need not be between rows of the same table - it could be between rows in different tables. Hence it is important to place rules on the order in which tables are updated as well as the order of the rows within each table.

 

Show all processes causing a dead lock

SELECT a.session_id, username, TYPE, mode_held, mode_requested, lock_id1,
       lock_id2
  FROM v$session b, dba_blockers c, dba_locks a
 WHERE c.holding_session = a.session_id AND c.holding_session = b.sid
/