Create a stored procedure that keeps returning new rows

Question

I have a table with x number of rows. I want to create a stored procedure that always select a new row and return that row (when all rows has been returned it will start over from first row). My idea is to select top 1 row (ordered by a date time row) return that from the stored procedure and then set an datetime column so next time it will be a new row that is returned. It needs to be thread safe so I would expect some row locking is needed (I don't know if this is true). How would you create a stored procedure like that? I am not sure of you need to use variables or it can be done in a single query. Something like:

select top 1 * 
from [dbo].[AppRegistrations] 
order by LastUsed

update [dbo].[AppRegistrations] 
set LastUsed = getdate()

In the comments it is stated that it cannot be done in a single query. If I added following to a stored procedure will it then be thread safe? Or do I need to add a lock? And does the query make sense or should it be done differently?

declare @id int
declare @name as nvarchar(256)
select top 1 @id=id,@name=name from [dbo].[AppRegistrations] order by LastUsed
Update [dbo].[AppRegistrations] set LastUsed=getdate() where id=@id
select @id,@name

It is important that another query cannot interrupt returning a unique row because it updates a row between the select and the update. That is why I wanted it in a single query.

I tried to gather everything up and added a row lock. Following sample works as expected, but I dont know whether the row lock is the right way, or I should expect some challenges. Can someone validate if this approach is correct?

BEGIN TRAN
declare @id int
declare @name as nvarchar(256)
select top 1 @id=id,@name=name from [dbo].[AppRegistrations] WITH   (HOLDLOCK, ROWLOCK) order by LastUsed 
Update [dbo].[AppRegistrations] set LastUsed=getdate() where id=@id
select @id as id,@name as name 
COMMIT TRAN

SELECT TOP (1) * FROM [dbo].[AppRegistrations] ORDER BY LastUsed DESC — Mark Schultheiss, Nov 11 '19 at 20:04
Because it will be a procedure that will be called by multiple jobs. Each job needs a unique key (each row has a key) that other jobs dosent use. — Thomas Segato, Nov 11 '19 at 20:20
You select the oldest row, then you update the entire table. That makes no sense. Are you missing a where clause? And why in the world do you need to select rows over and over like this? This has all the signs of a classic [XY Problem](http://xyproblem.info/) — Sean Lange, Nov 11 '19 at 20:26
I makes no sense that is true. But it is because I dont know whether you can do it in a single query, or I need to do the select, save the id in a variable, and then update the row. It was just to illustrate a sample knowing it is not correct. I am no SQL expert so it could be the solution is fairly simple. — Thomas Segato, Nov 11 '19 at 20:31
wouldn't it make more sense to simply pull the entire table into an array and loop through the records to call the other functions? Is this a pure sql project? — JoshGivens, Nov 11 '19 at 20:39
@ThomasSegato - well one row until a new one is updated (as is alluded to) — Mark Schultheiss, Nov 11 '19 at 20:41
This feels like some sort of a queuing mechanism here or a round-robin like here https://stackoverflow.com/a/2479120/125981 — Mark Schultheiss, Nov 11 '19 at 20:47
If the question is can you select a row AND update it in the same statement then the answer is NO. You cannot do both in a single statement. You would need to use two statements for that. The real issue here is that this question doesn't have enough meat on the bone to provide an answer. — Sean Lange, Nov 11 '19 at 20:48
It is not pure SQL. The problem is, it is multiple jobs on different hardware that all needs a unique row from the table. So I can not just pull all the data from the table. The other jobs dosent know what each single job takes from a given array. I have 10 jobs running at all time and 10 rows in the table. Each job needs a unique row from that table. — Thomas Segato, Nov 11 '19 at 20:48
Dont give up on me yet. I am pretty sure you DBA guy can solve this. I allready got more information. I just updated my post. — Thomas Segato, Nov 11 '19 at 20:50
Thanks all I updated my post. I think we are getting really close now. — Thomas Segato, Nov 11 '19 at 20:52
I added an answer but then found this https://stackoverflow.com/a/16595820/125981 — Mark Schultheiss, Nov 11 '19 at 20:59
@mauridb Exactly! Just without the queue being emptied. When all rows/messages has been used it just restarts. And it needs to be thread safe. So two jobs will never take same message/row. — Thomas Segato, Nov 11 '19 at 21:01
I dont really understand the query to be honest, but I just tested it, and does exact what I was looking for. And all in one query so it should be thread safe. Thanks alot! — Thomas Segato, Nov 11 '19 at 21:07
Just an idea, but perhaps use a [Sequence](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-sequence-transact-sql) with a fixed min & max and with a CYCLE. Then in the procedure get a row from the reference table based on the [next_value](https://learn.microsoft.com/en-us/sql/t-sql/functions/next-value-for-transact-sql)? — LukStorms, Nov 11 '19 at 21:08
That alone, unfortunately won't prevent the case in which two (or more) jobs could get the same id. If you want to prevent this situation, you need to add some more logic to handle this — mauridb, Nov 11 '19 at 21:08
What about the samle I have in my post. Would it be possible to add a row lock or something like that? — Thomas Segato, Nov 11 '19 at 21:09

Mark Schultheiss · Accepted Answer · 2019-11-11T21:23:51.717

0

I make a good number of assumptions here

UPDATE [dbo].[AppRegistrations]
SET LastSelected = CURRENT_TIMESTAMP
OUTPUT INSERTED.*
WHERE Id = (SELECT TOP (1) Id 
            FROM [dbo].[AppRegistrations] 
            ORDER BY LastSelected
            )

Here is some background on the OUTPUT https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql?view=sql-server-ver15

Here is another reference where you can do slightly more complex things https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver15#CaptureResults

edited Nov 11 '19 at 21:23

answered Nov 11 '19 at 20:55

Mark Schultheiss

32,614
12
69
100

Thanks. As other mention this is not thread safe. I extended my own sample can you validate if that is correct and post the response as an answer? – Thomas Segato Nov 12 '19 at 10:43
It actually look it is atomic. Following states OUTPUT exactly has this purpose: https://rusanu.com/2010/03/26/using-tables-as-queues/ – Thomas Segato Nov 12 '19 at 11:11

Create a stored procedure that keeps returning new rows

It is important that another query cannot interrupt returning a unique row because it updates a row between the select and the update. That is why I wanted it in a single query.

1 Answers1