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