2011年12月1日 oracle_lock

Oracle锁总结(这里只关心DML锁)

首先要明白两个概念，

共享锁与排他锁

在多用户的数据库系统中，Oracle 使用两种模式的锁：
   
   
排他锁（exclusive lock）模式：能够阻止共享被加锁的资源。对数据进行修改时必须获得此种模式的锁。第一个排他地对资源加锁的事物是唯一可以对此资源进行修改的事物，直至排他锁被释放。
   
   
共享锁（share lock）模式：依据操作类型有条件地允许共享被加锁的资源。对数据进行读取的多个用户可共享此数据，这些用户可以对资源加以共享锁，防止其他用户 并发地修改此资源（对数据进行修改的用户需要排他锁）。多个事务可以对相同的资源加共享锁。

上面这段文字来源于oracle concept。解释了共享锁与排他锁。但感觉不太清晰，我自己理解的是：

共享锁，transaction 对某个对象加了共享锁后，其它transaction还可以对其加锁。  加共享锁的目的是阻止别人修改。

排他锁, transaction 对某个对象加了排他锁后，其它transaction就不能对其加锁了。加排他锁的目的是修改对象。

比如，我想读一个表，我加上一个共享锁，告诉别人不要在我读的时候写这个表。这时候又有一个人也要读这个表，那么他也要在表上加个共享锁，告诉别人他要读这个表，请不要在他读的时候修改这个表。也许你有疑问，既然我已经申请了共享锁，他再申请，是不是就冗余了呢？其实不是，因为如果我读完表走了，把我申请的共享锁解除了，那么这时候他还在读，如果他没有申请自己的共享锁而完全依赖我申请的共享锁，这时我把我的共享锁取消了，他读的过程就危险了，容易被人的写操作打断。

表级锁与行级锁

表级锁为TM,行级锁为TX。这是他们在Oracle系统视图里的标记，当你查看v$lock这个视图的时候，可以通过type列的值是TM还是TX来判断一个锁是表级锁还是行级锁。当然这个列还有很多其它的值，代表一些系统级别的锁。不过目前可以不关心这些。

表级锁有很多种模式：行共享（row share，RS），行排他（row exclusive，RX），共享（share，S），共享行排他（share row exclusive，SRX），及排他（exclusive，X）。各种模式的表级锁具有的限制级别决定了其是否能与其他表级锁共处于同一数据表上。

行级锁只有一种模式，X 排他模式（这一点没有从任何文档上看到明确的说明，但我想是这样的，因为对于一行数据来说只有两种情况，可写，不可写。所以一个排他锁就足够了）。

下面来仔细看一下表级锁。

行共享表级锁（row share，RS，也叫 sub share table lock, ss）， （对于理解行共享表级锁，最好的办法是参照后面的行共享排他锁RX）表示已经锁定了表内的某一些数据行，并有意进行修改操作。可以通过下面的语句获得行共享表级锁 RS：

SELECT ... FROM table ... FOR UPDATE OF ... ; 
LOCK TABLE table IN ROW SHARE MODE;

获得行共享表级锁RS后，

允许的操作：允许任何不会企图在被锁定行上加锁的操作，也就是说其他事务依然可以并发地对相同数据表执行查询，插入，更新，删除操作，或对表内数据行加锁的操作。也就是说，其他事务同时也能获得相同表上的行共享，行排他，共享， 及共享行排他模式的表级锁。只要这些操作不会企图去锁定原来RS对应的行级锁锁定的行。

禁止的操作：LOCK TABLE table IN EXCLUSIVE MODE;其实应该说只要是企图锁定原来RS对应的行级锁锁定的行的操作，都要被禁止。

下表列出了RS模式下被允许和禁止的操作

SQL语句	表级锁模式	RS	RX	S	SRX	X
SELECT ... FROM table FOR UPDATE OF ...	RS	Y*	Y*	Y*	Y*	X
LOCK TABLE table IN ROW SHARE MODE	RS	Y	Y	Y	Y	N

要注意，上面的Y,N自然是代表允许和禁止，而Y*则是代表某些情况下允许，某些情况下发生等待。可以看到，出现Y*的都是for update of 语句产生的RS锁。因为这个语句只是锁定了表中的某些行，所以其它的锁只要不会影响到那些被锁定的行，就可以被允许添加。而第二个LOCK语句很明显没有指定去锁定哪些行，所以除了一个X锁外其它的锁都可以添加。

行排他表级锁，通常表明拥有此锁的事务已经对表内的某些数据行进行了更新操作。当事务使用以下语句修改数据表时将自动地获得行排他表级锁，

INSERT INTO table ... ; 
UPDATE table ... ; 
DELETE FROM table ... ; 
LOCK TABLE table IN ROW EXCLUSIVE MODE;

获取RX锁禁止与允许的操作如下

 

SQL语句	表级锁模式	RS	RX	S	SRX	X
INSERT INTO table ...	RX	Y	Y	N	N	N
UPDATE table ...	RX	Y*	Y*	N	N	N
DELETE FROM table ...	RX	Y*	Y*	N	N	N
LOCK TABLE table IN ROW EXCLUSIVE MODE	RX	Y	Y	N	N	N

一般来讲，RS与RX都是锁定表内的一些数据行，所以他们的共同点是如果一个操作试图去锁定现在已经被他们对应的行级锁锁定的行，那么这个操作就是不允许的。而又有些不同点，比如S和SRX还有X锁，并没有刻意的去锁定某个数据行，按理说与RS和RX是不冲突的，但实际上S和SRX的主要目的在与不让这个表被写（X的目的是不让这个表被别人写），而这是RX容忍不了的。RX获取的目的就是去改写表。但对于RS来说则无所谓，因为RS不一定要写。比如说一个表获得了RS锁，这时这个表又被加了个S锁，这是可以的。这个时候，如果最初获得RS锁的transaction不去写表，那么自然没有一丝影响。如果最初获取RS锁的transaction要去写表，那么由于S的存在，只能陷入等待，一直到S被解除。但这并不像RX那样，根本就不允许S锁或者SRX锁被添加。

共享表级锁S，  这个锁阻止其它transaction对表进行更新。可以通过如下语句获得

LOCK TABLE table IN SHARE MODE;

多个transaction可以同时获得同一个表上的S锁。这时候如果想对该表更新，只有当所有的transaction都把他们的 S 锁释放掉后才可以。

SQL语句	表级锁	RS	RX	S	SRX	X
LOCK TABLE table IN SHARE MODE	S	Y	N	Y	N	N

共享排他表级锁SRX，简单的说只是把S锁改进了一下，同一时间只允许有一个transaction获取SRX。这个锁可以通过如下语句获得，LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE;

SQL语句	表级锁	RS	RX	S	SRX	X
LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE	SRX	Y	N	N	N	N

排他表级锁X,这个锁最容易理解，施加这个锁后禁止其它transaction对表施加任何锁 。

LOCK TABLE table IN EXCLUSIVE MODE;

SQL语句	表级锁	RS	RX	S	SRX	X
LOCK TABLE table IN EXCLUSIVE MODE	X	N	N	N	N	N

-------------------------------------------

上面通篇讲的都是锁的概念，下面是实际工作中会遇到的一些问题及解决办法。

比如有时候客户会跟你说，他的某个DML操作被卡住了，让你看看是不是有锁。这时候你可以通过一下的一些步骤来查找出详细信息。

1. dba_blockers 

这个视图只有一列，内容是SID，也就是session的ID。但是即使系统中存在了锁，这个视图也不一定会有内容，因为这里的SID是blocker的ID，也就是说只有那个加锁的session block了某些操作的时候才会成为一个blocker，才会在这里体现，如果没有操作被它block，那么就不会体现在这里。

非常要注意的是，在RAC环境里，这个视图不太适用，因为它只能查到同一个实例上的信息。

2.v$lock

这个视图可以帮助我们得到很多信息，需要关注的列大概有这么几列。在RAC环境中要查的是gv$lock

block 这一列有0，1，2这几个值，0 代表没有阻塞其它操作，1代表阻塞了，2 是RAC环境下的，表示阻塞。

经过实验，我是这样理解的，如果在rac环境中，block=1则说明阻塞发生在同一个实例上，block=2说明阻塞发生在不同实例上。（但好像不是这么回事）

type  这列的值有很多，我们只需要关心是TM还是TX。前者代表表级锁，后者代表行级锁。

lmode 这列的值是0-6的数字，代表不同模式。none，NULL,rs , rx , s , srx , x.

ID1,ID2 这两列的值随着type的不同而不同。

ID1 NUMBER 锁的第1标识号。
如果锁的类型是TM，该值表示将要被锁定的对象的标识号；
如果锁的类型是TX，该值表示撤销段号码的十进制值
ID2 NUMBER 锁的第2标识号。
如果锁的类型是TM，该值为0；
如果锁的类型是TX，该值表示交换次数
LMODE NUMBER 会话保持的锁的模式。
0＝None；1＝Null；2＝Row-S (SS)；3＝Row-X (SX)；
4＝Share；5＝S/Row-X (SSX)；6＝Exclusive
REQUEST NUMBER 会话申请的锁的模式。与LMODE中的模式相同
CTIME NUMBER 以秒为单位的，获得当前锁（或转换成当前锁的模式）以来的时间
BLOCK NUMBER 当前锁是否阻塞另一个锁。0＝不阻塞；1＝阻塞

3.  v$locked_object

这个视图可以显示目前系统中那些对象被锁住，要注意在rac环境中要查gv$locked_object

需要了解的列如下

OBJECT_ID NUMBER 被锁定的对象的标识号
SESSION_ID NUMBER 会话的标识号
ORACLE_USERNAME VARCHAR2(30) Oracle用户名
OS_USER_NAME VARCHAR2(30) 操作系统用户名
PROCESS VARCHAR2(12) 操作系统进程标识号
LOCKED_MODE NUMBER 对象被锁定的模式

下面用这些视图来做一个测试。

1. 创建用户，并创建测试表

CREATE USER u01 IDENTIFIED BY u01;

GRANT CONNECT , RESOURCE TO u01;

CREATE TABLE tstlock(id NUMBER, val VARCHAR2(18));

INSERT INTO tstlock VALUES(1,'a')；

INSERT INTO tstlock VALUES(2,'b')；

COMMIT;

2. 用u01登录RAC节点中的一个节点，并在表上施加锁，要注意自己的SID

SELECT SYS_CONTEXT(‘USERENV’,'SID') FROM DUAL;

SELECT * FROM tstlock WHERE id=1 FOR UPDATE;

3. 查看gv$lock视图

SQL>  SELECT SID , TYPE , ID1 , ID2 , LMODE , REQUEST , BLOCK FROM GV$LOCK WHERE SID=211;


       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       211 TX     262183      71402          6          0          2
       211 TM     228597          0          3          0          2

这里解释一下这个输出，SID代表持有锁的session 的ID，也就是第二步中session的ID. 在type=TM这一行里，ID1表示被锁的对象ID.LMODE表示当前的锁模式，REQUEST表示一个被阻塞的锁想要获得的锁的模式。BLOCK这里不知道怎么解释。在非rac环境中block不为0表示该操作阻塞了其它操作，可是在这里并没有阻塞其它的操作，但不知道为什么还是非0值。(BLOCK应该是这样的，如果在非RAC环境中，0表示没有阻塞其它，1表示阻塞其它，这很明确。     如果是RAC环境中，那么0当前想要申请锁的动作被阻塞了。1表示了你正在阻塞别人，但这个别人或者说别人们，一定是和你在同一个instance上的。而2则表现不出太多的信息，它只能告诉你这一行没有被阻塞，因为如果被阻塞了应该是0.除此之外从2这个值上判断不出更多，因为它可能是什么都没发生，也可能是阻塞了其它instance上的session )

4. 在另外一个session里执行某项会被锁挡住的操作来实现

DELETE FROM U01.TSTLOCK WHERE ID=2;

5. 检查锁

SQL> SELECT SID , TYPE , ID1 , ID2 , LMODE , REQUEST , BLOCK FROM GV$LOCK WHERE CTIME<200000;


       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
       147 PS          2       3599          4          0          2
       197 PS          2       3599          4          0          2
       197 PS          1       3599          4          0          2
       308 XR          4          0          1          0          2
       137 PS          1       3599          4          0          2
       174 TM     228597          0          3          0          2
       174 TX     262183      71402          0          6          0
       194 TO  -40016333          1          3          0          2
       211 TX     262183      71402          6          0          1
       211 TM     228597          0          3          0          2

可以看到174这一session被锁住了。而且阻塞它的正是211。因为看 174  的TX锁在REQUEST模式是6，说明正在申请一个X锁。而对应的TM锁与211的TM锁锁住了同一个表，所以可以判断这两个session之间发生了锁竞争。