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

Discussion in 'ASP .Net' started by sloan, Aug 26, 2008.

  1. sloan

    sloan Guest

    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
    sloan, Aug 26, 2008
    #1
    1. Advertising

  2. sloan

    bruce barker Guest

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

    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)


    "sloan" wrote:

    >
    >
    > 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();
    >
    > }
    >
    bruce barker, Aug 26, 2008
    #2
    1. Advertising

  3. sloan

    G.S. Guest

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

    On Aug 26, 4:27 pm, bruce barker
    <> wrote:
    > 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)
    >
    >
    >
    > "sloan" wrote:
    >
    > > 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....
    > > 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.sqltran...,
    > > 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();

    >
    > > }- Hide quoted text -

    >
    > - 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.
    G.S., Aug 26, 2008
    #3
    1. Advertising

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. .Net Newbie
    Replies:
    2
    Views:
    694
    =?Utf-8?B?RGVlcA==?=
    Jul 9, 2004
  2. =?ISO-8859-1?Q?Thomas_Gagn=E9?=

    Connection auto commit - No begin?

    =?ISO-8859-1?Q?Thomas_Gagn=E9?=, Nov 19, 2003, in forum: Java
    Replies:
    1
    Views:
    2,794
    John C. Bollinger
    Nov 19, 2003
  3. Replies:
    0
    Views:
    386
  4. =?Utf-8?B?QWppdA==?=

    On TransactionScope and Table Adapter

    =?Utf-8?B?QWppdA==?=, Nov 8, 2007, in forum: ASP .Net
    Replies:
    1
    Views:
    779
    bruce barker
    Nov 8, 2007
  5. GaryDean

    using Transactionscope with 2 dbs

    GaryDean, Apr 17, 2008, in forum: ASP .Net
    Replies:
    2
    Views:
    362
    Steven Cheng [MSFT]
    Apr 17, 2008
Loading...

Share This Page