2008-5-27 11:18
意大利
在Oracle中如何实现读锁
虽然不清楚楼主的具体目的是什么，但是楼主提出的问题确实有点意思。因为Oracle中根本没有读锁，楼主的要求和Oracle尽可能提高并发的目的是截然相反的。
写这篇文章的目的并不是说这个需求有什么普遍性，而是为了说明在Oracle中其实没有什么是做不到的，即使这个需求和Oracle的设计本意相违背，另外希望这篇文章中的一些思路能起到抛砖引玉的作用。
[font=宋体][size=10.5pt][size=3]楼主提出的问题是“[color=black]怎么样让一个表,一个时间只能一个人读”，简单概括一下就是建立起读锁的机制。而且这个读锁还不能是共享锁，而必须是有个独占锁。[/color][/size][/size][/font]
[font=宋体][size=10.5pt][size=3]考虑到Oracle中根本不存在读锁，那么必须将思路进行转化。[/size][/size][/font]
[font=宋体][size=10.5pt][size=3]最先想到的是，将查询转化为DML，这样就可以获取到锁，避免其他用户对改对象同时进行访问。[/size][/size][/font]
[font=宋体][size=10.5pt][size=3]最简单的实现方式莫过于建立一个存储过程，在存储过程中首先LOCK TABLE，然后进行查询，将查询的结果返回。[/size][/size][/font]
[font=宋体][size=10.5pt][size=3]简单实现如下：[/size][/size][/font]
[size=10.5pt][font=宋体]SQL> CREATE TABLE T
2 (
3 ID NUMBER PRIMARY KEY,
4 NAME VARCHAR2(30)
5 );[/font][/size]
[size=10.5pt][font=宋体]表已创建。[/font][/size]
[size=10.5pt][font=宋体]SQL> INSERT INTO T SELECT ROWNUM, TNAME FROM TAB;[/font][/size]
[size=10.5pt][font=宋体]已创建23行。[/font][/size]
[size=10.5pt][font=宋体]SQL> COMMIT;[/font][/size]
[size=10.5pt][font=宋体]提交完成。[/font][/size]
[font=宋体][size=10.5pt][size=3]下面构建函数：[/size][/size][/font]
[size=10.5pt][font=宋体]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 /[/font][/size]
[size=10.5pt][font=宋体]函数已创建。[/font][/size]
[size=10.5pt][font=宋体]SQL> SELECT F_QUERY_T FROM DUAL;[/font][/size]
[size=10.5pt][font=宋体]F_QUERY_T
--------------------
CURSOR STATEMENT : 1[/font][/size]
[size=10.5pt][font=宋体]CURSOR STATEMENT : 1[/font][/size]
[size=10.5pt][font=宋体] 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[/font][/size]
[size=10.5pt][font=宋体]已选择23行。[/font][/size]
[font=宋体][size=10.5pt][size=3]下面在另外一个会话登陆，仍然通过函数来访问：[/size][/size][/font]
[size=10.5pt][font=宋体]SQL> SET SQLP 'SQL2> '
SQL2> SELECT F_QUERY_T FROM DUAL;[/font][/size]
[font=宋体][size=10.5pt][size=3]会话被锁定，只有会话1提交或回滚，会话2才能继续查询：[/size][/size][/font]
[size=10.5pt][font=宋体]SQL> COMMIT;[/font][/size]
[size=10.5pt][font=宋体]提交完成。[/font][/size]
[font=宋体][size=10.5pt][size=3]这时会话2解锁：[/size][/size][/font]
[size=10.5pt]
[font=宋体]F_QUERY_T
--------------------
CURSOR STATEMENT : 1[/font][/size]
[size=10.5pt][font=宋体]CURSOR STATEMENT : 1[/font][/size]
[size=10.5pt][font=宋体] 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[/font][/size]
[size=10.5pt][font=宋体]已选择23行。[/font][/size]
[size=10.5pt]
[font=宋体]SQL2> ROLLBACK;[/font][/size]
[size=10.5pt][font=宋体]回退已完成。[/font][/size]
[font=宋体][size=10.5pt][size=3]通过这种方法，简单的实现了读锁的功能，不过这种方法的缺点也很明显，要求用户必须通过函数的方式访问，而直接通过SQL方式访问是可以绕过锁机制的。[/size][/size][/font]
[font=宋体][size=3][/size][/font]
2008-5-27 11:19
意大利
上一篇文章给出了一种简单的方法，但是缺点也十分明显，就是要求用户必须采用调用函数的方式才能实现读锁。
很多情况下，上面的条件是无法实现的，这就要求必须有一种方法对于所有的访问情况都试用。
现在面临两个难题，一个是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的功能还不是很了解。