Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Thursday, 13 September 2012

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
/
References
What to do with "ORA-60 Deadlock Detected" Errors Doc ID: 62365.1
TX Transaction locks - Example wait scenarios Doc ID: 62354.1
Summary of Bugs Which Could Cause Deadlock Doc ID: 554616.1
ORA-60 / Deadlocks Most Common Causes 164661.1
OERR: ORA 60 "deadlock detected while waiting for resource" 18251.1
REFERENTIAL INTEGRITY AND LOCKING Doc ID: 33453.1

2 comments:

  1. I aam extremely impress&X65;d with your writing skilps as
    well aѕ with the layout оn your blog.
    Is t&X68;is a paid themе &X6f;r did yοu
    сuѕtomize it your&X73;elf? Eithеr way keep up the excеllent qua&X6c;ity
    wrіting, it is raгe to see a nіcе blog like this one toԁay.


    My b&X6c;οg :: children’s clothing ()

    ReplyDelete
  2. Did you know that you can make cash by locking special pages of your blog / site?
    To begin you need to join AdscendMedia and run their Content Locking tool.

    ReplyDelete