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

S

sloan

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 = mainConnection.CreateCommand();

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.CommandText = "dbo.uspEmployeeUpdate";

cmd1.ExecuteNonQuery();

SqlCommand cmd2 = mainConnection.CreateCommand();

cmd2.CommandType = CommandType.StoredProcedure;

cmd2.CommandText = "dbo.uspDepartmentUpdate";

cmd2.ExecuteNonQuery();



SqlCommand cmd3 = mainConnection.CreateCommand();

cmd3.CommandType = CommandType.StoredProcedure;

cmd3.CommandText = "dbo.uspJobTitleUpdate";

cmd3.ExecuteNonQuery();



/*

The only difference I see I could make above is to call

cmd1.ExecuteNonQuery();
cmd2.ExecuteNonQuery();
cmd3.ExecuteNonQuery();

But I don't know...like I said, I usually stick with BEGIN TRAN stuff

*/







}

ts.Complete();

}

}





private void Two()

{

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

using (SqlConnection mainConnection = new SqlConnection(connectionString))

{

mainConnection.Open();

SqlCommand cmd1 = mainConnection.CreateCommand();

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.CommandText = "dbo.uspEmployeeDepartmentJobTitleUpdate";

cmd1.ExecuteNonQuery();

}

}











private void Eleven()

{

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

using (SqlConnection connection = new SqlConnection(connectionString))

{

connection.Open();

SqlCommand cmd1 = connection.CreateCommand();

SqlTransaction transaction;

// Start a local transaction.

transaction = connection.BeginTransaction("SampleTransaction");

// Must assign both transaction object and connection

// to Command object for a pending local transaction

cmd1.Connection = connection;

cmd1.Transaction = transaction;

try

{

/*

//While here I don't have any parameters, my usp's usually do. So re-using
the same cmd1 might not be a good idea for me, since I think tearing down
and rebuilding a SqlCommand is more expensive then just creating new ones...

But I pulled the example code from
http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqltransaction(VS.80).aspx ,
so I left it.

*/

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.CommandText = "dbo.uspEmployeeUpdate";

cmd1.ExecuteNonQuery();

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.CommandText = "dbo.uspDepartmentUpdate";

cmd1.ExecuteNonQuery();

cmd1.CommandType = CommandType.StoredProcedure;

cmd1.CommandText = "dbo.uspJobTitleUpdate";

cmd1.ExecuteNonQuery();



transaction.Commit();

}

catch (Exception ex)

{

Console.WriteLine("Commit Exception Type: {0", ex.GetType());

Console.WriteLine(" Message: {0", ex.Message);

// Attempt to roll back the transaction.

try

{

transaction.Rollback();

}

catch (Exception ex2)

{

// This catch block will handle any errors that may have occurred

// on the server that would cause the rollback to fail, such as

// a closed connection.

Console.WriteLine("Rollback Exception Type: {0", ex2.GetType());

Console.WriteLine(" Message: {0", ex2.Message);

}

}

}

}







--=================================================





----------------Start TSQL CODE for dbo.uspEmployeeDepartmentJobTitleUpdate


if exists (select * from sysobjects
where id = object_id('uspEmployeeDepartmentJobTitleUpdate') and sysstat &
0xf = 4)
drop procedure uspEmployeeDepartmentJobTitleUpdate
GO





CREATE PROCEDURE dbo.uspEmployeeDepartmentJobTitleUpdate
AS


SET NOCOUNT ON



BEGIN TRAN T1

--Obviously none of these are prone to error, just example code.


Update dbo.Employee Set HireDate = GetDate()
IF (@@ERROR <> 0) GOTO ON_ERROR_T1


Update dbo.Department Set CreateDate = GetDate()
IF (@@ERROR <> 0) GOTO ON_ERROR_T1


Update dbo.JobTitle Set UpdateDate = GetDate()
IF (@@ERROR <> 0) GOTO ON_ERROR_T1



ON_SUCCESS_T1:
COMMIT TRANSACTION T1
GOTO ON_DONE


ON_ERROR_T1:
ROLLBACK TRANSACTION T1
print 'Failure, Rollback T1'

GOTO ON_DONE


ON_DONE:



SET NOCOUNT OFF


GO


GRANT EXECUTE ON dbo.uspEmployeeDepartmentJobTitleUpdate TO someUser

GO



----------------END TSQL CODE for dbo.uspEmployeeDepartmentJobTitleUpdate
 
B

bruce barker

begin 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)
 
G

G.S.

begin 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)












































- 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,904
Latest member
HealthyVisionsCBDPrice

Latest Threads

Top