1

Running SS2005. I have a script that calls procedureA which in turn calls procedureB.

procedureA has no explicit transactions. procedureB is split up into several small blocks of non-nested BEGIN and COMMIT TRANSACTION.

I have peppered procedureB with PRINT statements to show me the value of @@TRANCOUNT. It's always at 0 or 1.

But when I check master..sysprocesses it shows open_tran at 1 or 2. Where this extra transaction is coming from?

I am posting the 2 procedures below. pr_Get12Relatives has been stripped of most of its content for clarity as it contains 1600 lines! It just repeats the same behavior as it searches for different types of relationships.

procedureA = pr_GetRelatives

procedureB = pr_Get12Relatives

  • Can you post your test code, if possible? It'd be easier to understand what you're doing, and then in turn explain what is happening if we can all reference the same test/sample code. – Thomas Stringer Feb 10 '14 at 18:02
  • Code posted above. – Andy Feb 10 '14 at 18:27
  • Is IMPLICIT_TRANSACTIONS turned on in the query options? – Jon Seigel Feb 10 '14 at 20:42
  • 'IMPLICIT_TRANSACTIONS' is NOT turned on. – Andy Feb 11 '14 at 16:03
1

This thread answers my question..

When a DML statement is currently being executed in a session the open_tran and @@trancount is always at least 2.

For example

Connection 1

CREATE TABLE T( X INT PRIMARY KEY)

GO

BEGIN TRAN

INSERT INTO T VALUES(1)

WAITFOR DELAY '00:01'

ROLLBACK

Connection 2

INSERT INTO T VALUES(1) /*Blocks on Connection 1*/

Connection 3

(look at blocked connection 2)

SELECT lastwaittype,
       open_tran
FROM   master..sysprocesses
WHERE  spid = <spid of connection 2>

Results

+----------------------------------+-----------+
|           lastwaittype           | open_tran |
+----------------------------------+-----------+
| LCK_M_X                          |         2 |
+----------------------------------+-----------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.