Rick Strahl's Weblog  

Wind, waves, code and everything in between...
.NET • C# • Markdown • WPF • All Things Web
Contact   •   Articles   •   Products   •   Support   •   Advertise
Sponsored by:
Markdown Monster - The Markdown Editor for Windows

LINQ to SQL and Transactions


:P
On this page:

LINQ to SQL handles a lot of functionality out of the box for you, including the ability to perform updates under transaction contexts for you transparently. If you modify, add or delete instances of entities the changes are updated in the context of a transaction when SubmitChanges() is called. This covers a wide range of operations and this is as it should be for an ORM: Transactions should be handled mostly transparently.

Every once in a while though you may need to perform some update tasks manually rather than through the LINQ model and you may end up combining operations in multiple data contexts that logically need to operate as a single transaction. I’m making up a slightly contrived example here as this can and probably should be handled as part of your model, but bear with me for demonstration purposes. Assume you have a parent record and you need to also delete all children and there’s no explicit relation defined in the model, so that you have to manually delete the items. As weird as that sounds I actually have several apps where this situation exists because the relationship is a rarely used one and managed manually rather than being enforced through the model.

So, inside of a business object that holds code snippets and comments associated with a snippet handles child deletion involving two explicit data operations like this:

public override bool Delete(CodeSnippet entity)
{
    if (entity == null)            
        entity = this.Entity;            

    using (TransactionScope trans = new TransactionScope())
    {
        // base business layer delete behavior through DataContext
        if (!base.Delete(entity))
            return false;

        // plain Ado.Net operation using Context and Context’s connection
// no retrieval first to delete int result = this.Context.ExecuteCommand("delete from comments where snippetId = {0}", entity.Id)); if (result < 0) return false; trans.Complete(); return true; } }

In the business base layer the Delete operation in this case submits the active entity for deletion which internally looks something like this (simplified):

public virtual bool Delete(TEntity entity)
{        
     Table<TEntity> table = this.Context.GetTable(typeof(TEntity)) as Table<TEntity>;
     table.DeleteOnSubmit(entity);                    
     return this.SubmitChanges();
}

The idea is that there are two data operations involved – one to delete the parent entity and one to delete the children. Two separate operations, one using straight LINQ to SQL and SubmitChanges() and one using a plain old ADO.NET operation (internally wrapped in the base business layer). Both are using the same DataContext instance and therefore also the same connection object.

If your operation requires multiple data operations you’ll want to wrap the whole process into a transaction. LINQ to SQL will provide automatic transactions for any single SubmitChanges() updates performed, but it won’t help if you need to run multiple separate commands commands that need to be committed as one. So explicit transactions are required.

Another example of multiple data operations that require transaction wrapping might be Stored Procedure calls – either manual as I did above or for imported LINQ to SQL Stored Proc wrappers.

TransactionScope to the Rescue

The easiest way to do manual transactions with LINQ to SQL is by using TransactionScope. As you can see TransactionScope is very easy to use by wrapping a using{} statement around the transactable data access code. Calling Complete() on the TransactionScope commits the transaction, exiting the using block without calling Complete() causes the transaction to roll back.

TransactionScope is a high level Transaction wrapper that makes it real easy to wrap any code into a transaction without having to track transactions manually. Traditionally TransactionScope was a .NET wrapper around the Distributed Transaction Coordinator (DTC) but it’s functionality has expanded somewhat. One concern is that the DTC is rather expensive in terms of resource usage and it requires that the DTC service is actually running on the machine (yet another service which is especially bothersome on a client installation).

However, recent updates to TransactionScope and the SQL Server Client drivers make it possible to use TransactionScope class and the ease of use it provides without requiring DTC as long as you are running against a single database and with a single consistent connection string. In the example above, since the transaction works with a single instance of a DataContext, the transaction actually works without involving DTC. This is in SQL Server 2008.

To check this out, shut down the DTC service and run multiple operation wrapped in a transaction context against a single DataContext and notice that both committing and rolling back does all the right things even though DTC is not available.

However, if your code is a little more isolated and requires multiple connections or DataContexts things get a little more tricky. For example take this code that offloads the detail deletion to a separate business object which in my case uses a separate connection/data context:

public override bool Delete(CodeSnippet entity)
{
    if (entity == null)            
        entity = this.Entity;            

    using (TransactionScope trans = new TransactionScope())
    {
        // base business layer delete behavior through DataContext
        if (!base.Delete(entity))
            return false;

        // Use a new bus object and separate DataContext to delete
        busComment comment = new busComment();
        if (!comment.DeleteCommentsForSnippet(entity.Id))
        {
            this.SetError(comment.ErrorMessage);
            return false;
        }

        trans.Complete();
        return true;
    }
}
// in busComment.cs  - uses separate DataContext instance/connection
/// <summary>
/// Deletes all comments for a specific user
/// </summary>
/// <param name="snippetId"></param>
/// <returns></returns>
public bool DeleteCommentsForSnippet(string snippetId)
{
    int result =  this.Context.ExecuteNonQuery("delete from Comments where snippetId = @snippetId",
         this.Context.CreateParameter("@snippetId", snippetId));
    
   return result > -1;                
}

In this scenario two separate connections are involved and this transaction by default tries to escalate to a DTC transaction. If the DTC Service isn’t running the first LINQ to SQL or low level ADO.NET operation will fail with an error to the effect that DTC transaction context isn’t available.

TransactionScope DTC Requirements

The good news is that as of .NET 3.5 and SQL 2008/2005 local transactions no longer have to escalate to distributed transactions, when single connections are involved. According to that article with SQL 2008 multiple connections should also work as long as the same connection string is used but I was unable to get that behavior to work. When the DTC  service is not running and when using multiple data context I always get an error on the first data update operation.

You can control whether the connection participates in DTC transaction via the Enlist attribute on the connection string:

    <add name="CodePasteBusiness.Properties.Settings.CodePasteConnectionString"
         connectionString="Data Source=.;Initial Catalog=CodePaste;Integrated Security=True;Enlist=True;"
         providerName="System.Data.SqlClient"/>

The default for Enlist=True which means that transactions can escalate to DTC transactions. False treats all transactions as local ADO.NET transactions. Using False is useful only if you know for sure that you are not using distributed transactions anywhere in your application – in which case it provides a very slight performance improvement (no checks for distributed transactions pending).

When set to the default of true, and when escalating a transaction to the DTC when the service is not running, any update command produces an error:

MSDTC on server 'xxx' is unavailable.

If you want to figure out which transactions are escalating and which stay local you can turn off DTC and catch SqlExceptions  Number 8501 – those will be distributed transactions that fail.

To sum up – the DTC should be required only if you run commands across multiple connections/DataContexts. For single connections/Contexts TransactionScope stays with local and efficient Sql Transactions.

Transactions without TransactionScope

LINQ to SQL also supports transactions without TransactionScope although given how easy TransactionScope is and that it doesn’t have to escalate to DTC, I see little reason to do so. If you prefer this more traditional approach though it’s available. Using manual transactions also works against a single DataContext only as you can’t transfer an active transaction to another DataContext.

The following demonstrates manually using transactions with explicit commands which works just as you would expect it to:

public override bool Delete(CodeSnippet entity)
 {
     if (entity == null)            
         entity = this.Entity;

     this.Context.Connection.Open();
     DbTransaction trans = this.Context.Connection.BeginTransaction();
     this.Context.Transaction = trans;
     
     int result = this.Context.ExecuteCommand("delete from CodeSnippets where Id = {0}",
                                      entity.Id);

     if (result < 0) // !base.Delete(entity))
     {
         this.Context.Transaction.Rollback();
         return false;
     }

     result = this.Context.ExecuteCommand("delete from comments where snippetId = {0}",
                                          entity.Id);
     if (result < 0)
     {
         this.Context.Transaction.Rollback();
         return false;
     }

     this.Context.Transaction.Commit();
     return true;

 }

This even works if you call SubmitChanges() inside of the transaction – LINQ to SQL will respect the open transaction and keep it open until you Commit or Rollback explicitly or the connection is explicitly closed.

This works across a single DataContext, but not across multiple DataContexts. Trying to assign the transaction to another context throws an exception “Transaction does not match connection”, which is not surprising.

Summary

  • SubmitChanges() implicitly uses transactions for all batch updates
  • Multiple connections/DataContext update coordination requires manual transactions

If you use LINQ to SQL explicit transactions against a single DataContext:

  • You can use TransactionScope – it’s easiest and won’t escalate to DTC within a single DataContext
  • You can also use explicit DataContext.Transaction to manually manage an ADO.NET transaction

If you need to jump transactions across DataContexts:

  • You have to use TransactionScope and DTC has to be available to use the DTC externally
Posted in LINQ  ADO.NET  

The Voices of Reason


 

Andrei Rinea
July 14, 2009

# re: LINQ to SQL and Transactions

Excellent post, I wish I could have read it 10 months ago when having a database for Membership and one for the application forced me into the use of MSDTC, which of course I didn't have in the shared hosting that I used back then.

In the end I merged the Membership database with the App database.

DotNetShoutout
July 14, 2009

# LINQ to SQL and Transactions - Rick Strahl

Thank you for submitting this cool story - Trackback from DotNetShoutout

diane wilson
July 14, 2009

# re: LINQ to SQL and Transactions

I've had a real love-hate relationship with TransactionScope, mostly because of DTC. The worst case was developing on a remote database (connected through a VPN) for an application that would eventually run on a server with a local database. I ended up using a factory to generate TransactionScopes, so I could use an appSetting in web.config to tell me whether I could really use transactions at all. Most of development was done with transactions turned off, but a flip of the appSetting would turn them on for testing when a local database was available.

I've also had trouble with nested TransactionScopes. It's supposed to work, and simple examples work, but more complex cases have been problematic. (e.g., MethodA calls MethodB, but MethodB also needs to be able to run independently of MethodA. Both need TransactionScopes.) The factory approach saved me in that case also; if a TransactionScope already existed, I didn't bother creating a nested transaction. This was not a DTC issue. Without the factory, one of two symptoms would occur; either the transaction was completed by the nested method, and the calling method couldn't use the transaction any more, or the TransactionScope was disposed by the nested method, and once again the calling method couldn't use it. According to MS documentation, neither of these situations should occur, but as I said, it only works right in simple examples.

For ASP.NET Membership, the solution is to build the membership tables in the application database, so that you can share a single connection string. use aspnet_regsql.exe to generate the membership tables.

dz
July 14, 2009

# re: LINQ to SQL and Transactions

Could you write such great article about transaction isolation levels? I mean why linq2sql by default is using readcommited, when to use spanshot isolation and so on - i mean more or less old things in current technologies background (linq, wcf)

ormnewbie
July 15, 2009

# re: LINQ to SQL and Transactions

Hi Rick,

a little bit offtopic, but how will Entity Framework v4 "encounter" this? Any change in Transactions framework (in v4 of .NET?).

Max C
July 15, 2009

# re: LINQ to SQL and Transactions

Being able to use with TransactionScope without the DTC service is not actually a recent change -- I've been doing it for some years on SQL 2005 and .NET 2.0. But looks like you can now use multiple connections whereas previously you had to be careful not to open a second one.

Rick Strahl
July 18, 2009

# re: LINQ to SQL and Transactions

@Max - yes the document referenced in the post implies that you should be able to use multiple connections within a single transaction scope as long as the connection is coming off the same connection pool. However, I tried this with the examples above with multiple data contexts and it would ALWAYS escalate to a DTC transaction.

If anybody has gotten multiple data context transactions to work without DTC escalation please leave note with how you got this to work.

#.think.in
July 18, 2009

# #.think.in infoDose #37 (12th July - 19th July)

#.think.in infoDose #37 (12th July - 19th July)

Molay Ash
July 22, 2009

# re: LINQ to SQL and Transactions

I think by using TransactionScope ,there may be some problem that we have experienced . Let me explain.
Suppose you have a function that is used for deleting record in a particular table (i.e order) and you used “Context.SubmitChanges();” in that function.
Now you have another function which is also used for deleting record (i.e product) and you also used “Context.SubmitChanges();” in that function.
The reason of using “Context.SubmitChanges();” separetely in both function is that they might be called separtely.
Now if someone try to use both function in a single transaction then as per I have found TransactionScope will not be worked perfectly. In that case it will be better (As I found) if we use “Context.Transaction” .
Here is the example for this:
public bool RejectOrder(int orderid)
        {
            Boolean isDeleted = false;
      if (this.Context.Connection.State == System.Data.ConnectionState.Closed)
            {
                this.Context.Connection.Open();
            }
            DbTransaction trans = this.Context.Connection.BeginTransaction();
            this.Context.Transaction = trans;
            try
            {
                if (DeleteOrder (orderid))
                {
                    if (DeleteProduct(orderid))
                    {
                        DeleteClient(orderid);
                        this.Delete(this.GetEntity(orderid));
                        this.SubmitChanges();
                       
                    }
                }
                trans.Commit();
                isDeleted = true;
            }
            catch
            {
                trans.Rollback();
                isDeleted = false;
            }
            finally
            {
                trans.Dispose();
                this.Context.Connection.Close();
            }
            return isDeleted;
        }
.

Note: here ‘context’ means ‘DataContext’
Pls let me know if someone has better idea in that case.

Syed Mehroz Alam
August 04, 2009

# re: LINQ to SQL and Transactions

Nice to learn that info regarding TransactionScope and DTC. Thanks, Rick.

Andrey Dudarev
January 12, 2010

# re: LINQ to SQL and Transactions

There is another way to use TransactionScope among different DataContexts without promoting to DTC - using DataContext(IDbConnection conn) constructor. If this constructor is used, DataContext uses provided connection and doesn't open/close its own.
The code might look like this:

using (TransactionScope trans = new TransactionScope())
{
using (SqlConnection conn = new SqlConnection(...))
{
conn.Open();
var _context1 = new Context1(conn);
....
context1.SubmitChanges();

var _context2 = new Context2(conn);
....
context2.SubmitChanges();

}
trans.Complete();
}

Peter
April 20, 2010

# re: LINQ to SQL and Transactions

Thanks for sharing the info.
I had a trouble with multiple submits using try catch (getting "SqlConnection does not support parallel transactions." errors).
Had to use the following

<code lang="c#">
db.Connection.Open();

using (System.Data.Common.DbTransaction trans = db.Connection.BeginTransaction())
{
db.Transaction = trans;

try
{
</ code>

Fabrizio
May 21, 2010

# re: LINQ to SQL and Transactions

@Rick, about escalation & SQL 2008,

in the same TransactionScope you can use more than one SqlConnection based on the same connection string. But you must close the first before opening the second.

This does not escalete:
using(TransactionScope ts ...)
{
    SqlConnection conn1 = new SqlConnection(connStr);
    conn1.Open();
    //... use conn1
    conn1.Close();
    
    SqlConnection conn2 = new SqlConnection(connStr);
    conn2.Open();
    //... use conn2
    conn2.Close();
    
    ts.Complete();
}


This escalate!
using(TransactionScope ts ...)
{
    SqlConnection conn1 = new SqlConnection(connStr);
    SqlConnection conn2 = new SqlConnection(connStr);
    
    conn1.Open();
    conn2.Open();   // <--- excetption here! MSTDC required!
 
    //... use conn1
    //... use conn2
 
    conn1.Close();
    conn2.Close();
    
    ts.Complete();
}

Expert Comment
August 22, 2010

# re: LINQ to SQL and Transactions

Here is a link that provide a good explanation of Transaction in LINQ to SQL

http://www.a2zmenu.com/LINQ/Maintain%20Transaction%20in%20LINQ%20to%20SQL.aspx

Lisa
December 17, 2010

# re: LINQ to SQL and Transactions

OMG, you just saved my life! I've been fighting with EF local transactions that have been escalating for no good reason all day. Enlist=False works like a charm.

Dharmesh
December 23, 2010

# re: LINQ to SQL and Transactions

Can transactionScope or transaction with multiple submitchanges() with different function call and it will work or not??

Panya
January 05, 2011

# re: LINQ to SQL and Transactions

Can anyone confirm the behavior of TransactionScope as Molay Ash had pointed out.
I'm currently using IDbTransaction but still in doubt if TransactionScope would be a better practice
otherwise I'll rather stick to the IDbTransaction's way.

yo
March 01, 2011

# re: LINQ to SQL and Transactions

hi
thanks for the excellent information
is it possible use 2 datacontexts to the same db in 1 TransactionScope without DTC?
in the article you write that you never got it to work. I'm trying it now, and it doesn't work either.
this is my code
Protected Sub btnDoesnt_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnDoesnt.Click
        Using ts As New TransactionScope
            Dim dcx = DBContext()
            Dim NewItem = New Item
            NewItem.AddedBy = -1
            NewItem.OrigDate = Now
            NewItem.OrigHand = -1
            NewItem.ProductID = 1
            NewItem.AddedOn = Now
            dcx.Items.InsertOnSubmit(NewItem)
            dcx.SubmitChanges()
            Dim dcx2 = DBContext()
            Dim edt = New Edit
            edt.AddedBy = -1
            edt.AddedOn = Now
            edt.AuditIP = "1.1.1.1"
            edt.FieldName = "f"
            edt.ObjectName = "o"
            edt.RecordID = 1
            dcx2.Edits.InsertOnSubmit(edt)
            dcx2.SubmitChanges()
            lblResult.Text = "New Item Id is :" & NewItem.ItemID
            ts.Complete()
        End Using
    End Sub
and this is causing an error that the msdtc server is not available. i need to turn it off, as in my shared hosing, there aint no DTC
Has MS fixed this already, or is there any workaround?
thanks again for your most edifying article!

Max
April 20, 2011

# re: LINQ to SQL and Transactions

"Enlist=false" FTW. If only I had found this post 3 days ago.

JuanB
October 27, 2012

# re: LINQ to SQL and Transactions

Thanks a lot! Very good explanation. I had some issues trying to insert some data using TransactionScope. At the end, I've change it to manual transactions. This post helped me a lot.

Thanks again.

Thusitha H
November 22, 2022

# re: LINQ to SQL and Transactions

One downside of TransactionScope is that we cannot assign that to a specific data context. It usually tries to scope all the contexts with all the different connection strings. This makes things escalated to DTC. DTC is not running in Azure app services, and that was a huge problem for us. We had to resort to the old Transaction way of things.


West Wind  © Rick Strahl, West Wind Technologies, 2005 - 2024