Lock and Blocking Analysis

Comprehensive lock detection and analysis with detailed explanations (Access: DBA or lock management privileges)

-- LOCK DETECTION AND ANALYSIS - COMPREHENSIVE GUIDE
-- ================================================
-- Oracle uses different types of locks to maintain data consistency
-- This guide shows how to identify, analyze, and resolve lock issues

-- 1. CURRENT LOCKS IN DATABASE
-- =============================
-- Shows all active locks with session and object information
-- Access: DBA or users with DBA_LOCKS privilege
SELECT 
    s.sid,                    -- Session ID
    s.serial#,                -- Session serial number
    s.username,               -- Database username
    s.program,                -- Application program name
    l.type,                   -- Lock type (TM=table, TX=transaction, etc.)
    l.mode_held,              -- Lock mode held (S=share, X=exclusive, etc.)
    l.mode_requested,         -- Lock mode requested
    o.owner,                  -- Object owner
    o.object_name,            -- Object name (table, index, etc.)
    o.object_type             -- Object type (TABLE, INDEX, etc.)
FROM v$session s, v$lock l, dba_objects o
WHERE s.sid = l.sid
AND l.id1 = o.object_id
AND l.type IN ('TM', 'TX')    -- Focus on table and transaction locks
ORDER BY s.sid;

-- 2. BLOCKING SESSIONS ANALYSIS
-- =============================
-- Identifies which sessions are blocking others
-- Critical for resolving performance issues
SELECT 
    blocking_sid,             -- Session ID of the blocking session
    blocked_sid,              -- Session ID of the blocked session
    blocking_serial#,         -- Serial number of blocking session
    blocked_serial#,          -- Serial number of blocked session
    blocking_username,        -- Username of blocking session
    blocked_username,         -- Username of blocked session
    blocking_program,         -- Program name of blocking session
    blocked_program,          -- Program name of blocked session
    wait_time,                -- Time waited (in seconds)
    seconds_in_wait           -- Current wait time
FROM v$session_blockers
ORDER BY blocking_sid;

-- 3. LOCK WAITERS - SESSIONS WAITING FOR LOCKS
-- ============================================
-- Shows sessions that are waiting for locks to be released
SELECT 
    waiting_sid,              -- Session ID waiting for lock
    waiting_serial#,          -- Serial number of waiting session
    waiting_username,         -- Username of waiting session
    waiting_program,          -- Program name of waiting session
    holding_sid,              -- Session ID holding the lock
    holding_serial#,          -- Serial number of holding session
    holding_username,         -- Username of holding session
    holding_program,          -- Program name of holding session
    lock_type,                -- Type of lock being waited for
    mode_held,                -- Lock mode held by holding session
    mode_requested            -- Lock mode requested by waiting session
FROM v$session_waiters
ORDER BY waiting_sid;

-- 4. DEADLOCK DETECTION
-- =====================
-- Identifies deadlock situations where sessions are waiting for each other
SELECT 
    deadlock_id,              -- Unique deadlock identifier
    session_id,               -- Session involved in deadlock
    serial#,                  -- Session serial number
    username,                 -- Username of session
    program,                  -- Program name
    deadlock_time             -- When deadlock occurred
FROM v$deadlock_detection
ORDER BY deadlock_time DESC;

-- 5. LOCK STATISTICS - PERFORMANCE METRICS
-- ========================================
-- Shows lock performance statistics
SELECT 
    lock_type,                -- Type of lock
    total_waits,              -- Total number of waits
    time_waited,              -- Total time waited (in seconds)
    average_wait,             -- Average wait time per lock
    max_wait                  -- Maximum wait time
FROM v$lock_statistics
ORDER BY time_waited DESC;

-- 6. SESSION MANAGEMENT - RESOLVING BLOCKING ISSUES
-- ================================================
-- Commands to resolve blocking situations

-- Kill blocking session immediately (use with caution)
-- Access: DBA or users with ALTER SYSTEM privilege
-- ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

-- Kill session gracefully (allows current transaction to complete)
-- Access: DBA or users with ALTER SYSTEM privilege
-- ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

-- Example: Kill session with SID=123 and SERIAL#=456
-- ALTER SYSTEM KILL SESSION '123,456' IMMEDIATE;

-- 7. LOCK TYPES EXPLANATION
-- ========================
-- TM (Table Mode): Table-level locks
--   - S (Share): Multiple sessions can read, none can write
--   - X (Exclusive): Only one session can read/write
--   - SS (Row Share): Allows concurrent reads, blocks exclusive writes
--   - SX (Row Exclusive): Allows concurrent reads, blocks exclusive writes
--   - SSX (Share Row Exclusive): Allows reads, blocks all writes
--   - X (Exclusive): Blocks all other locks

-- TX (Transaction): Transaction-level locks
--   - Used for row-level locking
--   - Automatically acquired when modifying data
--   - Released when transaction commits or rolls back

-- 8. PREVENTIVE MEASURES
-- ======================
-- - Use appropriate transaction isolation levels
-- - Keep transactions short
-- - Use SELECT FOR UPDATE NOWAIT for immediate feedback
-- - Implement proper application design to minimize lock contention
-- - Monitor lock statistics regularly

Wait event analysis and blocking resolution (Access: DBA or performance views)

-- Current wait events
-- Access: DBA or users with DBA_SESSION_WAITS privilege
SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.program,
    w.event,
    w.wait_time,
    w.seconds_in_wait,
    w.state,
    w.p1,
    w.p2,
    w.p3
FROM v$session s, v$session_wait w
WHERE s.sid = w.sid
AND s.status = 'ACTIVE'
ORDER BY w.seconds_in_wait DESC;

-- Top wait events
-- Access: DBA or users with DBA_SYSTEM_EVENT privilege
SELECT 
    event,
    total_waits,
    total_timeouts,
    time_waited,
    average_wait,
    time_waited_micro
FROM v$system_event
WHERE wait_class != 'Idle'
ORDER BY time_waited DESC
FETCH FIRST 10 ROWS ONLY;

-- Enqueue waits
-- Access: DBA or users with DBA_ENQUEUE_STATISTICS privilege
SELECT 
    eq_type,
    total_req,
    total_wait,
    succ_req,
    failed_req,
    cum_wait_time
FROM v$enqueue_statistics
WHERE cum_wait_time > 0
ORDER BY cum_wait_time DESC;

-- Lock contention analysis
-- Access: DBA or users with DBA_LOCK_CONTENTION privilege
SELECT 
    lock_type,
    lock_mode,
    blocking_sessions,
    blocked_sessions,
    wait_time
FROM v$lock_contention
ORDER BY wait_time DESC;

-- Resource waits
-- Access: DBA or users with DBA_RESOURCE_WAITS privilege
SELECT 
    resource_name,
    current_waits,
    total_waits,
    time_waited,
    average_wait
FROM v$resource_wait
ORDER BY time_waited DESC;