One of the new features in System.Transactions is the ability for the transaction manager to delegate directly to a resource manager (known as PSPE for Promotable Single Phase Enlistment). Currently, this is only supported by SQL 2005. That, then, raises the question about SQL 2000 -- does it promote directly to MSDTC, and is there anything that can be done about it?
It is the case that SQL 2000 will cause a promotion to MSDTC. This is because the SQL 2000 ADO.Net support does not provide a IPromotableSinglePhaseEnlistment instance, and the SQL 2000 resource manager is located in the SQL server process. Distribution to a second process is one of the actions that causes promotion.
However, for topologies that only involve a single database connection, there is a fairly simple mechanism that can be built that provides a PSPE-like shim for databases such as SQL 2000. This sort of shim would provide a PSPE interface to System.Transactions, and directly manage a SQL-local transaction behind the scenes. Its main limitation would be that it would for a rollback if the transaction ever needed to be promoted to MSDTC.
As an example, John Doty from our team has built such a set of classes. These are available for download at http://download.microsoft.com/download/B/D/0/BD0D4D33-89DC-497E-B3F2-95871A03A5F7/PrivateTransactionAdapter.msi. They are pretty heavily commented and provide both a SQL shim and a messaging shim (for use with a transactional queue resource, such as MSMQ). Note that for SQL 2000, you need to make sure that your connection string has the phrase to prevent global auto-enlistment.
To use this, you simply:
using (TransactionScope scope = new TransactionScope ())
{
...
SqlConnection conn = ...
DatabaseTransactionAdapter adap = new DatabaseTransactionAdapter (conn);
adap.Begin (); // This creates the PSPE enlistment and the underlying
// private database transaction
...
scope.Complete ();
}
Take a look at these classes, and let me know what you think.
UPDATE: The msi file above (http://download.microsoft.com/download/B/D/0/BD0D4D33-89DC-497E-B3F2-95871A03A5F7/PrivateTransactionAdapter.msi) has been updated with a couple of bugfixes.
Posted
Sep 13 2005, 07:58 AM
by
jim-johnson