V$LOCK中的连接列

Column View Joined Column(s)
SID V$SESSION SID
ID1, ID2, TYPE V$LOCK ID1, ID2, TYPE
ID1 DBA_OBJECTS OBJECT_ID
TRUNCID1/65536) V$ROLLNAME USN

如果session在等待锁,这可被用于找出session持有的锁,。
可被用于找出DML锁类型的被锁对象(type='TM')
可被用于找出行级事务锁(TYPE='TX')使用中的回滚段,不过,需要通过V$TRANSACTION连接查询得到。

表1 Oracle的TM锁类型
锁模式 锁描述 解释 SQL操作
0 none
1 NULL 空 Select
2 SS(Row-S) 行级共享锁,其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share
3 SX(Row-X) 行级排它锁,在提交前不允许做DML操作 Insert、Update、Delete、Lock row share
4 S(Share) 共享锁 Create index、Lock share
5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive
6 X(Exclusive) 排它锁 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

  数字越大锁级别越高, 影响的操作越多。一般的查询语句如select ... from ... ;是小于2的锁, 有时会在v$locked_object出现。select ... from ... for update; 是2的锁。

  当对话使用for update子串打开一个游标时,所有返回集中的数据行都将处于行级(Row-X)独占式锁定,其他对象只能查询这些数据行,不能进行update、delete或select...for update操作。insert / update / delete ... ; 是3的锁。

  没有commit之前插入同样的一条记录会没有反应, 因为后一个3的锁会一直等待上一个3的锁, 我们必须释放掉上一个才能继续工作。

  创建索引的时候也会产生3,4级别的锁。locked_mode为2,3,4不影响DML(insert,delete,update,select)操作, 但DDL(alter,drop等)操作会提示ora-00054错误。有主外键约束时 update / delete ... ; 可能会产生4,5的锁。DDL语句时是6的锁。

  如果出现了锁的问题, 某个DML操作可能等待很久没有反应。当你采用的是直接连接数据库的方式,也不要用OS系统命令 $kill process_num 或者 $kill -9 process_num来终止用户连接,因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。记得在数据库级别用alter system kill session 'sid,serial#';杀掉不正常的锁。

示例:
我按照自己的理解演示的TX,TM锁如下:
1.create table TMP1(col1 VARCHAR2(50));--创建临时表
2.select * from v$lock;--关掉当前锁信息
3.select * from tmp1 for update; --加锁
4.select * from v$lock; ---看看现在的锁列表,是不是多了两条记录。Type分别为tx,tm,对照表1。
5.新开一个连接,然后
select * from tmp1 for update; --呵呵,等待状态了吧
6.select * from v$lock; --又新增了两条记录,其它一条type=tx,lmode=0
7.查看当前被锁的session正在执行的sql语句
select /*+ NO_MERGE(a) NO_MERGE(b) NO_MERGE(c) */ a.username, a.machine, a.sid, a.serial#, a.last_call_et "Seconds", b.id1, c.sql_text "SQL"
from v$session a, v$lock b, v$sqltext c
where a.username is not null and a.lockwait = b.kaddr and c.hash_value =a.sql_hash_value
8.将之前的for update语句commit或者rollback,然后新开连接的session拥有锁。有兴趣的朋友还可以试试两条for update的时候,关闭先执行的那个窗口,看看oracle会给出什么样的响应。

  这一节是我在自整理v$系列视图以来花费时间和精力最多的一个,我反复看了document,又从网上搜索了各种资料实际使用案例等,就是不开窍。这一节至今我也仍未有把握说尽在掌握,所以在上述文字中除了例子,我如实贴出了收集来的内容,未加任何自我理解,就是担心万一我的理解有误,会对其它浏览本文的人造成困扰。同时我把在收集过程中自我感觉对理解v$lock可能有帮助的资料地址列出,供有心人参考:

Oracle数据库中的锁机制研究
http://soft.zdnet.com.cn/software_zone/2007/0208/377403.shtml

DB2和 Oracle的并发控制(锁)比较
http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0512niuxzh/

Itpub论坛的oracle专题深入讨论区也有一篇非常精彩的讨论,地址如下:
我对ORACLE数据锁的一点体会
http://www.itpub.net/270059.html