.NET TransactionScopes and SQL 2005 Lightweight Transaction Manager - Multiple Connections same SPID?

Question

Can someone shed light on what is happening behind the scenes with the SQL Lightweight transaction manager when multiple connections are opened to the same DB, using the Microsoft Data Access Application Block (DAAB)?

With the below code, we verified that MSDTC is indeed not required when opening 'multiple connections' to the same database.

This was the first scenario I tested: (where Txn1 and Txn2 use EntLib 4.1 to open a connection to the same DB and call different SPROCS)

using (var ts = new TransactionScope(TransactionScopeOption.Required))
{
    DAL1.Txn1();
    DAL2.Txn2();
    ts.Complete();
}

Tracing this from profiler revealed that the same connection SPID was used for Txn1 and Txn2. After Txn1() was called, the Sql SPID would have been released back into the pool and Txn2() was able to re-use it.

However, when repeating this experiment and this time holding the connections open:

using (var ts = new TransactionScope(TransactionScopeOption.Required))
{
    Database db1 = DatabaseFactory.CreateDatabase("db1");
    DAL1.Txn1OnCon(db1);
    Database db2 = DatabaseFactory.CreateDatabase("db1");
    DAL2.Txn2OnCon(db2);
    ts.Complete();
}

Viewing this from Profiler indicated that the 2 transactions were STILL using the same SPID. I was expecting the TransactionScope to have escalated to DTC as a distributed transaction should be required to control 2 concurrent connections. What have I missed?

score 1 · Accepted Answer · answered Sep 07 '10 at 16:06

Quoting from MSDN http://msdn.microsoft.com/en-us/library/8xx3tyca(VS.80).aspx

Connection pooling reduces the number of times that new connections need to be opened. The pooler maintains ownership of the physical connection. It manages connections by keeping alive a set of active connections for each given connection configuration. Whenever a user calls Open on a connection, the pooler looks to see if there is an available connection in the pool. If a pooled connection is available, it returns it to the caller instead of opening a new connection. When the application calls Close on the connection, the pooler returns it to the pooled set of active connections instead of actually closing it. Once the connection is returned to the pool, it is ready to be reused on the next Open call.

Just because a connection was used in a transaction doesn't mean it cannot be available for the next call. I found that If the connection string varied by the slightest thing, such as capitalization of a hostname, then you'd get a new physical connection to the db.

Thanks Dan - this would mean that there isn't a 1:1 correlation between the EntLib DAAB Database and a physical connection. — StuartLC, Sep 08 '10 at 06:40

score 1 · Answer 2 · answered Sep 08 '10 at 09:23

1

Sql 2005 or Sql 2008?
If you use sql 2008, a sequence of open+close connections are not escalated to a distributed transaction. But all the connection must use exactly the same connection string.

(pseudo-code)

string connstring = "...."
using (TransactionScope ts=...)
{
  c1 = new connection(connstring );
  c1.open
  ...use c1
  c1.close

  c2 = new connection(connstring );
  c2.open
  ...use c2
  c2.close

  ts.complete()
}

The same code with sql2005 escalates to distributed transaction --> yuo need MSDTC

answered Sep 08 '10 at 09:23

Fabrizio Accatino

2,284
20
24

Thanks Fabrizio - my post below - I had assumed that by keeping my DAAB Database in scope that the connection would be held in scope as well, but this was not the case. But you are right - the same conn will be reassigned if it is closed and reopened. – StuartLC Sep 08 '10 at 11:23

StuartLC · Answer 3 · 2014-12-09T04:27:49.653

OK, my misunderstanding was with DAAB. The DAAB Database opens and closes connections as needed (or obtains / releases them from the pool), i.e. connections aren't held for the lifespan of the DAAB Database object.

It is possible to manually control the database connections in DAAB as per below - by holding the actual connections open, they cannot be reused. This then requires MSDTC to be running as soon as 2 physical connections are open, as I had expected in the original question.

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
    using (DbConnection dbConn1 = DatabaseFactory.CreateDatabase("myDb").CreateConnection())
    using (DbConnection dbConn2 = DatabaseFactory.CreateDatabase("myDb").CreateConnection())
    {
        dbConn1.Open();
        DAL1.Txn1OnCon(dbConn1);
        dbConn2.Open();
        DAL2.Txn2OnCon(dbConn2);
        DAL1.Txn1OnCon(dbConn1);
        ts.Complete();
    }
}

.NET TransactionScopes and SQL 2005 Lightweight Transaction Manager - Multiple Connections same SPID?

3 Answers3

Linked