\nThere'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.\n<\/p>\n
\nThe 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.\n<\/p>\n
\nThe 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.\n<\/p>\n
\nAfter 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.\n<\/p>\n
\nIt 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.\n<\/p>\n
\nTo "knit" two lock spaces togther you'd need something fairly drastic, a la sp_getbindtoken and sp_bindsession. And they're not doing that.\n<\/p>\n
\nThe 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:\n<\/p>\n
\nvoid DoTransfer(int accta, int acctb, double amt)
\n{
\n DoWithdrawal(accta, amt);
\n DoDeposit(acctb, amt);
\n}\n<\/p>\n
\nBoth 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.\n<\/p>\n
\nTransaction is scoped to the connection (modulo sp_bindsession).<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12,28],"tags":[],"class_list":["post-966","post","type-post","status-publish","format-standard","hentry","category-data-access","category-sql-server-2005"],"yoast_head":"\n