ORACLE锁机制深入理解

     若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性,加锁是实现数据库并发控制的一个非常重要的技术,需要的朋友可以了解下数据库是一个多用户使用的共享资源。当多个用户并发地存取数据时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。 

    加锁是实现数据库并发控制的一个非常重要的技术。当事务在对某个数据对象进行操作前，先向系统发出请求，对其加锁。加锁后事务就对该数据对象有了一定的控制，在该事务释放锁之前，其他的事务不能对此数据对象进行更新操作。 

 

    在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两种基本的锁类型来对数据库的事务进行并发控制。 

 

Oracle数据库的锁类型 
    根据保护的对象不同，Oracle数据库锁可以分为以下几大类：

    1、DML锁（data locks，数据锁），用于保护数据的完整性；

    2、DDL锁（dictionary locks，字典锁），用于保护数据库对象的结构，如表、索引等的结构定义；

    3、内部锁和闩（internal locks and latches），保护 数据库的内部结构。 
    DML锁的目的在于保证并发情况下的数据完整性。在Oracle数据库中，DML锁主要包括TM锁和TX锁，其中TM锁称为表级锁，TX锁称为事务锁或行级锁。

    当Oracle执行DML语句时，系统自动在所要操作的表上申请TM类型的锁。当TM锁获得后，系统再自动申请TX类型的锁，并将实际锁定的数据行的锁标志位进行置位。这样在事务加锁前检查TX锁相容性时就不用再逐行检查锁标志，而只需检查TM锁模式的相容性即可，大大提高了系统的效率。

 

oracle 锁机制锁问题的详细分析(处理锁定),以及死锁的解决方案 

    在任何多用户数据库应用中,最终必然会出现两个用户希望同时处理相同记录的情况.这种情况在逻辑上是不可能的,并且数据库必须确保其在物理上也是不可能的.事务隔离性原则要求数据库保证:在 ,这个会话无法影响另一个会话,并且后者也无法看到前者.为了实现这个要求,数据库创行话并发的数据访问,甚至在多个会话请求访问相同的记录时,数据库也必须确保这些会话排队依次进行.  借助于记录和表锁定机制,我们可以实现并发的串行化.oracle数据库中的锁定是完全自动的.一般而言,只有在试图结合软件与自动锁定机制是或者编程人员编写的代码太糟糕时才会引发某些问题

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

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

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

 

提示: 

    只有在特别请求并且编程人员具有充分理由的情况下,才可以要求在整个表上放置排他锁.   

   

    1、DML锁与DDL锁

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

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

  

    为了更好的说明锁机制,我们还是看一个实际的例子吧: 

    1.使用SQL*PLUS,作为用户SYSTEM连接数据库. 

    2.创建一个表,并且在这个表中插入一条记录.

    

create table t1(c1 number); 
insert into t1 values(1); 
commit;

 

    3.再次使用SQL*PLUS并作为用户SYSTEM进行连接,从而打开另一个会话. 

    4.在第一个会话中执行一个DML命令,这个命令会在插入的记录上放置一个排他锁,同时还会在创建的表上放置一个共享锁. 

   

update t1 set c1=2 where c1=1; 

 

    5.如下所示,在第二个会话中执行第一条针对新建表的DDL语句. 

alter table t1 add(c2 date);

出现以下错误
error at line 1: 
ora-00054:resource busy and acquire with nowait specified

 

    因为DDL语句需要表上的排他锁,而这与DML语句已在表上放置了共享锁相冲突,所以试图在表中插入一个列的这条DDL语句会失败.需要注意的是:在类似情况下,DML语句会等待并不断进行尝试,直至获得其所需的锁(换句话说就是挂起);而DDL语句则会由于错误立即终止. 

    6.在第一个会话中,提交当前事务  

commit; 

    7.在第二个会话中,重新执行步骤5.此时,因为不纯在与DDL排他锁相冲突的DML共享锁,因此DDL语句将成功的执行. 

    8.在第一个会话中 ,锁定整个表.

 

lock table t1 in exclusive mode;  

 

    9.在第二个会话中,插入一条记录.此时,这个会话将被挂起.      

 

insert into t1 values (1,sysdate); 

 

    10.在第一个会话中,通过执行COMMIT命令解除整个表上的锁定.需要注意的是,ROLLBACK命令也可以实现相同的目的.         

 

commit; 

    11.第二个会话会释放并且现在会完成插入操作.随后,执行COMMIT命令,终止当前事务斌且解除该记录上的排他锁.  

 

    2、关于如何解决死锁的问题.

    最近遇到这样一个问题，操作一个表，发现被人锁定了，于是查询谁锁定的表，发现多人锁定，安全起见，不能全部kill，于是用一下语句判定，谁锁定，谁等待。 

     

SELECT /*+ rule */
 lpad(' ', decode(l.xidusn, 0, 3, 0)) || l.oracle_username User_name,
 o.owner,
 o.object_name,
 o.object_type,
 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
 ORDER BY o.object_id, xidusn DESC;

 如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待 

以上的语句可以查询到谁锁了表，而谁在等待。 
以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。 
如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN 

找出谁锁定的记录，kill掉就行了。 

--ALTER SYSTEM KILL SESSION '查出的SID,查出的SERIAL#'; 
alter system kill session '11,973' ;

--以下几个为相关表 
SELECT * FROM v$lock; 
SELECT * FROM v$sqlarea; 
SELECT * FROM v$session; 
SELECT * FROM v$process ;       
SELECT * FROM v$locked_object;   
SELECT * FROM all_objects;   
SELECT * FROM v$session_wait; 

--1.查出锁定object的session的信息以及被锁定的object名 
SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username, 
       l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time 
    FROM v$locked_object l, all_objects o, v$session s 
   WHERE l.object_id = o.object_id 
     AND l.session_id = s.sid 
ORDER BY sid, s.serial# ; 

 

--2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句 
--比上面那段多出sql_text和action 
SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#, 
       l.os_user_name,s.machine, s.terminal,a.sql_text, a.action 
     FROM v$sqlarea a,v$session s, v$locked_object l 
    WHERE l.session_id = s.sid 
      AND s.prev_sql_addr = a.address 
ORDER BY sid, s.serial#; 

 
      

--3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode 
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine, 
       s.terminal, s.logon_time, l.type 
    FROM v$session s, v$lock l 
   WHERE s.sid = l.sid 
     AND s.username IS NOT NULL 
ORDER BY sid; 

 

 

行级锁 

       行级锁只对用户正在访问的行进行锁定。如果该用户正在修改某行，那么其他用户就可以更新同一表中该行之外的数据。例如：如果用户1正在更新Jobs 表中的第一行，则用户2可以同时修改该表中的第二行。也就是说除了该表中的第一行，其他用户可以修改任意行，但是第一行的数据其他用户只能select。 

      行级锁是一种排他锁，防止其他事务修改此行，但是不会阻止读取此行的操作。在使用INSERT、UPDATE、DELETE 和SELECT„FOR UPDATE 等 语句时，Oracle 会自动应用行级锁锁定。SELECT...FOR UPDATE 语句允许用户每次选择多行记录进行更新，这些记录会被锁定，且只能由发起查询的用户进行编辑。只有在回滚或提交事务之后，锁定才会释放，其他用户才可以编辑这些记录。      SELECT...FOR UPDATE 语句的语法如下：   

SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT]; 

其中： 

OF 子句用于指定即将更新的列，即锁定行上的特定列。 

WAIT 子句指定等待其他用户释放锁的秒数，防止无限期的等待。 “使用FOR UPDATE WAIT”子句的优点如下：

⒈防止无限期地等待被锁定的行； 

⒉允许应用程序中对锁的等待时间进行更多的控制。 

⒊对于交互式应用程序非常有用，因为这些用户不能等待不确定 举例：比如一个用户在SQL PLUS下输入这条语句：SQL>SELECT * FROM order_master WHERE vencode='V002' FOR UPDATE; 

此时再开启一个SQL PLUS，以相同的用户登陆，执行下面的命令。 

SQL<> SELECT * FROM order_master WHERE vencode='V02' FOR UPDATE WAIT 5; 由于要更新的行已经被锁定，上述命令在等待5秒钟之后返回，并给出如下的错误信息： 

ERROR 位于第1行； 

ORA-30006；资源已被占用；执行操作时出现WAIT超时。 

PS：再开启的SQL PLUS 是以相同用户登陆的，用其他用户登陆更是不可能。 

表级锁 

     表级锁被锁定的表，暂时放在内存中，不提交不进去数据库，也就是说，多个用户在同一时间，同时修改同一个表的同一行时，同时点提交，但是还是按随机的先后被提交进数据库，而不是同时被提交，而是先随机存储后，被再次提交的覆盖。 

     表级锁将保护表数据，在事务处理过程中，表级锁会限制对整个表的访问。可以使用LOCK TABLE 语句显示地锁定表。表级锁用来限制对表执行添加、更新和删除等修改操作。锁定表的语法如下： 

   LOCK TABLE <table_name> IN <lock_mode> MODE [NOWAIT]; 其中：

lock_mode 是锁定的模式。 

NOWAIT 关键字用于防止无限期的等待其他用户释放锁。 表级锁的模式包括以下内容： 

行共享 (ROW SHARE, RS): 允许其他用户访问和锁定该表，但是禁止排他锁定整个表。行共享锁锁定后，在同一时刻，不同用户可以对同一个表中的被行共享锁锁定后的该行，具备增、删、改、查的功能。 

行排他(ROW EXCLUSIVE, RX):与行共享模式相同，同时禁止其他用户在此表上使用共享锁。使用SELECT...FOR UPDATE 语句会在表上自动应用排他锁。被行排他后，其他用户不能同时修改该行，但是可以插入行，可以查询该行，其他用户也不能再在该表上对此行进行排他。 

共 享（SHARE, S）:共享锁将锁定表，仅允许其他用户查询表中的行，但不允许插入、更新或删除行。多个用户可以同时在同一个表中放置共享锁，即允许资源共享，因此得名 “共享锁”。例如，如果用户每天都需要在结帐时更新日销售表，则可以在更改该表时使用共享锁以确保数据的一致性。也就是说该表只能查，其他用户想修改表中行的数据，只需要对该表进行共享锁。 

共享行排他（SHARE ROW EXCLUSIVE , SPX）:执行比共享表更多的限制。防止其他事务在表上应用共享锁、共享行排他锁以及排他锁。共享行排他是除了该行以外的其他行也不能增、删、改。只能在此表中加低级表。要是想在该表中更改其他行的数据，就只有其他用户对该行进行共享行排他锁，也仅仅只能修改被这个用户锁定的行，而其他的行也修改不了。 

排他（EXCLUSIVE，E）:对表执行最大限制。除了允许其他用户查询该表的记录，排他锁防止其他事务对表做任何更改或在表上应用任何类型的锁。这个锁应该叫锁中之王，他锁住了的话，其他用户就只有查询的功能了，就别想在该表中干别的事了。 

BTW：在能加很多锁的表中，如果第一个用户对该表锁定时，没有使用“NOWAIT”语句，是需要第一个用户对该表COMMIT或ROLLBACK 命令释放锁定后，其他用户才能对该表进行锁定。如果其他用户违反了该条，就会无期限的等待。。。。 SQL> LOCK TABLE order_mater IN SHARE MODE; 而使用下面的语句就可以很少的预防这种情况的存在： SQL> LOCK TBALE order_mater IN SHARE MODE NOWAIT; 

      下面对行级锁和表级锁来个总结，行级锁锁定的只是行，而表级锁是对表的锁定，但是在表级锁锁定表后，还可以在该表中使用行级锁。

v$session 

查询会话的信息和锁的信息。 sid,serial#：表示会话信息。 

program：表示会话的应用程序信息。 row_wait_obj#：表示等待的对象。 和dba_objects中的object_id相对应。 v$session_wait 

查询等待的会话信息。 

sid：表示持有锁的会话信息。 

seconds_in_wait：表示等待持续的时间信息 event：表示会话等待的事件。 v$lock 

列出系统中的所有的锁。 

sid：表示持有锁的会话信息。 

type：表示锁的类型。值包括tm和tx等。 id1：表示锁的对象标识。 

lmode,request：表示会话等待的锁模式的信 息。用数字0－6表示，和表1相对应。 dba_locks 

对v$lock的格式化视图。 

session_id：和v$lock中的sid对应。 lock_type：和v$lock中的type对应。 lock_id1： 和v$lock中的id1对应。 mode_held,mode_requested：和v$lock中 的lmode,request相对应。 

v$locked_object 

只包含dml的锁信息，包括回滚段和会话信息。 xidusn,xidslot,xidsqn：表示回滚段信息。和 v$transaction相关联。 

object_id：表示被锁对象标识。 

session_id：表示持有锁的会话信息。 

locked_mode：表示会话等待的锁模式的信 息，和v$lock中的lmode一致。    

   select * from v$db_object_cache where  owner='OCP' and locks<>0     

   select * from v$access a where a.object=upper('对象')     

   select * from v$session where sid=3279     

   alter system kill session '3279,48861'     

   select * from v$process where addr='9CE3E048'