I have a table that is concurrently accessed by many users.
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?