oracle lock 1,Oracle之DBMS_LOCK包用法详解

概述与背景

某些并发程序，在高并发的情况下，必须控制好并发请求的运行时间和次序，来保证处理数据的正确性和完整性。对于并发请求的并发控制，EBS系统可以通过Concurrent Program定义界面的Incompatibilities功能配置实现。但是Incompatibilities功能存在其局限性，它只能把整个并发请求作为一个整体来判断，并不能细化到并发请求的请求参数。而某些并发程序，只需要对其中某几个参数做并发控制，例如时间段，OU等。对于这种业务需求，可以使用标准包DBMS_LOCK，把需要做并发控制的参数，加上锁，实现并发控制。

不兼容设置：

例如：在调用并发的程序时，假设两个人同时提交了某一个并发请求，并且传入了同样的参数，假设在程序中使用了对状态的控制，例如在刚进入main函数的时候马上就将状态update了，那么其中某一个人就会查不到该条数据最后请求没有执行。也可以使用另外一种方法，使用DBMS_LOCK，将提交的并发锁住，其他提交的只能排队。

DBMS_LOCK相关知识介绍

锁模式

名字描述数据类型值

nl_modeNullINTEGER1

ss_modeSub Shared:used on an aggregate object to indicate taht share locks are being acquired on subparts of the object(对象的子部分，加上了Share锁)INTEGER2

sx_modeSub Exclusive:used on an aggregate object to indicate that exclusive locks are being acquired on subparts of the object(对象的子部分，加上了Exclusive锁)INTEGER3

s_modeShared:indicates that the entire aggregatr object has a share lock,but some of the sub-parts may additionlly have exclusive locks(对象加上了Share锁)INTEGER4

ssx_modeShared SubExclusive(对象加上了Share锁，其子部分加上了Exclusive锁)INTEGER5

x_modeExclusive(对象加上了Exclusive锁)INTEGER6

锁兼容性

NLSSSXSSSXX

NLSUCCSUCCSUCCSUCCSUCCSUCC

SSSUCCSUCCSUCCSUCCSUCCFAIL

SXSUCCSUCCSUCCFAILFAILFAIL

SSUCCSUCCFAILSUCCFAILFAIL

SSXSUCCSUCCFAILFAILFAILFAIL

XSUCCFAILFAILFAILFAILFAIL

DBMS_LOCK  Procedure

1、ALLOCATE_UNIQUE

Sql代码:dbms_lock.request(lockhandle        IN VARCHAR2,

lockmode          IN INTEGER DEFAULT x_mode,

timeout           IN INTEGER DEFAULT maxwait,

release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;

说明：锁模式转换。

类型：Procedure

参数：lockname  ：产生唯一的LockID。大小不超过128B，大小写敏感。不能以'ORA$'字符串开头，Oracle      Products保留。如果lockname已分配LockID，则返回handle；否则，生成一个新的LockID，并返回handle。

Lockhandle     ：返回值，request，convert，release调用。

expiration_secs：执行'allocate_unique'后，Clean Up的时间间隔。

2、 CONVERT

Sql代码：    dbms_lock.convert(lockhandle IN VARCHAR2,

lockmode   IN INTEGER,

timeout    IN NUMBER DEFAULT maxwait) RETURN INTEGER;

说明：锁模式转换。

类型：Function

参数：Lockhandle：allocate_unique取得的handle。

Lockmode  ：转换的锁模式。

Timeout   ：转换等待时间。

返回值：Return Values

0Success

1Timeout

2Deadlock

3Parameter error

4Don't own lock specified bu id or lockhandle

5Illegal lock handle

3、RELEASE

Sql代码:dbms_lock.release(lockhandle IN VARCHAR2) RETURN INTEGER;

说明：释放锁。

类型：Function

参数：Lockhandle：allocate_unique取得的handle。

返回值：Return Values

0Success

3Parameter error

4Don't own lock specified by id or lockhandle

5Illegal lock handle

4、REQUEST

Sql代码:    dbms_lock.request(lockhandle        IN VARCHAR2,

lockmode          IN INTEGER DEFAULT x_mode,

timeout           IN INTEGER DEFAULT maxwait,

release_on_commit IN BOOLEAN DEFAULT FALSE) RETURN INTEGER;

说明：请求锁。

类型：Function

参数：Lockhandle       ：allocate_unique取得的handle。

Lockmode         ：加锁模式。

Timeout          ：转换等待时间。

release_on_commit：在commit或者rollback之后释放锁，默认False。

返回值：Return Values

0Success

1Timeout

2Deadlock

3Parameter error

4Don't own lock specified by id or lockhandle

5Illegal lock handle

5、SLEEP

Sql代码  :dbms_lock.sleep(seconds IN NUMBER);

说明：Session睡眠。

类型：Procedure

参数：seconds：睡眠时间。

例子：SQL代码：

DECLARE

-- lock

l_lockname    VARCHAR2(100);

l_lockhandle  VARCHAR2(200);

l_lock_output NUMBER;

l_locked      BOOLEAN := FALSE;

g_pkg_name    VARCHAR2(240) := 'test_package';

g_org_id      NUMBER := 125;

BEGIN

/* 定义按何种方式并发(此处为同一个OU不能同时执行)

* lockname类似于定义一个唯一的名字，当并发程序执行时就会去判断是否这个唯一标识已经存在*/

l_lockname := g_pkg_name || '_' || g_org_id;

--根据l_lockname获取唯一标识l_lockhandle

dbms_lock.allocate_unique(lockname   => l_lockname,

lockhandle => l_lockhandle);

/*用l_lockhandle这个唯一标识去给当前请求加锁

--  Return value:

--    0 - success

--    1 - timeout

--    2 - deadlock

--    3 - parameter error

--    4 - already own lock specified by 'id' or 'lockhandle'

--    5 - illegal lockhandle*/

l_lock_output := dbms_lock.request(l_lockhandle,

6,

60,

FALSE);

IF l_lock_output <> 0 THEN

--Output lock failure message

RAISE apps.fnd_api.g_exc_error;

END IF;

--此处添加请求的业务逻辑

--dbms_lock.sleep(seconds => 50);

/*特别注意的是一定要将lockname释放掉 否则这个并发就永远别想再执行了*/

IF l_lock_output = 0 THEN

l_lock_output := dbms_lock.release(l_lockhandle);

END IF;

EXCEPTION

WHEN apps.fnd_api.g_exc_error THEN

IF l_lock_output = 0 THEN

l_lock_output := dbms_lock.release(l_lockhandle);

END IF;

WHEN OTHERS THEN

IF l_lock_output = 0 THEN

l_lock_output := dbms_lock.release(l_lockhandle);

END IF;

END;