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
是和事务有关的.
3.要知道的有2个概念:
(1).锁定数据的锁,也就是行级锁,只有一种:排它锁 exclusive
(ROW)
(2).锁定表上的锁,即锁定元数据的锁 metadata
(table),一共有5种:
2 RS: row share
3 RX: row exclusive
4 S: share
5 SRX: share row exclusive
6 X: exclusive
4.根据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锁.
5.常用的动态性能视图:
select * from v$lock_type where type in ('TM','TX');
代码如下
select * from v$lock;
select * from v$transaction;
重点说明一下v$lock
视图:
先设置一个场景:在session A
中对一个表的记录进行更新,更新完后并不提交,在session B
中对改表的同一条记录进行删除
代码如下
Session A:
SQL> create table ttt as select * from dba_objects where rownum<=10;
表已创建。
代码如下
SQL> update ttt set object_name='TEST' where object_id=20;
已更新 1 行。
代码如下
SQL>
Session B:
SQL> delete from ttt where object_id=20;
此时因为A并没有提交,所以B会一直夯住再打开一个session C
,查询相关的v$lock
视图
代码如下
SQL> select * from v$lock where type in ('TM','TX');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
CA4244F4 CA424508 147 TX 393242 563 0 6 270 0
C8E7F704 C8E7F71C 140 TM 55156 0 3 0 301 0
C8E7F7C8 C8E7F7E0 147 TM 55156 0 3 0 270 0
C8ED3C38 C8ED3D54 140 TX 393242 563 6 0 301 1
可以很清晰的看到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次方,得到相关信息:
代码如下
SQL> select 393242 / 65536, mod(393242, 65536) from dual;
393242/65536 MOD(393242,65536)
------------ -----------------
6.00039673 26
可以神奇的发现和v$transaction
中的XIDUSN
和XIDSLOT
对应上了!
6.最后补充一个操作,创建索引的时候会生成的锁操作:
首先将ttt表插入很多数据
代码如下
SQL> insert into ttt select * from dba_objects;
SQL> commit;
提交完成。
代码如下
SQL> select count(*) from ttt;
COUNT(*)
----------
1739045
接着再在改表上创建一个索引
代码如下
SQL> 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
常用查询DB中锁的SQL语句:
select b.owner,b.object_name,l.session_id,l.locked_mode
from v$locked_object l, dba_objects b
where b.object_id=l.object_id
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
如果有长期出现的一列,可能是没有释放的锁。我们可以用下面SQL语句杀掉长期没有释放非正常的锁:
alter system kill session 'sid,serial#';
如果出现了锁的问题, 某个DML
操作可能等待很久没有反应。
当你采用的是直接连接数据库的方式,也不要用OS系统命令 $kill process_num
或者 $kill -9 process_num
来终止用户连接,因为一个用户进程可能产生一个以上的锁, 杀OS进程并不能彻底清除锁的问题。