tshad (7/31/2014)
I have a table to store int primary keys. Where the value nextID is the value to use.I need to get the value and update it (nextID + 1) before the next person gets it.
I thought using tran would work but it doesn't stop a select.
How do I get a value (lock the table for the update), update the table (preventing any selects until done) and release the table.
BEGIN TRAN
UPDATE TableIds
SET NextId = NextId + 1
WHERE TableName = 'Users'
WAITFOR DELAY '00:00:20'
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
COMMIT TRAN
I really want to do this in the reverse order but it doesn't work.
But if I do this and have another query to just do a select:
SELECT NextId
FROM TableIds
WHERE TableName = 'Users'
This query never returns. Doesn't the COMMIT TRAN release the lock on the table?
Thanks,
Tom
If you insist on using a home-grown sequence table, the following will keep others from "getting in" at the same time and will also help you avoid a total world of hurt when it come to deadlocks.
DECLARE @NextId INT
UPDATE TableIds
SET @NextId = NextId = NextId + 1
WHERE TableName = 'Users'
;
SELECT @NextID
;
Unfortunately, that also relegates you to a single row at a time just as your original code did, Will you ever be doing inserts on, say, the "Users" table in multi-row batches?
Be aware that if the code that uses this has an explicit transaction around it, fails, and rolls back, you could also experience gaps.
--Jeff Moden
Change is inevitable... Change for the better is not.