There's been a lot of interest in the new System.Transactions.dll assembly lately. Especially from users of SQL Server 2005. This is based around two functionality points.
The first point of interest is that you will use System.Transactions to use transactions in SQLCLR procedural code in SQL Server 2005. In the beta 2 implementation of the SqlServer data provider, transactional coding had to use two different code paths based on whether a transaction was already started before your procedure was called. There was a section in the "First Look at SQL Server 2005 for Developers" book on this, transaction handling seemed rather complex. Using System.Transactions will make this simpler and more elegant.
The second point is that SQL Server 2005 has a feature known as promotable transactions. When you use a single connection to SQL Server 2005 and a System.Transactions TransactionScope, a local transaction is started. If SQL Server 2000 is used, or more than one database connection is used, the same TransactionScope starts a distributed transaction. Which is a few times slower than a local transaction.
After starting a local transaction with SQL Server 2005, another connection is opened in the same TransactionScope, the original local transaction is promoted to a distributed transaction, because now a distributed transaction is needed. Hence the name promotable transactions.
It is important to remember, however, that the transaction is still scoped to the *connection*. The usual cool TransactionScope demo shows a local transaction on SQL Server 2005 instance #1 being promoted to distributed when you open a second connection to a *different* database instance. It will be also be promoted if you open a second SqlConnection to *the same instance*. Each connection has a different transaction space (lock space), even if you are using promotable transactions. Therefore, you need a distributed transaction with two connections to the same database. Even if the connection string and other environment is exactly the same.
To "knit" two lock spaces togther you'd need something fairly drastic, a la sp_getbindtoken and sp_bindsession. And they're not doing that.
The reason why this is puzzling (I was recently reminded by a student from a recent class) is that, in MTS/COM+ you could flow transactions by composing method calls, like this:
void DoTransfer(int accta, int acctb, double amt)
{
DoWithdrawal(accta, amt);
DoDeposit(acctb, amt);
}
Both DoWithdrawal and DoDeposit would open a connection in MTS/COM+. System.Transactions has some COM+-like transaction composition properties. But if both DoWithdrawal and DoDeposit each open a separate SqlConnection with enlist=true in the connection string (its the default), promotable transactions won't help, they'll be running a *distributed* transaction. If you really want promotable to mean: multiple operations, one database == local transaction, you'll have to pass the SqlConnection object around too. This makes things complex, because SqlConnections aren't "agile". They don't pass from process to process, for example.
Transaction is scoped to the connection (modulo sp_bindsession).
2 thoughts on “System.Transactions, promotable transactions, and composition”
Hello!
Great article on System.Transactions.
I have a question on the TransactionScope and smart transactions. However, before I get to that, here’s a quick overview of our scenario:
Lets assume that we have a hierarchical tree with objects marked for persistence (i.e. should be committed to a SQL Server backend). The tree is residing within a transaction manager.
Requirements:
1. Each type of object should join a transaction in order to maintain data consistency.
2. Each type of object (i.e. User, Group ..) is associated with a manager, resposible for persisting that particular type of object and talking with the data access layer.
Question:
Are nested persistence calls from managers (within a created outer TransactionScope) automatically joining the current outer TransactionScope?
I am currently creating an outer SqlTransaction and passing that to each manager, so that they can join the transaction. This works quite well, but I would like to optimize this and provide a cleaner solution.
Following is an extremely simplified model – illustrating my question on nested persistence calls.
// A CmsTransactionManager
public void Commit()
{
using (TransactionScope scope = new TransactionScope())
{
PersistUser
}
}
// This method should illustrate any kind of manager
public void Manager(Object o)
{
// persist object to SQL Server backend
}
Please let us know about this – it’s really interesting and something we would like to look out for.
Thanks in advance!
Hi Anders,
Sorry it took me so long to get back on this one. The Feb CTP build contains a version of System.Transactions.dll that gives you much more control over how you can flow the transaction. If I understand your question correctly, I think that the COM+ Interop option (3rd parm on TransactionScope constructor) and the use of a specific Transaction object in one of the constructors of TransactionScope will do what you want…and maybe more.
Cheers,
Bob
Comments are closed.