Hi,

Lets say I have a table with following column


Id	Status	        Creation date 
1	SUBMITTED	01/07/2020 
2	SUBMITTED	01/07/2020 
3	INPROGRESS	30/06/2020 
4	COMPLETE	29/06/2020 


CREATE TABLE TEST ( ID NUMBER, STATUS VARCHAR2(20), CREATION_DATE DATE);
INSERT INTO TEST VALUES (1,'SUBMITTED',TRUNC(SYSDATE));
INSERT INTO TEST VALUES (2,'SUBMITTED',TRUNC(SYSDATE));
INSERT INTO TEST VALUES (3,'INPROGRESS',TRUNC(SYSDATE)-10);
INSERT INTO TEST VALUES (4,'COMPLETE',TRUNC(SYSDATE)-20);
commit;

I want to fetch only one row for Update where status = submitted

Two JVM threads trigger parallel:

JVM Thread 1 – Picks row 1
JVM Thread 2 – Need to pick row 2 (because row 1 is locked by thread 1)

How to write such query?


JVM Thread 1:
select ID from TEST where ID IN
(
SELECT MAX(ID)
FROM  TEST
GROUP BY STATUS,CREATION_DATE
)
FOR UPDATE SKIP LOCKED;

Output:
2
3
4

JVM Thread 2:

select ID from TEST where ID IN
(
SELECT MAX(ID)
FROM  TEST
GROUP BY STATUS,CREATION_DATE
)
FOR UPDATE SKIP LOCKED;

Output:
Nothing

I should get 1

I know max(id) logic is wrong, what can be alternate so that i get required output , should i use rowid or something different.

Thanks,
SRK

select ID from TEST
FOR UPDATE SKIP LOCKED;

select ID from TEST
where status = 'SUBMITTED'
FOR UPDATE SKIP LOCKED;

select ID from TEST
where status = 'SUBMITTED' and last_update_id != 'GUID'
FOR UPDATE SKIP LOCKED;
...
UPDATE TEST
SET some_column=some_value,...,last_update_id = 'GUID'
WHERE id = ...

Session 1:

select ID from TEST 
where rownum = 1 and STATUS = 'SUBMITTED'
FOR UPDATE SKIP LOCKED;

Output
1

Session 2:
select ID from TEST 
where rownum = 1 and STATUS = 'SUBMITTED'
FOR UPDATE SKIP LOCKED;

Output 
Nothing

Expected : 2

I was expecting the output here as 2, not sure how rownum is sticking to the first record and not showing the data

SQL> alter table test add (locked varchar2(3) default 'NO');

Table altered.

SQL> select * from test;
        ID STATUS               CREATION_DATE       LOC
---------- -------------------- ------------------- ---
         1 SUBMITTED            02/07/2020 00:00:00 NO
         2 SUBMITTED            02/07/2020 00:00:00 NO
         3 INPROGRESS           22/06/2020 00:00:00 NO
         4 COMPLETE             12/06/2020 00:00:00 NO

4 rows selected.

18:00:22 SESS1> col id new_value id
18:00:22 SESS1> select ID from TEST
18:00:22   2  where rownum = 1 and STATUS = 'SUBMITTED' and LOCKED='NO'
18:00:22   3  FOR UPDATE SKIP LOCKED;
        ID
----------
         1

1 row selected.

18:00:23 SESS1> update test set locked='YES' where id=&id;

1 row updated.

18:00:23 SESS1> commit;

Commit complete.

18:00:23 SESS1> select ID from TEST
18:00:23   2  where id = &id
18:00:23   3  FOR UPDATE;
        ID 
---------- 
         1 

1 row selected.

18:00:24 SESS2> col id new_value id
18:00:24 SESS2> select ID from TEST
18:00:24   2  where rownum = 1 and STATUS = 'SUBMITTED' and LOCKED='NO'
18:00:24   3  FOR UPDATE SKIP LOCKED;
        ID
----------
         2

1 row selected.

18:00:24 SESS2> update test set locked='YES' where id=&id;

1 row updated.

18:00:24 SESS2> commit;

Commit complete.

18:00:24 SESS2> select ID from TEST
18:00:24   2  where id = &id
18:00:24   3  FOR UPDATE;
        ID 
---------- 
         2 

1 row selected.

SQL> col id new_value id
SQL> select ID from TEST
  2  where rownum = 1 and STATUS = 'SUBMITTED' and LOCKED='NO'
  3  FOR UPDATE SKIP LOCKED;

        ID
----------
         2

SQL>  -- Sleep for 10 seconds to mimic situation where session 2 issues the above FOR UPDATE
SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> update test set locked='YES' where id=&id;
old   1: update test set locked='YES' where id=&id
new   1: update test set locked='YES' where id=         2

1 row updated.

SQL> commit;

Commit complete.

SQL> select ID from TEST
  2  where id = &id
  3  FOR UPDATE;
old   2: where id = &id
new   2: where id =          2

        ID
----------
         2

SQL>

SQL> col id new_value id
SQL> select ID from TEST
  2  where rownum = 1 and STATUS = 'SUBMITTED' and LOCKED='NO'
  3  FOR UPDATE SKIP LOCKED;

no rows selected <-- As you can see, session 2 gets no rows back.

SQL>  -- Sleep for 10 seconds to mimic situation where session 2 issues the above FOR UPDATE
SQL> EXEC DBMS_LOCK.SLEEP(10);

PL/SQL procedure successfully completed.

SQL> update test set locked='YES' where id=&id;
old   1: update test set locked='YES' where id=&id
new   1: update test set locked='YES' where id=
update test set locked='YES' where id=
                                     *
ERROR at line 1:
ORA-00936: missing expression


SQL> commit;

Commit complete.

SQL> select ID from TEST
  2  where id = &id
  3  FOR UPDATE;
old   2: where id = &id
new   2: where id =
FOR UPDATE
*
ERROR at line 3:
ORA-00936: missing expression


SQL>