DB-deadlocks (Sql server 2008 R2 Express) - How to debug?

Topics: Customizing Orchard, General, Troubleshooting, Writing modules
Sep 22, 2012 at 4:50 PM

The coming week we're supposed to go live with part 1 of the 'big' website that we're building for our client.

We never had any big issue, except the fact that some Orchard features do not like our large amount of 'content' (mostly because our rather large user list because a 'user' is also content in Orchard)

But now, 2-3 days before going live while filling in content, we ran in multiple deadlocks (why always near the end of a deadline...)

I'm rather sure it has to do something with our custom menu system, since once we're done filling in the menu, the deadlocks stopped occuring.

It seems to be a 'database' deadlock since once we restart the sql server, the site 'unblocks'.

As I stated, I don't think the problem is caused by something from the Orchard core, but does anyone have any advice on how I should go in finding the cause and a solution for this kind of problem?

We have a NHibernate Profiler license and so far I don't see anything truly bad (plenty of queries as always, but none of them are really heavy nor return plenty of records) but we did notice, once the issue occurs, that one transaction session wasn't committed (causing the blockage).

Is there a possibility that somehow a transaction is not properly committed / rolled back when something blows up (unhandled exception)?

Any advice is VERY welcome since if this isn't resolved we'll not reach our deadline :/

FYI: Afaik, we don't use any 'custom' locks others than the locks 'caused' by transactions) to synchronize access in our custom code.

If you can help us out or put us on the correct path, I'll give you a sneak peak at our 'big' Orchard project and you'll get free cookies (after visiting the website that is :P)!.

Coordinator
Sep 22, 2012 at 6:02 PM

Did you try this? http://msdn.microsoft.com/en-us/library/ms173799(v=sql.105).aspx

Sep 22, 2012 at 6:04 PM

Is that available for Express?

Coordinator
Sep 22, 2012 at 6:06 PM

I'm not sure. You may have to upgrade your dev environment in order to be able to debug this. Hopefully it doesn't just affect Express and is still reproducible.

Coordinator
Sep 23, 2012 at 6:14 PM

Sébastien is suggesting switching to ReadUncommitted transaction isolation level. To do that, open TransactionManager.cs (in Orchard.Framework/Data) and in the Demand method, change ReadCommitted to ReadUncommitted. Which makes me wonder, Sébastien, why isn't this the default when using Sql Server?

Apr 2, 2013 at 5:07 PM
We are experiencing the exact same problem on SQL server 2012. The exact records/tables/code has not been pinpointed yet. However, I'd like to point out here that a change to ReadUncommitted means you will see ghost records.

Upon switching to the suggested isolation level, we can see ghost items in the UI, which dissapear after the transaction is committed. I think this is funky behavior at the very least.

I rather go for a solution where the error doesn't happen. It will take some time, but I think it's possible. I will let you know once we found the exact cause for our deadlocks. It might be different, but it's something to look at.
Apr 2, 2013 at 5:55 PM
I got something similar a couple of weeks ago. Our site had been working fine in production for several months, but suddenly everything was failing with transaction errors. I'm not sure what caused it but restarting iis resolved it.

If anyone can suggest some kind data I should collect or investigation to conduct next time this happens i'll be glad to try to provide more data so we can find out what's happening.