4

Imagine I am running multiple batches through management studio, separated by the GO command. I'd like to know how implicit transactions will behave - is the transaction committed on a per batch basis or once for the entire execution.

  • 1
    I think you are confusing implicit transactions with autocommit. Autocommit will commit each statement individually whereas a transaction started via an Implicit transaction or an explicit transaction can span batches and will not be committed until an explicit COMMIT is executed. See docs.microsoft.com/en-us/sql/t-sql/statements/…. – Dan Guzman Jul 2 '17 at 17:25
6

What exactly do you mean by "implicit transactions"? Do you mean that you have the session property of IMPLICIT_TRANSACTIONS enabled and are using that feature? Or do you mean that you just aren't specifying BEGIN TRANSACTION? (i.e. creating an "explicit" transaction)?

If you are meaning the IMPLICIT_TRANSACTIONS feature, that simply issues the BEGIN TRAN and nothing more. So whether or not you are using IMPLICIT_TRANSACTIONS to issue the BEGIN TRAN or if you are doing that explicitly yourself, you are still responsible for issuing the COMMIT or ROLLBACK. And yes, Transactions can span batches, whether you are using GO to separate batches or executing multiple queries by hitting F5 at different times.

In either case -- IMPLICIT_TRANSACTIONS set to ON or an explicit BEGIN TRAN statement -- nothing is automatically committed. The only thing that might be automatic in these two cases is a ROLLBACK if you close the connection without issuing the COMMIT or ROLLBACK first.

Without either IMPLICIT_TRANSACTIONS set to ON or an explicit BEGIN TRAN statement, then each individual query is its own transaction, that is "auto-committed" and you need not worry about it. Meaning, 2 separate INSERT statements are two completely separate transactions.

4

Implicit transactions (aka chained transaction mode) span batches; the go command has no effect on the status of your transaction.

See SET IMPLICIT_TRANSACTIONS for some examples that include transactions spanning batches (ie, spanning the go command).

1

Neither nor. They are per statement as per SQL Standard.

Which means that a batch that runs for example in a loop does not run in one transaction, but every statement is it's own atomic transaction.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.