Here is a sample deadlock dump file:
*** SESSION ID:(75.13484) 2005-12-20 07:55:46.669
DEADLOCK DETECTED
Current SQL statement for this session:
INSERT INTO “ASI”.”SEARCH_RESULT_L” (“USER_ID”,”LOSS_TYPE_CD”,”REF_ID”,”CREATN_TS”,”SOURCE_TYPE_CD”) VALUES (:B5,:B4,:B3,:B2,:B1)
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-00040022-0005196e 30 75 X 42 58 S
TX-00060011-0005664b 42 58 X 30 75 S
session 75: DID 0001-001E-00238EDA session 58: DID 0001-002A-00197ABD
session 58: DID 0001-002A-00197ABD session 75: DID 0001-001E-00238EDA
Rows waited on:
Session 58: no row
Session 75: obj – rowid = 0000D53F – AAANU/AAZAAAAAAAAA
(dictionary objn – 54591, file – 25, block – 0, slot – 0)
Information on the OTHER waiting sessions:
Session 58:
pid=42 serial=57018 audsid=3686940 user: 82/ASI
O/S info: user: ASPNET, term: , ospid: 8176, machine: sunsrv01
program: oracle@sunsrv01 (TNS V1-V3)
application name: oracle@sunsrv01 (TNS V1-V3), hash value=0
Current SQL Statement:
INSERT INTO “ASI”.”SEARCH_CRITERIA_VALUE_L” (“USER_ID”,”SEARCH_CRITERIA_CD”,”DT_VALUE”,”NUM_VALUE”,”STR_VALUE”,”CREATN_TS”) VALUES (:B6,:B5,:B4,:B3,:B2,:B1)
End of information on OTHER waiting sessions.
Different Lock Types and Modes
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
The most common lock types seen in deadlock graphs are TX and TM locks.
These may appear held / requested in a number of modes. It is the lock type and modes which help determine what situation has caused the
deadlock.
Lock Mode
Type Requested Probable Cause
~~~~ ~~~~~~~~~ ~~~~~~~~~~~~~~
TX X (mode 6) Application row level conflict.
Avoid by recoding the application to ensure rows are always locked in a particular order.
TX S (mode 4) There are a number of reasons that a TX lock may be requested in S mode. See Note 62354.1
for a list of when TX locks are requested in mode 4.
TM SSX (mode 5) This is usually related to the existence of foreign key constraints where the columns are not indexed on the child table.
or S (mode 4) See Note 33453.1 for how to locate such constraints. See below for locating the OBJECT being waited on.
TM locks – which object ?
~~~~~~~~~~~~~~~~~~~~~~~~~
ID1 of a TM lock indicates which object is being locked. This makes it very simple to isolate the object involved in a deadlock when a TM lock
is involved.
1. Given the TM lock id in the form TM-AAAAAAAA-BBBBBBBB
convert AAAAAAAA from hexadecimal to a decimal number
2. Locate the object using DBA_OBJECTS:
SELECT * FROM dba_objects WHERE object_id= NNNN;
This is the object id that the TM lock covers.
Note that with TM locks it is possible that the lock is already held in some mode in which case the REQUEST is to escalate the lock mode.
3. Which lock modes are required for which table action?
==========================================================
The following table describes what lock modes on DML enqueues are actually gotten for which table operations in a standard Oracle installation.
Operation Lock Mode LMODE Lock Description
————————- ——— —– —————-
Select NULL 1 null
Select for update SS 2 sub share
Insert SX 3 sub exclusive
Update SX 3 sub exclusive
Delete SX 3 sub exclusive
Lock For Update SS 2 sub share
Lock Share S 4 share
Lock Exclusive X 6 exclusive
Lock Row Share SS 2 sub share
Lock Row Exclusive SX 3 sub exclusive
Lock Share Row Exclusive SSX 5 share/sub exclusive
Alter table X 6 exclusive
Drop table X 6 exclusive
Create Index S 4 share
Drop Index X 6 exclusive
Truncate table X 6 exclusive
———————————————————–
4. How compatibility of locks work
====================================
The compatibility of lock modes are normally represented by following matrix:
NULL SS SX S SSX X
—————————————————–
NULL YES YES YES YES YES YES
SS YES YES YES YES YES no
SX YES YES YES no no no
S YES YES no YES no no
SSX YES YES no no no no
X YES no no no no no
– See what object is involved
select * from dba_objects where object_id = …
select * from dba_objects where object_id = to_number(‘ …’)
– check the datafile name
select * from v$datafile where file# = 25
SELECT * FROM V$SESSION_WAIT
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type=’TX’;
select event,P1RAW, p1,p2,p3 from v$session_wait where wait_time=0 and event=’enqueue’
–WHERE EVENT = ‘enqueue’;
–V$SESSION_WAIT When a session is waiting on a resource, it can be found waiting on the enqueue wait event,
– SID identifier of session holding the lock
– P1, P2, P3 determine the resource when event = ‘enqueue’
– SECONDS_IN_WAIT gives how long the wait did occurs
select ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid = 10;
–see what object is
select * from dba_objects where object_id = 33327;
select * from V$SESSION where username = ‘USER1′
select ROW_WAIT_OBJ#, ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW# from v$session where sid=18
–session information and row locking information
– SID, SERIAL# identifier of the session
– LOCKWAIT address of the lock waiting, otherwise null
– ROW_WAIT_OBJ# object identified of the object we are waiting on (object_id of dba_objects)
– ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# file_id , block_id and row location within block of the locked row
select * from V$LOCK
select * from v$lock where type=’TX’ and request>0
select * from v$lock where type=’TX’ and lmode>0
select SID,TYPE,ID1,ID2,LMODE,REQUEST from v$lock where type=’TX’
– list of all the locks in the system
– SID identifier of session holding the lock
– TYPE, ID1 and ID2 determine the resource
– LMODE and REQUEST indicate which queue the session is waiting on, as follows:
– LMODE > 0, REQUEST = 0 owner
– LMODE = 0, REQUEST > 0 acquirer
– LMODE > 0, REQUEST > 0 converter
– CTIME time since current mode was converted (see Note 223146.1)
– BLOCK are we blocking another lock
– BLOCK = 0 non blocking
– BLOCK = 1 blocking others
select * from DBA_LOCK
select * from DBA_LOCKS
– formatted view on V$LOCK (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
– SESSION_ID == SID in V$LOCK
– LOCK_TYPE, LOCK_ID1, LOCK_ID2 formatted value of TYPE, ID1, ID2 from V$LOCK
– MODE_HELD and MODE_REQUESTED formatted value of LMODE and REQUEST from V$LOCK
– LAST_CONVERT == CTIME of V$LOCK
– BLOCKING_OTHERS formatted value of BLOCK from V$LOCK
select * from V$TRANSACTION_ENQUEUE
–subset of V$LOCK for the blocking TX resources only
– (same description as for the V$LOCK view)
select * from V$ENQUEUE_LOCK
– subset of V$LOCK for the system resources only and blocked TX resources only. (same description as for the V$LOCK view)
select * from DBA_DML_LOCKS
– subset of the V$LOCK for the DML (TM) locks only
– (created via $ORACLE_HOME/rdbms/admin/catblock.sql
– same description as the DBA_LOCK view)
select * from V$LOCKED_OBJECT
–same info as DBA_DML_LOCKS, but linked with the rollback and session information
– XIDUSN, XIDSLOT and XIDSQN rollback information to be linked with V$TRANSACTION
– OBJECT_ID object being locked
– SESSION_ID session id
– ORACLE_USERNAME oracle user name
– OS_USER_NAME OS user name
– PROCESS OS process id
– LOCKED_MODE lock mode
select * from V$RESOURCE
– list of all the currently locked resources in the system.
– Each row can be associated with one or more rows in V$LOCK
– TYPE, ID1 and ID2 determine the resource
select * from DBA_DDL_LOCKS
–has a row for each DDL lock that is being held, and one row for each outstanding request for a DDL lock. It is subset of DBA_LOCKS
– same description as the DBA_LOCK view
select * from DBA_WAITERS
– view that retrieve information for each session waiting on a
– lock (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
– WAITING_SESSION waiting session
– HOLDING_SESSION holding session
– LOCK_TYPE, LOCK_ID1, LOCK_ID2 resource locked
– MODE_HELD lock type held
– MODE_REQUESTED lock type requested
select * from DBA_BLOCKERS
–view that gives the blocking sessions (created via $ORACLE_HOME/rdbms/admin/catblock.sql)
–HOLDING_SESSION holding session
0 Comments.