Blocking and Waiting Locks Analysis (vlockb.sql)

What This Script Does

This script provides comprehensive analysis of blocking and waiting locks in the database, showing the relationship between blocking sessions and sessions waiting for locks. It identifies lock contention scenarios, displays session information for both blockers and waiters, and provides essential details for resolving deadlock situations and performance issues caused by lock contention.

The Script

rem vlockb.sql
ttitle 'Blocking/Waiting Locks'
rem
set lines 500
rem
col ID1 format 9999999999
col ID2 format 9999999999
col LMODE format 999
col REQUEST format 999
col SID format 9999
col username format a9
col osuser format a8
col module format a15
rem ADDR             RAW(4)
rem KADDR            RAW(4)
rem TYPE             VARCHAR2(2)
col CTIME format 99999
col BLOCK format 99999
col blocker format a7
rem
break on id1 on id2 skip 1
rem
select /*+ ordered use_nl(l,s) */
   l.ID1,
   l.ID2,
   decode(l.block, 0, null, 'Blocker') blocker,
   l.SID,
   s.username,
   s.osuser,
   s.module,
   l.LMODE,
   l.REQUEST,
   l.TYPE,
   l.ADDR,
   l.KADDR,
   l.CTIME,
   l.BLOCK
from v$lock l,
     v$session s
where (l.id1, l.id2) in
   (select id1, id2
      from v$lock
     where request > 0)
  and s.sid=l.sid+0
order by l.id1, l.id2, l.block desc, l.lmode desc, l.CTIME desc
/

Key Features

Blocking Chain Analysis: Shows blocker and waiter relationships
Lock Type Identification: Displays specific lock types causing contention
Session Details: Shows username, OS user, and application module
Lock Mode Analysis: Shows held vs. requested lock modes
Timing Information: Displays how long locks have been held
Resource Identification: Shows specific resources being locked
Optimized Query: Uses hints for efficient execution

Usage

@vlockb.sql

No parameters required - analyzes all current blocking/waiting lock situations.

Required Privileges

SELECT on V$LOCK
SELECT on V$SESSION

Sample Output

                                    Blocking/Waiting Locks

      ID1       ID2 BLOCKER   SID USERNAME  OSUSER   MODULE          LMODE REQUEST TY ADDR     KADDR        CTIME  BLOCK
---------- --------- ------- ----- --------- -------- --------------- ----- ------- -- -------- -------- --------- ------
    131075    234567 Blocker   123 HR_USER   jsmith   HR_APP              6       0 TX 1A2B3C4D 5E6F7890      1234      1
                               456 SALES_APP rjones   SALES_MODULE        0       6 TX 1A2B3C4D 5E6F7890       234      0

    131076    345678 Blocker   789 DBA_USER  admin    SQL*Plus            3       0 TM 2B3C4D5E 6F789012       567      1
                               234 APP_USER  dbuser   APP_MODULE          0       3 TM 2B3C4D5E 6F789012       123      0
                               567 BATCH_JOB batch    BATCH_PROC          0       3 TM 2B3C4D5E 6F789012        89      0

    131077    456789 Blocker   345 MAINT_USR maint    MAINT_SCRIPT        6       0 TX 3C4D5E6F 789012AB       890      1
                               678 APP_USER1 user1    WEB_APP             0       6 TX 3C4D5E6F 789012AB       345      0
                               901 APP_USER2 user2    WEB_APP             0       6 TX 3C4D5E6F 789012AB       278      0

Key Output Columns

ID1/ID2: Lock resource identifiers (object and specific resource)
BLOCKER: Indicates if session is blocking others
SID: Session ID
USERNAME: Database username
OSUSER: Operating system username
MODULE: Application or tool name
LMODE: Lock mode held (0=none, 1=null, 2=row-S, 3=row-X, 4=share, 5=S/row-X, 6=exclusive)
REQUEST: Lock mode requested (same scale as LMODE)
TYPE: Lock type (TX=transaction, TM=table, etc.)
ADDR/KADDR: Lock structure addresses
CTIME: Time lock has been held (centiseconds)
BLOCK: Number of sessions this session is blocking

Understanding Lock Types

Transaction Locks (TX)

TX Lock Characteristics

-- TX locks protect:
-- Row-level modifications
-- Transaction rollback segments
-- Unique constraint violations
-- Primary key conflicts

TX Lock Scenarios

-- Common TX lock waits:
-- Row-level contention
-- Duplicate key insertion attempts
-- Foreign key constraint checks
-- Index contention on high-activity tables

Table Locks (TM)

TM Lock Characteristics

-- TM locks protect:
-- Table structure integrity
-- DDL operation coordination
-- DML operation control
-- Foreign key relationships

TM Lock Scenarios

-- Common TM lock waits:
-- DDL operations (ALTER TABLE)
-- Unindexed foreign key updates
-- Table-level lock escalation
-- Import/export operations

Other Lock Types

Additional Lock Types

-- Other common lock types:
-- DX: Distributed transaction locks
-- TT: Temporary table locks
-- UL: User-defined locks
-- MR: Media recovery locks
-- RT: Redo thread locks
-- UN: User name locks

Lock Mode Analysis

Lock Mode Values

Mode Interpretation

-- Lock modes (LMODE/REQUEST):
-- 0: None
-- 1: NULL (NL)
-- 2: Row Share (RS)
-- 3: Row Exclusive (RX)
-- 4: Share (S)
-- 5: Share Row Exclusive (SRX)
-- 6: Exclusive (X)

Compatibility Matrix

-- Lock compatibility:
-- Higher numbers generally block lower numbers
-- Exclusive (6) blocks all other modes
-- Share modes (2,4) can coexist
-- Row exclusive (3) allows other row operations

Common Use Cases

Deadlock Resolution
- Identify blocking session chains
- Find root cause of application hangs
- Support immediate incident resolution
- Plan deadlock prevention strategies
Performance Troubleshooting
- Diagnose slow application response
- Identify lock contention bottlenecks
- Analyze transaction design issues
- Support application optimization
Application Analysis
- Review locking patterns by application
- Identify problematic modules or users
- Assess transaction scope and duration
- Support development troubleshooting
Capacity Planning
- Monitor lock contention trends
- Assess concurrency requirements
- Plan system scaling strategies
- Support architecture decisions

Advanced Analysis

Blocking Chain Analysis

Chain Hierarchy

-- Blocking chain identification:
-- Root blockers (BLOCKER with REQUEST=0)
-- Intermediate blockers (both blocking and waiting)
-- Final waiters (REQUEST>0, BLOCK=0)
-- Complex multi-level scenarios

Impact Assessment

-- Assess blocking impact:
-- Number of sessions affected
-- Duration of blocking (CTIME)
-- Applications involved
-- Business process impact

Lock Duration Analysis

Time-Based Prioritization

-- Prioritize by duration:
-- Long-running locks (high CTIME)
-- Recent contention (low CTIME)
-- Escalating situations
-- Recurring patterns

Performance Impact

-- Duration impact factors:
-- User response time degradation
-- Transaction throughput reduction
-- Application timeout risks
-- System resource consumption

Application Module Analysis

Module-Based Patterns

-- Analyze by application:
-- Specific modules causing blocks
-- Application interaction conflicts
-- Batch job interference
-- Maintenance operation impacts

User Activity Correlation

-- User-based analysis:
-- Problematic user activities
-- OS user vs. database user correlation
-- Session activity patterns
-- Access pattern optimization

Troubleshooting Applications

Immediate Response

Critical Situation Handling

-- Emergency procedures:
-- Identify root blocking sessions
-- Contact session owners
-- Consider session termination
-- Implement immediate workarounds

Session Management

-- Session control options:
-- Request graceful termination
-- Force session termination if necessary
-- Monitor for cascade effects
-- Validate resolution effectiveness

Root Cause Analysis

Lock Pattern Analysis

-- Identify root causes:
-- Application design issues
-- Transaction scope problems
-- Indexing deficiencies
-- Timing and scheduling conflicts

Systemic Issues

-- Broader problem identification:
-- Recurring contention patterns
-- Resource capacity limitations
-- Configuration inadequacies
-- Process coordination failures

Prevention Strategies

Application Design

-- Design improvements:
-- Minimize transaction scope
-- Implement proper indexing
-- Use consistent lock ordering
-- Optimize SQL execution paths

Operational Practices

-- Operational improvements:
-- Schedule batch jobs appropriately
-- Coordinate maintenance activities
-- Implement connection pooling
-- Monitor application behavior

Lock Type Specific Analysis

Transaction Lock Scenarios

Row-Level Contention

-- TX lock analysis:
-- Identify hot rows or blocks
-- Analyze update patterns
-- Assess indexing effectiveness
-- Consider partitioning strategies

Constraint Violations

-- Constraint-related TX locks:
-- Unique constraint violations
-- Primary key conflicts
-- Check constraint failures
-- Resolution and prevention strategies

Table Lock Scenarios

DDL Operation Blocking

-- TM lock analysis:
-- DDL operations in progress
-- Unindexed foreign key impacts
-- Table maintenance conflicts
-- Schema modification coordination

DML Escalation

-- DML-related TM locks:
-- Bulk operation impacts
-- Lock escalation scenarios
-- Resource contention
-- Performance optimization opportunities

Performance Impact Assessment

Response Time Analysis

User Experience Impact

-- Assess user impact:
-- Response time degradation
-- Transaction timeout risks
-- Application availability
-- Service level violations

System Performance

-- System impact evaluation:
-- Resource utilization effects
-- Throughput reduction
-- Concurrency limitations
-- Scalability implications

Business Impact

Process Disruption

-- Business considerations:
-- Critical process interruption
-- Revenue impact assessment
-- Customer service effects
-- Operational efficiency reduction

Best Practices

Monitoring Strategy

Proactive Monitoring

-- Regular monitoring:
-- Scheduled contention checks
-- Real-time alert systems
-- Trend analysis procedures
-- Capacity planning reviews

Incident Response

-- Response procedures:
-- Rapid detection protocols
-- Escalation procedures
-- Resolution documentation
-- Post-incident analysis

Prevention Measures

Development Guidelines

-- Development best practices:
-- Transaction design principles
-- Lock ordering standards
-- Testing procedures
-- Code review processes

Operational Standards

-- Operational procedures:
-- Maintenance scheduling
-- Batch job coordination
-- Performance monitoring
-- Capacity management

Integration with Monitoring Tools

Automated Detection

-- Automation capabilities:
-- Real-time lock monitoring
-- Blocking chain alerts
-- Performance threshold monitoring
-- Automated notification systems

Dashboard Integration

-- Dashboard components:
-- Current blocking situations
-- Lock contention trends
-- Session activity summaries
-- Performance impact metrics

vlock.sql - General lock analysis
enqueue.sql - Enqueue wait analysis
vsession.sql - Session analysis
gvlock.sql - RAC lock analysis

Troubleshooting Common Scenarios

Scenario 1: Long-Running Transaction Blocks

-- Symptoms: High CTIME, many waiters
-- Investigation: Check transaction scope
-- Resolution: Optimize transaction design
-- Prevention: Implement transaction timeouts

Scenario 2: DDL Operation Blocking

-- Symptoms: TM locks, DDL waiting
-- Investigation: Check active sessions
-- Resolution: Coordinate maintenance windows
-- Prevention: Schedule DDL operations

Scenario 3: Application Deadlocks

-- Symptoms: Circular waiting patterns
-- Investigation: Analyze lock ordering
-- Resolution: Implement consistent ordering
-- Prevention: Application design review

Summary

This script is essential for:

Lock Contention Analysis - Comprehensive analysis of blocking and waiting relationships
Deadlock Resolution - Rapid identification and resolution of blocking situations
Performance Troubleshooting - Diagnosing lock-related performance issues
Application Optimization - Understanding and improving application locking behavior
System Monitoring - Proactive detection and prevention of lock contention problems