HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

what does "if @@trancount > 0 commit tran" mean?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
committranwhatmeantrancountdoes

Problem

What does this operation mean please?

if @@trancount > 0 commit tran


and after that

SET IMPLICIT_TRANSACTIONS ON


or

SET IMPLICIT_TRANSACTIONS OFF


I do not understand the meaning of this sequence of commands even though I searched a lot online.

Solution

I think there are two concepts that you need to understand:

-
IF @@trancount > 0 COMMIT TRANSACTION

This checks whether there are still any open transactions from earlier in the
script, or that will be present if IMPLICIT_TRANSACTIONS are on. If you don't
check for open transaction before doing a COMMIT, you will receive the
following error:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

-
IMPLICIT_TRANSACTIONS

To simplify this, when IMPLICIT_TRANSACTIONS is ON, there is an invisible BEGIN TRANSACTION before specific statements (see list here) if there aren't any transactions open already. And when IMPLICIT_TRANSACTIONS is OFF, your statement is in autocommit mode

If you provide us with a code example, we might be able to answer your question more specifically. You can play with this code to see how these commands affect a statement:

/*
    SET IMPLICIT_TRANSACTIONS ON
    SET IMPLICIT_TRANSACTIONS OFF
    */
    
    
    --BEGIN TRANSACTION  --Uncomment if IMPLICIT_TRANSACTIONS is ON
    
    CREATE TABLE Test (col int)
    
    DROP TABLE Test
    
    SELECT @@TRANCOUNT
    
    BEGIN TRANSACTION
    
    SELECT @@TRANCOUNT
    
    COMMIT
    
    SELECT @@TRANCOUNT
    
    COMMIT
    
    SELECT @@TRANCOUNT
    
    --When IMPLICIT_TRANSACTIONS is ON, SQL will begin a new transaction count here, 
    --You need to explicitly end the transaction at the end
    CREATE TABLE Test (col int)
    
    DROP TABLE Test
    
    SELECT @@TRANCOUNT
    
    IF @@TRANCOUNT > 0
        COMMIT
    
    SELECT @@TRANCOUNT

Code Snippets

/*
    SET IMPLICIT_TRANSACTIONS ON
    SET IMPLICIT_TRANSACTIONS OFF
    */
    
    
    --BEGIN TRANSACTION  --Uncomment if IMPLICIT_TRANSACTIONS is ON
    
    CREATE TABLE Test (col int)
    
    DROP TABLE Test
    
    SELECT @@TRANCOUNT
    
    BEGIN TRANSACTION
    
    SELECT @@TRANCOUNT
    
    COMMIT
    
    SELECT @@TRANCOUNT
    
    COMMIT
    
    SELECT @@TRANCOUNT
    
    --When IMPLICIT_TRANSACTIONS is ON, SQL will begin a new transaction count here, 
    --You need to explicitly end the transaction at the end
    CREATE TABLE Test (col int)
    
    DROP TABLE Test
    
    SELECT @@TRANCOUNT
    
    IF @@TRANCOUNT > 0
        COMMIT
    
    SELECT @@TRANCOUNT

Context

StackExchange Database Administrators Q#199361, answer score: 5

Revisions (0)

No revisions yet.