TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on a SINGLE 2005 database

TransactionScope vs SqlTransaction vs (BEGIN TRAN/COMMIT TRAN) on a SINGLE 2005 database

Post by sloa » Thu, 28 Aug 2008 04:35:12



Recently I got into a discussion about transactions ON A SINGLE DATABASE,
and I actually wasn't able to defend my stance, outside of "My view is that
it only makes one round trip".
And the fact I've been coding the (fairly few) transactional needs into
stored procedures for 10 years.



The issue is using:

Transactions inside the TSQL code (BEGIN TRAN/COMMIT TRAN/ROLLBACK TRAN) and
a single ".ExecuteNonQuery" call.

OR

Calling 3 different .ExecuteNonQuery and wrapping them in a
TransactionScope.

OR

Calling 3 different .ExecuteNonQuery and wrapping them in a SqlTransaction.

.

This is a high hitting database, with lots of activity. Deadlocks aren't an
issue because I feel pretty good that no one is working on the same records
( pesstimistic locking scheme on the most important entities).

But this is one Sql Server Database...and the idea of hitting one database
with several calls inside of a TransactionScope never really was a
considered option, since I could get my transaction level stuff in the
database itself.

Below I have 3 c# methods. C# Method "One" is pseudo code for using
TransactionScope and multiple calls.

C# Method "Two" is calling a stored procedure that updates 3 tables inside a
single TRAN.

C# Method "Eleven" is pseudo code for using SqlTransaction and multiple
calls.

I've also included what the 3 table usp (user stored procedure) would look
like. (uspEmployeeDepartmentJobTitleUpdate). Its very basic.
I didn't include the contents of what uspEmployeeUpdate, uspDepartmentUpdate
and uspJobTitleUpdate would look like, since it isn't too hard to imagine
them.

Any thoughts or links about which will perform better under high stress,
high concurrency, but not any prone to exist deadlock situations?

I am NOT looking for syntax corrections (unless they affect performance and
are germane to the conversation). My code below is as basic as it could
get, to avoid unnecessary clutter.

I'm after some discussion on whether or not TransactionScope could actually
perform better than using BEGIN TRAN / (3 updates) / COMMIT TRAN inside a
single stored procedure.
I guess I've just done Sql Server and single usp's so long (with BEGIN TRAN)
if never occurred to me that using ADO.NET Transactions might perform
better.

I'm looking for some hints before I go to the big step of coding up some
alternate versions.



I've googled out the wazoo, but haven't hit a really good tidbit yet.
Either I get back 1,000,000 hits, or none when I get to anal with the seach
string.

I did find this:
http://msdn.microsoft.com/en-us/magazine/cc163527.aspx
about "Lightweight Support" and is where I drew my "One" method example.

I found references to this article:
http://www.codebetter.com/blogs/sahil.malik/archive/2004/12/09/35816.aspx
But the link forwards you to a dead link. :<







Currently:

VS2005 , 2.0/3.0 Framework
Sql Server 2005 SP2



My roughest TRAN code could possible update 6 different tables (instead of
the 3 in the example above). All the tables are different.



THANKS................







private void One()

{

string connectionString = string.Empty; // A single Sql Server 2005 database

using (TransactionScope ts = new TransactionScope())

{

using (SqlConnection mainConnection = new SqlConnection(connectionString))

{

mainConnection.Open();

SqlCommand cmd1 = mainConnect