SQLServer lock table during stored procedure

Asked 2010-11-10T10:40:04.643

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!

sql sql-server locking

Edit Report

2 Answers

You might wanna consider deadlocks. This usually happens when multiple users use the stored procedure simultaneously. In order to avoid deadlock and make sure every query from the user will succeed you will need to do some handling during update failures and to do this you will need a try catch. This works on Sql Server 2005,2008 only.

DECLARE @Tries tinyint

SET @Tries = 1

WHILE @Tries <= 3

BEGIN

  BEGIN TRANSACTION

  BEGIN TRY

-- this line updates the last_auto_id

update last_auto_id set lastid = lastid+1

   COMMIT

   BREAK
  END TRY

  BEGIN CATCH

   SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_MESSAGE() as ErrorMessage

   ROLLBACK

   SET @Tries = @Tries + 1

   CONTINUE

 END CATCH

END

answered 2010-11-10T11:01:51.543

I prefer doing this using an identity field in a second table. If you make lastid identity then all you have to do is insert a row in that table and select @scope_identity to get your new value and you still have the concurrency safety of identity even though the id field in your main table is not identity.

answered 2010-11-10T12:16:50.507

SQLServer lock table during stored procedure

2 Answers

Your Answer