none
Transaction count mismatch if Stored Procedure runs ROLLBACK TRANSACTION RRS feed

  • Question

  • I'm using an ADO.NET SqlTransaction object to span three SQL Server 2005 Stored Procedures.

    One of those Stored Procedures starts a transaction and either commits or rolls back this transaction at completion.

    If this stored procedure calls RAISERROR() and rolls back the transaction, a SqlException object is thrown, containing the original RAISERROR() message plus the following text: "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0."

    AFAIK, this additional text is wrong:

    1. The SqlTransaction object raised the transaction count to 1.
    2. The BEGIN TRANSACTION statement in the Stored Procedure itself raised the transaction count to 2.
    3. The ROLLBACK TRANSACTION statement in the Stored Procedure itself decreased the transaction count to 0.

    This is all perfectly alright and according to the rules.

    The question is: Is this an ADO.NET bug or is this a SQL Server bug?

    I'd like to find the appropriate channel for Connect.


    Vote here for a Microsoft Connect feedback channel on Windows - and win a better Windows!


    • Edited by BetterToday Tuesday, October 25, 2011 4:58 PM
    Tuesday, October 25, 2011 4:57 PM

Answers

  • This error is raised by SQL Server and not the ADO.NET driver.  The ADO.NET driver knows nothing about @@TRANCOUNT.   When you begin a transaction using ADO.NET driver, this sends a transaction manager request to SQL Server (TDS TM TM_BEGIN_XACT request).   Server then responds to client with the transaction ID (local transaction id which is session scoped).   The transaction id is all ADO.NET knows about.  When SQL decides to roll back the transaction it will send a message to client with TM_ROLLBACK_XACT.  Anyway, you probably don't care about these details. :)

    What I found from digging in server source code is this will be raised IF you are in implicit transaction mode (which when you start a txn from client you enter this mode) AND you finish execution of a sproc and you are not at base level (you are in some nested level for example in a stored proc) and starting transaction count does not match ending transaction count then this error is raised (I think, I am not super familiar with this code).

    Hence there appears to be a rule here that SQL enforces ->

    You can reproduce like so:

    if exists(select 1 from sys.procedures where name = 'p1') drop procedure p1
    go
    if exists(select 1 from sys.procedures where name = 'p2') drop procedure p2
    go
    
    create procedure p2 as
    begin tran
    -- commit tran -- Missed commit!
    go
    
    create procedure p1 as
    begin tran
    exec p2
    commit tran
    go
    
    while (@@trancount > 0) rollback tran
    go
    
    -- No error here.
    print 'Running test 1'
    set implicit_transactions on
    go
    exec p1
    go
    
    while (@@trancount > 0) rollback tran
    go
    
    -- Error here.
    print 'Running test 2'
    set implicit_transactions off
    go
    exec p1
    go
    


    Matt
    Monday, October 31, 2011 6:35 AM
    Moderator
  • No, that is not going to work.   What you want to do is something like this (which I just randomly found, you may want to read around some more) ->

     

    http://www.informit.com/articles/article.aspx?p=24903

     

    The technque is to structure your stored procedures to avoid bumping trancount if there is an active transaction.  This will ensure they work properly from all callers and in all transactional situations.  However the above article does not explain the resulting flow control in stored procedures (you need the top-most stored procedure to do the rollback).

    I'm not an expert in this area perhaps check with the Transact-SQL MSDN forums on this one.

    I have heard of two options in the past.  One is to use return code to tell parent sproc to rollback or not.  Other is to have try/catch in topmost sproc and have it control the rollback.

    There is also this issue ->

    http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx

    I also found this very detailed article, looks good, I would read this one (note it starts talking about SQL 2000 but has some pointers for 2005 and later) ->

    http://www.sommarskog.se/error-handling-II.html

     


    Matt
    Monday, October 31, 2011 6:39 PM
    Moderator

All replies

  • Hi BetterToday,

    Welcome!

    To make sure that the .NET Framework Data Provider for SQL Server transaction management model performs correctly, avoid using other transaction management models, such as the one provided by SQL Server.

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 26, 2011 8:31 AM
    Moderator
  • Hi Alan,

    thanks for replying.

    It is impossible to avoid using transactions in stored procedures. Some higher-level stored procedures simply require their own transaction handling. You can't just always shift whole table contents to the client only to have it do things a stored procedure could perform much better.

    So you believe this is an ADO.NET problem?

    Take care,
    Axel


    Vote here for a Microsoft Connect feedback channel on Windows - and win a better Windows!

    Wednesday, October 26, 2011 8:49 AM
  • Hi Axel,

    We will do some more pending research  about your problem and come back as soon as possible, Thanks for understanding.
    Have a nice day. 


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, October 26, 2011 9:10 AM
    Moderator
  • Thanks Alan, I appreciate it!

     


    Vote here for a Microsoft Connect feedback channel on Windows - and win a better Windows!

    Wednesday, October 26, 2011 8:09 PM
  • Hi Better,

    I think the issue doesn't relate to ADO.NET, SqlConnection.BeginTransaction is always stating a database Transaction(http://msdn.microsoft.com/en-us/library/86773566.aspx). I find some help information on MSDN Liabary:

    -----------------------------------------------------------------

    ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement. In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.(http://msdn.microsoft.com/en-us/library/ms181299.aspx)

    -----------------------------------------------------------------

    So your sqltransactions ---->1---->2----->roll back in sp---->0, it seems make sense now.

    By the way, we should use try and catch on the client to handle this scenario, you can refer the code in first link.

    Have a nice day.

     

     


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, October 27, 2011 7:37 AM
    Moderator
  • Hi Alan,

    thanks again for your reply.

    "... should use try and catch on the client to handle this scenario, you can refer the code in first link."

    I already do. My problem is that the error message I get from ADO.NET adds above error text to the message. This shouldn't happen.

     

    Here's the current (valid) scenario:

    Stored Procedure:

    IF ... BEGIN
      RAISERROR('Error Message', 11, 1)
      ROLLBACK TRANSACTION
      RETURN -1
    END

    ADO.NET:

    • Creates a SqlException from the RAISERROR statement
    • Compares @@TRANCOUNT to saved transaction count
    • Creates warning message from mismatched comparison result

    .NET client:

    try
    {
      SqlCommand cmd = ...;

      cmd.ExecuteNonQuery(...);
    }
    catch (SqlException ex)
    {
      MessageBox.Show(ex.Message);
    }

     

    The lines marked red should be altered in ADO.NET to NOT add a warning message to the SqlException message if @@TRANCOUNT == 0.

    So, if:

    • A SqlException object is created
    • @@TRANCOUNT == 0

    then this warning message should not be emitted.


    Vote here for a Microsoft Connect feedback channel on Windows - and win a better Windows!


    • Edited by BetterToday Friday, October 28, 2011 1:53 PM
    Friday, October 28, 2011 1:53 PM
  • This error is raised by SQL Server and not the ADO.NET driver.  The ADO.NET driver knows nothing about @@TRANCOUNT.   When you begin a transaction using ADO.NET driver, this sends a transaction manager request to SQL Server (TDS TM TM_BEGIN_XACT request).   Server then responds to client with the transaction ID (local transaction id which is session scoped).   The transaction id is all ADO.NET knows about.  When SQL decides to roll back the transaction it will send a message to client with TM_ROLLBACK_XACT.  Anyway, you probably don't care about these details. :)

    What I found from digging in server source code is this will be raised IF you are in implicit transaction mode (which when you start a txn from client you enter this mode) AND you finish execution of a sproc and you are not at base level (you are in some nested level for example in a stored proc) and starting transaction count does not match ending transaction count then this error is raised (I think, I am not super familiar with this code).

    Hence there appears to be a rule here that SQL enforces ->

    You can reproduce like so:

    if exists(select 1 from sys.procedures where name = 'p1') drop procedure p1
    go
    if exists(select 1 from sys.procedures where name = 'p2') drop procedure p2
    go
    
    create procedure p2 as
    begin tran
    -- commit tran -- Missed commit!
    go
    
    create procedure p1 as
    begin tran
    exec p2
    commit tran
    go
    
    while (@@trancount > 0) rollback tran
    go
    
    -- No error here.
    print 'Running test 1'
    set implicit_transactions on
    go
    exec p1
    go
    
    while (@@trancount > 0) rollback tran
    go
    
    -- Error here.
    print 'Running test 2'
    set implicit_transactions off
    go
    exec p1
    go
    


    Matt
    Monday, October 31, 2011 6:35 AM
    Moderator
  • Hi Matt,

    thanks a lot for digging deeply into this!!

    So you'd suggest to "SET implicit_transactions ON" in order to avoid the addition to the error message?


    Vote here for a Microsoft Connect feedback channel on Windows - and win a better Windows!

    Monday, October 31, 2011 4:56 PM
  • No, that is not going to work.   What you want to do is something like this (which I just randomly found, you may want to read around some more) ->

     

    http://www.informit.com/articles/article.aspx?p=24903

     

    The technque is to structure your stored procedures to avoid bumping trancount if there is an active transaction.  This will ensure they work properly from all callers and in all transactional situations.  However the above article does not explain the resulting flow control in stored procedures (you need the top-most stored procedure to do the rollback).

    I'm not an expert in this area perhaps check with the Transact-SQL MSDN forums on this one.

    I have heard of two options in the past.  One is to use return code to tell parent sproc to rollback or not.  Other is to have try/catch in topmost sproc and have it control the rollback.

    There is also this issue ->

    http://weblogs.sqlteam.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-Stored-Procedures.aspx

    I also found this very detailed article, looks good, I would read this one (note it starts talking about SQL 2000 but has some pointers for 2005 and later) ->

    http://www.sommarskog.se/error-handling-II.html

     


    Matt
    Monday, October 31, 2011 6:39 PM
    Moderator
  • Your repro code seems to suggest that SQL Server only throws this error if IMPLICIT_TRANSACTIONS is OFF, but you say that it only throws this error if IMPLICIT_TRANSACTIONS is ON. Did you mean the opposite of what you said?
    Tuesday, March 28, 2017 2:50 PM
  • Here’s my guess as to why IMPLICIT_TRANSACTIONS ON behaves this way:

    When you call a sproc, you expect your session to begin possibly with @@TRANCOUNT 0 and expect it to have risen to 1 upon completion if any data modification actually happened. If SQL Server were to perform its normal mismatch check, almost all calls would result in the error being thrown unless you surround all of your sproc calls with explicit transactions. Thus, SQL Server turns off the before/after checks so that you can actually take advantage of the implicit behavior by omitting BEGIN TRANSACTION.

    Tuesday, March 28, 2017 3:47 PM
  • When you call a stored procedure using EXEC on SQL Server (and apparently also when IMPLICIT_TRANSACTIONS is OFF, which is considered normal), SQL Server will throw an error when the stored procedure returns if its final @@TRANCOUNT value is not equal to its initial @@TRANCOUNT value.

    The text which you say is wrong looks quite correct to me. If you start a transaction using SqlTransaction, @@TRANCOUNT will be 1. If you call a stored procedure from that connection, SQL Server will record the initial @@TRANCOUNT as 1. If you call ROLLBACK in that procedure, @@TRANCOUNT will be set to 0 (a plain ROLLBACK always goes all the way back to 0, it might be possible to do something else with named transactions (I really can’t think of another reason for named transactions to exist), but I don’t know). If SQL Server reaches the end of the stored procedure’s execution at this point and checks the current @@TRANCOUNT against the initial, it will see that 1 is not equal to 0 and raise that error.

    I personally would advise against using SqlTransaction. If you manage transactions from the client, a client “going away” (e.g., losing a network connection temporarily) may have issued a `BEGIN TRANSACTION` (via SqlTransactino) and have executed some statements which take locks. If you have a hung/temporarily gone client with an open transaction with locks taken out, other queries from other clients may hit those locks and have to wait for the gone client’s connection to time out and be killed, etc.. You should try to ensure that transactions do not span multiple SQL batches sent to the server if possible (unless it doesn’t matter—e.g., if your app is the only thing ever to connect to SQL Server in the first place). Since you’re using stored procedures anyway, it might be straight forward to write a new stored procedure which manages transactions and calls all three for you. Or you can just write BEGIN TRANSACTION and COMMIT TRANSACTION directly in the SqlCommand.CommandText.

    Tuesday, March 28, 2017 4:01 PM