When you are writing a resource manager, one of the decisions you need to make is – How will this RM enlist?

(How to write a Resource Manager? Please read this article I wrote back when I was a young little kid).

So how does an RM enlist?

a)      Volatile: An RM may choose to enlist in a volatile fashion, if the resources it manages are volatile in nature. A volatile enlistment is managed by LTM, and RMs working with LTM, due to the nature of the transaction have the luxury of working under lower isolation levels.

b)      Durable: An RM needs to choose to enlist using Durable enlistment, if the resources it manages are durable in nature. Such RMs need to provide a recovery contract to the DTC, and such RMs usually have to work under a higher isolation level. A good example would be transactional file copy, or anything that deals with the file system (there’s a lot more to transactional file system than this hyper-over-simplified picture).

c)       PSPE : Now, by that definition, SQL Server would need to enlist as durable. But what if there was only one single SQL Server involved in the transaction – it could technically manage its own transactions. Why should it have to pay a higher price for a DTC?.. well .. it shouldn’t, and which is why, SqlConnection connected to SQL Server 2005 will exhibit PSPE. Promotable Single Phase Enlistment (PSPE), means “lets start as volatile, and bump up – promote – to Durable, as situations change). The promotion will cause a higher isolation level, so the transaction isolation level changes half way thru the tx.

Practical Tip: It may be worth mentioning that only SQL2k5 will exhibit PSPE, a SqlConnection connected with SQL2k, will always enlist durable. Also, a TransactionScope in SQLCLR will always cause SQLConnection to promote. Thus, when in SQLCLR, and using Context Connections – stay away from TransactionScope. (It is still okay to use System.Transactions.Current for rollback operations). But if you were not using a context connection, the transaction would have promoted anyway, so in those situations it is okay to use TransactionScope.

So the transaction changes - half way thru.. hmm .. what causes it to promote? As it turns out, there are well laid out rules for Promotion -

a) A durable resource that doesn't support single-phase notifications is enlisted in the transaction.
b) Two durable resources that support single-phase notification enlist in the same tx.
c) The TC receives a request to marshal a tx to a different appdomain or process.