Managing SQL database connections accross many transactions

Question

Hello, we have a DAL that needs to wrap many database inserts in a single transaction that we can rollback or commit.

What is the best practice for handling that?

We are currently doing the following:

1. Create DB Connection and Transaction
2. Fill a collection with all the classes that represent an action to perform. Pass in the connection via constructor.
3. In a try/catch, loop through all the action classes and call thier Publish() method
4. Commit if successful (closes connection) or rollback if errors (closes connections).

The process can take sometime and we seem to be running out of pooled database connections. Is there a better way to manage the transactions?

This is using SQL 2008, .net 3.5 and the 4.1 version of enterprise library data access.

Answer 1

Either you've mistyped, or the problem could be because you're passing the SqlConnection to the Publish method (rather than passing the SqlTransaction).

The SqlTransaction has a connection property, which all the updates should be using.

So you want to be doing something like

// Create connection
SqlConnection connection = ObtainSqlConnection()

// Create transaction
SqlTransaction sqlTransaction = connection.BeginTransaction();

try
{    
    foreach (Action action in collectionOfActionsToPerform)
    {
        action.Publish(sqlTransaction)
    }

    sqlTransaction.Commit();
}
catch
{
    sqlTransaction.Rollback();
}

Try posting some pseudo code if this is a misunderstanding.

Answer 2

Take a look at the implementation that the web site MySpace uses with SQL Server technology.

http://www.microsoft.com/casestudies/Case_Study_Detail.aspx?CaseStudyID=4000004532

They use SQL Server Service Broker to manage database transactions across hundreds of databases.

Answer 3

Some transaction best practices are to:

• Keep transactions as short as possible
• Access as little data as possible in the transaction

In terms of Enterprise Library, the DAAB is System.Transaction aware so I would use TransactionScope. Based on what you said something like:

Database database = DatabaseFactory.CreateDatabase();

using (TransactionScope scope =
    new TransactionScope(TransactionScopeOption.RequiresNew))
{
    foreach(MyClass myClass in myClasses)
    {
         myClass.Publish(database);
    }

    scope.Complete();
}

This example does not have error handling and is assuming that an exception is thrown if the transaction needs to be rolled back.

It seems your transactions involve a large number of records and are taking quite a long time. How many records are you updating in a transaction? How long are your transactions lasting? Do all of your SQL statements need to be in one transaction or can you break them up into smaller transactions?

Have you tried to profile your SQL statements to ensure they are efficient. Also check that you are not taking out excessive locks and getting locking/blocking issues.