Select for update no wait
David H., November 27, 2020 - 6:26 am UTC
I had the same requirement but AQ was not an option for us. I came up with this solution. (Assuming each row has an "InProcess" status indicator column)
Query the table with "for update no wait" where status not in process. If it's not locked, update the status to in process. If the rows are locked, you'll get an error that you can trap, wait and try again. No other session will pick it up.
This is the basic idea, some nuances to work through like wait times and max attempts etc. Not super efficient.
November 27, 2020 - 7:25 am UTC
Check out SELECT FOR UPDATE SKIP LOCKED
This will solve your needs.