- 浏览: 25679 次
- 性别:
- 来自: 长沙
最新评论
-
去你姑:
都是单例模式?唉,我了解的太肤浅了
java单例模式实现方法 -
wushuangyan26:
wangym 写道若单例在构造时需要参数,请教如何写最好?
...
java单例模式实现方法 -
wangym:
若单例在构造时需要参数,请教如何写最好?
java单例模式实现方法 -
yuhui136126:
from dual union all select ''te ...
拼接查询sql中指定列的结果集 -
litingkb:
...
oracle redo
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所申请的用户锁时 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
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所申请的用户锁时 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
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)
- 下载次数: 14
相关推荐
作用:DBMS_STATS.GATHER_TABLE_STATS统计表,列,索引的统计信息. DBMS_STATS.GATHER_TABLE_STATS的语法如下: DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_...
Oracle DOM编程 文档,有要的没 Start from toc.htm DBMS_XMLDOM DBMS_XMLPARSER DBMS_XMLQUERY
oracle dbms_lob
dbms_obfuscation_toolkit加密解密数据
ORACLE数据库使用dbms_stats包手动收集关于表的、索引的统计信息。
支持ArcGIS10.2版本的PostgreSQL_DBMS_for_windows_922,ESRI官方原版资源。
ORA-04063: package body SYS.DBMS_REGISTRY_SYS has errors
详细介绍DBMS_JOB包的各函数操作,以及各函数对job的使用作用等。
oracle中DBMS_SQL的使用,详细讲解oracle DBMS_SQL的使用办法
亲测有效 通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考...脚本里有说明 先运行upg_tzv_check.sql再运行upg_tzv_apply.sql
一、dbms_job涉及到的知识点 1、创建job: variable jobno number; dbms_job.submit(:jobno, —-job号 'your_procedure;',—-执行的存储过程, ';'不能省略 next_date, —-下次执行时间 'interval' —-每次间隔...
DBMS_XPLAN是Oracle提供的一个用于查看SQL计划,包括执行计划和解释计划的包;在以前查看SQL执行计划的时候,我都是使用set autotrace命令,不过现在看来,DBMS_XPLAN包给出了更加简化的获取和显示计划的方式。 这5...
Oracle 18c bug 执行 DBMS_PDB.CHECK_PLUG_COMPATIBILITY报错_ITPUB博客.mhtml
dbms_comp_advisor.getratio 预估压缩比例的存储过程脚本 11gR2以前使用,11gR2后可以使用系统自带的存储。
DBMS STATS Package Fails with Error 'ORA-04063: package body "SYS.DBMS_REGISTRY_SYS" has errors'
DBMS_JOB的详细定义,有实例,有详细的解释
DBMS_PROFILER使用指南 介绍详细,从安装到使用都一目明了
使用C语言实现的数据库管理系统。 支持简单类 SQL语言。