How to retrieve only unlocked rows?

How to retrieve only those records from a table that are not locked by other users? One way of doing it is by writing a PL/SQL code based on locking error (-54). Is there any other way of viewing unlocked records in SQL only?

Example:

Sql*plus session 1:
A user locks records with update emp set sal = sal*1.2
where deptno = 40;

Sql*plus session 2:
Another user likes to view all unlocked records from
table emp and is not aware of what is locked.
What should he/she do?

SKIP LOCKED option of select for update will list unlocked records. This is an undocumented feature.
select empno, ename, job, sal
from emp
for update skip locked;

Tip taken from :
http://www.amar-padhi.com/oradb_retrieve_unlocked_rows.html

3 comments :

  1. hi,

    usefull option, but there's only a problem : to select the unlocked records you locks the full table.

    bye

    ReplyDelete
  2. Interesting, but when and how can this be useful?

    ReplyDelete