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