patternsqlMinor
what does "if @@trancount > 0 commit tran" mean?
Viewed 0 times
committranwhatmeantrancountdoes
Problem
What does this operation mean please?
and after that
or
I do not understand the meaning of this sequence of commands even though I searched a lot online.
if @@trancount > 0 commit tranand after that
SET IMPLICIT_TRANSACTIONS ONor
SET IMPLICIT_TRANSACTIONS OFFI 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:
-
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:
-
IF @@trancount > 0 COMMIT TRANSACTIONThis 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 @@TRANCOUNTCode 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 @@TRANCOUNTContext
StackExchange Database Administrators Q#199361, answer score: 5
Revisions (0)
No revisions yet.