0

Lets say I have 10 records in the table.

  • Id Name
  • 1 abc
  • 2 def
  • 3 ghi
  • 10 xyz

Now there will be multiple request from multiple concurrent request to this SQL table with select top 1 record from this table.

  • When First request comes with Select TOP 1 record the first record should be returned and locked for others to update
  • When the Second request comes with select TOP 1 record, the next record should be locked and returned
  • When the Third request comes with select TOP 1 record, the 3rdrecord should be locked and returned
  • When the 10th request comes with select TOP 1 record, the 10th record should be locked and returned

Is there any way to perform this kind of operation?

Mathiyazhagan
  • 1,389
  • 3
  • 13
  • 37
  • Where do the requests come from? Expecting an answer like "10 users with a web page open running an angular app that calls to a back end c# web service, which uses the database" – Caius Jard Aug 10 '21 at 06:00
  • for me it looks like queue-implementation. As far as I remember in SQL Server you can use hint READPAST to achieve this – Sergey Aug 10 '21 at 06:46
  • Do it exactly as you say? Lock the table, pull the first record, set it as in progress, unlock the table? – Dale K Aug 10 '21 at 06:52
  • Thanks for sharing the inputs. I have an web application and different user or different browser session request number concurrently. I have pre populated the numbers in a SQL table. Now my application layer should get unique number from this SQL table without locking the whole table. If I lock the table it will work but then it will be sequential and impact the performance. 10 was just example I have mentioned but in real it may be thousands. Something similar to http://rusanu.com/2010/03/26/using-tables-as-queues/ this – Mathiyazhagan Aug 10 '21 at 10:48
  • SQL Server 2005 has been unsupported for many **years**. If you are really using that database, upgrade! If not, fix the tag. – Gordon Linoff Aug 10 '21 at 11:24
  • SQL server 2005 is not a constraint, I can user latest version may me SQL server 2018 or above as well. – Mathiyazhagan Aug 10 '21 at 19:12

1 Answers1

0

I would probably recommend adding a new column to the table and using the output clause. The new column would be something like processed_on. Then:

create table @ids (id int);

update top (1) t with (rowlock)
    set processed_on = getdate()
    output inserted.id into @ids
    where processed_on is null;

You want an index on processed_on. I believe this will lock only the row in question.

Then go about processing @ids.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786