ORA-00060 (DEADLOCKS)
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
/
No comments:
Post a Comment