如何在其他Session跳过被锁定的记录

浅浅的花香味﹌ 2024-02-17 19:56 30阅读 0赞

如何在其他Session跳过被锁定的记录  
作者:eygle  
出处:http://www.eygle.com/blog  
日期:March 04, 2005  
浏览次数:187  
本文链接:http://www.eygle.com/archives/2005/03/eciouaeeeusessi.html

? 如何重建UNDO TABLESPACE | Blog首页 | CSDN的Blog,我们最需要什么? ?

\--------------------------------------------------------------------------------  
有人问到这样的问题:如果一个用户对记录加锁，如何使其他用户看不到这条记录。  
此前并未注意到这样一个用法（需要8.1.7.4.16以上的版本支持）:

\[oracle@jumper oracle\]$ sqlplus scott/tiger

SQL\*Plus: Release 9.2.0.4.0 - Production on Fri Mar 4 11:59:46 2005

Connected to:  
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  
With the Partitioning option  
JServer Release 9.2.0.4.0 - Production

SQL> select \* from dept;

DEPTNO DNAME LOC  
\---------- -------------- -------------  
10 ACCOUNTING NEW YORK  
20 RESEARCH DALLAS  
30 SALES CHICAGO  
40 OPERATIONS BOSTON  
33 ORACLE beijing  
35 Oracle Beijing  
36 Oracle Beijing  
37 Oracle Beijing

8 rows selected.

SQL> select \* from dept where deptno =10 for update;

DEPTNO DNAME LOC  
\---------- -------------- -------------  
10 ACCOUNTING NEW YORK

SQL> !  
\[oracle@jumper oracle\]$ sqlplus scott/tiger

SQL\*Plus: Release 9.2.0.4.0 - Production on Fri Mar 4 12:00:14 2005

Connected to:  
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production  
With the Partitioning option  
JServer Release 9.2.0.4.0 - Production

SQL> select \* from dept for update skip locked;

DEPTNO DNAME LOC  
\---------- -------------- -------------  
20 RESEARCH DALLAS  
30 SALES CHICAGO  
40 OPERATIONS BOSTON  
33 ORACLE beijing  
35 Oracle Beijing  
36 Oracle Beijing  
37 Oracle Beijing

7 rows selected.

SQL>