Is it possible to do a 'dirty update' ?

Question

I have a SPROC that runs every hour. This SPROC calculates the popularity of a users artwork and updates the popularity column on the main artwork table. This is the same table where users are writing to it each time they upload a new artwork.

The problem is, this sproc keeps getting deadlocked with another transaction.

Considering I am not fussed about the actual score of each artwork (it just needs to roughly present the quality of the content on the site) and it doesnt need to be 100% accurate as this score is not public.

Hence, I would like to be able to do a 'dirty update' on this column.

Is this possible?

Answer 1

I don't believe there's any such thing as a dirty update. It would be better to investigate and resolve the deadlock condition.

Answer 2

Put a TRY...CATCH block around your update.

Answer 3

Assuming that you're issuing a single update statement to do the calculations for the entire table, the SQL Server engine is getting an UPDLOCK on the entire table while it does the transaction. The transaction is probably taking a long time and that is why you're seeing lock contention with the online system.

You can ensure which transaction dies (either the online transaction or your art score SPROC) by using SET DEADLOCK_PRIORITY either to LOW or HIGH in your SPROC. Then have the loser check for error 1205 and retry if it was rolled back.

The other (better) choice would probably be to have your artwork scoring system do the updates in chunks. First do the A's, then the B's, etc. That way the transaction would be faster. (You might need to split the data into even finer grains. Write it fairly generically and test it out)