Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004

    Unanswered: SP and Transaction Names

    Is it possible for MS SQL 2k to get confused when we have two stored proces that has the same name for their transactions and both stored procs are exec at the same time?


    Create Procedure SP1


    Begin Transaction TranName1
    Commit Transaction TranName1

    Begin Transaction TranName2
    Commit Transaction TranName2

    Create Procedure SP2


    Begin Transaction TranName2
    Commit Transaction TranName2

    Begin Transaction TranName3
    Commit Transaction TranName3

    We are seeing some very weird data corruption but luckily its been way under 1%(something like 500 bad rows out of a few million). The only thing we can think of since it is such a very low number is maybe when blocking occurs SQL has trouble handing two transactions with the same name in its queue. Beyond that we have no idea what could be causing this issue.

  2. #2
    Join Date
    Feb 2004
    Transactions are usually local, in your case, within the sp. However, if the sp exists prior a commit or rollback the @@trancount is still up. When another procedure starts and has a transaction with the name _and_ it's executed using the same connection, you could end up in trouble. I think as long as different connections are used and the sp's with transactions do a proper commit or rollback, it's hard to follow how they could corrupt oneanother.

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    All transactions, including implicit or explicit distributed transactions, are local to to the session that originated them. If another session uses the same connection object to execute a batch with the same transaction name, the only thing in common between the two is the increment/decrement of @@TRANCOUNT variable that is tied to the connection.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts