Post reply

How to tell the current setting for IMPLICIT_TRANSACTIONS

  • April 16, 2015 at 6:55 pm

    #299911

    Hi,

    I'm studying for 70-461 and I'm looking at the Explicit and Implicit Transaction modes and wonder how to tell which mode is set and how to change it. Is there only one mode set at a time?

    1) AUTOCOMMIT

    2) IMPLICIT

    3) EXPLICIT

    MSDN To view the current setting for IMPLICIT_TRANSACTIONS, run the following query.

    DECLARE @IMPLICIT_TRANSACTIONS VARCHAR(3) = 'OFF';

    IF ( (2 & @@OPTIONS) = 2 ) SET @IMPLICIT_TRANSACTIONS = 'ON';

    SELECT @IMPLICIT_TRANSACTIONS AS IMPLICIT_TRANSACTIONS;

    Is this the only way to see what the setting? How does this code tell me the current setting when it appears to update the setting?

    John

    SQL 2012 Standard VPS Windows 2012 Server Standard

  • April 17, 2015 at 2:50 am

    #1791603

    Errr, kinda

    Autocommit is the default. If implicit transactions is off, then every statement is it's own transaction that's automatically started and committed. Hence autocommit

    If you explicitly BEGIN TRANSACTION, then you have an explicit transaction

    Implicit transactions is a connection setting, not database or server. If you turn it on on your session, then any statement begins a transaction which must then be explicitly committed.

    Edit: Missed the @ in the SET statement

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • April 17, 2015 at 7:24 am

    #1791672

    First, I don't see that code as turning on any setting. It sets a local variable value, that's it. You happened to name that local variable "[@]IMPLICIT_TRANSACTIONS", but that doesn't mean it has anything to do with a connection setting by the same name. Similarly, setting a local variable named @XACT_ABORT to "ON" wouldn't change your XACT_ABORT setting either.

    Keep in mind, too, that that implicit transaction setting only applies to dblib connections. For other connections, you'll stay in implicit transaction mode until you issue a "BEGIN TRANSACTION" statement.

    There are only two settings: implicit and explicit. Autocommit applies as a consequence of being in implicit mode.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply