Pages

Tuesday, October 14, 2014

Locks in Oracle by Example

In Short How to find a lock on a table

SELECT 
'ALTER SYSTEM KILL SESSION '''||V$SESSION.sid||','||V$SESSION.serial#||''' IMMEDIATE; ' AS kill_command, 
  a.session_id,       
  a.oracle_username, 
  a.os_user_name,
  b.owner "OBJECT OWNER",  
  b.object_name,   
  b.object_type,
  a.locked_mode 
 FROM (SELECT object_id, session_id, oracle_username, os_user_name, locked_mode
        FROM V$LOCKED_OBJECT) a, 
  (SELECT object_id, owner, object_name,object_type      
     FROM DBA_OBJECTS) b,
  V$SESSION
WHERE a.object_id=b.object_id
  AND a.session_id = V$SESSION.sid;

This is working, tested!

SESSION_ID ORACLE_USERNAME  OS_USER_NAME  OBJECT OWNER  OBJECT_NAME   OBJECT_TYPE  LOCKED_MODE
---------- ---------------- ------------- ------------- ------------- ------------ -----------
       592 ORA_USER         linux_user    ORA_USER      TABLE_A       TABLE                  3
       592 ORA_USER         linux_user    ORA_USER      TABLE_B       TABLE                  0
       441 ORA_USER         linux_user    ORA_USER      TABLE_B       TABLE                  6

V$LOCKED_OBJECT Oracle Reference
lock mode 
0 -  lock requested but not yet obtained
3 - ROW_X (SX): Row Exclusive Table Lock
6 - Exclusive (X): Exclusive Table Lock


General
Update same table from two sessions
How to find the locking and the locked session?






Flow
Run UPDATE statement without commit, and find the uncommitted session.


From sqlplus:
SQL> UPDATE ADDRESS SET addr_zip_code = '21213' WHERE addr_id = 1;
> no commit!!

SELECT * FROM V$SESSION 
 WHERE username = 'ALEC_USER' 
  AND program = 'sqlplus.exe'

The sid and serial# are:
sid: 205
serial#: 21296


==========================
Relate Locking Session ID to SQL Text
==========================
SQL Text could be found in several tables:

V$SQLAREA
V$SQLTEXT
V$SQLTEXT_WITH_NEWLINES


--Ignore INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
       SESSIONS.username, 
       SESSIONS.osuser, 
       SESSIONS.machine, 
       SESSIONS.program, 
       SESSIONS.module, 
       REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text       
FROM V$SQLAREA SQLTEXT,
     V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.sql_address
  AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
  AND SESSIONS.status != 'INACTIVE'  --Ignore only INACTIVE Sessions
  AND SESSIONS.audsid <> USERENV('sessionid') --Ignore current session
ORDER BY SQLTEXT.address

--For INACTIVE Sessions
SELECT SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
       SESSIONS.username, 
       SESSIONS.osuser, 
       SESSIONS.machine, 
       SESSIONS.program, 
       SESSIONS.module, 
       REPLACE(SQLTEXT.sql_text,CHR(10),'') sql_text       
FROM V$SQLAREA SQLTEXT,
     V$SESSION SESSIONS
WHERE SQLTEXT.address = SESSIONS.prev_sql_addr
  AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
  AND SESSIONS.status = 'INACTIVE'
ORDER BY SQLTEXT.address

==========================
Find locking and locked sessions
==========================
Option A. 
Use V$LOCK table as parent/child relation. 
block-1 - blocking others
block-0 - not blocking others

SELECT           

         LOCKING.SID AS LOCKING_SID,           
         LOCKED.SID AS LOCKED_SID,
         LOCKING.type
  FROM   V$LOCK LOCKING,
         V$LOCK LOCKED
  WHERE 1=1
    AND LOCKING.block=1
    AND LOCKED.block=0
    AND LOCKING.ID1||LOCKING.ID2 = LOCKED.ID1||LOCKED.ID2;



LOCKING_SID LOCKED_SID TYPE
----------- ---------- ----
        194        205 TX


Elapsed: 00:04:00.03


Option B.
Use UNION ALL between V$LOCK LOCKS and V$LOCK LOCKED.
This one runs much faster, approx 2 seconds.

SELECT LOCKED_SESSIONS.lock_status,
       LOCKED_SESSIONS.sid,    
       LOCKED_SESSIONS.type,
       SESSIONS.sid||','|| SESSIONS.serial# AS SID_SERIAL#, SESSIONS.username, osuser, machine, program, SESSIONS.module,       
       SUBSTR(REPLACE(SQLTEXT.sql_text,CHR(10),''),0,400) AS SQL_TEXT
FROM(    
    SELECT 
       DECODE(LOCKING.block,0,'Not Locking','Locking') AS LOCK_STATUS,
       LOCKING.SID AS SID,
       LOCKING.type, 
       LOCKING.ID1, 
       LOCKING.ID2, 
       LOCKING.lmode, 
       LOCKING.request, 
       LOCKING.block
    FROM   V$LOCK LOCKING
    WHERE 1=1
      AND LOCKING.block=1
   UNION ALL
    SELECT 
       DECODE(LOCKED.block,0,'Not Locking','Locking') AS LOCK_STATUS,
       LOCKED.SID AS SID,           
       LOCKED.type, 
       LOCKED.ID1, 
       LOCKED.ID2, 
       LOCKED.lmode, 
       LOCKED.request, 
       LOCKED.block
    FROM   V$LOCK LOCKED
    WHERE 1=1
      AND LOCKED.block=0
      AND ID1||ID2 IN
         (SELECT ID1||ID2 FROM V$LOCK WHERE V$LOCK.block = 1)
)LOCKED_SESSIONS,
 V$SESSION SESSIONS, 
 V$SQLAREA SQLTEXT
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
  --AND SESSIONS.username = 'ALEC_USER' 
  --AND SESSIONS.program = 'sqlplus.exe'
  AND SQLTEXT.address = SESSIONS.prev_sql_addr
  AND SQLTEXT.hash_value = SESSIONS.prev_hash_value
ORDER BY 1

LOCK_STATUS  LOCK_ID1_ID2     SID TYPE SID_SERIAL USERNAME   OSUSER     MACHINE       
------------ --------------- ---- ---- ---------- ---------- ---------- ------------- 
OK           196623-379112    194 TX   194-9665   ALEC_USER  akaplan    1\ALEC-KAPLAN 
Being Locked 196623-379112    205 TX   205-21296  ALEC_USER  akaplan    1\ALEC-KAPLAN 


PROGRAM     MODULE     SQL_TEXT

----------- ---------- ----------------------------------------
sqlplus.exe SQL*Plus   commit
sqlplus.exe SQL*Plus   UPDATE ADDRESS SET addr_zip_code = '212

Elapsed: 00:00:02.00




==========================
Find locking and locked Objects
==========================
SELECT LOCKED_SESSIONS.lock_status,
  id1||'-'||id2 LOCK_ID1_ID2,
  LOCKED_SESSIONS.sid,    
  LOCKED_SESSIONS.type,
  SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
  SESSIONS.username, 
  SESSIONS.osuser, 
  SESSIONS.machine, 
  SESSIONS.program, 
  SESSIONS.module,
  OBJECTS.owner,
  OBJECTS.object_name,
  OBJECTS.object_type AS TYPE,
  LOCKED_OBJECTS.process,
  PROCESS.spid
FROM(    
     SELECT 
  DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
  BEING_LOCKED.SID AS SID,
  BEING_LOCKED.type, 
  BEING_LOCKED.ID1, 
  BEING_LOCKED.ID2, 
  BEING_LOCKED.lmode, 
  BEING_LOCKED.request, 
  BEING_LOCKED.block
     FROM   V$LOCK BEING_LOCKED
     WHERE 1=1
       AND BEING_LOCKED.block=1
    UNION ALL
     SELECT 
  DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,            
  LOCKS.SID AS SID,           
  LOCKS.type, 
  LOCKS.ID1, 
  LOCKS.ID2, 
  LOCKS.lmode, 
  LOCKS.request, 
  LOCKS.block
     FROM   V$LOCK LOCKS
     WHERE 1=1
       AND LOCKS.block=0
       AND ID1||ID2 IN
          (SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
       AND LOCKS.block=0
  )LOCKED_SESSIONS,
   V$SESSION SESSIONS, 
   DBA_OBJECTS OBJECTS,
   V$LOCKED_OBJECT LOCKED_OBJECTS,
   V$PROCESS PROCESS 
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
  AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
  AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
  AND PROCESS.addr = SESSIONS.paddr;



LOCK_STATUS  LOCK_ID1_ID2           SID TYPE       SID_SERIAL USERNAME   OSUSER     
------------ --------------- ---------- ---------- ---------- ---------- ---------- 
OK           196623-379112          194 TX         194-9665   ALEC_USER  akaplan   
Being Locked 196623-379112          205 TX         205-21296  ALEC_USER  akaplan   
Being Locked 196623-379112          205 TX         205-21296  ALEC_USER  akaplan   

MACHINE       PROGRAM     MODULE     OWNER      OBJECT_NAME     TYPE   PROCESS   SPID
------------- ----------- ---------- ---------- --------------- ------ --------- -----
 1\ALEC-KAPLAN sqlplus.exe SQL*Plus   ALEC_USER  ADDRESS         TABLE 4052:2348 30417
 1\ALEC-KAPLAN sqlplus.exe SQL*Plus   ALEC_USER  ADDRESS         TABLE 2176:3308 20300
 1\ALEC-KAPLAN sqlplus.exe SQL*Plus   ALEC_USER  MLOG$_ADDRESS   TABLE 2176:3308 20300


The SPID column is the Server Process ID
The PROCESS column is the Client Process ID:Thread ID
In this example, the Client is on Windows.

SPID - is the Process on Oracle server:
oracle@my_server:~>% ps -ef | grep oracle | grep 30417 | grep -v grep
oracle   30417     1  0 09:12 ?        00:00:00 oracleigt (LOCAL=NO)

oracle@my_server:~>% ps -ef | grep 20300 | grep -v grep
oracle   20300     1  0 Oct05 ?        00:00:01 oracleigt (LOCAL=NO)


The PROCESS column is the Client Process ID:Thread ID
In this example, the Windows Process:Thread.

This is the Process running on the Windows Client, as captured with Process Explorer.
The locking process is 4052:2348, i.e. process 4052, thread:2348
The locked process is 2176:3308, i.e. process 2176thread:3308









=================

V$TRANSACTION Table
=================


V$TRANSACTION Table holds additional info regarding the locked and the locking transactions.

SELECT LOCKED_SESSIONS.lock_status,
       id1||'-'||id2 LOCK_ID1_ID2,
       LOCKED_SESSIONS.sid,     
       LOCKED_SESSIONS.type,
       SESSIONS.sid||'-'|| SESSIONS.serial# AS SID_SERIAL#, 
       SESSIONS.username, SESSIONS.osuser, SESSIONS.machine, 
       SESSIONS.program, SESSIONS.module,
       OBJECTS.owner,
       OBJECTS.object_name,
       OBJECTS.object_type,
       LOCKED_OBJECTS.process,
       PROCESS.spid,
       TO_CHAR(TRANSACTION.start_date,'DD/MM/YYYY hh24:mm:ss') AS TX_START_DATE,
       TRANSACTION.status AS TX_STATUS,
       TRANSACTION.start_scn
    FROM(     
    SELECT 
         DECODE(BEING_LOCKED.block,0,'OK','Being Locked') AS LOCK_STATUS,
         BEING_LOCKED.SID AS SID,
         BEING_LOCKED.type, 
         BEING_LOCKED.ID1, 
         BEING_LOCKED.ID2, 
         BEING_LOCKED.lmode, 
         BEING_LOCKED.request, 
         BEING_LOCKED.block
    FROM V$LOCK BEING_LOCKED
    WHERE 1=1
      AND BEING_LOCKED.block=1
    UNION ALL
    SELECT 
         DECODE(LOCKS.block,0,'OK','Being Locked') AS LOCK_STATUS,            
         LOCKS.SID AS SID,           
         LOCKS.type, 
         LOCKS.ID1, 
         LOCKS.ID2, 
         LOCKS.lmode, 
         LOCKS.request, 
         LOCKS.block
    FROM V$LOCK LOCKS
    WHERE 1=1
      AND LOCKS.block=0
      AND ID1||ID2 IN (SELECT ID1||ID2 FROM V$LOCK BEING_LOCKED WHERE BEING_LOCKED.block = 1)
    AND LOCKS.block=0
    )LOCKED_SESSIONS,
V$SESSION SESSIONS, 
DBA_OBJECTS OBJECTS,
V$LOCKED_OBJECT LOCKED_OBJECTS,
V$PROCESS PROCESS,
V$TRANSACTION TRANSACTION
WHERE SESSIONS.sid = LOCKED_SESSIONS.sid
  AND LOCKED_SESSIONS.sid = LOCKED_OBJECTS.session_id
  AND SESSIONS.username = 'ALEC_USER' 
  AND SESSIONS.program = 'sqlplus.exe'
  AND OBJECTS.object_id = LOCKED_OBJECTS.object_id
  AND PROCESS.addr = SESSIONS.paddr
  AND SESSIONS.saddr = TRANSACTION.ses_addr


LOCK_STATUS LOCK_ID1_ID2 SID TYPE SID_SERIAL USERNAME OSUSER MACHINE PROGRAM MODULE
------------ ------------- --- ---- ---------- --------- ------- ------------- ----------- --------
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus
Being Locked 196623-379112 205 TX 205-21296 ALEC_USER akaplan 1\ALEC-KAPLAN sqlplus.exe SQL*Plus

OWNER OBJECT_NAME OBJECT_TYPE PROCESS SPID TX_START_DATE TX_STATUS START_SCN 
--------- ------------- ---------- --------- ----- ------------------- --------- ------------
ALEC_USER ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978
ALEC_USER MLOG$_ADDRESS TABLE 2176:3308 20300 06/10/2014 08:10:43 ACTIVE 230682831978




==========================
V$LOCK Table in details
==========================
V$LOCK.block
 0 - The session is not blocking other sessions
1 - The session is blocking other sessions

V$LOCK.request
0 - The session is not requesting for a lock
1-6 - The session is requesting for a lock. The number gives additional info.

V$LOCK.lmode  
0 - The session is not holding a lock
1-6 - The sessions is holding a lock. The number gives additional info.

V$LOCK.id1 and V$LOCK.id2
When sessions A is locking sessions B - sessions could be related by valID1 and ID2  values.

V$LOCK.sid
Session Identifier

V$LOCK.type
User Type Lock or System Type Lock
- User type locks:
  TM - DML enqueue
  TX - Transaction enqueue
  UL - User supplied

- System Types Locks
  Are held for extremely short periods of time. They are listed below.

  BL - Buffer hash table instance
  NA..NZ - Library cache pin instance (A..Z = namespace)
  CF - Control file schema global enqueue
  PF - Password File
  CI - Cross-instance function invocation instance
  PI, PS - Parallel operation
  CU - Cursor bind
  PR - Process startup
  DF - datafile instance
  QA..QZ - Row cache instance (A..Z = cache)
  DL - Direct loader parallel index create
  RT - Redo thread global enqueue
  DM - Mount/startup db primary/secondary instance
  SC - System change number instance
  DR - Distributed recovery process
  SM - SMON
  DX - Distributed transaction entry
  SN - Sequence number instance
  FS - File set
  SQ - Sequence number enqueue
  HW - Space management operations on a specific segment
  SS - Sort segment
  IN - Instance number
  ST - Space transaction enqueue
  IR - Instance recovery serialization global enqueue
  SV - Sequence number value
  IS - Instance state
  TA - Generic enqueue
  IV - Library cache invalidation instance
  TS - Temporary segment enqueue (ID2=0)
  JQ - Job queue
  TS - New block allocation enqueue (ID2=1)
  KK - Thread kick
  TT - Temporary table enqueue
  LA .. LP - Library cache lock instance lock (A..P = namespace)
  UN - User name
  MM - Mount definition global enqueue
  US - Undo segment DDL
  MR - Media recovery

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
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)


==========
Appendix
==========

http://www.orafaq.com/node/854
http://select-star-from.blogspot.co.il/2013/07/how-to-find-sqlsqlid-history-on-oracle.html

1 comment:

  1. Thanks for sharing, nice post! Post really provice useful information!

    An Thái Sơn chia sẻ trẻ sơ sinh nằm nôi điện có tốt không hay võng điện có tốt không và giải đáp cục điện đưa võng giá bao nhiêu cũng như mua máy đưa võng ở tphcm địa chỉ ở đâu uy tín.

    ReplyDelete