dbms_lock控制串行详解

标签：dbms lock request DBMS 会话 SUCC 串行 flag

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