共享锁与排他锁–锁表查询与解锁

Posted by Harid七月 - 2 - 2014 Leave comments

Oracle数据库中锁定的标准级别保证了最大可能的并发级别。也就是说,如果某个会话正在更新一行,那么只有这行会被锁定。此外,锁定这一行只是为了防止其它会话对其进行更新,其他会话可以随时执行读取操作。只有在使用COMMIT或ROLLBACK命令结束事务之后,锁定才会被解除。这种锁定是一个“排他(exclusive)”锁:在指定记录上请求排他锁的第一个会话得到这个锁定,其他请求对该记录进行写访问的会话则必须等待。虽然这一行已通过锁定会话进行了更新,但是对其进行读访问是被允许的,并且这些读操作会涉及撤销数据的使用,从而确保读会话不会看到任何未提交的变更。

对于一行或一个完整表上的排他锁来说,每次只能有一个会话可以获得这个排他锁,不过许多会话可以同时获得同一对象上的“共享(shared)”锁。在一行上设置共享锁毫无意义,其原因在于锁定一行的唯一目的就是不允许其他会话更改它。共享锁被置于整个表上,同时许多会话可以获得同一个表上的共享锁。在一个表上放置共享锁的目的是为了防止另一个会话获得这个表上的排他锁(在已存在共享锁的情况下无法再获得排他锁)。在表上放置排他锁时需要执行DDL语句。如果其他会话已经在一个表上放置了共享锁,那么我们就无法执行修改某个对象的语句(例如删除这个表的一列)。

为了在行上执行DML语句,当前会话必须获取更改行上的排他锁以及包含这些行的表上的共享锁。如果另一个会话已经获取了待更改行上的排他锁,那么当前会话将被挂起,直至使用COMMIT或ROLLBACK命令解除这些锁定。如果另一个会话已经获取了表上的共享锁以及其它行上的排他锁,那么就不存在任何问题。一个表上的排他锁也是允许的,但是,除非DDL语句要求这么做,默认锁定机制是不锁定整个表。

所有DML语句至少都需要两种锁:受影响记录上的排他锁,以及包含受影响记录的表上的共享锁。排他锁能够防止其他会话干预指定的行,而共享锁则能够阻止其他传话使用DDL语句修改表的定义。这两种锁定会被自动请求。如果某条DML语句在指定记录上无法获取所需的排他锁,那么会挂起这条语句直至获得所需的排他锁。

执行DDL命令需要使用所涉及对象上的揍他锁。只有在针对指定表的所有DML事务结束,且行上的排他锁以及表上的共享锁都被解除之后,我们才可以获得执行DDL命令所需的排他锁。任何DDL语句所需的排他锁都是被自动请求的。但是,如果无法获取所需的排他锁(通常是因为其它会话已经获得用于DML语句的共享锁),那么DDL语句会由于错误立即终止。

查看被锁定的对象:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SELECT t2.username,
       t2.sid,
       t2.serial#,
       t3.object_name,
       t2.OSUSER,
       t2.MACHINE,
       t2.PROGRAM,
       t2.LOGON_TIME,
       t2.COMMAND,
       t2.LOCKWAIT,
       t2.SADDR,
       t2.PADDR,
       t2.TADDR,
       t2.SQL_ADDRESS,
       t1.LOCKED_MODE
  FROM v$locked_object t1, v$session t2, dba_objects t3
 WHERE t1.session_id = t2.sid
   AND t1.object_id = t3.object_id
 ORDER BY t2.logon_time;

上面这条SQL语句用到了Oracle的两个视图和一个表,分别是v$locked_object、v$session、dba_objects:

v$locked_object 视图中记录了所有session中的所有被锁定的对象信息。

v$session 视图记录了所有session的相关信息。

dba_objects 为oracle用户对象及系统对象的集合,通过关联这张表能够获取被锁定对象的详细信息。

解除数据库中被锁住的表(SID,SERIAL):

1
ALTER system KILL SESSION '39,1390';

v$locked_object中的LOCKED_MODE字段表示锁的模式,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级锁有: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。

   声明:本文采用 BY-NC-SA 协议进行授权 | 星期九
   原创文章转载请注明:转自《共享锁与排他锁–锁表查询与解锁


分享按钮