ORACLE基础之oracle锁（oracle lock mode）详解

迷南。 2021-06-26 16:06 483阅读 0赞

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进程并不能彻底清除锁的问题。