In SQL Server, how do I know what transaction mode I'm currently using?

Question

In SQL Server, how do I know what transaction mode I'm currently using? Such as autocommit, explicit, or implicit. And how can I change one mode to another using tsql? Great thanks.

Solution 1

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 whether current transaction was started explicitly or implicitly. So, this code just tries to guess: if IMPLICIT_TRANSACTIONS is OFF, the transaction is assumed to be started explicitly.

MSDN references:

• @@OPTIONS function
• Numeric values for options
• @@TRANCOUNT

Solution 2

select @@OPTIONS & 2

if this returns 2, you're in implicit transaction mode. If it returns 0, you're in autocommit.

BOL for @@OPTIONS

BOL for what each option is

To switch which mode you're in, you'd use

set implicit_transactions on

or

set implicit_transactions off

Solution 3

Slight modification to previously posted script - connection is in autocommit mode if there's no active transaction AND implicit transactions are off:

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))

Comments

[daniel_aren]

It should be implicit_transactions.

[Damien_The_Unbeliever]

@daniel_aren - true, changed now. Don't know why it wasn't spotted before.

[SQLnbe]

Turning this ON/OFF does it happen at the Server level or the active session where the transaction is happening?

[Damien_The_Unbeliever]

@SQLnbe - it's a connection level setting. See implicit transactions: "When a connection is operating in implicit transaction mode ..." (my emhpasis)

[MaxiWheat]

I think that the message No current transaction, autocommit mode (default) is a little misleading since autocommit is not totally sure at this point, the connection could be set for implicit transactions but since no statement were issued yet, no transaction have been started.