Oracle AI Database 26ai: SELECT FOR UPDATE mit Zeilenlimit

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.

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.

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.