今天来深入学习下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 就可以同时开始执行测试了。
再看看执行之后的结果如下:
这样就能够保证每一行都只被更新一次了。
分享到:
相关推荐
作用: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
NULL 博文链接:https://wushuangyan26.iteye.com/blog/1307514
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
oracle中DBMS_SQL的使用,详细讲解oracle DBMS_SQL的使用办法
亲测有效 通过此版本可以把oracle时区版本调整到为最新版本,一般配合时区补丁使用 可以参考...脚本里有说明 先运行upg_tzv_check.sql再运行upg_tzv_apply.sql
详细介绍DBMS_JOB包的各函数操作,以及各函数对job的使用作用等。
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使用指南 介绍详细,从安装到使用都一目明了
简单的dbms_stats操作,简单的dbms_stats操作简单的dbms_stats操作
DBMS_AQ 莫名其妙不知道为什么不能出队, 入队正常, 手动出队正常, 不能自动出队
使用C语言实现的数据库管理系统。 支持简单类 SQL语言。
NULL 博文链接:https://duqiangcise.iteye.com/blog/648491