在Oracle中如何实现读锁（二）

写这个题目是由于看到论坛里面的一个帖子：http://www.itpub.net/thread-992141-1-1.html。

虽然不清楚楼主的具体目的是什么，但是楼主提出的问题确实有点意思。因为Oracle中根本没有读锁，楼主的要求和Oracle尽可能提高并发的目的是截然相反的。

写这篇文章的目的并不是说这个需求有什么普遍性，而是为了说明在Oracle中其实没有什么是做不到的，即使这个需求和Oracle的设计本意相违背，另外希望这篇文章中的一些思路能起到抛砖引玉的作用。

这一篇介绍一种更高级的实现方法，这使得无论是通过存储过程还是直接SQL访问都可以实现读锁的机制。

在Oracle中如何实现读锁（一）：http://yangtingkun.itpub.net/post/468/462871

上一篇文章给出了一种简单的方法，但是缺点也十分明显，就是要求用户必须采用调用函数的方式才能实现读锁。

很多情况下，上面的条件是无法实现的，这就要求必须有一种方法对于所有的访问情况都试用。

现在面临两个难题，一个是Oracle的读不加锁，因此必须自己实现锁的功能，二是如何将锁的实现添加到SELECT语句中，普通的触发器不会被SELECT所触发，因此通过触发器来实现这个功能是不现实的。

对于第一个问题，可以通过Oracle的DBMS_LOCK包来实现定制用户自定义锁的实现，而第二个问题可以利用Oracle的精细访问控制来实现。

简单描述一下思路，利用DBMS_LOCK.REQUEST过程，指定一个ID，来获取独占锁，其他会话获取同样的锁就会被锁定：

SQL> DECLAREITPUB个人空间C)h#`d2t&G
  2  V_LOCK NUMBER;
Mi~.B*T1d$j0  3  BEGINITPUB个人空间eTh2hj S
  4  V_LOCK := DBMS_LOCK.REQUEST(0, RELEASE_ON_COMMIT => TRUE);
y*l6S!l$TgG0  5  END;ITPUB个人空间7HXM fgr+J
  6  /

PL/SQL过程已成功完成。

会话2获取同样的锁，就会被锁定：

SQL2> DECLARE
0gU+Ig]l3}6u"Z0  2  V_LOCK NUMBER;ITPUB个人空间$m|'qa3d.FH8JQGB3e
  3  BEGIN
'T y'JPV)v#Y0  4  V_LOCK := DBMS_LOCK.REQUEST(0, RELEASE_ON_COMMIT => TRUE);ITPUB个人空间quw*Wo Ze
  5  END;
M1i*m G U0  6  /

直到会话1提交、回滚或明确的释放锁资源：

SQL> COMMIT;

提交完成。

会话2才解锁：

N!Y1w.{`|+H ^0PL/SQL过程已成功完成。

SQL2> COMMIT;

提交完成。

利用DBMS_LOCK包可以实现锁的功能，下面就是利用DBMS_RLS包添加精细访问策略，在访问目标表的时候，将锁添加到查询语句中，简单的实现如下：

SQL> SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME = 'T';

 OBJECT_ID
k(T*n9d k0----------
J Y]$D0`}t3F
j(Bv
l0     93789

SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR2, OBJECT_NAME IN VARCHAR2)
l*[0R3S4Q/u`0  2   RETURN VARCHAR2 ASITPUB个人空间z4c Me,W6m}
  3   V_NUM NUMBER;ITPUB个人空间3E#i8O {#?1q0V\
  4  BEGIN
"]J6yT$pN;C_i0  5   RETURN 'DBMS_LOCK.REQUEST(93789, 6, 60) IN (0, 4)';
H9[%}e6l0  6  END;ITPUB个人空间@,ao
zeB5_&F
  7  /

函数已创建。

SQL> EXEC DBMS_RLS.ADD_POLICY(USER, 'T', 'MYPOLICY', USER, 'F_POLICY');

PL/SQL过程已成功完成。

利用T的OBJECT_ID作为锁ID，避免和其他对象获取锁发生冲突，由于DBMS_LOCK.REQUEST过程的RELEASE_ON_COMMIT参数要求布尔类型，而布尔类型无法在SQL中使用，这里暂时使用默认值FALSE。将这个函数作为T表的访问策略添加成功后，访问T表时，Oracle会自动将DBMS_LOCK.REQUEST(93789, 6, 60) IN (0, 4)放到WHERE语句之后，从而实现读锁的功能：

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)ITPUB个人空间2vi8Y8Eqw}e
----------
,@8uR$XIq;I$^J0        23

SQL> COMMIT;

提交完成。

会话2对T表的查询被锁定：

SQL2> SELECT COUNT(*) FROM T;

由于没有指定RELEASE_ON_COMMIT为TRUE，会话1提交或回滚仍然会占有锁资源，只有断开会话或明确的释放锁资源：

SQL> SELECT DBMS_LOCK.RELEASE(93789) FROM DUAL;

DBMS_LOCK.RELEASE(93789)ITPUB个人空间&PwW&Bv;e-A%RJ
------------------------ITPUB个人空间?I4P/sp
                       0

会话2被解锁：

V~qj:`0  COUNT(*)
9SZ;mRKf X/O0----------ITPUB个人空间 Kov&Lt]J&g"gu'u
        23

SQL2> SELECT DBMS_LOCK.RELEASE(93789) FROM DUAL;

DBMS_LOCK.RELEASE(93789)
B$d-]s+j6Rl#_E1A0------------------------
-In.?,xU-@/T8b%T0                       0

解决这个问题的方法是修改函数，由于这个函数调用发生在查询之前，因此将锁定放到函数中结果是一样的：

SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR2, OBJECT_NAME IN VARCHAR2)ITPUB个人空间 L8pK6w3}t
  2   RETURN VARCHAR2 AS
%axz&D7arn0  3   V_NUM NUMBER;
sV1S,XOB0  4  BEGINITPUB个人空间3B h#\R,H$gqu g5G[
  5   V_NUM := DBMS_LOCK.REQUEST(93590, 6, 60, TRUE);ITPUB个人空间.sQ8Vw.f8C2l
  6   RETURN V_NUM || ' IN (0, 4)';ITPUB个人空间N$CP7^&BD
  7  END;ITPUB个人空间'f3m `2IR*@N
  8  /

函数已创建。

SQL> SELECT COUNT(*) FROM T;

  COUNT(*)ITPUB个人空间
sX/]nY$ev]K
----------
e0C5]$n:]0        23

会话2尝试查询T表：

SQL2> SELECT COUNT(*) FROM T;

这时会话1可以通过提交或回滚来释放锁：

SQL> COMMIT;

提交完成。

会话2获取锁资源并查询T表记录：

ITPUB个人空间HL ] Y7[S,@wx+j
  COUNT(*)
cMUk5n0----------
8@B+pbF(@q0        23

SQL2> COMMIT;

提交完成。

这样，通过DBMS_LOCK包自定义锁和DBMS_RLS包设置精细访问策略实现了Oracle中的读锁功能。需要注意的是，这种方法对于SYS用户无效，因为SYS用户不受精细访问策略的影响。

个人比较欣赏Tom的那句话，在Oracle中，很少会说不能做什么，而是会有你用多少中选择来实现这个功能。

这里想加一句，如果某个功能你在Oracle中无法实现，那么并不意味着在Oracle中无法实现，而多半是你对Oracle的功能还不是很了解。