刚接手一个Oracle生产库还没坐热椅子业务那边就吼过来“表锁了动不了了赶紧处理”这种场景相信干过数据库的人都不陌生。Oracle的表锁是日常运维里碰到频率最高的故障之一处理起来本身并不复杂但很多人栽在“不敢动手”或者“杀错会话”上小问题拖成大事故。这篇就把Oracle表锁的定位、解锁、防锁完整捋一遍照着做基本能解决九成以上的锁表问题。1. 先搞清楚Oracle的表锁到底是怎么回事很多新手一听到“锁表”就慌其实锁是数据库保证数据一致性的基本机制本身不是坏事。Oracle的锁分为很多种和“表锁”直接相关的主要是DML锁也叫TM锁和行级锁TX锁。平时说的“表被锁了”绝大多数情况是某个会话对表做了INSERT、UPDATE、DELETE操作但事务一直没提交COMMIT或者没回滚ROLLBACK导致其他会话想操作同一张表时被阻塞表现为卡住、报ORA-00054或ORA-00060错误。1.1 锁的本质不是“锁表”而是“锁事务”Oracle的行锁机制是这样的当你执行UPDATE某一行时Oracle会在这一行上打一个锁标记同时记录下持有这个锁的事务ID。其他会话如果也想更新同一行就必须等待这个事务结束。如果事务一直不结束锁就一直存在这就是“锁表”现象。想象一下图书馆的预约系统你预约了某本书系统里就有一条“已预约”记录别人想借就得等你还回来或者取消预约。事务不提交就等于你一直占着这本书不还别人自然借不了。1.2 最常见的三种锁表场景场景一开发人员在PL/SQL Developer或Navicat里执行了UPDATE忘了提交。这是最常见的窗口开着人走开了事务挂着锁就一直在。场景二程序代码里开了事务但异常处理没写好事务既没COMMIT也没ROLLBACK。Java的Spring事务、存储过程里都可能出现这种情况连接池里的连接一直被占用事务悬挂。场景三批处理任务跑了一半跑挂了但会话没断开。尤其是一些跑了好几个小时的大事务进程被杀了但数据库会话还在锁也不会自动释放。还有一类是DDL锁比如有人执行了ALTER TABLE或TRUNCATE如果表上有未提交的DML操作DDL会被阻塞或者反过来DDL持有锁导致DML被阻塞。这种症状表现不一样排查思路类似。2. 快速定位三步找到锁的源头解锁之前必须先找到是谁锁的、锁的哪张表、持锁会话的ID是什么千万不能盲杀。Oracle提供了数据字典视图来查看锁信息核心是v$lock、v$locked_object、v$session、v$sql这几个视图。2.1 标准查询SQL直接复制可用第一步查哪些对象被锁了SELECT l.session_id AS sid, s.serial#, s.username, s.osuser, s.machine, s.program, o.object_name, o.object_type, l.locked_mode 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;这个查询会列出所有被锁的对象、持锁会话的SID和SERIAL#、操作系统用户、客户端机器名和程序名。locked_mode的含义需要解释一下locked_mode值锁模式含义2RX行级共享锁最常见的行锁其他会话可以读但写同一行会被阻塞3S共享锁表级共享锁通常是DDL操作持有4SX共享排他锁少见部分DDL场景出现6X排他锁表级排他锁其他会话读写都被阻塞看到locked_mode2基本可以断定是行锁DML产生。看到6那就是表级排他锁多半是DDL或者LOCK TABLE语句干的。第二步查持锁会话到底在执行什么SQLSELECT s.sid, s.serial#, s.username, s.status, s.last_call_et, -- 会话最后一次活动到现在经过的秒数 s.event, -- 会话当前等待事件 s.wait_class, -- 等待类型 q.sql_text FROM v$session s LEFT JOIN v$sql q ON s.sql_id q.sql_id WHERE s.sid 输入上一步查到的SID;第三步如果是锁等待不是持锁者可以查谁在等谁SELECT blocking_session AS 阻塞者SID, sid AS 等待者SID, wait_class, seconds_in_wait AS 已等待秒数 FROM v$session WHERE blocking_session IS NOT NULL;这个查询直接列出阻塞者和被阻塞者的关系。如果一个会话的blocking_session不为空说明它正在等其他会话释放锁。顺着blocking_session往上找最终能找到根因会话。2.2 实操心得先看会话状态再动手拿到SID之后不要急着杀先看这个会话的状态SELECT status, -- ACTIVE还是INACTIVE last_call_et, -- 停滞时间 event, -- 在等待什么 sql_text -- 最近执行的SQL FROM v$session s, v$sql q WHERE s.sql_id q.sql_id AND s.sid SID;这里有一个非常关键的判断逻辑如果会话状态是INACTIVE且last_call_et很大说明这个会话已经很久没有执行操作了很可能是开发人员忘了提交事务这种会话可以放心处理。如果状态是ACTIVE且event是类似“enq: TX - row lock contention”的等待说明它正在执行SQL但由于锁被别的事务阻塞这种会话本身是受害者杀它没用要杀的是阻塞它的那个会话。我见过不少人把受害者和加害者搞混杀了一圈发现锁还在就是因为杀错了对象。定位锁的源头永远找v$session里blocking_session为空、但持有v$locked_object记录的那个会话。2.3 死锁的识别和处理死锁是另一种常见的锁问题Oracle检测到死锁后会自动回滚其中一个事务并给另一个会话报ORA-00060错误。DBA要做的主要是查死锁报告-- 查看死锁相关的跟踪文件的位置 SELECT value FROM v$diag_info WHERE name Diag Trace;然后在trace目录里找ora_xxxx_进程号.trc的跟踪文件搜索“DEADLOCK DETECTED”关键字能看到死锁的完整事务链和涉及的SQL。死锁的排查重点是找到两条互相等待的SQL然后从应用逻辑层面修复执行顺序。3. 解锁实操从温和到粗暴的完整方案找到持锁会话之后解锁的核心操作只有一句话把持锁会话干掉。但“干掉”也有不同的手法按推荐顺序排列如下。3.1 最推荐的解锁方式ALTER SYSTEM KILL SESSION这是Oracle官方的会话终止命令语法如下ALTER SYSTEM KILL SESSION SID,SERIAL# IMMEDIATE;注意两点SID和SERIAL#之间是英文逗号用单引号包起来。IMMEDIATE是强制立即终止不加它Oracle会等待事务自己结束效果不可控。执行成功后的表现会话状态变为KILLED持有的锁在PMON进程清理后会释放。清理速度通常很快但如果在某些特殊状态下会话正在等待I/O等可能需要几十秒甚至几分钟。有一个常见的坑有时候执行了KILL SESSION查v$session还会看到这个会话的状态是KILLED但锁还没释放。这种情况出现在会话所在的服务器和Oracle实例之间的连接还保持着PMON清理受阻。此时需要到操作系统层面确认这个会话对应的服务器进程是否还存在如果存在直接杀操作系统进程。3.2 更暴力的选择系统层面杀进程先通过会话信息找到操作系统进程PIDSELECT s.sid, s.serial#, p.spid AS OS进程号, s.username, s.program FROM v$session s JOIN v$process p ON s.paddr p.addr WHERE s.sid SID;拿到SPID后在数据库服务器上执行Linux环境kill -9 12345 # 12345是SPIDWindows环境orakill ORCL 12345 -- ORCL是实例名12345是SPID这也是我在生产环境最常用的一招。当ALTER SYSTEM KILL SESSION执行太慢、或者会话状态已经是KILLED但迟迟不释放锁时直接kill -9是最快的解锁手段。杀操作系统进程和杀数据库会话本质上是两回事操作系统把进程杀了数据库感知到异常断开后会自行清理事务和锁资源。3.3 最后的保底手段重启实例SHUTDOWN ABORT再STARTUP可以解决一切锁问题但代价太大不到万不得已不用。什么时候用如果锁的会话恰好是一些核心后台进程或者锁量巨大、涉及几十上百个会话一条条杀太慢可以考虑重启。需要注意SHUTDOWN ABORT属于异常关闭数据库需要做实例恢复启动时间可能比正常SHUTDOWN IMMEDIATE时间更长。对于OLTP核心系统这个操作是最后的方案实施前必须评估业务影响范围。3.4 一条SQL批量杀掉所有锁会话生产环境经常出现一种情况一张表被锁连锁导致十几个会话都在等待。这时候一个个杀太累可以按照下面这个逻辑一次处理BEGIN FOR c IN ( SELECT s.sid, s.serial# FROM v$locked_object l JOIN v$session s ON l.session_id s.sid WHERE s.username 具体的数据库账号 ) LOOP EXECUTE IMMEDIATE ALTER SYSTEM KILL SESSION || c.sid || , || c.serial# || IMMEDIATE; END LOOP; END; /这个脚本会把指定账号下所有持有锁的会话全部终止。使用时务必确认这个账号下没有正在跑的正常事务否则会把正常业务也杀掉。4. 解锁之后必须做的事防止再锁解锁只是治标如果根本问题不解决同样的锁表事故过几天还会再来。我在实际运维中总结了几条务实的防锁方案不同的来源对应不同的措施。4.1 从应用层控制锁最大的锁源是开发人员手动执行SQL不提交。大部分表锁事故都是开发者在PL/SQL Developer里执行了一条UPDATE忘了提交然后人走了。针对这个问题的措施开发规范里强制要求手动执行DML语句后立即COMMIT或者在工具里开启“连接自动提交”。PL/SQL Developer里可以在“工具-首选项-窗口类型-SQL窗口”里勾选“Automatic commit after executing SQL”Navicat也有类似设置。规范DBA权限和开发账号的使用最小化DML权限不要让所有开发都拥有生产库的表操作权限。4.2 从代码层控制锁如果是应用代码里的事务悬挂需要检查代码逻辑Spring框架里事务边界没有正确设置异常没有触发回滚就会导致连接池里的连接被占满并且事务悬挂。关键排查点Transactional有没有放在正确的类或者方法上是否会因为自调用而失效。异常是否被try-catch吞掉了导致事务管理器收不到异常信号。连接池最大连接数设置是否合理不够用时所有线程都在等连接连接池本身也会“锁死”。事务粒度过大比如在一个事务里做了大量查询和外部调用会成倍增加锁冲突概率能拆分就拆分事务。4.3 从运维监控层控制锁最好的防锁策略是提前发现而不是事后去杀。DBA可以做一个半小时级别的定时巡检脚本检测v$locked_object的记录。如果发现有锁记录自动发告警到钉钉或者邮件让人在业务受影响之前介入处理。最简单的一个监控SQLSELECT COUNT(*) AS 锁数量, s.username, s.machine FROM v$locked_object l JOIN v$session s ON l.session_id s.sid GROUP BY s.username, s.machine HAVING COUNT(*) 0;把这个脚本放到crontab里每30分钟跑一次输出到日志文件配合一个简单的文本判断就能实现告警。5. 实际案例复盘一次生产环境的表锁事故2023年的时候我接手过一个制造企业的Oracle EBS系统用户反馈WIP工单模块无法操作界面一直转圈。登录数据库执行查询发现WIP_OPERATIONS表上有大量的enq: TX - row lock contention等待事件。5.1 定位过程执行v$locked_object查询发现wip_operations表被两个会话锁住locked_mode2行锁。再查v$session发现这两个会话的username是某个应用账号machine是某台应用服务器program显示是Java程序statusINACTIVElast_call_et36000说明这两个事务已经悬挂了10个小时。通过blocking_session关系查出来真正阻塞源头是一个执行时间特别长的批处理会话。这个批处理会话对wip_operations表执行了一条UPDATE事务没有提交导致了之后所有对该表的操作全部被阻塞。5.2 解锁操作先和业务确认这个批处理任务是否还需要保留业务反馈这个任务已经在凌晨跑挂了后台进程都没了但数据库会话一直没释放。确认之后我直接用操作系统层面杀掉这个会话对应的SPID锁立刻释放整个WIP模块恢复正常前后不超过3分钟。5.3 根源修复解锁后复盘发现这个批处理是外包团队写的代码里没有正确的异常回滚逻辑更新失败后事务没有结束。我们做了三件事第一要求外包修复代码在存储过程里增加异常处理块BEGIN -- 业务逻辑 UPDATE wip_operations SET ... WHERE ...; COMMIT; EXCEPTION WHEN OTHERS THEN ROLLBACK; RAISE; END;第二在数据库层面增加了锁监控每小时巡检一次有锁自动发钉钉告警。第三联系开发规范手册明确要求所有DML操作必须在事务结束后主动提交或回滚。6. 常见问题与排查技巧速查表这节把平时在群里被问得最多的问题集中整理一下做个速查表方便定位。问题现象可能原因处理方式ORA-00054: resource busy有未提交事务持有DML锁DDL操作被阻塞查询并结束持锁会话或者等事务结束后再执行DDLORA-00060: deadlock detected两个会话互相持有对方需要的资源形成死循环等待Oracle已自动回滚其中一个事务查trace文件定位死锁SQL从应用层修复执行UPDATE卡住不动目标行被其他会话锁住本会话在等待查blocking_session找到源头会话并处理KILL SESSION后锁未释放会话处于KILLED状态PMON未清理完成找到对应操作系统SPIDkill -9强制结束v$locked_object查不到记录但表还是卡死锁可能发生在其他层库缓存锁、DDL锁查询v$lock里type为TM或TX的记录或查dba_blockers、dba_waiters视图重启后锁消失但业务很快又锁死应用代码有隐患每次跑都产生悬挂事务从代码层修复事务逻辑增加告警监控dba_blockers和dba_waiters两个视图比较冷门但很实用。dba_blockers直接列出阻塞其他会话的会话SIDdba_waiters列出正在等待的会话SID。如果不想写复杂的关联SQL可以直接查这两个视图。6.1 一个容易踩的坑SHUTDOWN IMMEDIATE也会卡住有锁的时候执行SHUTDOWN IMMEDIATE数据库会等待所有未提交事务回滚完成才关闭。如果持锁会话的SQL特别大、回滚需要很久SHUTDOWN IMMEDIATE可能卡几十分钟。这时候要么等要么改用SHUTDOWN ABORT。生产环境高可用架构下其实不用太担心因为RAC集群里可以先关闭有问题的实例另一个实例继续承担业务。真正的体会是Oracle表锁这个问题处理手段就那么几种但每种手段背后的判断逻辑才是关键。杀错会话、杀早了、杀晚了都会造成二次事故。把上面这些查询SQL存成一个脚本文件遇到锁表的时候顺序执行一遍定位到源头再动手这个流程稳稳当当。最后分享一个非常实用的小技巧把第一节里的定位SQL做成一个视图比如CREATE VIEW v_locked_info AS ...以后遇到锁表问题直接SELECT * FROM v_locked_info;一条命令就能看到所有关键信息不用每次敲长SQL。这个习惯帮我在多次故障处理中节省了大量时间。