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:
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 GO
When the script has run, you will get some output like this:
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:
kill 205; kill 62;
Voila, the deadlock is gone.
WHO CAUSED THE DEADLOCK?
Well, lets see. Run the following SQL statement:
SELECT spid, status, loginame=SUBSTRING(loginame,1,40), hostname=SUBSTRING(hostname,1, 40), blk = CONVERT(char(3), blocked), dbname=SUBSTRING(DB_NAME(dbid),1, 40), cmd, waittype FROM master.dbo.sysprocesses WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
The output reveals the sinner:
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"> <DaysToKeep>1</DaysToKeep> </agent>
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:
- Sitecore EventQueue Table growing out of control by Stackoverflow.
- Basics of Sitecore Event Queue by Sitecore basics
- Basic ways of troubleshooting EventQueue challenges by Sitecore basics