Thursday, December 23, 2010

TransactionScope and SqlServer

Well, here it is, almost the end of the month, and as usual I’m behind in my blog posting. It seems that I’m lucky to get one post a month this past year. But this month, being almost the end of December, I should at least wish all my readers “Happy Holidays”.

Now, on to today’s topic. This may be old news; after all, TransactionScope has been around since 2.0, but I hadn’t been using it back then. I’ve just really started utilizing it during the past year and recently discovered some interesting gotchas.

First, let’s start with a little snippet of code:

using (TransactionScope scope = new TransactionScope())
{
try
{
// transactional stuff in here, perhaps calls to SQL Server

...

scope.Complete();
}
catch (Exeception ex)
{
// do whatever you need to here, we will NOT complete the transaction
Console.WriteLine("*** ERROR " + ex.Message);
}
}

Now, this is all well and good … or so you think. But, there is one little gotcha here …. and that is that when TransactionScope is instantiated without any parameters, the default for its IsolationLevel is Serialized … the most restrictive that there is. On the other hand, for Transactions in SQL Server, the default IsolationLevel is ReadCommitted and, for the most part, that is what you typically will want to use.

In my case, I’m doing a lot of writing to the database, in lots of different threads. If I don’t mess with the TransactionScope, then IsolationLevel.Serialized will severely slow things down, because the database tables, in effect, get locked … because Serializable prevents new data from being added. Wow, not really what I intended!

Well, OK, so I guess we can change the above snippet to this:

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted}))
{
try
{
// transactional stuff in here, perhaps calls to SQL Server

...

scope.Complete();
}
catch (Exeception ex)
{
// do whatever you need to here, we will NOT complete the transaction
Console.WriteLine("*** ERROR " + ex.Message);
}
}

Wow, that’s messy, but it’s necessary.

But wait, there’s more!!  If your current transaction is operating under another ambient transaction (“ambient” is like a parent transaction … I don’t know why they didn’t use the same terminology), the IsolationLevel has to be the same! An exception is thrown if it’s not! So, you’ve got to remember to use this same syntax every time you instantiate a TransactionScope! Yuck!

But, fortunately, there’s a better idea. It’s much easier and cleaner to use a Utility class with static methods for this sort of thing:

public class Utils
{
/// <summary>
/// The reason for this method to return a TransactionScope is because the default IsolationLevel
/// when not specified is Serializable, which is the most restrictive level and will cause all kinds
/// of deadlock problems with Sql Server!!
///
/// We can add more overloads later if we want more options.
/// The TransactionScoope that gets returned here is Required and IsolationLevel.ReadCommitted.
/// </summary>
/// <returns></returns>
public static TransactionScope GetTransactionScope()
{
return new TransactionScope(TransactionScopeOption.Required, new TransactionOptions(){IsolationLevel = IsolationLevel.ReadCommitted});
}
}

And now, we make sure that all our developers know to always use this method when creating a TransactionScope, like this:

using (TransactionScope scope = Utils.GetTransactionScope())
{
try
{
// transactional stuff in here, perhaps calls to SQL Server

...

scope.Complete();
}
catch (Exeception ex)
{
// do whatever you need to here, we will NOT complete the transaction
Console.WriteLine("*** ERROR " + ex.Message);
}
}

Happy Coding and Happy Holidays!

11 comments:

  1. Hi Bonnie,

    We need your help~
    Please see this:
    http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/42f9d376-07e1-426c-aa57-a780d8307a9d

    Thank you!

    ReplyDelete
  2. Hi Bonnie,

    This seems very useful, but I have this question: I assign a Transaction object to every DataAdapter I use in order to update a dataset (see code). If I insert your code, should I still use the SqlTransaction object in the adapters?
    Thanks in advance.
    Marco

    public int UpdateSales(dsetSales ds)
    {
    Int32 _newId = 0;
    SqlDataAdapter da = null;
    SqlDataAdapter daDetail = null;
    SqlTransaction tran = null;
    try
    {
    conn.Open();
    tran = conn.BeginTransaction();
    // preparing adapters, see methods below
    da = getDA2updateSales(conn, tran);
    daDetail = getDA2updateSalesDetail(conn, tran);
    // prepare deleted, changed o added tables
    DataTable DeletedDetail = ds.SalesDetail.GetChanges(DataRowState.Deleted);
    DataTable AddedDetail = ds.SalesDetail.GetChanges(DataRowState.Added);
    DataTable ModifiedDetail = ds.SalesDetail.GetChanges(DataRowState.Modified);
    // delete rows
    if ((DeletedDetalle != null))
    daDetail.Update(DeletedDetail);
    // main row
    da.Update(ds, "Sales");

    // get new ID
    r = (dsetSales.SalesRow)ds.Sales.Rows[0];
    _newId = r.IdSale;
    // updates detail rows
    if ((ModifiedDetail != null))
    daDetail.Update(ModifiedDetail);
    // add new rows
    if ((AddedDetail != null))
    {
    foreach (dsetSales.SalesDetailRow d in AddedDetail.Rows)
    {
    d.IdSale = _newId;
    }
    daDetail.Update(AddedDetail);
    }
    tran.Commit();
    ds.AcceptChanges();
    }
    catch (Exception ex)
    {
    _newId = 0;
    if (tran != null)
    tran.Rollback();
    }
    finally
    {
    if (conn.State != ConnectionState.Closed)
    conn.Close();
    }
    tran.Dispose();
    return _newId;
    }

    ReplyDelete
  3. private SqlDataAdapter getDA2updateSales(SqlConnection conn, SqlTransaction objTran)
    {
    SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Sales WHERE IdSale = @IdSale ", conn);
    da.DeleteCommand = new SqlCommand("spd_Sales", conn);
    da.InsertCommand = new SqlCommand("spi_Sales", conn);
    da.UpdateCommand = new SqlCommand("spu_Sales", conn);

    da.SelectCommand.CommandType = CommandType.Text;
    da.SelectCommand.Parameters.Add("@IdSale", SqlDbType.Int, 0, "IdSale");
    da.InsertCommand.CommandType = CommandType.StoredProcedure;
    da.InsertCommand.Parameters.Add("@IdSale", SqlDbType.Int, 0, "IdSale");
    da.InsertCommand.Parameters.Add("@IdCust", SqlDbType.Int, 0, "IdCust");
    ...
    da.InsertCommand.Parameters.Add("@new_id", SqlDbType.Int, 0, "IdSale").Direction = ParameterDirection.Output;
    //
    da.InsertCommand.Transaction = objTran;
    //
    da.UpdateCommand.CommandType = CommandType.StoredProcedure;
    da.UpdateCommand.Parameters.Add("@IdSale", SqlDbType.Int, 0, "IdSale");
    da.UpdateCommand.Parameters.Add("@IdCust", SqlDbType.Int, 0, "IdCust");
    ...
    da.UpdateCommand.Transaction = objTran;

    da.DeleteCommand.CommandType = CommandType.StoredProcedure;
    da.DeleteCommand.Parameters.Add("@IdSale", SqlDbType.Int, 0, "IdSale");
    da.DeleteCommand.Transaction = objTran;
    da.InsertCommand.UpdatedRowSource = UpdateRowSource.Both;
    return da;
    }

    ReplyDelete
    Replies
    1. Hi Marco,

      The TransactionScope totally takes the place of SqlTransactions. So remove any SqlTransaction stuff in your code, use TransactionScope and the scope.Complete() takes the place of the tran.Commit() ... no Rollback is needed. So the code in your try/catch would look like this:

      using (TransactionScope scope = Utils.GetTransactionScope())
      {
      try
      {
      conn.Open();

      // preparing adapters, see methods below
      da = getDA2updateSales(conn, tran);
      daDetail = getDA2updateSalesDetail(conn, tran);
      // prepare deleted, changed o added tables
      DataTable DeletedDetail = ds.SalesDetail.GetChanges(DataRowState.Deleted);
      DataTable AddedDetail = ds.SalesDetail.GetChanges(DataRowState.Added);
      DataTable ModifiedDetail = ds.SalesDetail.GetChanges(DataRowState.Modified);
      // delete rows
      if ((DeletedDetalle != null))
      daDetail.Update(DeletedDetail);
      // main row
      da.Update(ds, "Sales");

      // get new ID
      r = (dsetSales.SalesRow)ds.Sales.Rows[0];
      _newId = r.IdSale;
      // updates detail rows
      if ((ModifiedDetail != null))
      daDetail.Update(ModifiedDetail);
      // add new rows
      if ((AddedDetail != null))
      {
      foreach (dsetSales.SalesDetailRow d in AddedDetail.Rows)
      {
      d.IdSale = _newId;
      }
      daDetail.Update(AddedDetail);
      }

      scope.Complete();
      ds.AcceptChanges();
      }
      catch (Exception ex)
      {
      _newId = 0;
      }
      finally
      {
      if (conn.State != ConnectionState.Closed)
      conn.Close();
      }
      return _newId;
      }

      Delete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Hey! Thank you very much Bonnie!
    So, I guess I should remove the parameter tran when creating the DataAdapters (in daDetail = getDA2updateSalesDetail(conn, tran);) because it is no longer needed.
    Now, what if this transaction becomes deadlocked, how can I relaunch it? (It can happen once in a few thousands transactions)
    Greetings!

    ReplyDelete
    Replies
    1. You're welcome, Marco! Yeah, sorry, I missed that you were using the tran parameter when you created that second DataAdapter. You're right, remove it, you don't need it.

      As far as deadlocked transactions, I'm not sure what will happen ... I've never run into that situation. How are you handling the deadlocks now?

      Delete
    2. * ashamed * I don't… I currently have near 300,000 transactions and a few have failed because deadlocking, so I'm concerned about it. The most suitable reason is that immediately after saving the sale, the front end needs to read and send it to the printer, so now I will try inserting a SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; sentence into the stored procedure that reads the data after saving.

      Delete
    3. You can set the ISOLATION LEVEL to READ UNCOMMITTED in the TransactionScope too, as you can see from my blog post ... if you are reading this data in the same transaction, simply nest your TransactionScopes and use the IsolationLevel.ReadUncommitted in the inner transaction (the one where you're reading the data). Create a new method Utils.GetTransactionScopeUncommited() to do this.

      Or yes, I suppose you could put it into the Stored Procedure, but I think it's better to do that in the nested TransactionScope.

      Delete
    4. Well, I've included your into my app, except for this final advice because this process has already a nested transaction so it is becoming a little complicated, but everything goes fine. Let's put it on production and we'll see. More news later..

      Thanks!

      Delete
    5. araña --- did you get it working ok?

      Delete