c# - Is maintain the transaction with a static LINQ to SQL DataContext in asp.net possible?

Question

I have ASP.NET application which is connected with SQL server using LINQ to SQL. Where as i have a static class , certainly it would work on application level. Where as I have created static object of DataContext in this static class. I have not created any data context object in application except this. Where as i am using this static data context object for each data base manipulation.

So will this maintain the transaction as thread-safe for each logged in user?

Answer 1

Note: the following advice holds for all O/RM tools that implement the unit of work pattern such as Entity Framework's ObjectContext, DbContext, NHibernate's Session, and LINQ to SQL's DataContext.

The LINQ to SQL DataContext is NOT thread-safe. You should create (at least) 1 context per web request. Reusing the same instance over multiple threads means that one thread can call SubmitChanges while another thread is still inserting new objects. If you're lucky the DataContext will throw an exception, because it cannot persist the changes. If you're unlucky, the DataContext succeeds and you break the atomicy of a single request, which can cause you to have inconsistent data in your database. In other words: You'll have a database full of shit!

Besides that, the DataContext keeps all objects in its cache, which mean that the memory consumption of your application will keep growing, which can lead to an OutOfMemoryException (OOM). And even if you won’t get an OOM, the objects in the cache get stale and especially if other applications or processes are updating your database, you will never see those changes when an entity is already in memory. And last thing to note is that there is no way for you to rollback changes made in the DataContext so when you (at one point) invalidated the DataContext (because of errors) there is no way to restore this (except from creating a totally new DataContext). In that case your AppDomain is doomed.