Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

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.

share|improve this answer

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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