SQLServer lock table during stored procedure

Question

I've got a table where I need to auto-assign an ID 99% of the time (the other 1% rules out using an identity column it seems). So I've got a stored procedure to get next ID along the following lines:

select @nextid = lastid+1 from last_auto_id
check next available id in the table...
update last_auto_id set lastid = @nextid

Where the check has to check if users have manually used the IDs and find the next unused ID.

It works fine when I call it serially, returning 1, 2, 3 ... What I need to do is provide some locking where multiple processes call this at the same time. Ideally, I just need it to exclusively lock the last_auto_id table around this code so that a second call must wait for the first to update the table before it can run it's select.

In Postgres, I can do something like 'LOCK TABLE last_auto_id;' to explicitly lock the table. Any ideas how to accomplish it in SQL Server?

Thanks in advance!

Answer 1

Following update increments your lastid by one and assigns this value to your local variable in a single transaction.

Edit

thanks to Dave and Mitch for pointing out isolation level problems with the original solution.

UPDATE  last_auto_id WITH (READCOMMITTEDLOCK)
SET     @nextid = lastid = lastid + 1