Re: select only the records without a lock

On Thu, 18 May 2006 18:43:36 -0700, Rich wrote:

> How can I select all set of records that without record lock? I don't
> want to use the undoc feature 'SKIP LOCKED' and I don't want to use
> 'FORALL.. SAVE EXCEPTION'. Is there any other ways to do this. I need
> to process those unlocked records.

"SKIP LOCKED" is a very convenient feature, expressly for that purpose. If you don't want it, the only other way would be to use SELECT FOR UPDATE NOWAIT from inner PL/SQL block and handle an exception for error 54 in the exception handler. You would have to collect primary keys or rowids into a PL/SQL table to keep track of what is locked.

For those who are not aware of the undocumented feature you mentioned, let me explain: SELECT FOR UPDATE has an undocumented extension, written for General Ledger, with the keywords "SKIP LOCKED" instead of "NOWAIT" or WAIT <timeout>. The thing works like this:

Session 1)
SQL> select * from emp where deptno=10
  2 for update of sal;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7934 MILLER     CLERK           7782 23-JAN-82       1300
        10

Session 2)
SQL> select * from scott.emp for update of sal skip locked;

     EMPNO ENAME JOB MGR HIREDATE SAL COMM ---------- ---------- --------- ---------- --------- ---------- ----------

    DEPTNO

      7369 SMITH      CLERK           7902 17-DEC-80        800
        20

      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30

      7876 ADAMS      CLERK           7788 23-MAY-87       1100
        20

      7900 JAMES      CLERK           7698 03-DEC-81        950
        30

      7902 FORD       ANALYST         7566 03-DEC-81       3000
        20

11 rows selected.

SQL> Of course, it's undocumented, so psssst, don't tell a soul.

-- 
http://www.mgogala.com