Home » SQL & PL/SQL » SQL & PL/SQL » Oracle query (Oracle 12c)
Oracle query [message #681261] Wed, 01 July 2020 16:38 Go to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member

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

Re: Oracle query [message #681262 is a reply to message #681261] Wed, 01 July 2020 17:50 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
SKIP LOCKED applies to main query, bot to subquery. ANd, in fact, there is no need for subquery. Just use:

select ID from TEST
FOR UPDATE SKIP LOCKED;
And if all you want is update rows where status = submitted


select ID from TEST
where status = 'SUBMITTED'
FOR UPDATE SKIP LOCKED;
SY.
Re: Oracle query [message #681263 is a reply to message #681262] Wed, 01 July 2020 18:05 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks for reply. I have shorten the requirement to an easy step. As mentioned I have two JVM threads, when they run in parallel i want to make sure 1st JVM thread picks the first record with status SUBMITTED and the second JVM thread should pick the second record with status SUBMITTED.

Is this achievable through select query.

Hope i explained clear.

Thanks,
SRK
Re: Oracle query [message #681264 is a reply to message #681263] Wed, 01 July 2020 22:21 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Not unless table has last update id column (e.g. GUID). Then you can pass same GUID to both threads. Each thread will issue:

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 = ...

SY.
Re: Oracle query [message #681266 is a reply to message #681264] Thu, 02 July 2020 04:08 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks for your reply. I will try with the column.

Thanks,
SRK
Re: Oracle query [message #681267 is a reply to message #681266] Thu, 02 July 2020 06:34 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Hi,

I tried to run below in two different sessions. If I have two JVM thread in my query I have to make sure 1 JVM picks just one row and another JVM picks different row and not the JVM 1 row.

At any point of time, JVM should just pick one record and other JVM should skip and move to next record if it is picked and process by other JVM.
I understand on GUID value kind, but is there a way to achieve this without GUIS column kind


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


Re: Oracle query [message #681268 is a reply to message #681267] Thu, 02 July 2020 07:10 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This is the expecting behavior.
"SKIP LOCKED" is not part of the WHERE clause but part of the "FOR UPDATE" one (which is not part of "WHERE" clause either).

So both pick the same unique row and the first one locked it, so the second one has to skip it and has nothing.

Re: Oracle query [message #681269 is a reply to message #681268] Thu, 02 July 2020 08:35 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thanks, Michel. Is there any method to get the output as I requested apart from adding column to track it in Oracle 12c.

Thanks,
SRK
Re: Oracle query [message #681270 is a reply to message #681269] Thu, 02 July 2020 11:04 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The first way I see is to add a column to the table:

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.
Re: Oracle query [message #681271 is a reply to message #681270] Thu, 02 July 2020 12:33 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
This will not work. Assume session 2 issues select before session 1 updates locked to Yes.

Session 1
---------

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>
Session 2
---------

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>
And there is another ticking bomb - assume session updated locked to YES, committed it and then failed. Now we have to roll back YES to NO manually.

SY.
Re: Oracle query [message #681272 is a reply to message #681271] Thu, 02 July 2020 13:04 Go to previous messageGo to next message
srinivas.k2005
Messages: 404
Registered: August 2006
Senior Member
Thank you, yes that explains me.

Thanks,
SRK
Re: Oracle query [message #681273 is a reply to message #681272] Thu, 02 July 2020 13:18 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Something like multiple threads needs master/slave approach. Master selects to be updated IDs, splits them into groups and spawns slave threads passing each slave ID group(s) to process. Slave does the processing and reports back to master when done.

SY.
Re: Oracle query [message #681276 is a reply to message #681271] Fri, 03 July 2020 00:09 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes, I know it won't work, it was just a starter, I expect 1) the commit will come very soon after the first select, 2) the user that gets nothing retries times the query.
It is like "optimistic locking" mechanism.

Previous Topic: Help with the minus operator
Next Topic: DB link issue in cloned databases on same host
Goto Forum:
  


Current Time: Thu Mar 28 17:46:57 CDT 2024