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
