SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deadlocking where there is a HOLDLOCK TABLOCKX hint applied


Deadlocking where there is a HOLDLOCK TABLOCKX hint applied

Author
Message
arrjay
arrjay
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 600
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...
Grant Fritchey
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39233 Visits: 32616
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
arrjay
arrjay
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 600
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
Grant Fritchey
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39233 Visits: 32616
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 Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Jacob Wilkins
Jacob Wilkins
Hall of Fame
Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)Hall of Fame (3.2K reputation)

Group: General Forum Members
Points: 3243 Visits: 7724
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
arrjay
Old Hand
Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)Old Hand (321 reputation)

Group: General Forum Members
Points: 321 Visits: 600
Thanks.

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

Laughing in the face of contention...
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search