【Oracle】“锁”一般解决步骤(故障排查操作说明)

文中使用的Oracle版本为10g。

本文将介绍在Oracle中当出现“锁”现象时的排查步骤，经验证此方法能够解决80%以上的“锁”问题。

基本操作步骤先查出锁定object的session信息以及被锁定的object名，执行如下语句：

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

一般来说通过查询出来的object_name和logon_time基本能够定位到要解除锁定的对象对应的记录。

这段语句是查出锁定脚本的session_id, sid, serial#,os_user_name, machine name, terminal相关信息的，执行如下语句：

SELECT l.session_id sid, 
      s.serial#, 
      l.locked_mode, 
      l.oracle_username, 
      s.user#,
      l.os_user_name,
      s.machine, 
      s.terminal,
      a.sql_text, 
      a.action
FROM v$sqlarea a,v$session s, v$locked_object l
WHERE l.session_id = s.sid
AND s.prev_sql_addr = a.address
ORDER BY sid, s.serial#;

第一条语句是通过session（会话）关联all_objects（所有对象）和locked_object（锁定对象），关注点在于对象。而第二条语句则通过sqlarea（脚本位置）关联session（会话）和locked_object（锁定对象），这说明第二条语句查询的是通过SQL脚本造成的锁定信息。两者有本质上的区别。

这段语句是查出锁定进程的sid, serial#,os_user_name, machine_name, terminal, type相关信息的，执行如下语句：

SELECT s.sid, 
      s.serial#, 
      s.username, 
      s.schemaname, 
      s.osuser, 
      s.process, 
      s.machine,
      s.terminal, 
      s.logon_time, 
      l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

这个通过语句中关联信息就能够猜出，这里通过session（会话）找到锁定的进程。

使用命令直接kill掉，执行如下语句：

alter system kill session 'sid,serial#';

通过上面三种锁定的查询可以拿到sid和serial#信息，之后直接kill掉就能够解锁，但是需要注意的是Oracle的锁不是kill就立刻释放，它有自己的释放机制，这个有机会再细讲。

锁表问题排查检查Oracle的job（定时器）是否存在资源没有释放的情况，执行如下语句：

Select * from dba_job;
Select * from dba_job_running;

确定没有job执行之后，将job中对应的定时计划先删除（这个用PL/SQL非常容易就不再解释）。

检查所有远程计算机，看看有没有客户端存在session占用的情况（这具体的做法就是访问各个远程计算机，看看有没有正在对表的数据进行操作，就算没有都要将客户端关闭）。

通过语句先看看有没有对象被锁的情况，执行如下语句：

select t2.username,
      t2.sid,
      t2.serial#,
      t2.logon_time 
from v$locked_object t1,v$session t2 
where t1.session_id=t2.sid 
order by t2.logon_time;

结果看到没有显示锁表的情况。

尝试对某一字段进行修改（这个比较简单直接在PL/SQL操作即可）

结果显示修改失败表被占用。

然后进行数据的插入，若可以对表中行数据进行增删查改操作的，初步断定是修改表结构时忘记提交了因此一直被锁定了（但这个只是猜测，不能真正确定问题所在）。

查询表的操作记录，执行如下语句：

select * from v$sqlarea a where a.sql_text like ‘%<table_name>%’;

看看这个表的历史操作，发现了lock table 表名 in exclusive mode。

这时可以确定表是因为系统DML的隐式table lock锁定了表。所以在操作记录上没有处理session。

由于第一次的exclusive mode锁定是系统隐式做的，所以是找不到sid是什么了（由于第一次锁的时候没有及时发现，所以没有找到sid，而在做完了锁定一段时间之后sid就会取消了）。

最后处理办法有两个

第一、重启当前数据库实例；

第二、另开一张新表（与当前被锁的表同构的）进行操作，数据操作都重新指向那个表。

后记一般情况下Oracle的“锁”操作都可以通过手动解锁，就算不手动解锁过一段时间也会自动解锁的。Oracle“锁表”无论是什么table-lock模式，都存在时间限制。当session结束或者操作结束后（commit 或 rollback）都会重新释放。若表长期被锁住就要检查一下是不是进行了多重锁定或者session等待的情况。

附录# table-lock模式

Oracle的锁定可以分为以下3类：

1. DML lock（data lock）

2. DDL lock（dictionary lock)

3. Internal lock/latch。

关于DDL lock和Internal lock/latch这两个如果后期还有时间的话会划分出另一篇文章来详细描述，今天只针对DML lock进行说明。

DML lock（data lock）可分为row lock和table lock两种

√row lock

select.. for update/insert/update/delete时隐式自动产生

√table lock

table lock除了隐式产生，也可以调用lock table <table_name> in </table_name> name来显示锁定。

如果不希望别的session lock/insert/update/delete表中任意一行只允许查询，可以用lock table table_name in exclusive mode（X）。这种锁定模式级别最高，并发度最小。

如果允许别的session查询或用select for update锁定记录，不允许insert/update/delete，可以用lock table table_name in share row exclusive mode。(SRX)

如果允许别的session查询或select for update以及lock table table_name in share mode，只是不允许insert/update/delete，可以用lock table table_name in share mode。(share mode和share row exclusive mode的区别在于一个是非抢占式的而另一个是抢占式的。进入share row exclusive mode后其他session不能阻止你insert/update/delete，而进入share mode后其他session也同样可以进入share mode，进而阻止你对表的修改。(S)

还有两种锁定模式row share(RS)和row exclusive(RX)。他们允许的并发操作更多，一般直接用DML语句自动获得不用lock语句。

# unlock tablekill session

解除锁定最容易想到的就是关闭会话，除了直接关闭窗口外还可以通过命令解除，执行如下语句：

SQL> select object_id,session_id from v$locked_object;              -- 注意session_id 就是上锁的 session标志
SQL> select username,sid,SERIAL# from v$session where sid='<sid>';  -- 这里的SID = session_id 
SQL> alter system kill session 'id,serial#';                        -- 杀死该session                

rollback/commit 终止事务处理（PL/SQL处理）