DEV Community

Vahid Yousefzadeh
Vahid Yousefzadeh

Posted on Edited on

Oracle AI Database 26ai(23.26.4):SELECT FOR UPDATE with Row Limit

#backend #database #sql

Oracle AI Database 26ai Release 23.26.4 introduces support for combining Top-N queries using the FETCH FIRST clause with FOR UPDATE [SKIP LOCKED].

This enhancement allows applications to retrieve and lock a limited number of rows using the FETCH FIRST clause, simplifying the implementation of batch-processing operations that require controlled row-level locking.

Testing the Feature

The PERSON table contains the following rows:

SQL> select * from person;

        ID FNAME      LNAME                  SAL
---------- ---------- --------------- ----------
         1 Vahid      Yousefzadeh              4
         2 Nader      DastNeshan               5
         3 Kourosh    Barmak                   6
Enter fullscreen mode Exit fullscreen mode

The FETCH FIRST clause works correctly when used without FOR UPDATE SKIP LOCKED:

SQL> select * from person order by 1 FETCH FIRST 1 ROWS ONLY;

        ID FNAME      LNAME                  SAL
---------- ---------- --------------- ----------
         1 Vahid      Yousefzadeh              4
Enter fullscreen mode Exit fullscreen mode

Consider the following query executed on 23.26.3:

SQL> select * from person 
order by 1 
FETCH FIRST 1 ROWS ONLY 
FOR UPDATE SKIP LOCKED;
*
ERROR at line 1:
ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY, etc.
Help: https://docs.oracle.com/error-help/db/ora-02014/
Enter fullscreen mode Exit fullscreen mode

However, the statement fails with ORA-02014, indicating that Oracle does not allow the requested locking operation in this query context. Although the FETCH FIRST clause works independently, combining it with FOR UPDATE SKIP LOCKED produces an error in this release.

Release 23.26.4

Next, I tested the same statement on Oracle AI Database 26ai Release 23.26.4:

SQL> SELECT *
 FROM person
 ORDER BY 1
 FETCH FIRST 1 ROWS ONLY
 FOR UPDATE SKIP LOCKED;

ID FNAME LNAME SAL
 - - - - - - - - - - - - - - - - - - - - - - - 
 1 Vahid Yousefzadeh 4
Enter fullscreen mode Exit fullscreen mode

Unlike the previous test on 23.26.3, this statement succeeds in the tested 23.26.4 environment, demonstrating that the combination works in this environment.

Top comments (0)