Oracle Lock Management

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:

  • 0 – none
  • 1 – null (NULL)
  • 2 – row-S (SS)
  • 3 – row-X (SX)
  • 4 – share (S)
  • 5 – S/Row-X (SSX)
  • 6 – exclusive (X)
REQUEST NUMBER Lock mode in which the process requests the lock:

  • 0 – none
  • 1 – null (NULL)
  • 2 – row-S (SS)
  • 3 – row-X (SX)
  • 4 – share (S)
  • 5 – S/Row-X (SSX)
  • 6 – exclusive (X)
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 🙂

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s