Error - Trancount Reset!

From: MikeF (MikeF_at_discussions.microsoft.com)
Date: 11/12/04

  • Next message: Joe Weinstein: "Re: Error - Trancount Reset!" 
    Date: Fri, 12 Nov 2004 12:21:03 -0800

    Hi,

      I am using the T-SQL commands "BEGIN TRANSACTION", "COMMIT TRANSACTION"
    and "ROLLBACK TRANSACTION" via the Statement.Execute method instead of
    setting the autoCommit feature of JDBC.

    We often have objects that contain transactional updates that may be called
    by another object that has already initiated a transaction or that may be
    used alone, thus we need a form of transaction nesting which the autoCommit
    feature does not provide.

    However, we have noticed that the @@TRANCOUNT will reset to 0 once a
    statement object is created on the same connection - for example, we would
    expect:

    FIRST OBJECT
    BEGIN TRANSACTION
     ... SOME WORK (TRANCOUNT = 1)
     SECOND OBJECT
      BEGIN TRANSACTION (TRANCOUNT = 2)
      .. SOME WORK
      COMMIT TRANSACTION (TRANCOUNT = 1)
    FIRST OBJECT
      COMMIT TRANSACTION (TRANCOUNT = 0)

    However, we have noticed that as soon as a Statement object is created in
    the inner transaction, the TRANCOUNT is immediately set to 0. Then, when the
    COMMIT TRANSACTION statement is issued, an error is raised because there is
    no matching BEGIN TRANSACTION.

    Has anyone ever encountered this error? Is it a bug?

    Thanks,

    Mike

  • Next message: Joe Weinstein: "Re: Error - Trancount Reset!"
    Loading