Home » .Net Framework

Is there a pattern that makes native tsql begin tran/commit work with TransactionScope

The native tsql transaction management: begin tran/commit/rollback is famaliar to db programmers, and has a smaller system overhead, while the .net transactionscope is more flexible and makes it easier to compose (automatically) multiple operations into one transaction at the client side programming level. However, it seems that the two do not work together as stated by MSDN:

"When using System.Transactions, applications should not directly utilize transactional programming interfaces on resource managers—for example the T-SQL BEGIN TRANSACTION or COMMIT TRANSACTION verbs, or the MessageQueueTransaction() object in System.Messaging namespace, when dealing with MSMQ. Those mechanisms would bypass the distributed transaction management handled by System.Transactions, and combining the use of System.Transactions with these resource manager "internal" transactions will lead to inconsistent results. As a rule, use System.Transactions in the general case, and use resource manager internal transactions only in specific cases where you are certain the transaction will not span multiple resources, and will not be composed into a larger transaction. Never mix the two."

But I suspect that there may be some coding patterns that makes the two work together.  For example, a pattern like the following,

CreatePROCEDURE Test   
set nocount on
set xact_abort on
begin tran    
    do something1  
    do something2  
    if someflag  
        raiserror(N'some message.', 16, 1)  
    do something3  

I guess might work with transactionscope.

Could someone from Microsoft shed some light to this? Thanks.

SourceCompanion, collabration tool build on vss

5 Answers Found


Answer 1

To use TransactionScope, the resource managers must understand the System.Transaction's interface and how to enlist in the ambient transaction. 
If you are doing stand alone updates to database, then you don't need TransactionScope.  I doubt native tsql could be directly used with TransactionScope unless you build some intermediate bridge layer.

System.Data with SQL server works together well and Sys.Data understands the ambient transaction. System.Transaction can also be escalated to perform Distributed Transaction using MSDTC service.


Answer 2

Hi, thanks. 

Here is what I think,
We know that, for the transaction manager, if transactions are nested, then only the outmost commit counts, and a rollback at any level also counts. With this in mind, now suppose a DAL routine that calls a stored procedure with tsql transaction in it is composed into a larger scope by including the DAL routing in a TransactionScope,  the transcation commands issued by System.Transaction will be like an outer transaction, while the tsql transaction commands by the stored  procedure will be the inner transaction.  I used the word "like", since I don't know the inner working of these stuff, it may be quite complicated, and may not work like this simple reasonning. 

Now consider a case, suppose that a DAL routine with its sole action being calling a stored procedure having the pattern given by my initial post, is composed into a TransactionScope.  I consider there can be one possible inconsistence, i.e.,  the inner tsql transaction says commit, but the outer one  managed by System.Transaction says rollback, however the inner one may not get rolled back as dictated by the outer one.  My question is,  could this happen???

As you can see in my pattern, if the tsql want to rollback, it raiserror and causes an exception at the client side, which in turn will be caught and used to rollback the System.Transaction, so there will never another inconsistence, i.e. the inner one rollback, while the outter one commits.


Answer 3

If SQL produces an exception, no transaction will ever commit. If it appears so in your client script, your client script login is wrong.

Answer 4


Am I right to conclude from what you're saying that you've made it so it's simply not possible to write a stored procedure in such a way that it's known to be correct (which often requires using transactions) AND also possible to use as part of a distributed transaction from a client using MSDTC, such as ADO.NET TransactionScope does?

Of course with databases one usually knows what the clients are going to be (and often there's perhaps only one), but if it really is so, this seems like a major shortcoming in the architecture.


Answer 5

I knew the paragraph you(TX Guru) cited, but it is a limitation that I dislike.

I am just seeking to find a way to get around of this.

Tbe need comes from this, when people write stored procedures, they often can not foresee in what context the stored procedures will be called, and it is desirable for the stored procedure to be usable both with a transactionscope environment or without a transactionscope(i.e. standalone). If used standalone, then the sql "begin transaction/commit/rollback" construct is needed, however, if a stored proc with such a construct is used in a transactionscope environment, it might cause inconsistence as stated in your citation.

I think there are two lines to achieve this. One is outlined in my initial post, but I am not sure if it's correct.

The other way, perhaps is to detect in stored proc if it's already in a transaction, by using soming like @@trancount, and if the function return 0, then we know we are not in a transaction. But again this approach not tested,  the question is whether @@trancount return 1 if the transaction is started by a transactionscope( System.Transaction), other than by "begin tran".



<< Previous      Next >>

Microsoft   |   Windows   |   Visual Studio   |   Sharepoint   |   Azure