引言
在Oracle数据库管理中，锁表问题是最常见且最棘手的性能问题之一。当数据库中的表被锁定时，其他用户无法对该表进行DML操作，严重影响业务连续性。锁表问题通常表现为应用程序响应缓慢、事务无法提交或回滚、甚至导致整个系统挂起。本文将详细讲解如何快速定位锁表源头，并提供安全解锁的方法，帮助DBA和开发人员有效解决锁表问题，同时避免数据丢失风险。
1. Oracle锁机制基础
1.1 锁的类型
Oracle数据库中的锁主要分为以下几类：
行级锁（Row-Level Lock）：DML操作（INSERT、UPDATE、DELETE）在行级别加锁，这是Oracle锁机制的核心特性，最大程度保证并发性。
表级锁（Table-Level Lock）：用于保护表结构，包括：
RS（Row Share）：行共享锁，允许其他事务并发执行DML操作
RX（Row Exclusive）：行排他锁，允许其他事务并发执行DML操作，但禁止其他事务加RS或RX锁
S（Share）：共享锁，允许其他事务读取，但禁止任何DML操作
SRX（Share Row Exclusive）：共享行排他锁，允许其他事务读取，但禁止任何DML操作
X（Exclusive）：排他锁，禁止其他任何事务访问该表
DDL锁（Data Dictionary Lock）：在执行DDL操作（如ALTER TABLE、DROP TABLE）时自动获取，保护表结构不被修改。
DML锁（Data Manipulation Lock）：DML语句自动获取的锁，包括行锁和表锁。
1.2 锁的兼容性
不同类型的锁之间存在兼容性关系，了解这些关系有助于分析锁等待问题：
锁类型
RS
RX
S
SRX
X
RS
✓
✓
✓
✓
✓
RX
✓
✓
✓
✓
✓
S
✓
✓
✓
✓
✓
SRX
✓
✓
✓
✓
✓
X
✓
✓
✓
✓
✓
注意：上表中的”✓”表示兼容，但实际上，当一个事务持有X锁时，其他事务无法获取任何类型的锁（除了NULL锁）。
1.3 锁的持有时间
Oracle中的锁默认会持续到事务结束（提交或回滚）。这意味着如果一个事务长时间未提交，它持有的锁也会一直存在，导致其他事务等待。这是锁表问题最常见的原因。
2. 锁表问题的常见场景
2.1 未提交的事务
最常见的锁表原因是某个会话执行了DML操作但未提交（COMMIT）或回滚（ROLLBACK）。例如：
-- 会话1执行
UPDATE employees SET salary = 5000 WHERE employee_id = 100;
-- 未提交，导致employees表被锁定
-- 会话2执行
UPDATE employees SET salary = 6000 WHERE employee_id = 101;
-- 会话2会等待会话1释放锁
2.2 长时间运行的事务
一个事务中包含多个DML操作，且执行时间很长，导致锁持有时间过长。例如：
-- 会话1执行
BEGIN
FOR rec IN (SELECT * FROM large_table) LOOP
UPDATE another_table SET column1 = rec.value WHERE id = rec.id;
-- 每次循环都持有锁，直到整个循环结束
END LOOP;
COMMIT;
END;
2.3 应用程序逻辑错误
应用程序未正确处理异常，导致事务未提交或回滚。例如：
-- 伪代码
try {
executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
// 如果这里抛出异常，没有catch块处理，事务不会提交
executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
commit();
} catch (Exception e) {
// 没有rollback，事务保持打开状态
}
2.4 外键约束未索引
当父表有未索引的外键列时，删除父表记录或更新外键列会导致子表被锁定。例如：
-- 创建表
CREATE TABLE parent (id NUMBER PRIMARY KEY);
CREATE TABLE child (id NUMBER PRIMARY KEY, parent_id NUMBER);
-- 未在parent_id上创建索引
-- 会话1执行
DELETE FROM parent WHERE id = 1;
-- 会话1会获取child表的表级锁，阻止其他会话修改child表
-- 会话2执行
INSERT INTO child VALUES (2, 1);
-- 会话2会等待会话1释放锁
2.5 DBMS_LOCK包的使用
使用DBMS_LOCK包显式锁定表，但忘记释放。例如：
-- 会话1执行
DECLARE
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.REQUEST(lock_handle => lock_handle, lock_mode => DBMS_LOCK.X_MODE);
-- 锁定后忘记释放
END;
3. 快速定位锁表源头
3.1 使用v\(lock和v\)session视图
这是最常用的方法，通过查询v\(lock和v\)session视图可以找到持有锁的会话信息。
-- 查询当前所有锁信息
SELECT
l.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
s.logon_time,
l.type,
l.id1,
l.id2,
l.lmode,
l.request,
l.block,
o.object_name,
o.object_type
FROM
v$lock l
JOIN
v$session s ON l.sid = s.sid
LEFT JOIN
dba_objects o ON l.id1 = o.object_id
WHERE
l.type IN ('TM', 'TX')
ORDER BY
l.block DESC, l.sid;
字段说明：
sid：会话ID
serial#：序列号，用于唯一标识会话
username：Oracle用户名
osuser：操作系统用户名
machine：客户端机器名
program：客户端程序名
type：锁类型（TM=表锁，TX=事务锁）
id1：对象ID（TM锁）或事务ID（TX锁）
id2：0（TM锁）或回滚段号（TX锁）
lmode：持有锁的模式（0=NULL,1=NULL,2=RS,3=RX,4=S,5=SRX,6=X）
request：请求锁的模式（0=NULL,1=NULL,2=RS,3=RX,4=S,5=SRX,6=X）
block：是否阻塞其他会话（1=是，0=否）
object_name：对象名称
object_type：对象类型
示例输出：
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM MODULE ACTION LOGON_TIME TYPE ID1 ID2 LMODE REQUEST BLOCK OBJECT_NAME OBJECT_TYPE
---- ------- -------- ------ ------- ------------ ------ ------ ---------- ---- --- --- ----- ------- ----- ----------- -----------
105 1234 SCOTT oracle server SQL*Plus SQL*Plus 2024-01-15 TM 7345 0 3 0 1 EMPLOYEES TABLE
106 5678 SCOTT oracle server SQL*Plus SQL*Plus 2024-01-15 TX 65537 10 0 6 0 NULL NULL
分析：
SID=105的会话持有EMPLOYEES表的RX锁（lmode=3），并且阻塞了其他会话（block=1）
SID=106的会话正在请求X锁（request=6），但被阻塞
3.2 使用v$locked_object和dba_objects视图
这种方法可以快速找到被锁定的对象和锁定它的会话。
-- 查询被锁定的对象及锁定会话
SELECT
lo.session_id,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
s.logon_time,
lo.object_id,
o.object_name,
o.object_type,
lo.locked_mode
FROM
v$locked_object lo
JOIN
v$session s ON lo.session_id = s.sid
JOIN
dba_objects o ON lo.object_id = o.object_id
ORDER BY
lo.session_id;
locked_mode字段说明：
0：None
1：Null
2：Row Share (RS)
3：Row Exclusive (RX)
4：Share (S)
5：Share Row Exclusive (SRX)
6：Exclusive (X)
3.3 使用v$session_wait视图
当会话正在等待锁时，可以通过v$session_wait查看等待事件。
-- 查询正在等待锁的会话
SELECT
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM
v$session_wait sw
JOIN
v$session s ON sw.sid = s.sid
WHERE
sw.event LIKE '%lock%'
OR sw.event LIKE '%enq%'
ORDER BY
sw.seconds_in_wait DESC;
3.4 使用v$transaction视图
通过事务视图可以找到长时间未提交的事务。
-- 查询长时间未提交的事务
SELECT
t.start_time,
t.sid,
t.serial#,
t.username,
t.osuser,
t.machine,
t.program,
t.module,
t.action,
t.used_ublk,
t.used_urec,
t.status
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
ORDER BY
t.start_time;
分析：
start_time：事务开始时间
used_ublk：使用的回滚块数
used_urec：使用的回滚记录数
status：事务状态（ACTIVE/INACTIVE）
3.5 使用Oracle Enterprise Manager (OEM)
Oracle Enterprise Manager提供了图形化界面来监控锁：
登录OEM
导航到”Performance” -> “Monitoring” -> “Instance Locks”
查看当前锁信息和等待链
3.6 使用第三方工具
Toad for Oracle：提供锁管理器功能
SQL Developer：提供锁监视器
PL/SQL Developer：提供锁信息查看功能
4. 安全解锁方法
4.1 识别可安全解锁的会话
在解锁之前，必须确认会话是否可以安全终止。需要考虑：
会话的登录时间
事务的开始时间
会话的业务重要性
是否有未提交的重要数据
4.2 使用ALTER SYSTEM KILL SESSION
这是最常用的解锁方法，用于终止持有锁的会话。
语法：
ALTER SYSTEM KILL SESSION 'sid,serial#';
示例：
-- 假设从查询中得到sid=105, serial#=1234
ALTER SYSTEM KILL SESSION '105,1234';
重要参数：
IMMEDIATE：立即终止会话，不等待事务回滚完成
POST_TRANSACTION：等待事务完成后终止会话
-- 立即终止会话（可能造成数据不一致）
ALTER SYSTEM KILL SESSION '105,1234' IMMEDIATE;
-- 等待事务完成后终止会话（推荐）
ALTER SYSTEM KILL SESSION '105,1234' POST_TRANSACTION;
注意事项：
需要ALTER SYSTEM权限
会话被终止后，事务会自动回滚
如果会话处于ACTIVE状态，回滚可能需要较长时间
如果使用IMMEDIATE，可能导致部分数据未提交
4.3 使用操作系统进程终止
当ALTER SYSTEM KILL SESSION无法立即生效时，可以使用操作系统命令终止Oracle进程。
步骤：
查找操作系统进程ID：
-- 查询操作系统进程ID
SELECT
p.pid,
p.spid,
s.sid,
s.serial#,
s.username
FROM
v$process p
JOIN
v$session s ON p.addr = s.paddr
WHERE
s.sid = 105;
在操作系统上终止进程：
# Linux/Unix
kill -9
# Windows
orakill
风险：
可能导致数据库实例异常
可能造成数据不一致
不推荐在生产环境使用
4.4 使用DBMS_SYSTEM.KILL_SESSION
DBMS_SYSTEM包提供了更强大的会话终止功能。
-- 需要SYS用户执行
EXEC DBMS_SYSTEM.KILL_SESSION(105, 1234);
4.5 使用DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
对于无法立即终止的会话，可以先设置跟踪，分析其行为：
-- 启用SQL跟踪
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(105, 1234, TRUE);
-- 稍后查看跟踪文件内容
-- 跟踪文件位于user_dump_dest目录
4.6 预防性措施：设置资源限制
通过资源限制预防长时间运行的事务：
-- 设置事务超时（1小时）
ALTER SYSTEM SET resource_limit = TRUE;
CREATE PROFILE lock_timeout_profile LIMIT
idle_time 60
logical_reads_per_call 1000000;
ALTER USER scott PROFILE lock_timeout_profile;
5. 避免数据丢失风险
5.1 解锁前的数据保护
在解锁前，应确保重要数据已备份或可以恢复：
检查事务状态：
-- 检查会话的事务信息
SELECT
s.sid,
s.serial#,
t.start_time,
t.used_ublk,
t.used_urec,
t.status
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
WHERE
s.sid = 105;
导出未提交数据（如果可能）：
-- 创建临时表保存当前状态
CREATE TABLE temp_backup AS
SELECT * FROM employees WHERE employee_id = 100;
-- 或者使用闪回查询（如果启用）
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE employee_id = 100;
5.2 使用闪回技术恢复数据
如果解锁后发现数据丢失，可以使用闪回技术：
-- 闪回表
FLASHBACK TABLE employees TO TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;
-- 闪回查询
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE employee_id = 100;
-- 闪回删除
FLASHBACK TABLE employees TO BEFORE DROP;
5.3 使用LogMiner分析
如果数据已经丢失，可以使用LogMiner分析重做日志：
-- 添加日志文件
EXEC DBMS_LOGMNR.ADD_LOGFILE('redo01.log', DBMS_LOGMNR.NEW);
-- 开始分析
EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-- 查询分析结果
SELECT * FROM V$LOGMNR_CONTENTS WHERE seg_name = 'EMPLOYEES';
-- 结束分析
EXEC DBMS_LOGMNR.END_LOGMNR;
5.4 使用数据泵导出/导入
对于重要数据，定期使用数据泵进行备份：
# 导出
expdp system/password@orcl tables=employees directory=DATA_PUMP_DIR dumpfile=employees.dmp
# 导入
impdp system/password@orcl tables=employees directory=DATA_PUMP_DIR dumpfile=employees.dmp
5.5 应用程序层面的保护
在应用程序中实现事务保护机制：
// Java示例：使用try-catch-finally确保事务正确处理
public void updateSalary(Long employeeId, BigDecimal newSalary) {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
// 执行更新
PreparedStatement stmt = conn.prepareStatement(
"UPDATE employees SET salary = ? WHERE employee_id = ?");
stmt.setBigDecimal(1, newSalary);
stmt.setLong(2, employeeId);
stmt.executeUpdate();
// 提交事务
conn.commit();
} catch (SQLException e) {
// 发生异常时回滚
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex) {
logger.error("Rollback failed", ex);
}
}
logger.error("Update failed", e);
throw new RuntimeException(e);
} finally {
// 确保连接关闭
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("Close connection failed", e);
}
}
}
}
6. 实战案例：完整解决流程
6.1 问题描述
某电商系统在高峰期出现订单表（orders）无法更新，用户无法下单。需要快速定位并解决问题。
6.2 诊断步骤
步骤1：查询当前锁信息
-- 查询被锁定的表和锁定会话
SELECT
lo.session_id,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
s.logon_time,
o.object_name,
o.object_type,
lo.locked_mode,
t.start_time,
t.used_ublk,
t.used_urec
FROM
v$locked_object lo
JOIN
v$session s ON lo.session_id = s.sid
JOIN
dba_objects o ON lo.object_id = o.object_id
LEFT JOIN
v$transaction t ON t.ses_addr = s.saddr
WHERE
o.object_name = 'ORDERS';
步骤2：分析阻塞链
-- 查询阻塞关系
SELECT
blocking_session,
sid,
serial#,
username,
event,
seconds_in_wait
FROM
v$session
WHERE
blocking_session IS NOT NULL
ORDER BY
seconds_in_wait DESC;
步骤3：检查会话详情
-- 查看会话正在执行的SQL
SELECT
s.sid,
s.serial#,
s.username,
sql_text
FROM
v$session s
JOIN
v$sqlarea sql ON s.sql_address = sql.address
WHERE
s.sid = 105;
6.3 解决方案
方案A：等待事务自然完成（推荐）
-- 如果事务即将完成，可以等待
-- 监控事务进度
SELECT
s.sid,
s.serial#,
t.start_time,
t.used_ublk,
(SELECT SUM(blocks) FROM dba_segments WHERE segment_name = 'ORDERS') total_blocks,
ROUND(t.used_ublk / (SELECT SUM(blocks) FROM dba_segments WHERE segment_name = 'ORDERS') * 100, 2) percent_complete
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
WHERE
s.sid = 105;
方案B：安全终止会话
-- 如果事务长时间未响应，安全终止会话
-- 首先记录会话信息
SELECT * FROM v$session WHERE sid = 105;
-- 然后终止会话（优先使用POST_TRANSACTION）
ALTER SYSTEM KILL SESSION '105,1234' POST_TRANSACTION;
-- 如果仍然无法终止，使用IMMEDIATE
ALTER SYSTEM KILL SESSION '105,1234' IMMEDIATE;
方案C：紧急情况下使用操作系统终止
# 查找操作系统进程ID
SELECT spid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = 105;
# Linux下终止进程
kill -9
# Windows下终止进程
orakill
6.4 后续处理
检查数据一致性：
-- 检查orders表是否有不一致
SELECT COUNT(*) FROM orders;
-- 检查是否有未提交的事务影响
SELECT * FROM orders WHERE order_id = ...;
分析根本原因：
-- 查看历史SQL
SELECT * FROM v$sqlarea WHERE sql_text LIKE '%orders%' ORDER BY last_active_time DESC;
-- 查看会话历史等待事件
SELECT * FROM v$session_event WHERE sid = 105 ORDER BY total_waits DESC;
7. 预防措施和最佳实践
7.1 应用程序设计最佳实践
短事务原则：
保持事务尽可能短
避免在事务中进行网络调用或文件I/O
避免在事务中执行复杂计算
异常处理：
// 确保在所有异常路径都正确处理事务
try {
// 业务逻辑
commit();
} catch (Exception e) {
rollback();
throw e;
} finally {
closeResources();
}
连接池配置：
设置合理的连接超时
启用连接验证
配置连接回收策略
7.2 数据库配置优化
设置资源限制：
-- 创建资源限制文件
CREATE PROFILE app_user_profile LIMIT
cpu_per_session DEFAULT
cpu_per_call DEFAULT
logical_reads_per_session DEFAULT
logical_reads_per_call 100000
connect_time 480
idle_time 60
failed_login_attempts 3
password_lock_time 1
password_life_time 90
password_grace_time 7;
ALTER USER appuser PROFILE app_user_profile;
监控长时间运行事务：
-- 创建监控视图
CREATE OR REPLACE VIEW v$long_running_transactions AS
SELECT
s.sid,
s.serial#,
s.username,
t.start_time,
t.used_ublk,
t.used_urec,
ROUND((SYSDATE - t.start_time) * 24 * 60, 2) duration_minutes
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
WHERE
(SYSDATE - t.start_time) * 24 * 60 > 30; -- 超过30分钟
-- 定期查询
SELECT * FROM v$long_running_transactions;
启用自动任务监控：
-- 创建作业监控长时间事务
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MONITOR_LONG_TXNS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
FOR rec IN (SELECT sid, serial# FROM v$long_running_transactions) LOOP
-- 记录到日志表
INSERT INTO lock_monitor_log VALUES (rec.sid, rec.serial#, SYSDATE);
END LOOP;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
enabled => TRUE);
END;
/
7.3 外键索引管理
确保所有外键列都有索引：
-- 查找未索引的外键
SELECT
table_name,
column_name,
constraint_name
FROM
user_cons_columns
WHERE
constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED'
)
AND position = 1
AND column_name NOT IN (
SELECT column_name
FROM user_ind_columns
WHERE index_name IN (
SELECT index_name
FROM user_indexes
WHERE table_name = user_cons_columns.table_name
)
);
7.4 定期维护脚本
创建定期执行的维护脚本：
-- 检查并记录锁信息
CREATE OR REPLACE PROCEDURE check_locks AS
BEGIN
INSERT INTO lock_history
SELECT
lo.session_id,
s.serial#,
s.username,
o.object_name,
lo.locked_mode,
SYSDATE
FROM
v$locked_object lo
JOIN
v$session s ON lo.session_id = s.sid
JOIN
dba_objects o ON lo.object_id = o.object_id;
COMMIT;
END;
/
-- 创建作业每小时执行
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CHECK_LOCKS_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'CHECK_LOCKS',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/
8. 高级技巧和工具
8.1 使用ASH/AWR报告分析锁问题
-- 生成ASH报告
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT(
l_dbid => (SELECT dbid FROM v$database),
l_inst_num => 1,
l_btime => SYSTIMESTAMP - INTERVAL '1' HOUR,
l_etime => SYSTIMESTAMP,
l_sql_id => NULL,
l_wait_class => 'Application'
));
-- 生成AWR报告
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid => (SELECT dbid FROM v$database),
l_inst_num => 1,
l_bid => (SELECT snap_id FROM (SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC) WHERE ROWNUM = 2),
l_eid => (SELECT snap_id FROM (SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC) WHERE ROWNUM = 1)
));
8.2 使用Oracle Trace
-- 启用10046跟踪
EXEC DBMS_SYSTEM.SET_EV(105, 1234, 10046, 1, '');
-- 禁用跟踪
EXEC DBMS_SYSTEM.SET_EV(105, 1234, 10046, 0, '');
-- 分析跟踪文件
tkprof
8.3 使用Hang Analysis
-- 执行Hang Analysis
ALTER SESSION SET events 'immediate trace name hanganalyze level 3';
-- 分析trace文件
-- 文件位于user_dump_dest目录
8.4 使用Oradebug
-- 设置错误事件
EXEC DBMS_SYSTEM.SET_EV(105, 1234, 10046, 1, '');
-- 使用oradebug
oradebug setospid
oradebug unlimit
oradebug dump processstate 10
oradebug dump systemstate 10
9. 总结
Oracle数据库锁表问题虽然常见，但通过系统的方法可以快速定位和解决。关键要点：
快速定位：使用v\(lock、v\)session、v$locked_object等视图组合查询
安全解锁：优先使用ALTER SYSTEM KILL SESSION 'sid,serial#' POST_TRANSACTION
数据保护：解锁前检查事务状态，必要时使用闪回技术
预防为主：优化应用程序设计，设置资源限制，定期监控
记住，解锁操作应该是最后手段。在生产环境中，应优先考虑：
等待事务自然完成
与业务部门沟通确认
做好数据备份和恢复准备
通过本文提供的完整方法论和脚本，您可以有效应对Oracle数据库锁表问题，确保业务连续性和数据安全。# Oracle数据库锁表查询与解锁方法详解 如何快速定位锁表源头并安全解锁避免数据丢失风险
引言
在Oracle数据库管理中，锁表问题是最常见且最棘手的性能问题之一。当数据库中的表被锁定时，其他用户无法对该表进行DML操作，严重影响业务连续性。锁表问题通常表现为应用程序响应缓慢、事务无法提交或回滚、甚至导致整个系统挂起。本文将详细讲解如何快速定位锁表源头，并提供安全解锁的方法，帮助DBA和开发人员有效解决锁表问题，同时避免数据丢失风险。
1. Oracle锁机制基础
1.1 锁的类型
Oracle数据库中的锁主要分为以下几类：
行级锁（Row-Level Lock）：DML操作（INSERT、UPDATE、DELETE）在行级别加锁，这是Oracle锁机制的核心特性，最大程度保证并发性。
表级锁（Table-Level Lock）：用于保护表结构，包括：
RS（Row Share）：行共享锁，允许其他事务并发执行DML操作
RX（Row Exclusive）：行排他锁，允许其他事务并发执行DML操作，但禁止其他事务加RS或RX锁
S（Share）：共享锁，允许其他事务读取，但禁止任何DML操作
SRX（Share Row Exclusive）：共享行排他锁，允许其他事务读取，但禁止任何DML操作
X（Exclusive）：排他锁，禁止其他任何事务访问该表
DDL锁（Data Dictionary Lock）：在执行DDL操作（如ALTER TABLE、DROP TABLE）时自动获取，保护表结构不被修改。
DML锁（Data Manipulation Lock）：DML语句自动获取的锁，包括行锁和表锁。
1.2 锁的兼容性
不同类型的锁之间存在兼容性关系，了解这些关系有助于分析锁等待问题：
锁类型
RS
RX
S
SRX
X
RS
✓
✓
✓
✓
✓
RX
✓
✓
✓
✓
✓
S
✓
✓
✓
✓
✓
SRX
✓
✓
✓
✓
✓
X
✓
✓
✓
✓
✓
注意：上表中的”✓”表示兼容，但实际上，当一个事务持有X锁时，其他事务无法获取任何类型的锁（除了NULL锁）。
1.3 锁的持有时间
Oracle中的锁默认会持续到事务结束（提交或回滚）。这意味着如果一个事务长时间未提交，它持有的锁也会一直存在，导致其他事务等待。这是锁表问题最常见的原因。
2. 锁表问题的常见场景
2.1 未提交的事务
最常见的锁表原因是某个会话执行了DML操作但未提交（COMMIT）或回滚（ROLLBACK）。例如：
-- 会话1执行
UPDATE employees SET salary = 5000 WHERE employee_id = 100;
-- 未提交，导致employees表被锁定
-- 会话2执行
UPDATE employees SET salary = 6000 WHERE employee_id = 101;
-- 会话2会等待会话1释放锁
2.2 长时间运行的事务
一个事务中包含多个DML操作，且执行时间很长，导致锁持有时间过长。例如：
-- 会话1执行
BEGIN
FOR rec IN (SELECT * FROM large_table) LOOP
UPDATE another_table SET column1 = rec.value WHERE id = rec.id;
-- 每次循环都持有锁，直到整个循环结束
END LOOP;
COMMIT;
END;
2.3 应用程序逻辑错误
应用程序未正确处理异常，导致事务未提交或回滚。例如：
-- 伪代码
try {
executeUpdate("UPDATE accounts SET balance = balance - 100 WHERE id = 1");
// 如果这里抛出异常，没有catch块处理，事务不会提交
executeUpdate("UPDATE accounts SET balance = balance + 100 WHERE id = 2");
commit();
} catch (Exception e) {
// 没有rollback，事务保持打开状态
}
2.4 外键约束未索引
当父表有未索引的外键列时，删除父表记录或更新外键列会导致子表被锁定。例如：
-- 创建表
CREATE TABLE parent (id NUMBER PRIMARY KEY);
CREATE TABLE child (id NUMBER PRIMARY KEY, parent_id NUMBER);
-- 未在parent_id上创建索引
-- 会话1执行
DELETE FROM parent WHERE id = 1;
-- 会话1会获取child表的表级锁，阻止其他会话修改child表
-- 会话2执行
INSERT INTO child VALUES (2, 1);
-- 会话2会等待会话1释放锁
2.5 DBMS_LOCK包的使用
使用DBMS_LOCK包显式锁定表，但忘记释放。例如：
-- 会话1执行
DECLARE
lock_handle VARCHAR2(128);
BEGIN
DBMS_LOCK.REQUEST(lock_handle => lock_handle, lock_mode => DBMS_LOCK.X_MODE);
-- 锁定后忘记释放
END;
3. 快速定位锁表源头
3.1 使用v\(lock和v\)session视图
这是最常用的方法，通过查询v\(lock和v\)session视图可以找到持有锁的会话信息。
-- 查询当前所有锁信息
SELECT
l.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
s.logon_time,
l.type,
l.id1,
l.id2,
l.lmode,
l.request,
l.block,
o.object_name,
o.object_type
FROM
v$lock l
JOIN
v$session s ON l.sid = s.sid
LEFT JOIN
dba_objects o ON l.id1 = o.object_id
WHERE
l.type IN ('TM', 'TX')
ORDER BY
l.block DESC, l.sid;
字段说明：
sid：会话ID
serial#：序列号，用于唯一标识会话
username：Oracle用户名
osuser：操作系统用户名
machine：客户端机器名
program：客户端程序名
module：模块名
action：动作名
logon_time：登录时间
type：锁类型（TM=表锁，TX=事务锁）
id1：对象ID（TM锁）或事务ID（TX锁）
id2：0（TM锁）或回滚段号（TX锁）
lmode：持有锁的模式（0=NULL,1=NULL,2=RS,3=RX,4=S,5=SRX,6=X）
request：请求锁的模式（0=NULL,1=NULL,2=RS,3=RX,4=S,5=SRX,6=X）
block：是否阻塞其他会话（1=是，0=否）
object_name：对象名称
object_type：对象类型
示例输出：
SID SERIAL# USERNAME OSUSER MACHINE PROGRAM MODULE ACTION LOGON_TIME TYPE ID1 ID2 LMODE REQUEST BLOCK OBJECT_NAME OBJECT_TYPE
---- ------- -------- ------ ------- ------------ ------ ------ ---------- ---- --- --- ----- ------- ----- ----------- -----------
105 1234 SCOTT oracle server SQL*Plus SQL*Plus 2024-01-15 TM 7345 0 3 0 1 EMPLOYEES TABLE
106 5678 SCOTT oracle server SQL*Plus SQL*Plus 2024-01-15 TX 65537 10 0 6 0 NULL NULL
分析：
SID=105的会话持有EMPLOYEES表的RX锁（lmode=3），并且阻塞了其他会话（block=1）
SID=106的会话正在请求X锁（request=6），但被阻塞
3.2 使用v$locked_object和dba_objects视图
这种方法可以快速找到被锁定的对象和锁定它的会话。
-- 查询被锁定的对象及锁定会话
SELECT
lo.session_id,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
s.logon_time,
lo.object_id,
o.object_name,
o.object_type,
lo.locked_mode
FROM
v$locked_object lo
JOIN
v$session s ON lo.session_id = s.sid
JOIN
dba_objects o ON lo.object_id = o.object_id
ORDER BY
lo.session_id;
locked_mode字段说明：
0：None
1：Null
2：Row Share (RS)
3：Row Exclusive (RX)
4：Share (S)
5：Share Row Exclusive (SRX)
6：Exclusive (X)
3.3 使用v$session_wait视图
当会话正在等待锁时，可以通过v$session_wait查看等待事件。
-- 查询正在等待锁的会话
SELECT
s.sid,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
sw.event,
sw.wait_time,
sw.seconds_in_wait,
sw.state
FROM
v$session_wait sw
JOIN
v$session s ON sw.sid = s.sid
WHERE
sw.event LIKE '%lock%'
OR sw.event LIKE '%enq%'
ORDER BY
sw.seconds_in_wait DESC;
3.4 使用v$transaction视图
通过事务视图可以找到长时间未提交的事务。
-- 查询长时间未提交的事务
SELECT
t.start_time,
t.sid,
t.serial#,
t.username,
t.osuser,
t.machine,
t.program,
t.module,
t.action,
t.used_ublk,
t.used_urec,
t.status
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
ORDER BY
t.start_time;
分析：
start_time：事务开始时间
used_ublk：使用的回滚块数
used_urec：使用的回滚记录数
status：事务状态（ACTIVE/INACTIVE）
3.5 使用Oracle Enterprise Manager (OEM)
Oracle Enterprise Manager提供了图形化界面来监控锁：
登录OEM
导航到”Performance” -> “Monitoring” -> “Instance Locks”
查看当前锁信息和等待链
3.6 使用第三方工具
Toad for Oracle：提供锁管理器功能
SQL Developer：提供锁监视器
PL/SQL Developer：提供锁信息查看功能
4. 安全解锁方法
4.1 识别可安全解锁的会话
在解锁之前，必须确认会话是否可以安全终止。需要考虑：
会话的登录时间
事务的开始时间
会话的业务重要性
是否有未提交的重要数据
4.2 使用ALTER SYSTEM KILL SESSION
这是最常用的解锁方法，用于终止持有锁的会话。
语法：
ALTER SYSTEM KILL SESSION 'sid,serial#';
示例：
-- 假设从查询中得到sid=105, serial#=1234
ALTER SYSTEM KILL SESSION '105,1234';
重要参数：
IMMEDIATE：立即终止会话，不等待事务回滚完成
POST_TRANSACTION：等待事务完成后终止会话
-- 立即终止会话（可能造成数据不一致）
ALTER SYSTEM KILL SESSION '105,1234' IMMEDIATE;
-- 等待事务完成后终止会话（推荐）
ALTER SYSTEM KILL SESSION '105,1234' POST_TRANSACTION;
注意事项：
需要ALTER SYSTEM权限
会话被终止后，事务会自动回滚
如果会话处于ACTIVE状态，回滚可能需要较长时间
如果使用IMMEDIATE，可能导致部分数据未提交
4.3 使用操作系统进程终止
当ALTER SYSTEM KILL SESSION无法立即生效时，可以使用操作系统命令终止Oracle进程。
步骤：
查找操作系统进程ID：
-- 查询操作系统进程ID
SELECT
p.pid,
p.spid,
s.sid,
s.serial#,
s.username
FROM
v$process p
JOIN
v$session s ON p.addr = s.paddr
WHERE
s.sid = 105;
在操作系统上终止进程：
# Linux/Unix
kill -9
# Windows
orakill
风险：
可能导致数据库实例异常
可能造成数据不一致
不推荐在生产环境使用
4.4 使用DBMS_SYSTEM.KILL_SESSION
DBMS_SYSTEM包提供了更强大的会话终止功能。
-- 需要SYS用户执行
EXEC DBMS_SYSTEM.KILL_SESSION(105, 1234);
4.5 使用DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION
对于无法立即终止的会话，可以先设置跟踪，分析其行为：
-- 启用SQL跟踪
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(105, 1234, TRUE);
-- 稍后查看跟踪文件内容
-- 跟踪文件位于user_dump_dest目录
4.6 预防性措施：设置资源限制
通过资源限制预防长时间运行的事务：
-- 设置事务超时（1小时）
ALTER SYSTEM SET resource_limit = TRUE;
CREATE PROFILE lock_timeout_profile LIMIT
idle_time 60
logical_reads_per_call 1000000;
ALTER USER scott PROFILE lock_timeout_profile;
5. 避免数据丢失风险
5.1 解锁前的数据保护
在解锁前，应确保重要数据已备份或可以恢复：
检查事务状态：
-- 检查会话的事务信息
SELECT
s.sid,
s.serial#,
t.start_time,
t.used_ublk,
t.used_urec,
t.status
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
WHERE
s.sid = 105;
导出未提交数据（如果可能）：
-- 创建临时表保存当前状态
CREATE TABLE temp_backup AS
SELECT * FROM employees WHERE employee_id = 100;
-- 或者使用闪回查询（如果启用）
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE employee_id = 100;
5.2 使用闪回技术恢复数据
如果解锁后发现数据丢失，可以使用闪回技术：
-- 闪回表
FLASHBACK TABLE employees TO TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR;
-- 闪回查询
SELECT * FROM employees AS OF TIMESTAMP SYSTIMESTAMP - INTERVAL '1' HOUR
WHERE employee_id = 100;
-- 闪回删除
FLASHBACK TABLE employees TO BEFORE DROP;
5.3 使用LogMiner分析
如果数据已经丢失，可以使用LogMiner分析重做日志：
-- 添加日志文件
EXEC DBMS_LOGMNR.ADD_LOGFILE('redo01.log', DBMS_LOGMNR.NEW);
-- 开始分析
EXEC DBMS_LOGMNR.START_LOGMNR(OPTIONS => DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG);
-- 查询分析结果
SELECT * FROM V$LOGMNR_CONTENTS WHERE seg_name = 'EMPLOYEES';
-- 结束分析
EXEC DBMS_LOGMNR.END_LOGMNR;
5.4 使用数据泵导出/导入
对于重要数据，定期使用数据泵进行备份：
# 导出
expdp system/password@orcl tables=employees directory=DATA_PUMP_DIR dumpfile=employees.dmp
# 导入
impdp system/password@orcl tables=employees directory=DATA_PUMP_DIR dumpfile=employees.dmp
5.5 应用程序层面的保护
在应用程序中实现事务保护机制：
// Java示例：使用try-catch-finally确保事务正确处理
public void updateSalary(Long employeeId, BigDecimal newSalary) {
Connection conn = null;
try {
conn = dataSource.getConnection();
conn.setAutoCommit(false);
// 执行更新
PreparedStatement stmt = conn.prepareStatement(
"UPDATE employees SET salary = ? WHERE employee_id = ?");
stmt.setBigDecimal(1, newSalary);
stmt.setLong(2, employeeId);
stmt.executeUpdate();
// 提交事务
conn.commit();
} catch (SQLException e) {
// 发生异常时回滚
if (conn != null) {
try {
conn.rollback();
} catch (SQLException ex) {
logger.error("Rollback failed", ex);
}
}
logger.error("Update failed", e);
throw new RuntimeException(e);
} finally {
// 确保连接关闭
if (conn != null) {
try {
conn.close();
} catch (SQLException e) {
logger.error("Close connection failed", e);
}
}
}
}
6. 实战案例：完整解决流程
6.1 问题描述
某电商系统在高峰期出现订单表（orders）无法更新，用户无法下单。需要快速定位并解决问题。
6.2 诊断步骤
步骤1：查询当前锁信息
-- 查询被锁定的表和锁定会话
SELECT
lo.session_id,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.module,
s.action,
s.logon_time,
o.object_name,
o.object_type,
lo.locked_mode,
t.start_time,
t.used_ublk,
t.used_urec
FROM
v$locked_object lo
JOIN
v$session s ON lo.session_id = s.sid
JOIN
dba_objects o ON lo.object_id = o.object_id
LEFT JOIN
v$transaction t ON t.ses_addr = s.saddr
WHERE
o.object_name = 'ORDERS';
步骤2：分析阻塞链
-- 查询阻塞关系
SELECT
blocking_session,
sid,
serial#,
username,
event,
seconds_in_wait
FROM
v$session
WHERE
blocking_session IS NOT NULL
ORDER BY
seconds_in_wait DESC;
步骤3：检查会话详情
-- 查看会话正在执行的SQL
SELECT
s.sid,
s.serial#,
s.username,
sql_text
FROM
v$session s
JOIN
v$sqlarea sql ON s.sql_address = sql.address
WHERE
s.sid = 105;
6.3 解决方案
方案A：等待事务自然完成（推荐）
-- 如果事务即将完成，可以等待
-- 监控事务进度
SELECT
s.sid,
s.serial#,
t.start_time,
t.used_ublk,
(SELECT SUM(blocks) FROM dba_segments WHERE segment_name = 'ORDERS') total_blocks,
ROUND(t.used_ublk / (SELECT SUM(blocks) FROM dba_segments WHERE segment_name = 'ORDERS') * 100, 2) percent_complete
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
WHERE
s.sid = 105;
方案B：安全终止会话
-- 如果事务长时间未响应，安全终止会话
-- 首先记录会话信息
SELECT * FROM v$session WHERE sid = 105;
-- 然后终止会话（优先使用POST_TRANSACTION）
ALTER SYSTEM KILL SESSION '105,1234' POST_TRANSACTION;
-- 如果仍然无法终止，使用IMMEDIATE
ALTER SYSTEM KILL SESSION '105,1234' IMMEDIATE;
方案C：紧急情况下使用操作系统终止
# 查找操作系统进程ID
SELECT spid FROM v$process p, v$session s WHERE p.addr = s.paddr AND s.sid = 105;
# Linux下终止进程
kill -9
# Windows下终止进程
orakill
6.4 后续处理
检查数据一致性：
-- 检查orders表是否有不一致
SELECT COUNT(*) FROM orders;
-- 检查是否有未提交的事务影响
SELECT * FROM orders WHERE order_id = ...;
分析根本原因：
-- 查看历史SQL
SELECT * FROM v$sqlarea WHERE sql_text LIKE '%orders%' ORDER BY last_active_time DESC;
-- 查看会话历史等待事件
SELECT * FROM v$session_event WHERE sid = 105 ORDER BY total_waits DESC;
7. 预防措施和最佳实践
7.1 应用程序设计最佳实践
短事务原则：
保持事务尽可能短
避免在事务中进行网络调用或文件I/O
避免在事务中执行复杂计算
异常处理：
// 确保在所有异常路径都正确处理事务
try {
// 业务逻辑
commit();
} catch (Exception e) {
rollback();
throw e;
} finally {
closeResources();
}
连接池配置：
设置合理的连接超时
启用连接验证
配置连接回收策略
7.2 数据库配置优化
设置资源限制：
-- 创建资源限制文件
CREATE PROFILE app_user_profile LIMIT
cpu_per_session DEFAULT
cpu_per_call DEFAULT
logical_reads_per_session DEFAULT
logical_reads_per_call 100000
connect_time 480
idle_time 60
failed_login_attempts 3
password_lock_time 1
password_life_time 90
password_grace_time 7;
ALTER USER appuser PROFILE app_user_profile;
监控长时间运行事务：
-- 创建监控视图
CREATE OR REPLACE VIEW v$long_running_transactions AS
SELECT
s.sid,
s.serial#,
s.username,
t.start_time,
t.used_ublk,
t.used_urec,
ROUND((SYSDATE - t.start_time) * 24 * 60, 2) duration_minutes
FROM
v$transaction t
JOIN
v$session s ON t.ses_addr = s.saddr
WHERE
(SYSDATE - t.start_time) * 24 * 60 > 30; -- 超过30分钟
-- 定期查询
SELECT * FROM v$long_running_transactions;
启用自动任务监控：
-- 创建作业监控长时间事务
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'MONITOR_LONG_TXNS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
FOR rec IN (SELECT sid, serial# FROM v$long_running_transactions) LOOP
-- 记录到日志表
INSERT INTO lock_monitor_log VALUES (rec.sid, rec.serial#, SYSDATE);
END LOOP;
END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
enabled => TRUE);
END;
/
7.3 外键索引管理
确保所有外键列都有索引：
-- 查找未索引的外键
SELECT
table_name,
column_name,
constraint_name
FROM
user_cons_columns
WHERE
constraint_name IN (
SELECT constraint_name
FROM user_constraints
WHERE constraint_type = 'R'
AND status = 'ENABLED'
)
AND position = 1
AND column_name NOT IN (
SELECT column_name
FROM user_ind_columns
WHERE index_name IN (
SELECT index_name
FROM user_indexes
WHERE table_name = user_cons_columns.table_name
)
);
7.4 定期维护脚本
创建定期执行的维护脚本：
-- 检查并记录锁信息
CREATE OR REPLACE PROCEDURE check_locks AS
BEGIN
INSERT INTO lock_history
SELECT
lo.session_id,
s.serial#,
s.username,
o.object_name,
lo.locked_mode,
SYSDATE
FROM
v$locked_object lo
JOIN
v$session s ON lo.session_id = s.sid
JOIN
dba_objects o ON lo.object_id = o.object_id;
COMMIT;
END;
/
-- 创建作业每小时执行
BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'CHECK_LOCKS_JOB',
job_type => 'STORED_PROCEDURE',
job_action => 'CHECK_LOCKS',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=HOURLY',
enabled => TRUE);
END;
/
8. 高级技巧和工具
8.1 使用ASH/AWR报告分析锁问题
-- 生成ASH报告
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.ASH_REPORT(
l_dbid => (SELECT dbid FROM v$database),
l_inst_num => 1,
l_btime => SYSTIMESTAMP - INTERVAL '1' HOUR,
l_etime => SYSTIMESTAMP,
l_sql_id => NULL,
l_wait_class => 'Application'
));
-- 生成AWR报告
SELECT * FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
l_dbid => (SELECT dbid FROM v$database),
l_inst_num => 1,
l_bid => (SELECT snap_id FROM (SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC) WHERE ROWNUM = 2),
l_eid => (SELECT snap_id FROM (SELECT snap_id FROM dba_hist_snapshot ORDER BY snap_id DESC) WHERE ROWNUM = 1)
));
8.2 使用Oracle Trace
-- 启用10046跟踪
EXEC DBMS_SYSTEM.SET_EV(105, 1234, 10046, 1, '');
-- 禁用跟踪
EXEC DBMS_SYSTEM.SET_EV(105, 1234, 10046, 0, '');
-- 分析跟踪文件
tkprof
8.3 使用Hang Analysis
-- 执行Hang Analysis
ALTER SESSION SET events 'immediate trace name hanganalyze level 3';
-- 分析trace文件
-- 文件位于user_dump_dest目录
8.4 使用Oradebug
-- 设置错误事件
EXEC DBMS_SYSTEM.SET_EV(105, 1234, 10046, 1, '');
-- 使用oradebug
oradebug setospid
oradebug unlimit
oradebug dump processstate 10
oradebug dump systemstate 10
9. 总结
Oracle数据库锁表问题虽然常见，但通过系统的方法可以快速定位和解决。关键要点：
快速定位：使用v\(lock、v\)session、v$locked_object等视图组合查询
安全解锁：优先使用ALTER SYSTEM KILL SESSION 'sid,serial#' POST_TRANSACTION
数据保护：解锁前检查事务状态，必要时使用闪回技术
预防为主：优化应用程序设计，设置资源限制，定期监控
记住，解锁操作应该是最后手段。在生产环境中，应优先考虑：
等待事务自然完成
与业务部门沟通确认
做好数据备份和恢复准备
通过本文提供的完整方法论和脚本，您可以有效应对Oracle数据库锁表问题，确保业务连续性和数据安全。