深夜两点手机响了电话那头业务主管声音有点发颤“快帮忙看看产线保存工单一直转圈前端报错说数据库表被锁了。”干过Oracle的人对这句话绝对不陌生——表被锁、session卡住、业务停摆隔着屏幕都能感受到那种焦灼。这篇文章就围绕“ORACLE的表被锁了怎么解锁”这件事把一个DBA实际处理锁问题的完整思路写出来先搞懂锁到底锁了什么再用几条SQL精准定位阻塞源头最后稳稳地把锁释放掉。不管你是刚接触Oracle的开发还是偶尔要扛数据库运维的兼职DBA这套方法都能直接用。先说结论Oracle里的“表锁”绝大多数不是整张表被一把大锁扣住而是某个事务持有的行锁和表锁TM锁挡住了别人的DML或DDL操作。普通SELECT不会因为表被锁而卡住这是因为Oracle的读一致性机制会从undo里构造读快照真正会被卡住的是UPDATE、DELETE、SELECT FOR UPDATE以及ALTER TABLE、TRUNCATE这类DDL语句。所以接到“表被锁”的报障第一件事不是急着杀会话而是先判断锁的类型和阻塞链否则很容易错杀无辜把小问题搞成大事故。1. ORACLE表锁的本质先别急着杀搞清楚锁住了什么1.1 锁的类型行锁、表锁、DDL锁怎么区分Oracle锁机制里和“表被锁”直接相关的有三类TX锁事务锁事务修改行时在行上持有的锁也叫行级锁。多个事务更新同一行时后来者必须等待前者提交或回滚。业务卡住最常见的原因就是行锁等待。TM锁表锁事务对表执行DML时会在表级别加一个TM锁目的是防止事务执行期间表结构被DDL修改。我们平时说“锁表了”严格讲是TM锁和行锁共同作用的结果。DDL锁执行ALTER TABLE、DROP、TRUNCATE等DDL时Oracle要获取表级的排他锁。如果表上有未提交的DML事务DDL会被挡住直接抛ORA-00054。打个比方TX锁相当于有人在某个座位上坐着不走别人进不来TM锁相当于整个房间门口挂了“正在使用”的牌子施工队DDL没法动这个房间。绝大多数业务卡顿都是“某个座位上的人没走”——一个未提交的长事务牢牢占住几行数据其他人只能排队。1.2 业务场景里最常见的三种“锁表现”不同锁场景业务端看到的现象完全不同处理方式也天差地别场景业务端表现常见来源场景A行锁等待UPDATE/DELETE一直执行中不报错也不结束长事务未提交、SELECT FOR UPDATE忘记COMMIT场景BDDL被堵执行TRUNCATE/ALTER TABLE时报ORA-00054表上存在未提交的DML事务场景C应用界面卡死前端页面保存/提交一直转圈比如EBS工单界面表单会话长时间占用事务未提交我在实际维护中遇到过很多次“假锁表”业务方说表被锁了结果一查v$locked_object发现根本没有锁只是某个大SQL全表扫描把CPU打满了UI看起来像卡住。所以定位问题之前先用下面几节的办法确认是不是真有锁再决定要不要动手。2. 定位锁现场用SQL精准找到阻塞源头2.1 第一件事查v$locked_object看谁锁了什么接到报障后我习惯先跑这条SQL30秒内搞清楚“哪些表上有锁、哪个会话持有”SELECT l.session_id AS sid, s.serial#, s.username, o.object_name, o.object_type, l.locked_mode, s.status, s.program, s.machine, s.logon_time FROM v$locked_object l JOIN all_objects o ON l.object_id o.object_id JOIN v$session s ON l.session_id s.sid ORDER BY l.session_id;这条SQL的输出里重点看几个字段locked_mode3代表行级排他锁DML常见4/5/6代表表级共享/排他锁一般和LOCK TABLE语句或DDL有关。program / machine能看出是哪个应用连接的比如EBS表单、PLSQL Developer、还是Java应用。logon_time会话登录多久了。如果登录了几个小时还没提交问题基本就出在这个会话上。locked_mode字段的详细含义可以对照这张表locked_mode值锁模式说明0NONE无锁1NULL空锁2RS (Row Share)行共享允许并发DML3RX (Row Exclusive)行排他普通DML持有4S (Share)表共享阻止DML5SRX (Share Row Exclusive)共享行排他6X (Exclusive)表排他DDL/TRUNCATE场景普通UPDATE、DELETE、INSERT产生的锁模式是3RX这个其实不算“整表锁”只是事务正在操作这张表。如果看到4或6就要警惕是不是有人执行了LOCK TABLE这在OLTP系统里通常是异常操作。2.2 找到真正的阻塞者blocking_session和锁等待链查出谁在锁表还不够还要看谁在等锁、谁在阻塞别人。Oracle的v$session里有个字段叫blocking_session专门记录“当前这个会话被哪个会话阻塞”。SELECT s.sid, s.serial#, s.username, s.blocking_session, bs.username AS blocking_user, bs.program AS blocking_program, s.event, s.wait_class, s.sql_id FROM v$session s LEFT JOIN v$session bs ON s.blocking_session bs.sid WHERE s.blocking_session IS NOT NULL ORDER BY s.blocking_session;如果输出中blocking_session字段有值说明有会话在等锁而被等的那个session就是阻塞源头。锁等待链可能不止一层A等B、B等C这种情况要顺着blocking_session一层一层往上查直到找到最顶层、blocking_session为空且持有锁的那个会话。配套还要看锁等待事件。最常见的是enq: TX - row lock contention行锁竞争多个会话抢同一行。enq: TX - allocate ITL entry事务槽ITL耗尽典型是表上的事务并发太高或者initrans设置过小。enq: TM - contention表锁竞争DDL和DML互相等待。看到事件名基本就能判断是行级冲突还是表级冲突处理策略完全不同。2.3 把会话对应到具体业务语句定位到sid和serial#之后还要查它正在跑什么SQL才能决定能不能杀、要不要先沟通SELECT s.sid, s.serial#, s.username, s.sql_id, s.prev_sql_id, q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id WHERE s.sid IN (sid1, sid2);如果当前sql_id查不到语句就换prev_sql_id上一条执行的SQL看SELECT s.sid, s.serial#, s.username, q.sql_text FROM v$session s, v$sql q WHERE s.prev_sql_id q.sql_id AND s.sid IN (sid1, sid2);实际项目里我经常看到持有锁的会话状态是INACTIVESQL_ID为空看起来人畜无害。这时候千万别被“INACTIVE”骗了——如果它有未提交事务依然牢牢占着锁。查一下v$transaction就能确认SELECT s.sid, s.serial#, s.username, t.start_time, t.used_ublk, t.used_urec FROM v$transaction t JOIN v$session s ON t.addr s.taddr ORDER BY t.start_time;2.4 实战场景EBS WIP工单被锁怎么定位前段时间处理过一个典型问题业务方反馈“Oracle EBS里WIP工单保存不了。”这类报障几乎每次都能用上面三条SQL定位到一张叫WIP_DISCRETE_JOBS离散任务表或WIP_OPERATIONS工序表的核心表被某个会话锁住了。定位步骤就是先查v$locked_object看到object_name是WIP开头的表再查blocking_session发现是一个后台跑批的存储过程会话跑了一整夜还没提交。跑到v$session里看program通常是EBS的并发管理器进程sql_id对应的是一条UPDATE这就锁定了“跑批程序长事务未提交”这个根因。3. 动手解锁三种可靠的释放方式与使用时机3.1 首选方案让事务自己结束别一上来就KILL锁的根源是事务所以最稳妥的解锁方式是让持有锁的事务提交或回滚。如果锁来自某个业务人员的操作界面先联系他确认这个事务还有没有用能不能提交如果业务操作已经完成让他在前端点一下保存或提交锁立刻就释放了连数据库操作都不用做。如果找不到人或者对方也不知道自己在做什么就看看事务持续时间。如果事务刚开启几秒再等等也无妨如果已经挂了半小时以上通常就是程序里漏了COMMIT可以在和业务方确认后选择回滚。注意直接KILL会话会强制回滚事务大事务回滚可能要很久期间锁还会继续存在。所以能“让事务自然结束”就不要“杀会话”这是处理锁问题的第一原则。3.2 常用方案ALTER SYSTEM KILL SESSION当事务无法自然结束时就用Oracle官方的“杀会话”命令。语法很简单ALTER SYSTEM KILL SESSION sid,serial# IMMEDIATE;sid和serial#来自前面查出来的v$session。加IMMEDIATE的意思是立即中断会话不做等待不加的话Oracle会等当前操作完成可能会一直等下去。执行这条命令后会发生什么会话被标记为KILLED正在执行的SQL被中断。如果该会话有未提交事务Oracle会在后台启动回滚回滚完成前锁不会释放。在v$session里你会看到状态变成KILLED但可能残留一段时间。这里有个很多新手踩过的坑KILL之后以为万事大吉结果业务还是卡。原因就是回滚还没结束。判断回滚是否完成可以查v$session里KILLED会话是否还在也可以用以下语句看回滚进度SELECT s.sid, s.serial#, s.username, t.used_ublk, t.used_urec, t.start_time FROM v$session s, v$transaction t WHERE s.saddr t.ses_addr AND s.status KILLED;如果查不到记录说明事务已经清理完毕锁释放了。3.3 兜底方案DISCONNECT SESSION与操作系统级KILL有些顽固会话用ALTER SYSTEM KILL SESSION怎么也杀不掉典型的场景是网络断连导致的“假死”会话。某些后台进程状态异常Oracle内部无法正常中断。杀完后状态一直是KILLED但进程还在占用资源。这时候可以换ALTER SYSTEM DISCONNECT SESSIONALTER SYSTEM DISCONNECT SESSION sid,serial# IMMEDIATE;这条命令在RAC环境里也支持语义很像拔网线强制断开该会话。比KILL更暴力但更有效。如果再不行就只能去操作系统层面杀了。先查出会话对应的操作系统进程号SPIDSELECT s.sid, s.serial#, s.username, p.spid FROM v$session s JOIN v$process p ON s.paddr p.addr WHERE s.sid sid;然后在数据库服务器上执行kill -9 spid注意kill -9是最后手段必须确认SPID对应的是数据库进程不是别的业务进程。在RAC环境里还要先确定这个SPID落在哪个节点上。杀系统进程前一定要和团队确认避免误杀。3.4 处理ORA-00054DDL锁的解锁方式前面说的都是DML锁还有一种经典的“表被锁”是ORA-00054ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired遇到这个报错时说明表上有未提交的DML事务DDL语句无法获取排他锁。处理思路也是先查v$locked_object找到持有TM锁的会话然后联系业务方提交或回滚或者KILL那个会话。如果只是临时执行DDLOracle 11g以后还可以设置DDL锁超时让DDL等待一段时间ALTER SESSION SET ddl_lock_timeout 60; TRUNCATE TABLE t_test;设置后如果在60秒内锁释放DDL会继续执行超过60秒还没拿到锁再报ORA-00054。这个参数对维护窗口非常有用可以避免DDL任务因为偶发锁冲突直接失败。4. 从根上减少锁事务设计、监控与预防4.1 存储过程与长事务提交节奏是锁表的重灾区结合我处理过的案例表锁最大的来源不是业务人员乱操作而是存储过程里的长事务。很多存储过程在一个事务里批量更新几万、几十万行跑几个小时不COMMIT期间整个表的核心数据全被占住。举个例子一个批量更新工单状态的存储过程常见写法是CREATE OR REPLACE PROCEDURE p_update_wip IS BEGIN FOR c IN (SELECT job_id FROM wip_discrete_jobs WHERE status PENDING) LOOP UPDATE wip_discrete_jobs SET status COMPLETE WHERE job_id c.job_id; END LOOP; END;这个写法放在Oracle里整个循环是在一个大事务里完成的只要没跑完所有被更新过的行都会一直锁着。正确做法是分段提交每处理N条就COMMIT一次CREATE OR REPLACE PROCEDURE p_update_wip IS v_cnt NUMBER : 0; BEGIN FOR c IN (SELECT job_id FROM wip_discrete_jobs WHERE status PENDING) LOOP UPDATE wip_discrete_jobs SET status COMPLETE WHERE job_id c.job_id; v_cnt : v_cnt 1; IF MOD(v_cnt, 1000) 0 THEN COMMIT; END IF; END LOOP; COMMIT; END;注意分段提交也不是对任何场景都适用。如果中间某个批次失败前面已提交的数据不能回滚需要应用自己处理断点续跑。对于可以容忍“分段交付”的批处理任务这种写法能极大降低锁表概率。批处理任务尽量安排在业务低峰期也能避开高并发时段。4.2 监控脚本化不等业务来报障提前发现锁与其等业务半夜打电话不如自己先建一个锁监控脚本。我习惯把这样一个SQL存成运维脚本定时跑一遍遇到锁就报警SELECT l.session_id AS sid, s.serial#, s.username, s.program, o.object_name, s.event, s.blocking_session, s.status, s.logon_time, ALTER SYSTEM KILL SESSION ||l.session_id||,||s.serial#|| IMMEDIATE; AS kill_sql FROM v$locked_object l JOIN all_objects o ON l.object_id o.object_id JOIN v$session s ON l.session_id s.sid WHERE s.type USER AND o.object_name NOT LIKE BIN$% ORDER BY s.logon_time;脚本里我把KILL语句也拼出来了真到要操作时复制即可。有个小技巧我特意加了一个过滤条件NOT LIKE BIN$%避免把Oracle的回收站对象BIN$开头的表误报成业务表锁。以前有过一次误报警查了半天发现锁的是一张被DROP过的表虚惊一场加了过滤之后清净多了。4.3 应用层优化NOWAIT、SKIP LOCKED与超时控制代码层面也能主动规避锁等待SELECT FOR UPDATE加NOWAIT如果拿不到锁就立刻报错而不是无限等待。业务端可以捕获异常后提示“数据正在被其他用户修改”比让用户干瞪眼强得多。UPDATE批量操作加WHERE条件限定范围更新尽可能精确地定位到行缩小锁的范围。SKIP LOCKED12c适合队列类场景。比如多个并行程序抢任务用FOR UPDATE SKIP LOCKED让每个进程只拿未被锁的行避免互相阻塞。举个SKIP LOCKED的使用例子这在Oracle 12c以后可以这样写SELECT task_id FROM t_task_queue WHERE status READY ORDER BY task_id FOR UPDATE SKIP LOCKED;每个并行会话执行这条SQL时只会拿到未被别人锁住的任务行天然避免了并发抢占时的锁等待。这个写法在处理任务队列、批处理分片时非常实用。5. 高频问题速查实际处理中踩过的坑下面整理的这些问题每一个都是我或身边同事真实遇到过、处理过的建议收藏备用。5.1 常见问题对照表问题现象可能原因处理方式KILL SESSION后锁还在事务正在回滚kill后会话残留查v$transaction内KILLED会话等回滚完成查v$locked_object无记录但业务卡住锁在数据字典或内部对象上业务被内部锁阻塞查v$session、v$lock结合事件分析ORA-00054DDL和DML冲突定位持有TM锁的会话并释放或设置ddl_lock_timeoutORA-00060死锁两个会话互相等锁Oracle自动回滚一方应用需要捕获并重试数据库侧无需处理会话状态INACTIVE但锁未释放漏COMMIT客户端空闲但事务未提交联系业务方确认后回滚再考虑KILL会话KILL不掉网络断连/假死用DISCONNECT SESSION最后用操作系统killUPDATE慢但不报锁错误不是锁可能是SQL性能差或ITL等待查事件是不是enq: TX再做SQL优化锁的session找不到业务方后台跑批作业占锁未提交查LOGON_TIME和SQL确认任务后终止5.2 两个值得记住的教训教训一不要随手KILL第一个看到的会话。有一次我处理锁问题查v$locked_object看到一个会话锁了核心业务表顺手就KILL了。结果那是公司领导在测试环境打开的PLSQL Developer窗口正在做一个大事务的验证。那一瞬间电话就来了。之后我的习惯变成任何KILL操作前先看SQL、看登录时间、看客户端信息最好再跟业务方口头确认一下。宁可慢一分钟不要错杀一个。教训二不要把“锁表”和“SQL慢”混为一谈。有一次业务说表被锁所有更新都卡住。我按老脚本查锁发现根本没有锁。再看等待事件发现大量会话在等“db file sequential read”其实是某条UPDATE语句的WHERE条件没走索引在疯狂做全表扫描。这类问题的解法是优化SQL加索引而不是去解锁。判断一个卡顿到底是不是锁关键是看会话的wait eventenq开头的通常是锁其他IO、CPU相关的多半是性能问题。5.3 处理锁问题的标准流程最后把整套流程压缩成五步可以直接贴在工位上查v$locked_object确认哪些对象被锁、被谁锁。查blocking_session找出最顶层的阻塞会话。查阻塞会话的SQL和program判断是业务操作还是后台作业。优先让业务方自己提交或回滚不行再用ALTER SYSTEM KILL SESSION。KILL之后观察v$session和v$transaction确认锁真的释放。我个人在实际操作中的体会是锁问题超过八成都是“事务没提交”造成的而“事务没提交”里又有八成和存储过程、批处理脚本的提交策略有关。把代码层面的提交节奏管好把监控脚本跑起来真正需要半夜爬起来人工解锁的场合会越来越少。还是那句话——系统的稳定性靠的不是事后补救的手速而是事前设计的前瞻。