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.