arrjay (2/17/2014)
Thanks for your response Grant!!Grant Fritchey (2/17/2014)
Also, using table locking hints is pretty severe in terms of blocking off access to the system. Are you sure you need that level of locking?
Yes, it is our intention to block off all access when the table is locked. Its the way in which we hand user sessions.
So, the QueueItem has a full lock, the HandlerRule has a shared lock and the index requires some level of locking. I still can't see where the deadlock would come from. The Queueitem table is a large table however the HandlerRule table contains only a few row.
Could a deadlock occur when the index is being rebuilt and sql attempts a full lock on the QueueItem table?
The problem I'm trying to get my head round is the index rebuild causing a deadlock. Any ideas?
Thanks again.
Yeah, absolutely the rebuild could cause a deadlock. It has to take out locks on the same indexes that you're locking on the table, so it makes perfect sense that you're seeing it there. Getting around it is likely to be a little difficult.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning