0

I have multiple functions running multiple instances. I need each instance to take a unique id from a queue. The id's should stay in the queue forever so functions just keeps taking the id that was used latest. I tried with a SQL table with no luck.

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

I also tried following with a row lock:

BEGIN TRAN
declare @id int
declare @name as nvarchar(512)
declare @applicationId as nvarchar(512)
declare @applicationKey as nvarchar(512)
declare @lastUsed as datetime

select top 1 @id=id,@name=name,@applicationId=[ApplicationId],@applicationKey=[ApplicationKey],@lastUsed=LastUsed from [dbo].[AppRegistrations] WITH (updlock,readpast) order by LastUsed 
Update [dbo].[AppRegistrations] set LastUsed=getdate() where id=@id
select @id as Id,@name as Name, @applicationId as ApplicationId,@applicationKey as ApplicationKey,@lastUsed as LastUSed
COMMIT TRAN

Both of the samples above very often ends up with functions running with same id. So they dont work properly. It could be a SQL table is not best option. I have tried thinking some kind of azure queue. Can some one provide a strategy og sample on how to achive this scenario. All ideas are welcome.

Thomas Segato
  • 4,567
  • 11
  • 55
  • 104
  • `CURRENT_TIMESTAMP` returns a `datetime`, which is accurate only to 0.003 seconds. That is not going to be good enough to distinguish the "most recent" value. – Gordon Linoff Dec 18 '19 at 12:18
  • Ohh thats the problem. Would a datetime2 make any difference? – Thomas Segato Dec 18 '19 at 12:34
  • . . It would at least make it more likely that the most recent value would be unique. I don't think that will solve your duplication problem. For that you need locking -- but that might affect performance. – Gordon Linoff Dec 18 '19 at 13:25
  • My sample has locking but clearly wrong:D Getting the IP is OK performnce would be affected so if you have any ideas feel free to let me know. – Thomas Segato Dec 18 '19 at 13:39

0 Answers0