怎样在oracle 给表加锁？？

lock table 表名 exclusive mode nowait -- 锁整个表

select * from 表名 where XXX for update nowaitl -- 锁符合条件的记录

这一篇介绍一种更高级的实现方法 这使得无论是通过存储过程还是直接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 TCOUNT(*) SQL> MIT

提交完成

会话 对T表的查询被锁定

SQL >SELECT COUNT(*) FROM T

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

SQL> SELECT DBMS_LOCK RELEASE( ) FROM DUALDBMS_LOCK RELEASE( )

会话 被解锁

COUNT(*) SQL > SELECT DBMS_LOCK RELEASE( ) FROM DUALDBMS_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 TCOUNT(*)

会话 尝试查询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

---