怎么再Oracle中进行读锁（二）
这一篇介绍一种更高级的实现方法 这使得无论是通过存储过程还是直接SQL访问都可以实现读锁的机制
在Oracle中如何实现读锁(一) 上一篇文章给出了一种简单的方法 但是缺点也十分明显 就是要求用户必须采用调用函数的方式才能实现读锁
很多情况下 上面的条件是无法实现的 这就要求必须有一种方法对于所有的访问情况都试用
现在面临两个难题 一个是Oracle的读不加锁 因此必须自己实现锁的功能 二是如何将锁的实现添加到SELECT语句中 普通的触发器不会被SELECT所触发 因此通过触发器来实现这个功能是不现实的
对于第一个问题 可以通过Oracle的DBMS_LOCK包来实现定制用户自定义锁的实现 而第二个问题可以利用Oracle的精细访问控制来实现
简单描述一下思路 利用DBMS_LOCK REQUEST过程 指定一个ID 来获取独占锁 其他会话获取同样的锁就会被锁定
SQL> DECLARE V_LOCK NUMBER; BEGIN V_LOCK := DBMS_LOCK REQUEST( RELEASE_ON_MIT => TRUE); END; /
PL/SQL 过程已成功完成
会话 获取同样的锁 就会被锁定
SQL > DECLARE V_LOCK NUMBER; BEGIN V_LOCK := DBMS_LOCK REQUEST( RELEASE_ON_MIT => TRUE); END; /
SQL> MIT;
提交完成
会话 才解锁
PL/SQL 过程已成功完成
SQL > MIT;
提交完成
利用DBMS_LOCK包可以实现锁的功能 下面就是利用DBMS_RLS包添加精细访问策略 在访问目标表的时候 将锁添加到查询语句中 简单的实现如下
SQL> SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME = T ; OBJECT_ID SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR OBJECT_NAME IN VARCHAR ) RETURN VARCHAR AS V_NUM NUMBER; BEGIN RETURN DBMS_LOCK REQUEST( ) IN ( ) ; END; /
函数已创建
SQL> EXEC DBMS_RLS ADD_POLICY(USER T MYPOLICY USER F_POLICY );
PL/SQL 过程已成功完成
利用T的OBJECT_ID作为锁ID 避免和其他对象获取锁发生冲突 由于DBMS_LOCK REQUEST过程的RELEASE_ON_MIT参数要求布尔类型 而布尔类型无法在SQL中使用 这里暂时使用默认值FALSE 将这个函数作为T表的访问策略添加成功后 访问T表时 Oracle会自动将DBMS_LOCK REQUEST( ) IN ( )放到WHERE语句之后 从而实现读锁的功能
SQL> SELECT COUNT(*) FROM T; COUNT(*) SQL> MIT;
提交完成
会话 对T表的查询被锁定
SQL > SELECT COUNT(*) FROM T;
由于没有指定RELEASE_ON_MIT为TRUE 会话 提交或回滚仍然会占有锁资源 只有断开会话或明确的释放锁资源
SQL> SELECT DBMS_LOCK RELEASE( ) FROM DUAL; DBMS_LOCK RELEASE( )
会话 被解锁
COUNT(*) SQL > SELECT DBMS_LOCK RELEASE( ) FROM DUAL; DBMS_LOCK RELEASE( )
解决这个问题的方法是修改函数 由于这个函数调用发生在查询之前 因此将锁定放到函数中结果是一样的
SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR OBJECT_NAME IN VARCHAR ) RETURN VARCHAR AS V_NUM NUMBER; BEGIN V_NUM := DBMS_LOCK REQUEST( TRUE); RETURN V_NUM || IN ( ) ; END; /
函数已创建
SQL> SELECT COUNT(*) FROM T; COUNT(*)
会话 尝试查询T表
SQL > SELECT COUNT(*) FROM T;
这时会话 可以通过提交或回滚来释放锁
SQL> MIT;
提交完成
会话 获取锁资源并查询T表记录
COUNT(*) SQL > MIT;
提交完成
这样 通过DBMS_LOCK包自定义锁和DBMS_RLS包设置精细访问策略实现了Oracle中的读锁功能 需要注意的是 这种方法对于SYS用户无效 因为SYS用户不受精细访问策略的影响
个人比较欣赏Tom的那句话 在Oracle中 很少会说不能做什么 而是会有你用多少中选择来实现这个功能
lishixinzhi/Article/program/Oracle/201311/18182
