linq to sql with nservicebus table lock issue

Question

I am building a system using NServiceBus and my DataLayer is using Linq 2 SQL.

The system is made up of 2 services.

Service1 receives messages from NSB. It will query Table1 in my database and inserts a record into Table1 If a certain condition is met a new NSB message is sent to the 2nd service

Service2 will update records also in Table1 when it receives messages from Service1 and it does some other non database related work. Service2 is a long running process.

The problem I am having is the moment Service2 updates a record in Table1, the table is locked. The lock seems to be in place until Service2 has completed all it is processing. i.e. The lock is not released after my datacontext is disposed.

This causes the query in Service1 to timeout. Once Service2 completes processing, Service1 resumes processing again without problem.

So for example Service1 code may look like:

int x =0;
using (DataContext db = new DataContext())
{
  x = (from dp in db.Table1 select dp).Count(); // this line will timeout while service2 is processing

  Table1 t = new Table1();
  t.Data = "test";
  db.Table1.InsertOnSubmit(t);
  db.SubmitChanges();
}

if(x % 50 == 0)
  CallService2();

The code in service2 may look like:

using (DataContext db = new DataContext())
{
  Table1 t = db.Table1.Where(t => t.id == myId);
  t.Data = "updated";

  db.SubmitChanges();

}

// I would have expected the lock to have been released at this point, but this is not the case.

DoSomeLongRunningTasks();

// lock will be released once service2 exits

I don't understand why the lock is not released when the datacontext is disposed in Service2.

To get around the problem I have been calling:

db.ExecuteCommand("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED");

and this works, but I am not happy using it. I want to solve this problem properly.

Has any one experienced this sort of problem before and does any one know how to solve it? Why is the lock not released after the datacontext has been disposed?

Thanks in advance.

p.s. sorry for the extremely long post.

EDIT:

looking at this in a real world(ish) situation such as shipping:

service1 adds crate records to the database. the crate is added to a container record (if no container record exists one is created) if a certain number or crate records exist, then create a shipping record and close all containers and assign to a shipping record.

then call service2 to process shipping record. the call to service2 is a Bus.Send call, but could be part of a saga. service2 would update each crate record to which ship it is assigned to. then some other shipping instructions are processed. while service2 is processing, more crates could be received for the next shipping but as it stands they cannot be assigned to containers until service2 has finished processing the shipping.

Answer 1

The reason that you're seeing that behavior is that the default isolation level used by NServiceBus (which is the same for TransactionScope) is Serializable - which locks the entire table.

What you want to do is set a different isolation level at the NServiceBus level.

In order to do that you'll need to use the fluent initialization API and after the call to .MsmqTransport() call the method .IsolationLevel(IsolationLevel.ReadCommitted) or passing in some other value. I do not recommend going lower than that (like to read uncommitted).

In the scenario described above, I wouldn't put all this in a single handler, preferring the use of a saga to manage the top-level flow, since you seem happy enough to break isolation boundaries mid way through your handler.

Hope that helps.