dbms_lock控制串行详解

_Kafka_

于 2021-12-17 17:19:39 发布

阅读量1.2k

点赞数 1

分类专栏： Oracle 文章标签： sql p2p linq

原文链接：https://blog.csdn.net/wushuangyan26/article/details/69390336?locationNum=1&fps=1

版权

Oracle 专栏收录该内容

32 篇文章 4 订阅

订阅专栏

1 使用dbms_lock包控制串行

在pl/sql代码块中，有些操作代码块不能被多个会话同时进行执行，比如生成中间数据表（如先清除，后插入中间数据），

并且此表的数据在后续业务处理总需要使用，如果此部分代码块被另个会话调用，则会造成中间数据表的数据在同一个会话中不完整。

因此当有类似这样的需求时，就可能需要在pl/sql块中使用dbms_lock包控制来控制此部分代码块只能进行串行调用。

1.1 锁定原理

1.1.1 用户锁概念

1、通过dbms_lock获取的锁类型可以看成是oracle内部的一种队列锁，用户申请时通过指定的锁ID或通过指定需要锁定的名称

（此时会返回一个锁ID给用户）来获取锁定并独占此ID代表的信号量，从而达到控制并发的，因此称为用户锁(PL/SQL用户锁)。

2、用户锁有别于物理概念上的锁（如DML lock（data lock），DDL lock（dictionary lock)和internal lock/latch），可以看成是逻辑上的一种锁定，

且他们两者之间也是不相冲突的。比如如果会话1基于某个物理表的表明定义了一用户锁，此时会话2实际上是可以对此物理表做任何DDL,DML操作的。

1.1.2 锁定模式

用户申请锁时可以指定锁定模式（默认为x_mode），这里的锁定模式逻辑上可以对应到TM锁中的模式，有以下六种模式。

锁定模式	说明	TM锁中的模式
nl_mode	nl锁定模式	Null	并发度从上到下依次减小
ss_mode	subshared锁定模式	row share(RS)
sx_mode	subexclusive锁定模式	row exclusive(RX)
s_mode	shared锁定模式	share mode (S)
ssx_mode	subshared exclusive锁定模式	share row exclusive mode(SRX)
x_mode	exclusive锁定模式	exclusive mode (X)

1.1.2.1 锁定模式与并发度

需要注意的是，用户锁的锁定模式仅仅来用控制当其他会话试图获取自身会话所占有的锁ID（信号量）时，获取操作是成功、阻塞，

还是失败（如果没有指定阻塞时间或超时）。

关系	其他会话试图以某种锁定模式获取会话1所申请的用户锁时
会话1以某种锁定模锁定	NL	SS	SX	S	SSX	X
NL	SUCC	SUCC	SUCC	SUCC	SUCC	SUCC
SS	SUCC	SUCC	SUCC	SUCC	SUCC	fail
SX	SUCC	SUCC	SUCC	fail	fail	fail
S	SUCC	SUCC	fail	SUCC	fail	fail
SSX	SUCC	SUCC	fail	fail	fail	fail
X	SUCC	fail	fail	fail	fail	fail

1.1.3 查看

SELECT * FROM v$lock where type='UL';

1.2 申请用户锁

申请用户锁时有两种方式，分别通过指定的锁ID或通过指定锁名称（此时会返回一个锁ID给用户）来获取锁定并独占此ID，其返回值为integer, 含义如下，其中1和4代表申请成功：

0 申请锁定成功
　　1 申请锁定时超时
　　2 申请锁定时发生死锁
　　3 传入参数错误
　　4 已经获得了锁定，重复申请了锁
　　5 传入的锁定句柄错误

1.2.1 通过指定锁ID

此种方式不建议，因实际中如果指定的锁ID不一样，是无法达到对代码进行串行调用控制的，且指定的锁ID可能会与其他不相关业务冲突，从而造成没必要的并发控制。指定的锁ID需要位于0 到的1073741823区间。

1.2.1.1 api

dbms_lock中提供了函数request用来通过指定锁ID申请用户锁，

 function  request(id in integer,
                    lockmode in integer default x_mode,
                    timeout in integer default maxwait,
                    release_on_commit in boolean default FALSE)
    return integer;

1.2.1.2 实例

DECLARE
  v_def_lock_id    INTEGER;
  v_request_status INTEGER; --返回申请标识
  v_sid            NUMBER;
  v_relase_status  INTEGER;
BEGIN
  v_def_lock_id    := 100; --指定申请id为100的用户锁
  v_request_status := DBMS_LOCK.request(v_def_lock_id,
                                        DBMS_LOCK.ssx_mode, --锁定模式为5
                                        100, --最多等待时间
                                        release_on_commit => FALSE);
  --会话提交时也不释放release_on_commit=false,此时只有等待会话显示释放或会话结束后自动释放
  DBMS_OUTPUT.put_line('request_status=' || v_request_status);
  IF v_request_status IN (0, 4) THEN
    SELECT SID
      INTO v_sid
      FROM v$lock
     WHERE TYPE = 'UL'
       AND ID1 = v_def_lock_id;
    DBMS_OUTPUT.put_line('current session id=' || v_sid ||
                         ',request sucess');
  ELSE
    DBMS_OUTPUT.put_line('request fail');
  END IF;
  --这里暂且不释放，实际中一定要释放，且最好在异常代码中重复释放
 /* v_relase_status := DBMS_LOCK.release(v_def_lock_id);
EXCEPTION
  WHEN OTHERS THEN
    v_relase_status := DBMS_LOCK.release(v_def_lock_id);
    RAISE;*/
END;

1.2.2 通过指定需要锁定的名称

通过锁名称申请用户锁时相对更常用，常用的场景为指定一个表名称，名称区分大小写，不能以ORA$开头，最大支持128bytes

1.2.2.1 api

1、首先调用

procedure allocate_unique(lockname in varchar2,
               lockhandle out varchar2,
               expiration_secs in integer default 864000);

获取生成的lockid (相同的lockname,在expiration_secs时间范围内生成的lockid总是相同,大小为[1073741824,1999999999])。

2、然后调用另一个重载的函数request用来通过lockid申请用户锁。

function  request(lockhandle in varchar2,
                    lockmode in integer default x_mode,
                    timeout in integer default maxwait,
                    release_on_commit in boolean default FALSE)
    return integer;

1.2.2.2 实例

DECLARE
  v_def_lock_name      VARCHAR2(30);
  v_requset_lockhandle VARCHAR2(100);
  v_request_status     INTEGER; --返回申请标识
  v_sid                NUMBER;
  v_relase_status      INTEGER;
BEGIN
  v_def_lock_name := 'TEST_TABLE'; --指定申请名称为TEST_TABLE的用户锁
  DBMS_LOCK.ALLOCATE_UNIQUE(v_def_lock_name, v_requset_lockhandle, 30);
  DBMS_OUTPUT.put_line('lockhandle=' || v_requset_lockhandle);
  v_request_status := DBMS_LOCK.request(v_requset_lockhandle,
                                        DBMS_LOCK.ssx_mode, --锁定模式为5
                                        100, --最多等待时间
                                        release_on_commit => FALSE);
  --会话提交时也不释放release_on_commit=false,此时只有等待会话显示释放或会话结束后自动释放
  DBMS_OUTPUT.put_line('request_status=' || v_request_status);
  IF v_request_status IN (0, 4) THEN
    DBMS_OUTPUT.put_line('current session id' || ',request sucess');
  ELSE
    DBMS_OUTPUT.put_line('request fail');
  END IF;
  --这里暂且不释放，实际中一定要释放，且最好在异常代码中重复释放
  /* v_relase_status := DBMS_LOCK.release(v_requset_lockhandle);
  EXCEPTION
    WHEN OTHERS THEN
      v_relase_status := DBMS_LOCK.release(v_requset_lockhandle);
      RAISE;*/
END;

1.3 释放用户锁

如果在申请锁时为定义release_on_commit=true, 会话提交时也不释放此锁，此时只有会话必须显示释放，否则只能等待会话结束后由数据库自动清理自动清理，需要注意的时，最好在业务代码退出中捕获异常的代码中也显示调用释放用户锁代码。

相对于前面的两个申请方式，oracle中有两个对应的释放方法：

1.3.1 对应锁ID的释放方法

当以锁ID申请用户锁时，对应的方法为function release(id in integer) return integer; 入参为锁ID,返回值为0或4，说明释放成功。

返回值说明:

0 – 成功

3 – 参数错误
4 – 当前会话不再拥有指定的锁

1.3.2 对应名称的释放方法

当以名称指定申请用户锁时，对应的方法为function release(lockhandle in varchar2) return integer;，其中入参为allocate_unique过程产生的lockid。,返回值为0或4，说明释放成功。

返回值说明:

0 – 成功

3 – 参数错误
4 – 当前会话不再拥有指定的锁
5 – 不合法的lockhandle

1.4 转换用户锁模式

如果在会话中以某种锁定模式获得锁后，如果对锁定模式升级或降级时，以应对并发度的减少或增加。则需要用到所动模式转换

相对于前面的两个申请方式，oracle中有两个对应的释放方法：

1.4.1 对应锁ID的转换方法

当以锁ID申请用户锁时，对应的方法为function convert(id in integer,
lockmode in integer,
timeout in number default maxwait)
return integer; 入参为锁ID,新的锁定模式，最大等待时长（s）,返回值为0，说明释放成功。

返回值说明:

0 – 成功

2 –deadlock

3 – 参数错误
4 – 当前会话不再拥有指定的锁

1.4.2 对应名称的转换方法

当以名称指定申请用户锁时，对应的方法为function convert(lockhandle in varchar2, lockmode in integer, timeout in number default maxwait)
return integer;，其中入参为allocate_unique过程产生的lockid，新的锁定模式，最大等待时长（s）,返回值为0，说明转换成功。

返回值说明:

0 – 成功

2 –deadlock

3 – 参数错误
4 – 当前会话不再拥有指定的锁
5 – 不合法的lockhandle

1.5 当前会话睡眠

dbms_lock中提供了过程

procedure sleep(seconds in number);入参为睡眠时长(s)，用来显示指定当前会话阻塞时长。

1.6 全局测试实例

1.6.1 准备函数

这里暂且以名称申请用户锁来作为测试例子，（以锁ID进行申函数大体类似）

1.6.1.1 创建申请锁的函数

CREATE OR REPLACE FUNCTION f_request_lock(v_lock_name          VARCHAR2,
                                          v_requset_lockhandle OUT VARCHAR2, --generate lockid
                                          v_lock_mode          INTEGER DEFAULT dbms_lock.x_mode, --锁定模式                                          v_requst_time_out    INTEGER DEFAULT dbms_lock.maxwait,
                                          --请求超时时间（最多等待时间s）
                                          v_release_on_commit BOOLEAN DEFAULT FALSE)
  RETURN BOOLEAN IS
  v_request_status INTEGER DEFAULT - 1; 
  v_request_flag   BOOLEAN := FALSE;
  v_sid            NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE(v_lock_name, v_requset_lockhandle);
  DBMS_OUTPUT.put_line('lockhandle=' || v_requset_lockhandle);
  v_request_status := DBMS_LOCK.request(v_requset_lockhandle,
                                        v_lock_mode,
                                        v_requst_time_out,
                                        v_release_on_commit);
 
  DBMS_OUTPUT.put_line('request_status=' || v_request_status);
  IF v_request_status IN (0, 4) THEN
    DBMS_OUTPUT.put_line('request sucess');
    v_request_flag:=true;
  ELSE
    DBMS_OUTPUT.put_line('request fail');
  END IF;
  RETURN v_request_flag;
END;

1.6.1.2 创建转换锁的函数


CREATE OR REPLACE FUNCTION f_convert_lock(v_lock_handle VARCHAR2,
                                          ----generate lockid
                                          v_dest_lock_mode   INTEGER DEFAULT dbms_lock.x_mode, --目标转换模式
                                          v_convert_time_out INTEGER DEFAULT dbms_lock.maxwait
                                          --转换超时时间（最多等待时间s）
                                          ) RETURN BOOLEAN IS
  v_convert_status INTEGER;
  v_convert_flag   BOOLEAN := FALSE;
BEGIN
  v_convert_status := DBMS_LOCK.convert(v_lock_handle,
                                        v_dest_lock_mode,
                                        v_convert_time_out);
  DBMS_OUTPUT.put_line('convert_status=' || v_convert_status);
  IF v_convert_status = 0 THEN
    v_convert_flag := TRUE;
  END IF;
  RETURN v_convert_flag;
END;

1.6.1.3 创建释放锁的函数

CREATE OR REPLACE FUNCTION f_release_lock(v_lock_handle VARCHAR2
                                          ----generate lockid
                                          ) RETURN BOOLEAN IS
  v_release_status INTEGER;
  v_release_flag   BOOLEAN := FALSE;
BEGIN
  v_release_status := DBMS_LOCK.release(v_lock_handle);
  DBMS_OUTPUT.put_line('release_status=' || v_release_status);
  IF v_release_status IN (0, 4) THEN
    v_release_flag := TRUE;
  END IF;
  RETURN v_release_flag;
EXCEPTION
  WHEN OTHERS THEN
    v_release_status := DBMS_LOCK.release(v_lock_handle);
    RAISE;
END;

1.6.2 测试申请、转换、睡眠、释放

1.6.2.1 测试代码

DECLARE
  v_requset_lockhandle VARCHAR2(100); ----generate lockid
  v_lock_name          VARCHAR2(30);
  v_flag               BOOLEAN;
BEGIN
  v_lock_name := 'TEST_TABLE'; --测试基于表TEST_TABLE申请、转换、释放用户锁
  --1、以名称申请用户锁，锁定模式为dbms_lock.ssx_mode
  v_flag := f_request_lock(v_lock_name,
                           v_requset_lockhandle,
                           dbms_lock.ssx_mode);
  IF v_flag THEN
    DBMS_OUTPUT.put_line('--------request_flag=sucess------');
    --2、转换用户锁，目标锁定模式为dbms_lock.x_mode,即升级锁定类型
    v_flag := f_convert_lock(v_requset_lockhandle, dbms_lock.x_mode);
    IF v_flag THEN
      DBMS_OUTPUT.put_line('-----convert_flag=sucess---------');
    END IF;
  END IF;
  --3、测试，当前代码块睡眠5s
  dbms_output.put_line('time before sleep=' || to_char(SYSDATE, 'hh24:mi:ss'));
  DBMS_LOCK.sleep(5);
  dbms_output.put_line('time after sleep=' || to_char(SYSDATE, 'hh24:mi:ss'));
  --4、释放用户锁----------------
  v_flag := f_release_lock(v_requset_lockhandle);
   IF v_flag THEN
      DBMS_OUTPUT.put_line('-----relase_flag=sucess--------');
    END IF;
EXCEPTION
  WHEN OTHERS THEN
    v_flag := f_release_lock(v_requset_lockhandle);
    RAISE;
END;

1.6.2.2 测试输出

1.6.3 测试控制代码并发调用

测试指定业务操作的并发性控制：暂以两个会话调用同一代码为例，会话1称为A，会话2称为B；

1.6.3.1 测试原则

1、当A先进入调用指定模式锁定，并调用此过程业务操作，B以另外的模式试图锁定，并调用此过程业务操作能否成功

2、测试当B会话试图获取A会话所占有的锁ID（信号量）时，获取操作是成功、阻塞，还是失败（如果没有指定阻塞时间或超时）。注意：B间隔A的时间请不要超过20s(不超过业务操作的时间，否则A都调用结束了)

1.6.3.2 测试过程

CREATE OR REPLACE PROCEDURE p_business_lock_test(v_session_name VARCHAR2,
                                                 v_lock_mode    INTEGER DEFAULT dbms_lock.x_mode
                                                 --锁定模式的不同，意味着此过程能同时进行业务操作的数量
                                                 ) IS
  /*过程用来测试指定业务操作的并发性控制：
  暂以两个会话调用同一代码为例，会话1称为A，会话2称为B
  注意：B间隔A的时间请不要超过20s(不超过业务操作的时间，否则A都调用结束了)；
  测试原则为：
   当A先进入调用指定模式锁定，并调用此过程业务操作，
   B以另外的模式试图锁定，并调用此过程业务操作能否成功*/
  v_requset_lockhandle VARCHAR2(100); ----generate lockid
  v_lock_name          VARCHAR2(30);
  v_flag               BOOLEAN;
  v_start_time         DATE;
  v_wait_flag          BOOLEAN := FALSE;
BEGIN
  v_lock_name  := 'TEST_TABLE'; --测试基于表TEST_TABLE申请、转换、释放用户锁
  v_start_time := SYSDATE;
  DBMS_OUTPUT.put_line(v_session_name || ' start call,time=' ||
                       to_char(v_start_time, 'hh24:mi:ss'));
  --1、以名称申请用户锁，锁定模式为v_lock_mode
  FOR i IN 1 .. 1000 LOOP
    --为了更好的体现锁是否被占用，此处申请超时时间设置为5s,如果不成功则重复申请，直至最大次数1000
    v_flag := f_request_lock(v_lock_name,
                             v_requset_lockhandle,
                             v_lock_mode,
                             5);
    IF i = 1 THEN
      IF (SYSDATE - v_start_time) * 24 * 3600 > 2 THEN
        --正常不阻塞时，获取锁的时间很快，不会超过2s
        v_wait_flag := TRUE;
      END IF;
    END IF;
    IF v_flag THEN
      EXIT;
    END IF;
  END LOOP;
  IF v_flag THEN
    IF v_wait_flag THEN
      DBMS_OUTPUT.put_line(v_session_name ||
                           ' has been waited,waite times=' ||
                           round((SYSDATE - v_start_time) * 24 * 60 * 60,
                                 2) || ' s');
    END IF;
    DBMS_OUTPUT.put_line(v_session_name || ' request_flag=sucess,time=' ||
                         to_char(SYSDATE, 'hh24:mi:ss'));
    --执行原子业务操作开始
    DBMS_LOCK.sleep(20); --这里暂且以睡眠20s来代替实际中可能的业务操作
    --执行原子业务操作结束
    --2、释放用户锁----------------
    v_flag := f_release_lock(v_requset_lockhandle);
    IF v_flag THEN
      DBMS_OUTPUT.put_line(v_session_name || ' relase_flag=sucess,time=' ||
                           to_char(SYSDATE, 'hh24:mi:ss'));
    END IF;
  ELSE
    DBMS_OUTPUT.put_line(v_session_name || ' request_flag=fail,time=' ||
                         to_char(SYSDATE, 'hh24:mi:ss'));
  END IF;
EXCEPTION
  WHEN OTHERS THEN
    v_flag := f_release_lock(v_requset_lockhandle);
    RAISE;
END;

1.6.3.3 测试方式

关系	其他会话试图以某种锁定模式获取会话1所申请的用户锁时
会话1以某种锁定模锁定	NL	SS	SX	S	SSX	X
NL	SUCC	SUCC	SUCC	SUCC	SUCC	SUCC
SS	SUCC	SUCC	SUCC	SUCC	SUCC	fail
SX	SUCC	SUCC	SUCC	fail	fail	fail
S	SUCC	SUCC	fail	SUCC	fail	fail
SSX	SUCC	SUCC	fail	fail	fail	fail
X	SUCC	fail	fail	fail	fail	fail

1.6.3.3.1 测试1

实际中某些业务操作需要串行调用，因此用默认的最大锁定级别x_mode进行申请，是较常用的方式，此时并发度最小。

1、A会话以X模式先锁定，然后执行业务操作: exec p_business_lock_test('A',DBMS_LOCK.x_mode);

2、B会话以X模式试图再次锁定，执行业务操作，看是否被阻塞了: exec p_business_lock_test('B',DBMS_LOCK.x_mode);

A输出

A进入过程的时间大概19:45:01，释放锁的时间大概为19:45:21，中间的时间刚好为业务操作的时间（此处用sleep(20)来模拟实际业务操作）

B输出

B进入过程的时间大概19:45:06 B获得锁的时间大概为19:45:21（大致刚好）>=A释放锁的时间），因此B在A已经以X模式锁定占有业务操作时，再次试图以X模式锁定被阻塞了，即次粗的业务操作实际上达到串行了。

1.6.3.3.2 测试N

雷同于测试1，A、B可以组合多种锁定模式组合进行测试，验证阻塞与否的关系结构。暂不一一举例。

1、A会话以某种模式先锁定，然后执行业务操作: exec p_business_lock_test('A',DBMS_LOCK.x_mode);

2、B会话以另一模式试图再次锁定，执行业务操作，看是否被阻塞了: exec p_business_lock_test('B',DBMS_LOCK.x_mode);

1.6.4 测试用户锁与DDL,DML锁的无相关性

测试如果会话1基于某个物理表的表明定义了一用户锁，此时会话2实际上是可以对此物理表做任何DDL,DML操作的。

1.6.4.1 测试方式

1、会话A新建表TEST_TABLE

create table TEST_2 as select * from dba_tables;

2、会话B基于表 TEST_TABLE定义用户锁，且不释放


DECLARE
  v_requset_lockhandle VARCHAR2(100); ----generate lockid
  v_lock_name          VARCHAR2(30);
  v_flag               BOOLEAN;
BEGIN
  v_lock_name := 'TEST_TABLE'; --测试基于表TEST_TABLE申请、转换、释放用户锁
  --1、以名称申请用户锁，锁定模式为dbms_lock.ssx_mode
  v_flag := f_request_lock(v_lock_name,
                           v_requset_lockhandle,
                           dbms_lock.ssx_mode);
  IF v_flag THEN
    DBMS_OUTPUT.put_line('--------request_flag=sucess------');
  END IF;
END;

3、会话A中 drop table TEST_TABLE，看能否成功，能成功说明无相关性

1.6.4.2 测试输出

从以下图可以看出来，两者是无相关性的

dbms_lock控制串行详解.rar (148.9 KB)

_Kafka_

关注

1
点赞
踩
4

收藏

觉得还不错? 一键收藏
0
评论
dbms_lock控制串行详解

1使用dbms_lock包控制串行在pl/sql代码块中，有些操作代码块不能被多个会话同时进行执行，比如生成中间数据表（如先清除，后插入中间数据），并且此表的数据在后续业务处理总需要使用，如果此部分代码块被另个会话调用，则会造成中间数据表的数据在同一个会话中不完整。因此当有类似这样的需求时，就可能需要在pl/sql块中使用dbms_lock包控制来控制此部分代码块只能进行串行调用。1.1锁定原理1.1.1用户锁概念1、通过dbms_lock...
复制链接

扫一扫