SQL Query to Get the Deadlocks in SQL Server 2008

How to View Deadlock Transactions In SQL Server?

Use this query

SELECT db.name                  DBName,
       tl.request_session_id,
       wt.blocking_session_id,
       Object_name(p.OBJECT_ID) BlockedObjectName,
       tl.resource_type,
       h1.TEXT                  AS RequestingText,
       h2.TEXT                  AS BlockingTest,
       tl.request_mode
FROM   sys.dm_tran_locks AS tl
       INNER JOIN sys.databases db
               ON db.database_id = tl.resource_database_id
       INNER JOIN sys.dm_os_waiting_tasks AS wt
               ON tl.lock_owner_address = wt.resource_address
       INNER JOIN sys.partitions AS p
               ON p.hobt_id = tl.resource_associated_entity_id
       INNER JOIN sys.dm_exec_connections ec1
               ON ec1.session_id = tl.request_session_id
       INNER JOIN sys.dm_exec_connections ec2
               ON ec2.session_id = wt.blocking_session_id
       CROSS APPLY sys.Dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
       CROSS APPLY sys.Dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2 

Source : http://blog.sqlauthority.com/2010/10/06/sql-server-quickest-way-to-identify-blocking-query-and-resolution-dirty-solution/

Find Deadlock details happened one day ago in SQL Server

I use this sql to get all the deadlocks.

DECLARE @xml XML

SELECT @xml = target_data
FROM   sys.dm_xe_session_targets
JOIN sys.dm_xe_sessions
ON event_session_address = address
WHERE  name = 'system_health' AND target_name = 'ring_buffer'

SELECT CAST(XEventData.XEvent.value('(data/value)[1]', 'varchar(max)') AS XML)
FROM   (SELECT @xml AS TargetData) AS Data
CROSS APPLY TargetData.nodes ('RingBufferTarget/event[@name="xml_deadlock_report"]') AS XEventData (XEvent)

Deadlock troubleshooting in Sql Server 2008

Writes will block reads on SQL Server, unless you have row versioning enabled. You should use the sp_who2 stored procedure and a SQL Profiler trace. sp_who2 will tell you which processes are blocking which, and the profiler will tell you what the last statement was for the blocking process.

Deadlock on SELECT/UPDATE

Its not enough to have a serializable transaction you need to hint on the locking for this to work.

The serializable isolation level will still usually acquire the "weakest" type of lock it can which ensures the serializable conditions are met (repeatable reads, no phantom rows etc)

So, you are grabbing a shared lock on your table which you are later (in your serializable transaction) trying to upgrade to an update lock. The upgrade will fail if another thread is holding the shared lock (it will work if no body else it holding a shared lock).

You probably want to change it to the following:

SELECT * FROM SessionTest with (updlock) WHERE SessionId = @SessionId

That will ensure an update lock is acquired when the SELECT is performed (so you will not need to upgrade the lock).

---

Related Topics