Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 512 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 527 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 534 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-settings.php on line 570 Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1199 Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1244 Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1391 Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/classes.php on line 1442 Strict Standards: Redefining already defined constructor for class wpdb in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/wp-db.php on line 306 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 103 Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/cache.php on line 431 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/query.php on line 61 Deprecated: Assigning the return value of new by reference is deprecated in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/theme.php on line 1109 Strict Standards: Declaration of Walker_Comment::start_lvl() should be compatible with Walker::start_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_lvl() should be compatible with Walker::end_lvl(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::start_el() should be compatible with Walker::start_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Declaration of Walker_Comment::end_el() should be compatible with Walker::end_el(&$output) in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/comment-template.php on line 1266 Strict Standards: Redefining already defined constructor for class WP_Dependencies in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/class.wp-dependencies.php on line 31 Strict Standards: Redefining already defined constructor for class WP_Http in /customers/d/d/5/lincoln.se/httpd.www/sql-tidbits/wp-includes/http.php on line 61 SQL Tidbits » Blog Archive » How to keep a transaction from being escalated to the DTC

How to keep a transaction from being escalated to the DTC

When working on a project at a client, I some problems with a few functions in my code that wouldn’t work when we uploaded the code to our test environment. It turned out that the functions all contained transactions which were escalated to use the DTC (Distributed Transaction Coordinator) instead of plain old transactions. This made me quite confused, because all connections and transactions were against the same database, which should keep them out of the DTC. Anyways, to fix the problem in the short term, we started the DTC in the test environment and everything worked again. But I decided to take a closer look at the problem.

First, our setup. We wrote c# code in .Net 3.0, using a SQL Server 2005 Standard Edition database. Some actions in our business layer needed transactions, so we used TransactionScopes, developed just for this scenario. As we were using SQL Server 2005, which supports light-weight transactions (2000 does not), we shouldn’t ever need the DTC since we were using only one data source. But apparently, this wasn’t the case. So what was happening?

Here’s a snippet showing our situation:


using (TransactionScope ts = new TransactionScope())
{
    // First database connection, might happen in Business Logic -> Data Access Layer or anywhere
    using (SqlConnection conn = new SqlConnection( ... ))
    {
        // ...
    }

    // Second database connection, might happen in Business Logic -> Data Access Layer or anywhere. This is escalated to DTC. But why?
    using (SqlConnection conn = new SqlConnection( ... ))
    {
        // ...
    }

    ts.Complete();
}

When running this code in debug mode, reaching the second using-scope triggered an escalation to the DTC. But why? Apparently, we have two connections running at the same time, but that seems weird, since we’ve already left the using-scope so that connection should be closed in the dispose()-part of the using-scope.

It’s obvious when you think about it, really. When leaving the first using-scope, the connection should be closed. But that doesn’t happen, since we are in a transaction. The transaction scope needs to keep the connection open when we either commit or rollback. So when we reach the second using-scope, a second connection is opened and we are escalated to the DTC.

So, what can we do about this? There are at least two solutions:

If your are using SQL Server 2008 and Visual Studio 2008, this is implemented from the beginning and you don’t need any wrappers or overloads.

Recommended reading:

Posted on December 21st, 2008 by Jonas
» Feed to this thread
» Trackback

3 Comments a “How to keep a transaction from being escalated to the DTC”

  1. Verona says:

    This article is very interesting but it took me a long time to find it in google.
    I found it on 16 spot, you should focus on quality backlinks building,
    it will help you to rank to google top 10. And i know how to help
    you, just search in google - k2 seo tips and tricks

  2. Carlos says:

    I read a lot of interesting content here. Probably you spend a lot of time writing,
    i know how to save you a lot of time, there is an online tool that creates unique, google friendly articles in seconds,
    just search in google - laranitas free content source

  3. Reginald says:

    I read a lot of interesting posts here. Probably you
    spend a lot of time writing, i know how to save you a lot of work, there is an online tool that creates high quality, SEO friendly posts in seconds, just search in google - laranitas free content source

Leave a Reply