ITPub博客

首页 > 数据库 > Oracle > 【原创】oracle 锁表问题 rainbow

【原创】oracle 锁表问题 rainbow

Oracle 作者:www111999 时间:2013-12-05 14:55:43 0 删除 编辑

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:     电脑机器名如:WORKGROUPPC-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,序列号以及登录时间,并以登录时间排序。
select b.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):用于行的修改,sub exclusive
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 for update当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级(Row-X)独占式锁定,其他对象只能查询这些数据行,不能进行update、delete或select for update操作。
3级锁有:Insert,Update,Delete,Lock Row Exclusive 没有commit之前插入同样的一条记录会没有反应,因为后一个3级的锁会一直等待上一个3级的锁,我们必须释放掉上一个才能继续工作。
4级锁有:Create Index, Lock Share locked_mode为2、3、4级锁,不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。00054, 00000, "resource busy and acquire with NOWAIT specified"
5级锁有:Lock Share Row Exclusive 具体来讲有主外键约束时update / delete ... ;可能会产生4,5的锁。
6级锁有:Alter table, Drop table, Drop Index,Truncate table, Lock Exclusive

<!-- 正文结束 -->

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23868844/viewspace-1115547/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: 没有了~
请登录后发表评论 登录
全部评论

注册时间:2010-05-07