CODEWITHSUNDEEP
sqlsql-servert-sql
Gezim
Gezim

Reputation: 7308

TSQL double "commit" required to commit

First, in MS SQL Server Management Studio, I've enabled SET IMPLICIT_TRANSACTIONS options. I'm not sure if that has something to do with this behaviour I'm seeing.

I've got the following script:

BEGIN TRANSACTION;

WITH
    DocLinksTmp
AS
    (
    select *, 
     row_number() over (partition by col1, col2 order by col1) as [RowNumber]
     from doctable
    )  
DELETE
    DocLinksTmp
WHERE
    RowNumber > 1 

GO
COMMIT
COMMIT

print @@trancount

Leaving both COMMIT statements, the print command prints 0. When I removed one COMMIT, the print statement prints 1.

Any ideas why this is happening?

Upvotes: 2

Views: 394

Answers (1)

AgentSQL
AgentSQL

Reputation: 2930

From Books Online:

When IMPLICIT_TRANSACTIONS is ON, executing BEGIN TRANSACTION causes two nested transactions to open. BEGIN_TRANSACTION increments @@trancount whenever a transaction is active.

Upvotes: 4

Related Questions