My database has a table: tableX.

Task1 makes intensive INSERTs (1000 records per minute) as records to process and very seldom UPDATEs (1-2 records per minute) as records to recalculate.

At the same time other Task2 SELECTs not processed records (inserted by Task1) and then inserts new records (as processed) to the same tableX in one transaction (it's too long calculation, up to 10 seconds).

Task3 recalculates UPDATED records (by Task1): just UPDATEs tableX.

Could someone recommend design to avoid long locking and deadlocks for this scenario. It's very important to process all INSERTED records in order as its were inserted!

1) Should I separate (create a new tableX2) processed and non-processed records?

2) Should I separate transaction in Task1 and do SELECTs and INSERTs in two separated transactions?

3) Should I use rowlock, readpast...hints and where (can it help me)?

  • 2
    stackoverflow.com/questions/939831/… on SO
    – gbn
    Commented Aug 25, 2011 at 14:28
  • @gbn I am worried about READPAST: it allows to skip locked rows, how to avoid locking inserted records (if those can be locked)?
    – garik
    Commented Aug 25, 2011 at 14:34
  • 2
    you can't avoid locks. This works well: we've used it ourselves
    – gbn
    Commented Aug 25, 2011 at 14:39

1 Answer 1


Simple this one.

Task1 should not insert into the main table, but a staging table, say tableXstaging. This table should either include an identity column or a row_processed (TINYINT or BIT) column.

Task1 keeps inserting all the time into tableXstaging. I would allow updates to update the main table directly as they are rare.

Another task which runs once every minute will either: set the MaxID = max(identity column) from tableXstaging, or if you instead used row_processed, set row_processed = 1. This gives you the batch to work with. This task will insert into tableX all records where identity<=MaxID, or if your using row_processed, WHERE row_processed = 1. A single insert into your primary tableX is very fast. Then delete from tableXstaging where row_processed = 1, or WHERE identity<=MaxID. I would go with identity as you don't need to update row_processed = 1 where row_processed = 0, and so will be faster.

tableX is now only inserted into once a minute.

Actually re-reading your question, i'd go with row_processed, set row_processed = 1 for your first batch.

Task 2 works on the staging table WHERE row_processed = 1. Then sets row_processed = 2.

Task 3 works on the staging table WHERE row_processed = 2. Then sets row_processed = 3.

Finally, SET XACT_ABORT ON; BEGIN TRAN, you insert the records into tableX in one hit (row_processed=3). This is very efficient as the staging table is tiny, and tableX will be quite large and slow (by relative terms to the staging table). Then delete from staging where row_processed = 3. COMMIT;

  • 1
    it was a good suggestion to use buffer table. thanks
    – garik
    Commented Sep 6, 2011 at 15:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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