The Sitecore 7.0+ EventQueue mechanism can in some rare occurrences cause a deadlock to occur.
You will notice that when it happens, your Sitecore Shell will come to a complete halt, the DMS becomes unresponsive, and you experience SQL timeout exceptions. Sitecore resumes when the deadlock is solved.
HOW TO SOLVE THEM
You will need SQL Server admin access to solve them. Open SQL Management Studio, go to the CORE database and run the following:
h1.TEXT AS RequestingText,
h2.TEXT AS BlockingTest,
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
When the script has run, you will get some output like this:
Deadlocks in EventQueue
The blocking_session_id is the session id that causes the deadlock. You need to kill the blocking session. Run the kill command in the SQL Management Studio:
Voila, the deadlock is gone.
WHO CAUSED THE DEADLOCK?
Well, lets see. Run the following SQL statement:
blk = CONVERT(char(3), blocked),
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
The output reveals the sinner:
Who caused the deadlock?
Oops, looks like I am causing the deadlock myself.
HOW CAN I AVOID DEADLOCKS?
Deadlocks have a tendency to occur in multi-server environments (obviously, since the EventQueue is used to communicate between servers). They also have a tendency to occur in multi-developer enenvironmentswhere several developers use the same SQL server.
1) Check the EventQueue size
Deadlocks occur if your EventQueue is too large. Make sure you clean the EventQueue often and keep as little data as possible, preferably below 1000 items. The CleanupEventQueue task will help you:
<agent type="Sitecore.Tasks.CleanupEventQueue, Sitecore.Kernel" method="Run" interval="04:00:00">
2) Let your CM server do the publishing
Most of the EventQueue contents is publish information. If possible, your architecture should allow on the CM server to publish. The CD servers should not.
3) Have enough power in the server rack
Deadlocks occur more often on heavily loaded environments, because the insert and select statements will be slower. Make sure you do not constantly run out of CPU or memory on your web and SQL servers.
MORE TO READ: