Dienstag, 5. Februar 2013 09:59
I have two jobs that run concurrently on our data warehouse. The reference the same source tables. One of them creates RTT (referral to treatment data), the other a Clinic Slots dataset. At the point that they run, the source tables aren't touched by any other process and the calculation of them is complete.
About 1 night in 5 the Clinic Slots dataset is chosen as a deadlock victim. I've changed it so that every table reference it makes is (NOLOCK), but this is still happening. Should it be? I could also do the same to the RTT process. Anyone able to give me a quick pointer to stop this happening? Thanks,
Dienstag, 5. Februar 2013 12:25Hi there,
Its worth analyzing the indexes on your table, especially the FKey column. Also, take a look at the query plan.
Dienstag, 5. Februar 2013 13:36
Thanks for the reply. Certainly this might speed the process up. I am more interested in how a step in a job might get deadlocked when all table references are suffixed with NOLOCK. I don't understand how this can happen. Any thoughts? Thanks,
Dienstag, 5. Februar 2013 15:38
If there is update, delete, insert on your queries, even if you use (nolock) there won't be any affect. Best approach is to analyze your deadlock graph.
- Als Antwort markiert Mark Roworth 2 Dienstag, 5. Februar 2013 16:22
Dienstag, 5. Februar 2013 16:22
Aha. Thank you. That points me in the right direction.
Mittwoch, 6. Februar 2013 09:04Cheers!
Donnerstag, 7. Februar 2013 22:15
I would look at SNAPSHOT ISOLATION on the database and remove the NOLOCK.
I have seen many Deadlock victims that are caused by long running queiries that do not have covering indexes and the lock is on the Non-clustered Index and then the query needs to go to the Clustered Index for more columns.
NOLOCK is going to be removed in future versions because there is a chance for a row to be read twice because of page splits.
The suggestion about looking at the Query Plan is where I would start if I were you.
When A Covering Index no longer covers - http://thesmilingdba.blogspot.com/2012/10/when-covering-index-no-longer-covers.html
TheSmilingDBA Thomas LeBlanc MCITP 2008 DBA