在Oracle中如何实现读锁

xiangzhao

考虑到Oracle中根本不存在读锁，那么必须将思路进行转化。
最先想到的是，将查询转化为DML，这样就可以获取到锁，避免其他用户对改对象同时进行访问。
最简单的实现方式莫过于建立一个存储过程，在存储过程中首先LOCK TABLE，然后进行查询，将查询的结果返回。
简单实现如下：
SQL> CREATE TABLE T
  2  (
  3   ID NUMBER PRIMARY KEY,
  4   NAME VARCHAR2(30)
  5  );
表已创建。
SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB;
已创建23行。
SQL> COMMIT;
提交完成。
下面构建函数：
SQL> CREATE OR REPLACE FUNCTION F_QUERY_T RETURN SYS_REFCURSOR AS
  2   V_CURSOR SYS_REFCURSOR;
  3  BEGIN
  4   LOCK TABLE T IN EXCLUSIVE MODE;
  5   OPEN V_CURSOR FOR 'SELECT * FROM T';
  6   RETURN V_CURSOR;
  7  END;
  8  /
函数已创建。
SQL> SELECT F_QUERY_T FROM DUAL;
F_QUERY_T
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
        ID NAME
---------- ------------------------------
         1 CHAINED_ROWS
         2 DEPT
         3 EMP
         4 BONUS
         5 SALGRADE
         6 DUMMY
         7 T_PK
         8 S_T
         9 MV_CAPABILITIES_TABLE
        10 TB_OBJECT_1136
        11 MLOG$_TB_OBJECT_1136
        12 RUPD$_TB_OBJECT_1136
        13 MV_TB_OBJECT_1136
        14 ORD_ORDER
        15 TT
        16 MV_T
        17 T_PRIMARY
        18 T_UPDATE
        19 T
        20 INF_PRODUCT
        21 INF_DRUG
        22 T_OLD
        23 INF_PRODUCT_PROPERTY
已选择23行。
下面在另外一个会话登陆，仍然通过函数来访问：
SQL> SET SQLP 'SQL2> '
SQL2> SELECT F_QUERY_T FROM DUAL;
会话被锁定，只有会话1提交或回滚，会话2才能继续查询：
SQL> COMMIT;
提交完成。
这时会话2解锁：

F_QUERY_T
--------------------
CURSOR STATEMENT : 1
CURSOR STATEMENT : 1
        ID NAME
---------- ------------------------------
         1 CHAINED_ROWS
         2 DEPT
         3 EMP
         4 BONUS
         5 SALGRADE
         6 DUMMY
         7 T_PK
         8 S_T
         9 MV_CAPABILITIES_TABLE
        10 TB_OBJECT_1136
        11 MLOG$_TB_OBJECT_1136
        12 RUPD$_TB_OBJECT_1136
        13 MV_TB_OBJECT_1136
        14 ORD_ORDER
        15 TT
        16 MV_T
        17 T_PRIMARY
        18 T_UPDATE
        19 T
        20 INF_PRODUCT
        21 INF_DRUG
        22 T_OLD
        23 INF_PRODUCT_PROPERTY
已选择23行。

SQL2> ROLLBACK;
回退已完成。
通过这种方法，简单的实现了读锁的功能，不过这种方法的缺点也很明显，要求用户必须通过函数的方式访问，而直接通过SQL方式访问是可以绕过锁机制的。

xiangzhao

上一篇文章给出了一种简单的方法，但是缺点也十分明显，就是要求用户必须采用调用函数的方式才能实现读锁。
很多情况下，上面的条件是无法实现的，这就要求必须有一种方法对于所有的访问情况都试用。
现在面临两个难题，一个是Oracle的读不加锁，因此必须自己实现锁的功能，二是如何将锁的实现添加到SELECT语句中，普通的触发器不会被SELECT所触发，因此通过触发器来实现这个功能是不现实的。
对于第一个问题，可以通过Oracle的DBMS_LOCK包来实现定制用户自定义锁的实现，而第二个问题可以利用Oracle的精细访问控制来实现。
简单描述一下思路，利用DBMS_LOCK.REQUEST过程，指定一个ID，来获取独占锁，其他会话获取同样的锁就会被锁定：
SQL> DECLARE
  2  V_LOCK NUMBER;
  3  BEGIN
  4  V_LOCK := DBMS_LOCK.REQUEST(0, RELEASE_ON_COMMIT => TRUE);
  5  END;
  6  /
PL/SQL 过程已成功完成。
会话2获取同样的锁，就会被锁定：
SQL2> DECLARE
  2  V_LOCK NUMBER;
  3  BEGIN
  4  V_LOCK := DBMS_LOCK.REQUEST(0, RELEASE_ON_COMMIT => TRUE);
  5  END;
  6  /
直到会话1提交、回滚或明确的释放锁资源：
SQL> COMMIT;
提交完成。
会话2才解锁：

PL/SQL 过程已成功完成。
SQL2> COMMIT;
提交完成。
利用DBMS_LOCK包可以实现锁的功能，下面就是利用DBMS_RLS包添加精细访问策略，在访问目标表的时候，将锁添加到查询语句中，简单的实现如下：
SQL> SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME = 'T';
OBJECT_ID
----------
     93789
SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR2, OBJECT_NAME IN VARCHAR2)
  2   RETURN VARCHAR2 AS
  3   V_NUM NUMBER;
  4  BEGIN
  5   RETURN 'DBMS_LOCK.REQUEST(93789, 6, 60) IN (0, 4)';
  6  END;
  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(*)
----------
        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)
------------------------
                       0
会话2被解锁：

  COUNT(*)
----------
        23
SQL2> SELECT DBMS_LOCK.RELEASE(93789) FROM DUAL;
DBMS_LOCK.RELEASE(93789)
------------------------
                       0
解决这个问题的方法是修改函数，由于这个函数调用发生在查询之前，因此将锁定放到函数中结果是一样的：
SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR2, OBJECT_NAME IN VARCHAR2)
  2   RETURN VARCHAR2 AS
  3   V_NUM NUMBER;
  4  BEGIN
  5   V_NUM := DBMS_LOCK.REQUEST(93590, 6, 60, TRUE);
  6   RETURN V_NUM || ' IN (0, 4)';
  7  END;
  8  /
函数已创建。
SQL> SELECT COUNT(*) FROM T;
  COUNT(*)
----------
        23
会话2尝试查询T表：
SQL2> SELECT COUNT(*) FROM T;
这时会话1可以通过提交或回滚来释放锁：
SQL> COMMIT;
提交完成。
会话2获取锁资源并查询T表记录：

  COUNT(*)
----------
        23
SQL2> COMMIT;
提交完成。
这样，通过DBMS_LOCK包自定义锁和DBMS_RLS包设置精细访问策略实现了Oracle中的读锁功能。需要注意的是，这种方法对于SYS用户无效，因为SYS用户不受精细访问策略的影响。
个人比较欣赏Tom的那句话，在Oracle中，很少会说不能做什么，而是会有你用多少中选择来实现这个功能。
这里想加一句，如果某个功能你在Oracle中无法实现，那么并不意味着在Oracle中无法实现，而多半是你对Oracle的功能还不是很了解。