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 就可以同时开始执行测试了。

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

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