TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on

TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on

Post by YnJ1Y2UgYm » Thu, 28 Aug 2008 05:27:03


egin tran / commit tran only works for the current connection, so if you
execute all your sql stamements on the same connection it works fine.

using tranaction scope allow mutilple connections to partake in the same
transaction scope. beside handling the commit, they will not lock each other.


-- bruce (sqlwork.com)


"sloan" wrote:

 
 
 

TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on

Post by G.S. » Thu, 28 Aug 2008 06:15:59

n Aug 26, 4:27m, bruce barker
< XXXX@XXXXX.COM > wrote:
>> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> > - Show quoted text -

Two things:
1. To comment on bruce's comment - I know it's a remote possibility
but it's happend on my projects - OLEDB connections using MSDAtaShape
provider don't seem to be able to enlist on-going transactions.

2. As far as performance and scalability - I guess keeping it all in
SQL server (or keeping it a light-weight-transaction using .NET
System.Transaction) is going to perform better that escalating to
Distributed Transaction on multiple connections (besides the extra
trips app tier - DB tier, you're now competing for DB connections.)
My thought process is that app design will dictate which way you go -
sometimes it's against the overall design to try to kepp everything in
SPROCs, but if the design allows it - I would go for it.