Oracle 19c - SKIP LOCKED does not work

User_HWMGH

I try to SELECT a row, lock it during selection, then moments later do an UPDATE and a COMMIT.

I tried the following basic example in SQL Developer, but no success. I would expect the last SELECT to return 3,4 but instead it returns 1,2,3,4. Could someone explain me why it is going on like that and what to do to get locking work (V$LOCKED_OBJECT shows that lock is created on SELECT and discarded on COMMIT/ROLLBACK).

create table t1(id number);

insert into t1 values(1);

insert into t1 values(2);

insert into t1 values(3);

insert into t1 values(4);

commit;

select * from t1 where id = 2 for update skip locked;

select * from t1 where rownum <= 1 for update skip locked;

select * from t1 for update skip locked;

asahide

Hi,

You need to try it in another session.

• session1

SQL> select * from t1 where id = 2 for update skip locked;
        ID
----------
         2
SQL> select * from t1 where rownum <= 1 for update skip locked;
        ID
----------
         1

• session2

SQL> select * from t1 for update skip locked;
        ID
----------
         3
         4

Regards,

asahide

Hi,

You need to try it in another session.

• session1

SQL> select * from t1 where id = 2 for update skip locked;
        ID
----------
         2
SQL> select * from t1 where rownum <= 1 for update skip locked;
        ID
----------
         1

• session2

SQL> select * from t1 for update skip locked;
        ID
----------
         3
         4

Regards,

User_HWMGH

Thanks a lot!