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.

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

share|improve this question
2  
stackoverflow.com/questions/939831/… on SO –  gbn Aug 25 '11 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 Aug 25 '11 at 14:34
2  
you can't avoid locks. This works well: we've used it ourselves –  gbn Aug 25 '11 at 14:39
add comment

1 Answer

up vote 1 down vote accepted

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;

share|improve this answer
    
+1 it makes sense. I will try tomorrow. –  garik Aug 25 '11 at 22:39
1  
it was a good suggestion to use buffer table. thanks –  garik Sep 6 '11 at 15:46
add comment

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.