I have a table that is concurrently accessed by many users.

Typically, INSERTs to the table are made one at a time. Occassionaly, users will add a "batch" of rows at the same time. The batch is usually small, perhaps 30-50 rows.

The Identity for these rows that are added in batches must be consecutive, in order to match up with an external process that prints receipts for the added range.

I have a stored procedure that does the INSERTs. It has a transaction wrapped around the INSERT INTO... statements.

This is essentially how the stored procedure is designed:

WHILE @loopCounter <= @ReceiptCount
  SELECT  @pinNbr =  CAST(RAND() * 1000 AS INT);
  INSERT INTO MyTable (values)
  SELECT @insertedId = @@Identity;
  UPDATE MyTable SET externalId = CONVERT(VARCHAR,@pinNbr), 3) 
         + RIGHT('000000000' + CONVERT(VARCHAR,@insertedId), 9);
  SET @loopCounter = @loopCounter + 1;

What is happening, is that on occasion, some other users' typical INSERT process will insert a single row into the middle of the batch, messing up the consecutive range of IDs required.

I thought the transaction would lock the table and prevent this from happening.

How do I prevent the table from being updated while the batch process is adding records?

2 Answers 2


If you want to prevent messing up the sequence, you can lock the tables involved when you are doing the inserts. You will have to assess whether the duration of this locking is acceptable or not.

The command would be... Insert into table WITH (TABLOCK) (values).....

The scope of the transaction described in your question would not prevent external inserts from happening. Hence my suggestion of using table locks.


SQL is based on sets. This means that no database system guarantees any specific order in which the rows will be returned.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.