Learning Corner #8 – Transaction Modes

In this article, I will explain about the possible transaction modes in SQL Server. The article is structured in the form of interview questions.
Some questions may have a more detailed answer than what is needed, but the purpose is to give you a clear overview on this subject, not only a cheat sheet for your next interview 🙂

OK, so here goes:

1. What are the three ways of working with transactions in SQL Server?

  • Autocommit
  • Implicit transaction
  • Explicit transaction

 

2. What is autocommit mode?

Autocommit mode is the default transaction mode in SQL Server. Its behavior is the following: every DDL or DML statement is executed in the context of a transaction. If the statement succeeds, the transaction is committed. Otherwise, the transaction is rolled back.

In this mode, the commands BEGIN TRAN , ROLLBACK TRAN , COMMIT TRAN  are not needed, because everything is handled by SQL Server.

Also, @@TRANCOUNT  does not make sense in autocommit mode.

3. What is the database engine’s behavior in autocommit mode, in case an error occurs?

When in autocommit mode, the database engine behaves according to what type of error it encounters.

There are two possible types of errors:

  • compile errors (these prevent the database engine to generate an execution plan, so nothing gets executed)

Transactions - Message returned after executing a query with a compile error.
Message returned after executing a query with a compile error.

  • run-time errors (the statements within the transaction are executed until the one with the run-time error occurs). 

In the following example, I will first delete the Numbers table, because I am assuming it was created in the example above. Afterwards, I will insert some rows, but will make a mistake and will try to insert the same primary key value twice. Let’s see what happens:

Transactions - Message returned after executing a query with a run-time error.
Message returned after executing a query with a run-time error.

Observation: Object names are only resolved at run-time (deferred name resolution), which means that the errors generated by miswriting a database object are run-time errors. In the following example, I will insert again three rows in the Numbers table, but I will make a typo in the second row. What do you think the result will be? 

Transactions - Message returned after executing a query with a run-time error, caused by a typo.
Message returned after executing a query with a run-time error, caused by a typo.

 

4. How are the T-SQL statements handled in Implicit transaction mode?

In the implicit mode, the following events occur:

  • every DDL or DML or SELECT statement is included automatically in a transaction

In implicit mode, if no transaction is currently in progress,  a transaction will automatically be initiated if one of these commands is sent to the server:

ALTER TABLE INSERT
CREATE OPEN
DELETE REVOKE
DROP SELECT
FETCH TRUNCATE TABLE
GRANT UPDATE
  • @@TRANCOUNT  is increased by 1 with every transaction
  • you must issue a COMMIT or ROLLBACK by hand to finish the transaction, even if it was a simple SELECT statement

Implicit transaction mode is not the default mode in SQL Server.

In the following example, I will set the session to use the implicit transaction mode. Then, I will check how @@TRANCOUNT   changes after some DML commands. First, I will check that no transaction is in progress in this connection, which means that @@TRANCOUNT = 0 . Then, I will issue some INSERT statements, which should increase the number of transactions. A BEGIN TRANSACTION  command will create yet another transaction. These will remain open and the number of transactions can only be increased from now on, until SQL Server sees the words ROLLBACK or COMMIT. I will rollback all my commands and will check again the transaction number:

SQL Server shows the following results:

Transactions - Implicit transactions results

And gives the following messages:

Transactions - Implicit transactions messages

 

5. How do you enter the implicit transaction mode in SQL Server?

You can enter in the implicit transaction mode by executing this command:

SET IMPLICIT_TRANSACTIONS ON

The same thing would happen if you execute the following command (but it will only execute the previous one):

SET ANSI_DEFAULTS ON

 

6. What are the mandatory statements needed to be executed in implicit transaction mode?

In order to finish a transaction, you must manually execute a COMMIT or ROLLBACK statement. Otherwise, in this mode, the transaction will not finish. If, instead of committing or canceling the transaction, you add some more DDL or DML commands, they will become part of the transaction.

 

7. What are the advantages and disadvantages of implicit transaction?

Advantages:

  • an implicit transaction can be rolled back, after the command inside the transaction has been executed;
  • because the COMMIT or ROLLBACK are issued after the command is executed, it is easier to catch eventual errors that can appear.

Disadvantages:

  • until the transaction is finished, all the locks created by the command are kept active. This can have a major impact over the overall performance of the solution and can block other users from working on the database.

We can see the currently active locks, by executing this command:  EXECUTE sp_lock @@spid

For the example above from the implicit transactions, if we check the acquired locks before rolling back the transaction, we will get a result similar to the one below:

Transactions - sp_lock results

  • because it is not the default mode in SQL Server, you must always remember to enable it (it gets disabled automatically when the session is over).
  • the implicit transaction mode does not work well with explicit transactions, because an explicit transaction will cause @@TRANCOUNT  to increment from 0 to 2 immediately, after the BEGIN TRAN  command is issued.

 

8. How are the T-SQL statements handled in explicit transaction mode?

An explicit transaction happens when the command BEGIN TRANSACTION  or BEGIN TRAN  is executed.
@@TRANCOUNT  is incremented to 1 after BEGIN TRAN is executed.
Explicit transactions can be run interactively or in the code of stored procedures.

 

9. Can an explicit transaction be executed in implicit transaction mode?

An explicit transaction can be executed in the implicit transaction mode, but it is not considered a good practice.

 

10. Which one is the default transaction mode in SQL Server?

The default transaction mode in SQL Server is autocommit mode.

Leave a Reply

Your email address will not be published. Required fields are marked *

one × 3 =