【原创】oracle 锁表问题

2013-12-4
一、概述
1.常规操作什么时候会锁表：
UPDATE   会为WHERE条件部分的行加锁
DELETE   会为WHERE条件部分加锁
INSERT   会INSERT部分加锁。
SELECT FOR UPDATE  会对WHERE条件的行加锁。
有时候ORACLE数据的某些表由于频繁操作，而且比较大，也会导致锁表。

2.锁表的表现

（1）、对表进行操作时报如下错误：

      ORA-00054: resource busy and acquire with NOWAIT specified

（2）、对标进行操作长时间未响应。
二、查询锁表语句
SELECT object_name, machine, s.sid, s.serial#
      FROM v$locked_object l, dba_objects o, v$session s
           WHERE l.object_id　= o.object_id
                 AND l.session_id = s.sid;
--若要查询某张表，需在where条件里加o.object_name='tablename'.

三、如何解锁
--通过kill掉进程中的会话来进行解锁
alter system kill session 'sid, serial#';

--kill掉锁表会话后，对方就断开了登录，无法继续进行之前的操作，也无法进行提交和回滚等操作。
四、锁表相关知识
1.查询锁表会用到的视图和表：
  V$LOCK, V$LOCKED_OBJECT, V$SESSION, V$SQLAREA,V$PROCESS, dba_objects.
2.各个表的重要字段
 （1）V$LOCK
    sid：  会话SID。
  type：  锁类型，TM表示表锁或DML锁，TX表示行锁或事务锁，UL表示用户锁。我们主要关注TX和TM两种型的锁，其它均为系统锁，会很快自动释放，不用关注。
  lmode： 会话保持的锁的模式。
          0＝None；1＝Null；2＝Row-S (SS,行级共享锁，其他对象只能查询这些数据行)；3＝Row-X(SX,行级排它锁,在提交前不允许做DML操作)；4＝Share(共享锁)；5＝S/Row-X(SSX,共享行级排它锁)；6＝Exclusive(排它锁)

ID1,ID2:    ID1,ID2的取值含义根据type的取值而有所不同
            对于TM 锁ID1表示被锁定表的object_id 可以和dba_objects视图关联取得具体表信息，ID2 值为0
            对于TX 锁ID1以十进制数值表示该事务所占用的回滚段号和事务槽slot number号,其组形式:
            0xRRRRSSSS,RRRR=RBS/UNDO NUMBER,SSSS=SLOT NUMBER
            ID2 以十进制数值表示环绕wrap的次数，即事务槽被重用的次数
 （2）V$SESSION
     sid:      会话SID.通常与v$locked_object关联。
  serial#:    会话序列号，sid可能会重复，所以通过sid和serial#共同确定一个会话。
  username:   登录oracle用户名。
   logon_time: 登录oracle时间。 
  osuser:     电脑用户名如：Administrator
  machine:    电脑机器名如：WORKGROUP\PC-201009141057
 （3）V$LOCKED_OBJECT
  session_id：        会话id。通常与v$session关联。
  object_id：         被锁对象标识。通常与dba_objects关联。
  oracle_username：   登录oracle用户名。
  os_user_name：      电脑用户名如：Administrator
  locked_mode：       会话保持的锁的模式。
 （4）dba_objects
   object_id：         被锁对象标识。通常与v$locked_object关联。
  object_name：       被锁对象名。
  owner：             被锁对象拥有者。
 （5）V$SQLAREA
     sql_text：        执行的SQL语句。
 （6）V$PROCESS 
    serial#：            会话序列号。
  username：           进程拥有者。
3.常用联接方式
--查询被锁对象拥有者，对象名，会话id和锁的模式。
select b.owner,b.object_name,a.session_id,a.locked_mode
      from v$locked_object a,dba_objects b
           where b.object_id = a.object_id;
--查询登录用户，会话id，序列号以及登录时间，并以登录时间排序。
selectb.username,b.sid,b.serial#,logon_time
      from v$locked_object a,v$session b
           where a.session_id = b.sid order by b.logon_time;
--查出锁定表的会话的sid, serial#,os_user_name,machine_name和执行的语句
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#;

附：ORACLE里锁的几种模式：
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，subexclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X共享行独占(SRX)：阻止其他事务操作，share/sub exclusive
6：exclusive 独占(X)：独立访问使用，exclusive数字越大锁级别越高, 影响的操作越多。
1级锁有： select， 有时会在v$locked_object出现。
2级锁有：Select for update，Lock For Update，Lock Row Share select forupdate当对话使用forupdate子串打开一个游标时，所有返回集中的数据行都将处于行级(Row-X)独占式锁定，其他对象只能查询这些数据行，不能进行update、delete或selectfor update操作。
3级锁有：Insert，Update，Delete，Lock Row Exclusive没有commit之前插入同样的一条记录会没有反应，因为后一个3级的锁会一直等待上一个3级的锁，我们必须释放掉上一个才能继续工作。
4级锁有：Create Index， Lock Sharelocked_mode为2、3、4级锁，不影响DML(insert,delete,update,select)操作,但DDL(alter,drop等)操作会提示ora-00054错误。00054, 00000, "resource busy andacquire with NOWAIT specified"
5级锁有：Lock Share Row Exclusive 具体来讲有主外键约束时update / delete ...；可能会产生4，5的锁。
6级锁有：Alter table, Drop table, Drop Index,Truncate table, LockExclusive