Geek Answers Handbook

In SQL Server, how do I know which transaction mode I am using now?

In SQL Server, how do I know which transaction mode I am using now? For example, autocommit, explicit or implicit . And how can I change one mode to another using tsql? Many thanks.

+6
tsql sql-server-2005 transactions
source share
3 answers
IF @@TRANCOUNT = 0 PRINT 'No current transaction, autocommit mode (default)' ELSE IF @@OPTIONS & 2 = 0 PRINT 'Implicit transactions is off, explicit transaction is currently running' ELSE PRINT 'Implicit transactions is on, implicit or explicit transaction is currently running'

I don’t think there is a way to determine if the current transaction was started explicitly or implicitly. So this code is just trying to guess: if IMPLICIT_TRANSACTIONS is turned off, it is assumed that the transaction will be started explicitly.

MSDN Links:

+9
source share
 select @@OPTIONS & 2

if it returns 2, you are in implicit transaction mode. If it returns 0, you are in autocommit.

BOL for @@ OPTIONS

BOL for each parameter

To switch the mode you are in, you must use

 set implicit_transactions on

or

 set implicit_transactions off
+5
source share

A minor modification to a previously published script - the connection is in autosave mode if there is no active transaction and implicit transactions are disabled:

 IF @@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 0) PRINT 'No current transaction, autocommit mode (default)' ELSE IF @@TRANCOUNT = 0 AND (@@OPTIONS & 2 = 2) PRINT 'Implicit transactions is on, no transaction started yet' ELSE IF @@OPTIONS & 2 = 0 PRINT 'Implicit transactions is off, explicit transaction is currently running' ELSE PRINT 'Implicit transactions is on, implicit or explicit transaction is currently running' + CAST(@@OPTIONS & 2 AS VARCHAR(5))
+3
source share

More articles:


All Articles