INSERT statements in a transaction and locking a range of rows

Question

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:

BEGIN TRANSACTION  
WHILE @loopCounter <= @ReceiptCount
 BEGIN
  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;
 END 
COMMIT TRAN

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?

Answer 1

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.

Answer 2

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