Deadlocking where there is a HOLDLOCK TABLOCKX hint applied

Question

Deadlocking where there is a HOLDLOCK TABLOCKX hint applied

arrjay

Hall of Fame

Points: 3455
More actions
February 17, 2014 at 4:40 am

#282434

Hi there – I was hoping someone would be able to help with this one.
Recently we have noticed a deadlock occurring against two transactions.
Transaction 1 – A scheduled index optimisation script which re-indexes all indexes where the fragmentation levels are higher than 30%. This script rebuild indexes.
Transaction 2 – A select statement which uses the (HOLDLOCK, TABLOCKX) hints;
SELECT * FROM QueueItem WHERE RVER = (SELECT MIN(RVER) FROM [QueueItem] WITH (HOLDLOCK, TABLOCKX) WHERE [JobStatus] = 1 AND [JobType] IN (SELECT JobType FROM HandlerRule WHERE Online = 1 AND Handler = ‘XX’
I can’t see why I would receive a deadlock notification when Transaction 2 has an exclusive lock on the QUEUEITEM table.
Would a deadlock be caused if there was an exclusive lock on a table and the index went offline.
Thanks mucho in advance!
--------------------------------------------
Laughing in the face of contention...

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 1

You're taking out locks on both queueitem and handrule. It could be that the differences in order between those locks and the ones needed by the index rebuild could be the issue. And I realize that handrule is only a shared lock.

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?

"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

arrjay Hall of Fame Points: 3455 More actions · Answer 2

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.

--------------------------------------------

Laughing in the face of contention...

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 3

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

Jacob Wilkins One Orange Chip Points: 27976 More actions · Answer 4

Have you pulled the deadlock information (e.g., by enabling trace flag 1222)? That should clear up what is going on, as you'll be able to see exactly which locks each session owns and is waiting on.

If it's a system where enabling that trace flag or getting the deadlock graph from a trace means waiting on approval, then you could still have a chance of pulling the information for recent deadlocks from the ring buffers.

In 2008 you can try this:

SELECT OccurredAt,CAST(event_data.value('(event/data/value)[1]',

'varchar(max)') AS XML) AS DeadlockGraph

FROM ( SELECT Xevent.value('@timestamp','datetime') as OccurredAt,XEvent.query('.') AS event_data

FROM ( -- Cast the target_data to XML

SELECT CAST(target_data AS XML) AS TargetData

FROM sys.dm_xe_session_targets st

JOIN sys.dm_xe_sessions s

ON s.address = st.event_session_address

WHERE name = 'system_health'

AND target_name = 'ring_buffer'

) AS Data -- Split out the Event Nodes

CROSS APPLY TargetData.nodes('RingBufferTarget/

event[@name="xml_deadlock_report"]')

AS XEventData ( XEvent )

) AS tab ( OccurredAt,event_data )

order by OccurredAt desc

Cheers!

arrjay Hall of Fame Points: 3455 More actions · Answer 5

Thanks.

--------------------------------------------

Laughing in the face of contention...