We have a SQL Server 2005 stored procedure that updates the "batch_status" field of a specific row. This procedure is the one that is most frequently called in our entire system. It is typically called from within some third party software
that moves information from queue to queue to queue. The last thing the software does is to update the status.
One of our custom applications shells out to this third party software, gets the process handle, and waits for the handle to close. Then, it finds out what the batch_status is to deterrmine what to do next.
As volume increases in our system it becomes more and more likely for us to get a dirty read and the affected data gets "stuck" rather than moved on to where it should go. I am extremely puzzled as to how the stored procedure is supposedly done, yet the
change is not visible.
I don't know how to prevent these dirty reads. Should I use some locking hint in the update statement? If so, should I use it within a transaction block? I am very concerned about the effect either of these would be on overall performance. If I could
get just a rowlock that would be great. I'm concerned that page locks and tablelocks will create a "gridlock".
View Complete Post