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