Oracle11g r2.
select table_name from dict where table_name like '%V$LOCK%'; TABLE_NAME ------------------------------ GV$LOCK GV$LOCKED_OBJECT GV$LOCKS_WITH_COLLISIONS GV$LOCK_ACTIVITY GV$LOCK_ELEMENT GV$LOCK_TYPE V$LOCK V$LOCKED_OBJECT V$LOCKS_WITH_COLLISIONS V$LOCK_ACTIVITY V$LOCK_ELEMENT V$LOCK_TYPE
select name,type,description from v$lock_type;
There are over 205 types of locks that exists within Oracle11g r2 11.2.0.3.0 database.
Lock Escalation
Oracle never escalates locks.
Lock Management for Oracle RAC
gv$ dynamic performance views can be used to monitor the status for locking activities and to resolve lock conflicts within rac environment
Enqueue lock resources are accessed externally by querying the gv$resource
Lock requests can be viewed by query of the gv$lock.
Locking conflict Types
==========================================
Basically two types of lock conflicts
1.) Local Locking conflicts (block level)
Single Instance
Single Instance within RAC
column of BLOCK contains the value of 1 for blocking lock(session).
2.) Global Lock conflicts (block level)
BLOCK=2
The first task is to use a script to display all sessions that are holding or requesting locking of resources for a particular session.
Waiting sessions have a non-zero value for the column gv$lock.request.
SELECT TYPE,ID1,ID2 FROM GV$LOCK WHERE REQUEST > 0;
How to locate the root blocker with lock problems
first locate and kill the root blockers, Usually the row with the highest CTIME value
or
kill the oldest blocking session, which should have the highest CTIME value.
Detecting Object of Locking Conflict
object names for the TM locks can be easily identified by below SQL
SELECT * FROM V$LOCK L ,DBA_OBJECTS O WHERE O.OBJECT_ID = L.ID1 AND L.TYEP='TM';
Check locked object
SQL> desc v$locked_object Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- XIDUSN NUMBER XIDSLOT NUMBER XIDSQN NUMBER OBJECT_ID NUMBER SESSION_ID NUMBER ORACLE_USERNAME VARCHAR2(30) OS_USER_NAME VARCHAR2(30) PROCESS VARCHAR2(24) LOCKED_MODE NUMBER SQL> desc dba_objects Name Null? Type ----------------------------------------------------------------- -------- -------------------------------------------- OWNER VARCHAR2(30) OBJECT_NAME VARCHAR2(128) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED DATE LAST_DDL_TIME DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1) NAMESPACE NUMBER EDITION_NAME VARCHAR2(30) select OWNER,OBJECT_NAME,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME from v$locked_object,dba_objects where v$locked_object.object_id = dba_objects.object_id;
Check the Deadlock in single instance
SQL> desc v$lock Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) KADDR RAW(8) SID NUMBER TYPE VARCHAR2(2) ID1 NUMBER ID2 NUMBER LMODE NUMBER REQUEST NUMBER CTIME NUMBER BLOCK NUMBER ======= Single Instance ======= prompt CTIME is in Seconds set lines 120 col BLOCK for 9 col LMODE for 9 col INST_ID for 9 col REQUEST for 9 col SID for 999999 select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK from gv$lock where request>0;
Check the Deadlock in RAC
SQL> desc gv$lock Name Null? Type ----------------------------------------- -------- ---------------------------- INST_ID NUMBER ADDR RAW(8) KADDR RAW(8) SID NUMBER TYPE VARCHAR2(2) ID1 NUMBER ID2 NUMBER LMODE NUMBER REQUEST NUMBER CTIME NUMBER BLOCK NUMBER ======= RAC ======= prompt CTIME is in Seconds set lines 120 col BLOCK for 9 col LMODE for 9 col INST_ID for 9 col REQUEST for 9 col SID for 999999 select INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK from gv$lock where (ID1,ID2,TYPE) in (select ID1,ID2,TYPE from gv$lock where request>0);
V$LOCK lists the locks currently held by the Oracle Database and outstanding requests for a lock or latch.
| Column | Datatype | Description |
|---|---|---|
ADDR |
RAW(4 | 8) |
Address of lock state object |
KADDR |
RAW(4 | 8) |
Address of lock |
SID |
NUMBER |
Identifier for session holding or acquiring the lock |
TYPE |
VARCHAR2(2) |
Type of user or system lockThe locks on the user types are obtained by user applications. Any process that is blocking others is likely to be holding one of these locks. The user type locks are:TM – DML enqueueTX – Transaction enqueueUL – User suppliedThe system type locks are listed in Table 8-1. Be aware that not all types of locks are documented. To find a complete list of locks for the current release, query the V$LOCK_TYPE data dictionary view, described on “V$LOCK_TYPE”. |
ID1 |
NUMBER |
Lock identifier #1 (depends on type) |
ID2 |
NUMBER |
Lock identifier #2 (depends on type) |
LMODE |
NUMBER |
Lock mode in which the session holds the lock:
|
REQUEST |
NUMBER |
Lock mode in which the process requests the lock:
|
CTIME |
NUMBER |
Time since current mode was granted |
BLOCK |
NUMBER |
A value of either 0 or 1, depending on whether or not the lock in question is the blocker. |
Session-A and Time T1
Check the Locked Object by DBA from another sysdba session
Find the associate sql ID from v$session
Find the associate sql query statement from v$sqlarea
You can also check my earlier post War against the Deadlock 🙂
