Question about transactions in SQL Server

[aggle-rithm]

I'm in the middle of a monstrous upgrade of our timecard tracking software (not my area of expertise, but it got dumped in my lap). I keep getting errors running the scripts that are auto-generated by the upgrader the vendor provided. The scripts continue for a while despite the errors, but eventually the upgrade always fails.

The errors I'm getting are something like: "The number of 'begin transaction' statements do not match the number of 'commit/rollback' statements".

I can see where this could cause failures; some batches could commit too early or not get commited at all.

What I was wondering was: If a different process is accessing the same server, and they both use the same name for a named transaction, is it possible for SQL Server to get confused about which process is doing what?

Maybe "confused" is not such a good word: Is it normal behavior to SQL Server to apply transactions independently of the process it was started in?

(BTW, this is Microsoft, not Sybase.)

 

[Wudang]

I know I've had to patch vendor DDL before. The behaviour you ask about sounds implausible to me when you consider how lock management for example has to work. I'm not a DBA so keep a pinch of salt handy but I'd probably start by running a SQL server trace and send the output to the vendor or ask at http://dba.stackexchange.com/

 

[ravdin]

IANADBA but I think SQL Server transactions are thread safe. You might want to take a look at the @@trancount system function, which is incremented when you begin a transaction and decremented when you rollback or commit. My understanding is that your transactions aren't actually committed until @@trancount is 0, and you'll get this error if your stored procedure exits without restoring the balance. Hope this helps.

http://msdn.microsoft.com/en-us/library/ms187967.aspx