Oracle 查询表锁状态方法详解

常用查询锁状态的方法

1. 使用V$LOCK视图

SELECT 
    l.session_id sid,
    s.serial#,
    l.locked_mode,
    l.oracle_username,
    l.os_user_name,
    s.machine,
    s.terminal,
    o.object_name,
    o.object_type
FROM 
    v$locked_object l,
    dba_objects o,
    v$session s
WHERE 
    l.object_id = o.object_id
    AND l.session_id = s.sid
ORDER BY 
    sid, s.serial#;

2. 使用DBA_BLOCKERS和DBA_WAITERS视图

-- 查看阻塞会话
SELECT * FROM dba_blockers;

-- 查看等待会话
SELECT * FROM dba_waiters;

3. 使用V$SESSION_WAIT视图

SELECT 
    sw.sid,
    sw.event,
    sw.wait_time,
    sw.seconds_in_wait,
    sw.state
FROM 
    v$session_wait sw,
    v$session s
WHERE 
    sw.sid = s.sid
    AND s.status = 'ACTIVE'
    AND sw.event NOT LIKE 'SQL*Net%'
ORDER BY 
    sw.seconds_in_wait DESC;

4. 使用V$TRANSACTION和V$SESSION组合查询

SELECT 
    s.sid,
    s.serial#,
    s.username,
    s.status,
    t.start_time,
    t.used_ublk,
    t.used_urec
FROM 
    v$transaction t,
    v$session s
WHERE 
    t.ses_addr = s.saddr;

锁类型说明

Oracle中的锁模式： - 0：无锁 - 1：NULL锁 - 2：行共享锁(RS) - 3：行排他锁(RX) - 4：共享锁(S) - 5：共享行排他锁(SRX) - 6：排他锁(X)

解锁方法

1. 通过ALTER SYSTEM KILL SESSION命令

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

2. 通过操作系统级别终止进程

-- 先查询spid
SELECT s.sid, s.serial#, p.spid, s.osuser, s.program
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.sid = [被锁会话的SID];

-- 然后在操作系统级别kill进程
-- Linux/Unix: kill -9 [spid]
-- Windows: orakill [sid] [spid]

预防锁问题的建议

1. 合理设计事务，尽量缩短事务时间
2. 避免在事务中执行用户交互操作
3. 按照固定顺序访问表和行，避免死锁
4. 使用适当的隔离级别
5. 定期监控数据库中的长时间运行事务

高级锁监控脚本

SELECT 
    l.session_id,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.module,
    o.object_name,
    o.object_type,
    DECODE(l.locked_mode,
           0, 'None',
           1, 'Null',
           2, 'Row Share',
           3, 'Row Exclusive',
           4, 'Share',
           5, 'Share Row Exclusive',
           6, 'Exclusive',
           'Unknown') lock_mode,
    l.oracle_username,
    l.os_user_name,
    s.logon_time,
    s.status
FROM 
    v$locked_object l,
    dba_objects o,
    v$session s
WHERE 
    l.object_id = o.object_id
    AND l.session_id = s.sid
ORDER BY 
    l.session_id;

以上方法可以帮助DBA和开发人员快速定位Oracle数据库中的锁问题，并采取相应措施解决。