Working with the hosting company to try and resolve this and they have provided the following example from the SQL Profiler which looks a bit odd to me. It looks as though it could be those sort of queries (highlighted) that are running into one another. To the best of my knowledge there is no nodeId of 1 anywhere in Umbraco!?

update cmsdocument set published = **** where nodeid = 1

The following issue looks related if anyone can shed any light on what the fix was in v6 to see if it can be backported to v4.

http://issues.umbraco.org/issue/U4-2513

Having a dig around in the source to see if I can track down this problem I found this in the umbraco.cms.businesslogic.web.Document class:

    public bool Published
    {
        get
        {
            //this is always the same as HasPublishedVersion in 4.x
            return HasPublishedVersion();
        }
        set
        {
            _hasPublishedVersion = value;
            SqlHelper.ExecuteNonQuery(
                string.Format("update cmsDocument set published = {0} where nodeId = {1}", Id, value ? 1 : 0));
        }
    }

This I think might be a bug and the parameters are the wrong way around and should in fact read as follows

    public bool Published
    {
        get
        {
            //this is always the same as HasPublishedVersion in 4.x
            return HasPublishedVersion();
        }
        set
        {
            _hasPublishedVersion = value;
            SqlHelper.ExecuteNonQuery(
                string.Format("update cmsDocument set published = {0} where nodeId = {1}", value ? 1 : 0, Id));
        }
    }

I patched the core and tried this out and whilst it has corrected this one issue the deadlocks still persist. I've never come up against SQL Deadlocks on any project before and what confuses me more is why this occurs on the new hardware and not anywhere else. In case it's relevant the difference between the current host and the new host is as follows:

1. Load balanced environment (2 nodes)
2. Separate SQL server instance as opposed to on-server SQL Express

Is this likely to be related to speed issues e.g. queries taking too long to run or is it a code based problem?

I'm running out of ideas now and the customer urgently needs to launch as already overdue by some weeks.

Thanks, Simon

My SQL skills are limited so I don't fully understand the cause of this (fragmented indexes?) however the hosting company support team performed a rebuild of all indexes in the database and not only did the problem go away but the performance of the entire site as a whole increased massively.

In addition to this they added an index on nodeId column of the cmsDocument table in case that is relevant to someone.

All previous issues surrounding the sluggish performance of the site have now all but vanished and we have one very happy customer!

is working on a reply...