dbms_lock控制串行详解
1????????? 使用dbms_lock包控制串行
??? 在pl/sql代码块中，有些操作代码块不能被多个会话同时进行执行，比如生成中间数据表（如先清除，后插入中间数据），
并且此表的数据在后续业务处理总需要使用，如果此部分代码块被另个会话调用，则会造成中间数据表的数据在同一个会话中不完整。
因此当有类似这样的需求时，就可能需要在pl/sql块中使用dbms_lock包控制来控制此部分代码块只能进行串行调用。
??? 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操作的。
??? 用户申请锁时可以指定锁定模式（默认为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)
??? 需要注意的是，用户锁的锁定模式仅仅来用控制当其他会话试图获取自身会话所占有的锁ID（信号量）时，获取操作是成功、阻塞，
还是失败（如果没有指定阻塞时间或超时）。
关系
其他会话试图以某种锁定模式获取会话1所申请的用户锁时
NL
SS
SX
S
SSX
X
会话1以某种锁
定模
锁定
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
???? SELECT * FROM v$lock where type='UL';
??? 申请用户锁时有两种方式，分别通过指定的锁ID或通过指定锁名称（此时会返回一个锁ID给用户）来获取锁定并独占此ID，其返 回 值为integer, 含 义如下，其中1和4代表申请成功：
0 申请锁定成功
1 申请锁定时超时
2 申请锁定时发生死锁
3 传入参数错误
4 已经获得了锁定，重复申请了锁
5 传入的锁定句柄错误
?
??? 此种方式不建议，因实际中如果指定的锁ID不一样，是无法达到对代码进行串行调用控制的，且指定的锁ID可能会与其他不相关业务冲突，从而造成没必要的并发控制。指定的锁ID需要位于0 到的1073741823区间。
??? 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;
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;??
??? 通过锁名称申请用户锁时相对更常用，常用的场景为指定一个表名称，名称区分大小写，不能以ORA$开头，最大支持128bytes
??? 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;
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;??
?
?
?
如果在申请锁时为定义release_on_commit=true, 会话提交时也不释放此锁，此时只有会话必须显示释放，否则只能等待会话结束后由数据库自动清理自动清理，需要注意的时，最好在业务代码退出中捕获异常的代码中也显示调用释放用户锁代码。
相对于前面的两个申请方式，oracle中有两个对应的释放方法：
??? 当以锁ID申请用户锁时，对应的方法为function release(id in integer) return integer; 入参为锁ID,返回值为0或4，说明释放成功。
返回值说明:
0 – 成功
?3 – 参数错误
??????? ?4 – 当前会话不再拥有指定的锁
???当以名称指定申请用户锁时，对应的方法为function release(lockhandle in varchar2) return integer;，其中入参为allocate_unique过程产生的lockid。,返回值为0或4，说明释放成功。
返回值说明:
???0 – 成功
???3 – 参数错误
? ??4 – 当前会话不再拥有指定的锁
? ??5 – 不合法的lockhandle
如果在会话中以某种锁定模式获得锁后，如果对锁定模式升级或降级时，以应对并发度的减少或增加。则需要用到所动模式转换
相对于前面的两个申请方式，oracle中有两个对应的释放方法：
当以锁ID申请用户锁时，对应的方法为function convert(id in integer,
?????????????????? lockmode in integer,
???? ??????????????timeout in number default maxwait)
??? return integer; 入参为锁ID,新的锁定模式，最大等待时长（s）,返回值为0，说明释放成功。
???返回值说明:
0 – 成功
2 –deadlock
?3 – 参数错误
??????? 4 – 当前会话不再拥有指定的锁
?当以名称指定申请用户锁时，对应的方法为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
???dbms_lock中提供了过程
procedure sleep(seconds in number);入参为睡眠时长(s)，用来显示指定当前会话阻塞时长。
?
这里暂且以名称申请用户锁来作为测试例子，（以锁ID进行申函数大体类似）
?
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;??
?
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;?
?
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;??
?
?
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称为A，会话2称为B；
1、当A先进入调用指定模式锁定，并调用此过程业务操作，B以另外的模式试图锁定，并调用此过程业务操作能否成功
2、测试当B会话试图获取A会话所占有的锁ID（信号量）时，获取操作是成功、阻塞，还是失败（如果没有指定阻塞时间或超时）。注意：B间隔A的时间请不要超过20s(不超过业务操作的时间，否则A都调用结束了)
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所申请的用户锁时
NL
SS
SX
S
SSX
X
会话1以某种锁
定模
锁定
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
???? 实际中某些业务操作需要串行调用，因此用默认的最大锁定级别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进入过程的时间大概19:45:01，释放锁的时间大概为19:45:21，中间的时间刚好为业务操作的时间（此处用sleep(20)来模拟实际业务操作）
B输出?
?
?
?????? B进入过程的时间大概19:45:06 B获得锁的时间大概为19:45:21（大致刚好）>=A释放锁的时间），因此B在A已经以X模式锁定占有业务操作时，再次试图以X模式锁定被阻塞了，即次粗的业务操作实际上达到串行了。
??? 雷同于测试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基于某个物理表的表明定义了一用户锁，此时会话2实际上是可以对此物理表做任何DDL,DML操作的。
?????? 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，看能否成功，能成功说明无相关性
从以下图可以看出来，两者是无相关性的
?
?
?
?
?
?
?
?