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

By -

 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

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

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/

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

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.

Comments

Post a Comment

Popular posts from this blog

Oracle 21c Enhancements for TTS Export/Import

By -
  In Oracle version 21c, new features have been introduced for transportable tablespace (TTS) export/import. This article explores these capabilities in detail. Improvement 1: Parallel Execution of TTS Export/Import In Oracle 19c, TTS export/import operations could not be executed in parallel, as demonstrated by the following: Connected to: Oracle Database 19c Enterprise Edition Release 19.0 .0 .0 .0 – Production ORA-39002: invalid operation ORA-39047: Jobs of type TRANSPORTABLE cannot use multiple execution streams. Oracle addressed this limitation in version 21c, enabling parallel execution for TTS export/import: [oracle@oLinux7 ~]$ expdp directory=drm dumpfile=TTS_DUMP%U.dmp transport_tablespaces=tbs1,tbs2 transport_full_check= y parallel= 2 Export: Release 21.0 . 0 . 0 . 0 – Production on Tue Oct 12 06 : 48 : 54 2021 Version 21.3 . 0 . 0 . 0 Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS Processing object type TRANSPORTABLE_EXPOR...
Read more

Oracle 23ai — error_message_details Parameter for Displaying Error Details

By -
  error_message_details   is another new parameter introduced in Oracle version 23ai, which allows displaying detailed information about data value-related errors. By setting this parameter to   ON , we can see the exact value that caused errors such as   ORA-00001: unique constraint violated   within the error message itself. For example, in the following scenario, enabling the  error_message_details  parameter helps identify the value that triggered the error: SQL > create table tbl1(id number primary key); Table created. SQL > variable B number; SQL > exec :B: = 2547 ; PL / SQL procedure successfully completed. SQL > insert into tbl1 values (:B); 1 row created. SQL > insert into tbl1 values (:B); ERROR at line 1 : 'ORA-00001: unique constraint (USEF.SYS_C008328) violated on table USEF.TBL1 columns (ID)' 'ORA-03301: (ORA-00001 details) row with column values (ID:2547) already exists' Help: https: / / docs.oracle.com ...
Read more

Buffer Busy Wait and Read by Other Session in Oracle

By -
Image
Assume a block is accessed and pinned in the buffer cache by Session 1 (each buffer can be pinned by only one session at a time). While this block is pinned, if another session attempts to access the same block, it must wait. This type of wait is called  Buffer Busy Wait . What distinguishes Buffer Busy Wait from some other wait events is that the contention occurs at the  block level . Even if the two sessions intend to access different rows within the same block, they are still competing at the block level. As you know, Oracle blocks can contain different types of data. For example, one block may hold user data, while another may store control information. Therefore, contention for blocks can be divided into several categories: Data Block Data Segment Header Undo Header Undo Block Let’s briefly review the role of each block type in this wait event. 1) Data Block This refers to the blocks that store object data. Buffer Busy Waits at this level can have several causes: Poorly ...
Read more