Tuesday, October 9, 2007

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.2where deptno = 40;Sql*plus session 2:Another user likes to view all unlocked records fromtable 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, salfrom empfor update skip locked;Tip taken from :http://www.amar-padhi.com/oradb_retrieve_unlocked_rows.html

No comments: