Locking, deadlocking workaround while process data at the same table

Question

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)?

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;