ORACLE基础之oracle锁（oracle lock mode）详解
Posted by wukaiqiang; tagged with none
转自：https://blog.csdn.net/vertual/article/details/34540555
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.oracle提供的所类型可以根据v$lock_type 中的type来查询,我们平时接触的最多的是两种
select * from v$lock_type where type in ('TM','TX')
查看描述,可以大概的得知两种锁的信息.TM是同步访问对象用的,TX是和事务有关的.
2.要知道的有2个概念:
(1).锁定数据的锁,也就是行级锁,只有一种:排它锁 exclusive (ROW)
(2).锁定表上的锁,即锁定元数据的锁 metadata(table),一共有5种:
RS: row share
RX: row exclusive
S: share
SRX: share row exclusive
X: exclusive4.根据oracle联机文档的concepts的
我们可以从这个表找出至少2个东西,.第一是每种数据库操作都对应的是什么样的锁(参考中间那一列),第二是每种锁之间,如果遇到之后是否会产生冲突,所谓冲突就是是否会使当前的数据库操作夯住.其中Y*,表示如果两个操作锁定的是同一行,那么就会有冲突,后操作的会等待前一个操作完成之后再完成,否则会一直夯在那儿;如果不为同一行,那么则不会冲突,后操作的不会等待.举一个例子来说明:假设现在A操作为:对id=1的记录进行update,而B操作为:对id=2的记录进行删除,根据表格说明,在A上操作时在TM级别的锁会是RX,TX级别只有一个是X,在B上会有一个TM级别的锁会是RX,TX级别只有一个X,而根据表格说明,当RX遇到RX的时候,如果2个操作非同一条记录,那么则不会冲突,故AB两个操作均会按照各自的先加一个TM锁,再加一个TX锁,再顺利执行各自的操作,不会夯住。如果将B操作的记录id换位1,那么两个操作记录为同一条记录,在TM锁上会表现出冲突,所以B操作就会等待A操作完成提交后(也就是A的TX锁释放了后),B再相应的生成一个TX锁和一个TM锁再完成操作,否则的话会一直夯住,等待A释放TX锁.
3.常用的动态性能视图:
select * from v$lock_type where type in ('TM','TX');
select * from v$lock;
select * from v$transaction;
重点说明一下v$lock视图:
先设置一个场景:在session A中对一个表的记录进行更新,更新完后并不提交,在session B中对改表的同一条记录进行删除
很清晰的看到2个sid产生了锁.对于sid为140的session,产生了一个TM和一个TX锁,TM的锁模式(LMODE)=3,(3为RX: row exclusive和表格对照相符,当操作为update的时候,产生RX锁);对于sid为147的session,也产生了一个TM和一个TX锁,TM的锁模式(LMODE)=3 (3为RX: row exclusive和表格对照相符,当操作为delete的时候,产生RX锁),而TX的锁模式(LMODE)=0,代表正在等待一个锁.从v$lock_type的定义上面,我们也可以看出,type为TM的锁,ID1表示的是object_id,查询dba_objects可以很容易的得出锁定的对象是TTT这个obj. 从最后一列BLOCK(该block并不代表块,而是代表阻塞)=1也可以看出,sid=140的session在生成TX锁之后,发现之后一个的操作也是修改该条记录,所以BLOCK+1,表示阻塞其他的操作同时操作这条记录.
另外,此时,查询select * from v$transaction;视图,也可以得到相关联的信息
我们从v$lock_type对TX锁的描述,可以知道TX是和事务有关的.因此查看之前v$lock上TX锁的相关信息,可以看到ADDR的值与v$transaction的值是一样的.甚至可以根据ID1的值来计算,锁定的是哪个段:根据TX的ID1去除以和取余2的16次方,得到相关信息:
接着再在改表上创建一个索引 create index idx_ttt on ttt(object_id);
创建索引的同时,查询v$lock表
可以发现在创建索引的会生成2个TM锁,锁类别分别为4和3,我们查询这2个TM分别锁定的是什么对象:
根据查询结果发现lmode=4的object_id为55160的对象对应的是TTT这个表,LMODE=4对应的是TM的S锁
总结
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"
// *Cause: Resource interested is busy.
// *Action: Retry if necessary.
5级锁有：Lock Share Row Exclusive
具体来讲有主外键约束时update / delete ... ; 可能会产生4,5的锁。
6级锁有：Alter table, Drop table, Drop Index, Truncate table, Lock Exclusive
Oracle进程无法KILL处理方案：
出处：
1.oracle提供的所类型可以根据v$lock_type 中的type来查询,我们平时接触的最多的是两种
Oracle进程被KILL之后，状态被置为"KILLED"，但是锁定的资源长时间不释放，会出现类似下面这样的错误提示：
ORA-00030: User session ID does not exist
或
ORA-00031: session marked for kill
以往大多都是通过重启数据库的方式来强行释放锁资源。
现提供另一种方式解决该问题，在ORACLE中KILL不掉，在OS系统中再杀，操作方式如下：
1.下面的语句用来查询哪些对象被锁：
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;
2.下面的语句用来杀死一个进程：
alter system kill session '42,21993';
3在os一级再杀死相应的进程（线程），首先执行下面的语句获得进程（线程）号：
select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=24 （24是上面的sid）
4.在OS上杀死这个进程（线程）：
1)在unix上，用root身份执行命令:
#kill -9 12345（即第3步查询出的spid）
2)在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，语法为：
orakill sid thread
sid：表示要杀死的进程属于的实例名
thread：是要杀掉的线程号，即第3步查询出的spid。