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

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

[oracle@jumper oracle]$ sqlplus scott/tiger

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

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

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

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

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>

存此备忘。

历史上的今天...

>>

2011-03-04文章:

2010-03-04文章:

2008-03-04文章:

2007-03-04文章:

By eygle on 2005-03-04 21:40 |

Comments (5) |

FAQ | 197 |

5 Comments

以前我怎么没有想到使用skip locked.

使用for update nowait 倒是可以确定对应的记录是否已经被锁^_^.

这应该是一个内部语法，没有在文档上说明的。

Steve这里有一些介绍:

http://www.ixora.com.au/q+a/0010/17094208.htm

我发现在oracle816也可使用这种语法. 昨天我还在想这个问题呢 :)

oralce 10G 里 能这样使用吗？为什么我使用了没有效果？

对了，中间SQL> !

[oracle@jumper oracle]$ sqlplus scott/tiger

这个是何用意啊？

请您解答下，谢谢了。

select * from dept for update skip locked;

显示的是未被锁住的记录；

那么如何显示已经被锁住的记录呢？

用

select * from dept for update locked 不行；

莫非要用

select * from dept where rowid

not in (select rowid from dept for update skip locked)