Oracle 数据库锁与阻塞分析与解决指南

1. 概述

在Oracle数据库中，锁和阻塞是并发控制的关键概念。正确理解和管理它们对于确保数据一致性和提高系统性能至关重要。本文旨在提供关于锁和阻塞的全面分析，并给出相应的解决建议。

2. 锁（Lock）的概念

定义：

锁是一种机制，用于控制多个事务对同一数据库资源（如表、行等）的并发访问，以确保数据的一致性和完整性。

类型：

行级锁（Row-Level Locks）：锁定单个或多个行，允许其他事务访问未被锁定的行。
表级锁（Table-Level Locks）：锁定整个表，阻止其他事务对该表进行某些操作（如插入、更新、删除）。
模式锁（Schema-Level Locks）：用于保护数据库对象结构本身，例如防止对象被修改或删除。

目的：

保证事务的ACID属性（原子性、一致性、隔离性和持久性），特别是在并发环境中。
防止不同事务之间的冲突，比如避免“脏读”、“不可重复读”和“幻读”。

3. 阻塞（Blocking）的概念

定义：

阻塞是指当一个事务试图获取已经被另一个事务持有的锁时，该事务必须等待，直到前一个事务释放锁为止的状态。

原因：

发生阻塞是因为存在锁冲突，即两个或多个事务尝试在同一时间对相同的数据资源执行不兼容的操作（例如，一个事务持有排他锁，而另一个事务请求共享锁）。

影响：

阻塞会导致等待的事务暂时无法继续执行，可能降低系统响应速度，并在极端情况下导致死锁（Deadlock）。

4. 锁和阻塞的区别

特征	锁（Lock）	阻塞（Blocking）
定义	控制并发访问的机制	由于锁冲突导致的等待状态
作用对象	数据库资源（如表、行、模式对象）	尝试获取锁的事务
目的	确保数据一致性和完整性	确保事务按顺序执行，避免冲突
结果	可能引起阻塞	事务暂停执行，等待锁释放
解决方法	使用适当的隔离级别、优化查询	终止长时间等待的事务、调整应用逻辑

5. 查询锁和阻塞情况

查询当前持有锁的会话及其相关信息

SELECT o.owner AS "对象所属用户",o.object_name AS "对象名称",s.sid AS "会话SID",s.serial# AS "会话SERIAL#",s.username AS "用户名",s.osuser AS "操作系统用户",s.machine AS "客户端机器名",s.program AS "使用的程序",l.locked_mode AS "锁定模式"
FROM v$locked_object l
JOIN dba_objects o ON l.object_id = o.object_id
JOIN v$session s ON l.session_id = s.sid;

查询正在等待特定对象上释放锁的会话

WITH locked_objects AS (SELECT l.session_id,o.owner,o.object_name,o.object_type,l.locked_modeFROM v$locked_object lJOIN dba_objects o ON l.object_id = o.object_id
),
waiting_sessions AS (SELECT s.sid,s.serial#,s.username,s.osuser,s.machine,s.program,s.status,s.logon_time,s.event,s.seconds_in_wait,s.wait_class,s.blocking_session,lo.owner,lo.object_name,lo.object_type,lo.locked_modeFROM v$session sLEFT JOIN locked_objects lo ON s.blocking_session = lo.session_idWHERE s.wait_class != 'Idle' -- 排除空闲会话AND s.blocking_session IS NOT NULL -- 只选择有阻塞会话的记录
)
SELECT ws.sid AS "等待会话SID",ws.serial# AS "等待会话SERIAL#",ws.username AS "等待会话用户名",ws.osuser AS "操作系统用户",ws.machine AS "客户端机器名",ws.program AS "使用的程序",ws.status AS "会话状态",ws.logon_time AS "登录时间",ws.event AS "等待事件",ws.seconds_in_wait AS "等待时间(秒)",ws.wait_class AS "等待类别",ws.blocking_session AS "阻塞会话SID",lo.session_id AS "持有锁的会话SID",lo.owner AS "对象所属用户",lo.object_name AS "对象名称",lo.object_type AS "对象类型",CASE lo.locked_modeWHEN 0 THEN 'None'WHEN 1 THEN 'Null (NULL)'WHEN 2 THEN 'Row Share (SS)'WHEN 3 THEN 'Row Exclusive (SX)'WHEN 4 THEN 'Share (S)'WHEN 5 THEN 'Share Row Exclusive (SSX)'WHEN 6 THEN 'Exclusive (X)'ELSE 'Unknown'END AS "锁定模式描述"
FROM waiting_sessions ws
JOIN locked_objects lo ON ws.blocking_session = lo.session_id
ORDER BY ws.blocking_session, ws.sid;

查看阻塞情况并生成阻塞树

-- 查询阻塞树结构
SELECT LPAD(' ', 5 * (LEVEL - 1)) || s.username AS username,LPAD(' ', 5 * (LEVEL - 1)) || s.inst_id || ',' || s.sid AS inst_sid,s.serial#,s.sql_id,s.row_wait_obj#,s.wait_class,s.event,s.p1,s.p2,s.p3,s.seconds_in_wait,s.blocking_instance || ',' || s.blocking_session AS blocking_inst_sid
FROM gv$session s
WHERE s.blocking_session IS NOT NULLOR (s.inst_id || ',' || s.sid) IN (SELECT DISTINCT blocking_instance || ',' || blocking_session FROM gv$sessionWHERE blocking_instance IS NOT NULL AND blocking_session IS NOT NULL)
START WITH s.blocking_session IS NULL -- 从不被其他会话阻塞的会话开始
CONNECT BY PRIOR (s.inst_id || ',' || s.sid) = (s.blocking_instance || ',' || s.blocking_session)
ORDER SIBLINGS BY s.inst_id, s.sid;

查看特定会话正在执行的SQL语句

SELECT s.sid AS "会话SID",s.serial# AS "会话SERIAL#",s.username AS "用户名",s.status AS "会话状态",s.sql_id AS "SQL_ID",q.sql_text AS "SQL语句"
FROM v$session s
LEFT JOIN v$sql q ON s.sql_id = q.sql_id
WHERE s.sid = &sid; -- 替换为实际的会话SID