Database Locking

Topics: General, Troubleshooting
Aug 3, 2011 at 10:53 PM

One of the biggest problems we've experienced in Orchard has been with database locking/blocking tasks, namely with background tasks that need to modify tables, but end up blocking web requests that need to read from those tables. Is there a way to set read uncommitted or nolock hints on select queries within NHib on a global level so that content selects aren't hung waiting for a background task transaction to finish?

Oct 29, 2011 at 4:00 AM

 

http://chrisbower.com/2011/08/05/isolating-background-task-transactions/

Oct 29, 2011 at 4:01 AM

That's my blog :)

Nov 2, 2011 at 1:17 PM

Hi,

We've been seeing occasional deadlocks causing pages to error. I'm about to try out the solution from your blog. Are you saying that the problem still happens occasionally even with that workaround?

It might be worth raising this issue with the Performance team in Annoucements forum. Do you know if any workitem exists already for this issue?

Coordinator
Nov 2, 2011 at 5:45 PM

Deadlocks are not about background transaction issues. Chris' blog post is about preventing a failed task to fail all others, and it has already been fixed in 1.3. But what you need is to change the Isolation level to ReadUncommitted in the TransactionManager class. The performance effort is aware of it, and one goal is to make it configurable, because SQLCe doesn't handle this mode.

Nov 2, 2011 at 8:36 PM

It was kind of both Sebastien :)

Having all of the background tasks in a single transaction caused that transaction to stay open for a while, which was causing locking issues elsewhere. ReadUncommitted + isolating bg tasks solved it.

Nov 3, 2011 at 12:36 AM

sorry, I can not get clearly here,I could not find

ReadUncommitted in whole solution.

 I only got this code in BackgroundService.cs(LINE29):

================

using (var scope = new TransactionScope(TransactionScopeOption.RequiresNew)) {
                    try {
                        task.Sweep();
                        scope.Complete();
                    }
======================================
it seems there is no ReadUncommitted.
and the following in transactionmanager.cs(line26):
==========
 _scope = new TransactionScope(
                    TransactionScopeOption.Required, 
                    new TransactionOptions { 
                        IsolationLevel = IsolationLevel.ReadCommitted 
                    });


Nov 3, 2011 at 12:38 AM

does it mean that  i should change to ReadUncommitted somewhere to the purpose?

Nov 3, 2011 at 12:39 AM

Change


                        IsolationLevel = IsolationLevel.ReadCommitted 
to

                        IsolationLevel = IsolationLevel.ReadUncommitted 

Nov 3, 2011 at 12:39 AM

Or even better, implement your own transactionmanager and override the existing one.

Nov 3, 2011 at 12:50 AM

thanks for quick reply. i see now

Nov 3, 2011 at 2:09 PM

Thanks for the responses, our website is seeming very stable these days on Orchard 1.3 apart from these occasional lags, I'll give ReadUncommitted a shot and see how it goes. The problem of course is it's very intermittent!

Jan 25, 2012 at 2:22 PM

Hello, We implemented ReadUncommitted and this has reduced to deadlocks to one or two every 24 hours.