Forum Stats

  • 3,874,179 Users
  • 2,266,677 Discussions
  • 7,911,759 Comments

Discussions

Oracle 19c - SKIP LOCKED does not work

User_HWMGH
User_HWMGH Member Posts: 2 Green Ribbon
edited Nov 22, 2022 6:23AM in General Database Discussions

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;

Best Answer

  • asahide
    asahide Expert Technical Engineer Member Posts: 1,501 Gold Trophy
    Answer ✓

    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,

Answers

  • asahide
    asahide Expert Technical Engineer Member Posts: 1,501 Gold Trophy
    Answer ✓

    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
    User_HWMGH Member Posts: 2 Green Ribbon