Skip to Main Content
  • Questions
  • Extract and delete records from table by only one session

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Andreas.

Asked: November 24, 2020 - 8:03 am UTC

Last updated: November 27, 2020 - 7:25 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Hello,

we have a table with records that contain values that have to be filled in batch and should be consumed by the client application.
The various sessions should read a record and delete it and it must be guaranteed that one record is used by only one session.
We tried with select for update but had locking problems.

Is there another way to "consume" the records and have the security that only session gets the same record?

Regards
Andreas


and Chris said...

By default Oracle Database has statement-level consistency. Which means that queries can only see changes committed at the time the query starts.

If you have

select ... from tab
delete tab ...


Two sessions running at the same time will select the same row.

Select for update is to help avoid this problem. The query in the first session will block the second until the transaction completes. This will lead to some sessions blocking each-other and is hard to avoid.

Is there another way to "consume" the records and have the security that only session gets the same record?

If you want to ensure that a row is only read by only one session, I suspect you want to use Advanced Queueing. If you explain further why you're trying to do this and give working code examples we can help further.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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.
Connor McDonald
November 27, 2020 - 7:25 am UTC

Check out SELECT FOR UPDATE SKIP LOCKED

This will solve your needs.


More to Explore

SQL

The Oracle documentation contains a complete SQL reference.