Sitecore EventQueue deadlocks – how to solve them and how to avoid them

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:

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:

Deadlocks in EventQueue

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:

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:

Who caused the deadlock?

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">
    <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:

Advertisements

About briancaos

Developer at Pentia A/S since 2003. Have developed Web Applications using Sitecore Since Sitecore 4.1.
This entry was posted in Sitecore 7 and tagged , , . Bookmark the permalink.

One Response to Sitecore EventQueue deadlocks – how to solve them and how to avoid them

  1. Pingback: Sitecore Event Queue – How to clean it – and why | Brian Pedersen's Sitecore and .NET Blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s