A m Z o n e

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; 

Press the Back button of you Browser to go to previous page
Home