Finding a locking session

How to identify lockers?

This article will explain about locks on rows and on objects in ORACLE.
Locks on rows can cause performance problems or even impede a transaction from finishing, when there are processes running for long time we need to validate that they are not waiting on a row(s).

When there is a lock on a row there is also a lock on the dependent objects, if we want to perform a DDL on a locked object we will get an ORA-00054 error.

#1 - find blocking sessions with v$session

SELECT
   s.blocking_session, 
   s.sid, 
   s.serial#, 
   s.seconds_in_wait
FROM
   v$session s
WHERE
   blocking_session IS NOT NULL

#2 - find blocking sessions using v$lock

SELECT 
   l1.sid || ' is blocking ' || l2.sid blocking_sessions
FROM 
   v$lock l1, v$lock l2
WHERE
   l1.block = 1 AND
   l2.request > 0 AND
   l1.id1 = l2.id1 AND
   l1.id2 = l2.id2

#3 - Views to find blocking session

•         DBA_WAITERS

•         DBA_BLOCKERS

Scripts for detecting locking

 

Note 1020012.6 TFTS SCRIPT TO RETURN MEDIUM DETAIL LOCKING INFO Note 1020008.6TFTS FULLY DECODED LOCKING SCRIPT
Note 1020007.6 SCRIPT: DISPLAY LOCKS AND GIVE SID AND SERIAL # TO KILL ()
Note 1020010.6 SCRIPT: DISPLAY SQL TEXT FROM LOCKS
Note 1020047.6 SCRIPT: SCRIPT TO DISPLAY USER LOCK INFORMATION Note 1020088.6 SCRIPT: REPORT SESSIONS WAITING FOR LOCKS ()

 

 

How to resolve locking situations?

Most locking issues are application specifics. To resolve locking contention, one needs to free the resource by:

1. Asking the HOLDER to commit or rollback
2. Killing the session which holds the lock,
   For example:
   
   ALTER SESSION KILL SESSION 'sid, serial#';
3.  Killing the  unix/vms shadow process directly.
    This is not recommended as it may prevent proper cleanup of a session
   When killing the shadow process, please be careful of shared servers in a  multi-threaded environment.
4. ROLLBACK FORCE or COMMIT FORCE if 2pc pending transaction.