查看: 24951|回复: 65

[精华] 在Oracle中如何实现读锁

[复制链接]
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2008-5-27 11:04 | 显示全部楼层 |阅读模式
写这个题目是由于看到论坛里面的一个帖子:http://www.itpub.net/thread-992141-1-1.html
虽然不清楚楼主的具体目的是什么,但是楼主提出的问题确实有点意思。因为Oracle中根本没有读锁,楼主的要求和Oracle尽可能提高并发的目的是截然相反的。
写这篇文章的目的并不是说这个需求有什么普遍性,而是为了说明在Oracle中其实没有什么是做不到的,即使这个需求和Oracle的设计本意相违背,另外希望这篇文章中的一些思路能起到抛砖引玉的作用。

楼主提出的问题是“怎么样让一个表,一个时间只能一个人读”,简单概括一下就是建立起读锁的机制。而且这个读锁还不能是共享锁,而必须是有个独占锁。
考虑到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方式访问是可以绕过锁机制的。


原文出自:http://yangtingkun.itpub.net/post/468/462871
论坛徽章:
226
BLOG每日发帖之星
日期:2010-02-11 01:01:06紫蛋头
日期:2013-01-12 23:45:222013年新春福章
日期:2013-02-25 14:51:24问答徽章
日期:2013-10-17 18:06:40优秀写手
日期:2013-12-18 09:29:10马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
 楼主| 发表于 2008-5-27 11:05 | 显示全部楼层

上一篇文章给出了一种简单的方法,但是缺点也十分明显,就是要求用户必须采用调用函数的方式才能实现读锁。
很多情况下,上面的条件是无法实现的,这就要求必须有一种方法对于所有的访问情况都试用。
现在面临两个难题,一个是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的功能还不是很了解。

原文出自:http://yangtingkun.itpub.net/post/468/462908

使用道具 举报

回复
论坛徽章:
120
现任管理团队成员
日期:2011-05-07 01:45:08乌索普
日期:2019-02-14 23:54:04
发表于 2008-5-27 11:07 | 显示全部楼层
沙发,顶以下 慢慢看~

使用道具 举报

回复
论坛徽章:
0
发表于 2008-5-27 16:31 | 显示全部楼层
非常好的思路!楼主很强悍!

使用道具 举报

回复
认证徽章
论坛徽章:
76
双子座
日期:2015-07-28 14:26:072012新春纪念徽章
日期:2012-02-13 15:09:52ITPUB十周年纪念徽章
日期:2011-11-01 16:21:15鲜花蛋
日期:2011-08-26 02:02:24管理团队成员
日期:2011-05-07 01:45:082010广州亚运会纪念徽章:皮划艇
日期:2011-04-18 11:24:412011新春纪念徽章
日期:2011-02-18 11:43:342011新春纪念徽章
日期:2011-01-25 15:42:562011新春纪念徽章
日期:2011-01-25 15:42:332011新春纪念徽章
日期:2011-01-25 15:42:15
发表于 2008-5-27 16:33 | 显示全部楼层
已经在lz博克上看到了,学习

使用道具 举报

回复
招聘 : 产品经理/专员
论坛徽章:
67
管理团队2006纪念徽章
日期:2006-04-16 22:44:452012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25版主1段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24夏利
日期:2013-10-10 13:12:33
发表于 2008-5-27 16:57 | 显示全部楼层
既然SELECT不能产生锁,我们是否可以让v$lock也出现一条锁的信息呢

使用道具 举报

回复
论坛徽章:
27
设计板块每日发贴之星
日期:2007-08-24 01:05:17奥运会纪念徽章:拳击
日期:2012-06-25 14:17:112012新春纪念徽章
日期:2012-01-04 11:49:54生肖徽章2007版:龙
日期:2009-04-07 18:18:35生肖徽章2007版:鸡
日期:2008-10-14 14:14:30生肖徽章2007版:龙
日期:2008-10-08 21:22:20铁扇公主
日期:2008-09-28 11:20:58授权会员
日期:2008-09-05 13:30:44ITPUB元老
日期:2008-09-05 13:30:31奥运会纪念徽章:摔跤
日期:2008-07-26 08:05:05
发表于 2008-5-27 17:06 | 显示全部楼层
版主真太强了

使用道具 举报

回复
论坛徽章:
107
生肖徽章2007版:兔
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2008-01-02 17:35:53生肖徽章2007版:兔
日期:2008-05-06 11:21:14生肖徽章2007版:兔
日期:2009-02-21 18:08:58生肖徽章2007版:兔
日期:2009-03-10 21:20:36迷宫蛋
日期:2011-09-13 10:38:48ITPUB十周年纪念徽章
日期:2011-11-01 16:19:41紫蛋头
日期:2011-12-05 13:26:13
发表于 2008-5-27 17:11 | 显示全部楼层
学习

使用道具 举报

回复
招聘 : 产品经理/专员
论坛徽章:
67
管理团队2006纪念徽章
日期:2006-04-16 22:44:452012新春纪念徽章
日期:2012-01-04 11:50:442012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:252012新春纪念徽章
日期:2012-02-13 15:12:25版主1段
日期:2012-05-15 15:24:112013年新春福章
日期:2013-02-25 14:51:24夏利
日期:2013-10-10 13:12:33
发表于 2008-5-27 17:14 | 显示全部楼层
现在一想DBMS_LOCK+FGA相当于在v$locked_object种构建一条记录了,厉害

使用道具 举报

回复
论坛徽章:
9
会员2007贡献徽章
日期:2007-09-26 18:42:10ITPUB新首页上线纪念徽章
日期:2007-10-20 08:38:44生肖徽章2007版:鸡
日期:2008-01-02 17:35:53奥运会纪念徽章:花样游泳
日期:2008-05-27 23:33:24奥运会纪念徽章:垒球
日期:2008-06-17 15:23:21奥运会纪念徽章:足球
日期:2008-07-14 17:22:53奥运会纪念徽章:跳水
日期:2008-08-06 16:18:33奥运会纪念徽章:曲棍球
日期:2008-09-11 10:05:202011新春纪念徽章
日期:2011-02-18 11:43:35
发表于 2008-5-27 17:32 | 显示全部楼层
强.大师.无愧

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表