SQLServer lock table during stored procedure

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

You guys have between you answered my question. I'm putting in my own reply to collate the working solution I've got into one post. The key seems to have been the transaction approach, with locking hints on the last_auto_id table. Setting the transaction isolation to serializable seemed to create deadlock problems.

Here's what I've got (edited to show the full code so hopefully I can get some further answers...):

DECLARE @Pointer AS INTBEGIN TRANSACTION-- Check what the next ID to use should beSELECT @NextId = LastId + 1 FROM Last_Auto_Id WITH (TABLOCKX) WHERE Name = 'CustomerNo'-- Now check if this next ID already exists in the databaseIF EXISTS (SELECT CustomerNo FROM Customer           WHERE ISNUMERIC(CustomerNo) = 1 AND CustomerNo = @NextId)BEGIN  -- The next ID already exists - we need to find the next lowest free ID  CREATE TABLE #idtbl ( IdNo int )  -- Into temp table, grab all numeric IDs higher than the current next ID  INSERT INTO #idtbl  SELECT CAST(CustomerNo AS INT) FROM Customer  WHERE ISNUMERIC(CustomerNo) = 1 AND CustomerNo >= @NextId  ORDER BY CAST(CustomerNo AS INT)  -- Join the table with itself, based on the right hand side of the join  -- being equal to the ID on the left hand side + 1.  We're looking for  -- the lowest record where the right hand side is NULL (i.e. the ID is  -- unused)  SELECT @Pointer = MIN( t1.IdNo ) + 1 FROM #idtbl t1  LEFT OUTER JOIN #idtbl t2 ON t1.IdNo + 1 = t2.IdNo  WHERE t2.IdNo IS NULLENDUPDATE Last_Auto_Id SET LastId = @NextId WHERE Name = 'CustomerNo'COMMIT TRANSACTIONSELECT @NextId

This takes out an exclusive table lock at the start of the transaction, which then successfully queues up any further requests until after this request has updated the table and committed it's transaction.

I've written a bit of C code to hammer it with concurrent requests from half a dozen sessions and it's working perfectly.

However, I do have one worry which is the term locking 'hints' - does anyone know if SQLServer treats this as a definite instruction or just a hint (i.e. maybe it won't always obey it??)

How is this solution? No TABLE LOCK is required and works perfectly!!!

DECLARE  @NextId INTUPDATE   Last_Auto_Id SET      @NextId = LastId = LastId + 1WHERE    Name = 'CustomerNo'SELECT   @NextId 

Update statement always uses a lock to protect its update.