0

I have a big dataset in Oracle which I need to process some of the rows which has a column PROCESSED= 0.

I have multiple instances of an application which will read 1 row at a time and perform the processing. To avoid multiple threads to access to the same row - I am using

SELECT * FROM FOO WHERE ROWNUM = 1 FOR UPDATE

If I execute the above query, the first thread is locking the row and the other rows are not able to fetch any rows as the ROWUM = 1 is already locked by the first thread. What I am trying to achieve is to fetch the "next unlocked" row.

Is there an efficient way to do it via SQL?

Namandeep_Kaur
  • 368
  • 1
  • 3
  • 11
Mustafa G
  • 1
  • 1
  • https://stackoverflow.com/questions/30993756/how-can-i-retrieve-next-n-unlocked-rows-from-oracle Does this help? – Namandeep_Kaur Aug 18 '20 at 18:05
  • Rownum won't work because it is arbitrary - values are assigned when the query runs and apply only to the result set, not the order of the rows in the table itself. – pmdba Aug 19 '20 at 11:04

1 Answers1

1

Looks like SKIP LOCKED is what are you looking for. See documentation

select * from foo for update skip locked

will select only those rows which are not locked by other transactions

Dornaut
  • 553
  • 3
  • 7