dbms_lock案例一则

今天来深入学习下Oracle的锁：

About User Locks

You can use Oracle Lock Management services  for your applications by making calls

to the  DBMS_LOCK package. It is possible to request a lock of a specific mode, give it a

unique name recognizable in another procedure in the same or another instance,

change the lock mode, and release it. Because a reserved user lock is the same as an

Oracle Database lock, it has all the features of a database lock, such as deadlock

detection. Be certain that any user locks us ed in distributed transactions are released

upon COMMIT , or an undetected deadlock can occur.

在使用锁的过程中不免会涉及到 dbms_lock 这个包，我们可以通过 dbms_lock .request来获得某种类型的锁（NL,SS,SX,S,SSX,X），用(commit,release)等方式来释放该锁，可以通过dbms_lock.allocate_unique来获得唯一的lockid

在oracle的官方文档中对dbms_lock中的几个常数有如下说明，我们可以根据需要来获取不同的锁类型：

例如：

l_lock_flag   := dbms_lock.request(lockhandle => l_lock_handle,                                                  lockmode => dbms_lock.x_mode,                                                  timeout => 6000,                                                  release_on_commit => TRUE);

在这里获取一个eXclusive(排他dbms_lock.x_mode)锁，6000超时，提交时释放，并且lockhandle为l_lock_handle

包中各类型锁的常数值如下：

 nl_mode  constant integer := 1;

 ss_mode  constant integer := 2;-- Also called 'Intended Share'

 sx_mode  constant integer := 3;-- Also called 'Intended Exclusive'

 s_mode   constant integer := 4;

 ssx_mode constant integer := 5;

 x_mode   constant integer := 6;

当一个进程持有某个类型的锁，而另一个类型企图获得相同对象的某种锁的时候，他们之间的兼容关系如下：

不明白各个类型表示的意思可以看看下面这段话：

 --  A sub-share lock can be used on an aggregate object to indicate that

 --  share locks are being aquired on sub-parts of the object.  Similarly, a

 --  sub-exclusive lock can be used on an aggregate object to indicate

 --  that exclusive locks are being aquired on sub-parts of the object.  A

 --  share-sub-exclusive lock indicates that the entire aggregate object

 --  has a share lock, but some of the sub-parts may additionally have

 --  exclusive locks.

下面来做个例子，某个表中有如下15条数据：

很多时候我们可能会重复处理procedure_result表中行，导致出现如下情况：

这时候就需要对表中的行进行单独锁定：

下面是使用dbms_lock的一个例子：

CREATE OR REPLACE PACKAGE BODY pkg_lock_util IS  --  Given a name, generate a unique lockid for this lock.  This procedure  --    always performs a 'commit'.  --  Input parameters:  --    lockname  --      name of lock to generate unique lockid for.  If this name already  --      has been assigned a lockid, then return a handle to that lockid.  --      Otherwise generate a new lockid and return a handle to it.  --      WARNING: Do not use locknames beginning with 'ORA$'; these names  --      are reserved for products supplied by Oracle Corporation.  The  --      name can be up to 128 bytes, and is case-sensitive.  --    expiration_secs  --      number of seconds after an 'allocate_unique' is last performed on  --      this lock name that this lock is subject to cleanup (i.e.,  --      deleting from the dbms_lock_allocated table).  Defaults to 10  --      days.  --  Output parameters:  --    lockhandle  --      The actual lockid is not returned, rather a handle to it is  --      returned.  Use this handle in subsequent calls to request,  --      convert and release. Up to 128 bytes are returned.  A handle  --      is used to reduce the chance that a programming error can  --      accidentally create an incorrect but valid lockid.  This will  --      provide better isolation between different applications that are  --      using this package.  --  --      All sessions using a lockhandle returned by a call to  --      allocate_unique using the same name will be referring to the same  --      lock.  Different sessions may have different lockhandles for the  --      same lock, so lockhandles should not be passed from one session  --      to another.  --  --      The lockid's generated by allocate_unique are between 1073741824  --      and 1999999999, inclusive.  --  --      This routine will always do a commit.  --  --  Errors raised:  --    -20000, ORU-10003: Unable to find or insert lock <lockname>  --        into catalog dbms_lock_allocated.  FUNCTION allocate_handle(in_lock_type IN NUMBER,                           in_lock_name IN VARCHAR2) RETURN VARCHAR2 IS    l_lock_handle VARCHAR2(32767);  BEGIN    dbms_lock.allocate_unique(in_lock_type || ':' || in_lock_name,                              l_lock_handle);    RETURN(l_lock_handle);  END allocate_handle;  /*    nl_mode  constant integer := 1;    ss_mode  constant integer := 2; -- Also called 'Intended Share'    sx_mode  constant integer := 3; -- Also called 'Intended Exclusive'    s_mode   constant integer := 4;    ssx_mode constant integer := 5;    x_mode   constant integer := 6;        dbms_lock.request    --  Return value:    --    0 - success    --    1 - timeout    --    2 - deadlock    --    3 - parameter error    --    4 - already own lock specified by 'id' or 'lockhandle'    --    5 - illegal lockhandle  */  FUNCTION get_lock(in_lock_type IN NUMBER,                    in_lock_name IN VARCHAR2) RETURN INTEGER IS    l_lock_handle VARCHAR2(32767);    l_lock_flag   INTEGER;  BEGIN    l_lock_handle := allocate_handle(in_lock_type, in_lock_name);    l_lock_flag   := dbms_lock.request(lockhandle => l_lock_handle,                                       lockmode => dbms_lock.x_mode,                                       timeout => 6000,                                       release_on_commit => TRUE);    RETURN(l_lock_flag);  END get_lock;  PROCEDURE p_lock_util_test IS    v_lock_flag INTEGER;    v_result     VARCHAR2(10);  BEGIN    v_lock_flag := dbms_lock.request(1, dbms_lock.s_mode,                                     release_on_commit => TRUE);    COMMIT;    FOR c1 IN (SELECT * FROM procedure_result a WHERE a.result = 1) LOOP      v_lock_flag := get_lock(in_lock_type => c1.procedure_id,                              in_lock_name => c1.procedure_name);      IF v_lock_flag = 0 THEN        SELECT a.result        INTO   v_result        FROM   procedure_result a        WHERE  a.procedure_id = c1.procedure_id;        IF v_result = 1 THEN          UPDATE procedure_result a SET a.comments = a.comments || 'start...', a.result = 2 WHERE a.procedure_id = c1.procedure_id;          COMMIT;          dbms_output.put_line(c1.procedure_id);        END IF;      END IF;    END LOOP;  END p_lock_util_test;END pkg_lock_util;

先请求个排他锁，不要提交：

DECLARE

 v_lock_flag INTEGER;

BEGIN

 v_lock_flag := dbms_lock.request(1, dbms_lock.x_mode,

                                    release_on_commit => TRUE);

END;

/

然后开两个会话执行上面的过程p_lock_util_test ，因为过程一开始需要申请共享锁，但是对应的这个id已经被请求排他锁锁阻塞。

这时候提交最早之前的请求，这样两个会话执行的过程p_lock_util_test 就可以同时开始执行测试了。

再看看执行之后的结果如下：

这样就能够保证每一行都只被更新一次了。