Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deadlocking where there is a HOLDLOCK TABLOCKX hint applied Expand / Collapse
Author
Message
Posted Monday, February 17, 2014 4:40 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 91, Visits: 387
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...
Post #1542043
Posted Monday, February 17, 2014 7:26 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542115
Posted Monday, February 17, 2014 8:49 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 91, Visits: 387
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...
Post #1542173
Posted Monday, February 17, 2014 9:01 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 4:14 PM
Points: 15,725, Visits: 28,130
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
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #1542180
Posted Tuesday, February 18, 2014 7:45 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 2:37 PM
Points: 169, Visits: 437
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!
Post #1542578
Posted Wednesday, February 19, 2014 5:15 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 2:32 PM
Points: 91, Visits: 387
Thanks.

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

Laughing in the face of contention...
Post #1542930
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse