In this article, we will discuss the different modes of transactions in SQL Server and their features, similarities, and differences.

Auto-commit Transactions in SQL Server

The auto-commit transaction mode is the default transaction mode in SQL Server. In this mode, each SQL statement is evaluated as a transaction by the storage engine. If a statement completes successfully, it is committed and the data modification becomes permanent in the database. If an error occurs, the statement is rolled back.

Here is an example:

CREATE TABLE Person (
    PersonID INT PRIMARY KEY,
    LastName VARCHAR(255),
    FirstName VARCHAR(255),
    Address VARCHAR(255),
    City VARCHAR(255),
    Age INT
);

INSERT INTO Person VALUES (1, 'Hayes', 'Corey', '123 Wern Ddu Lane', 'LUSTLEIGH', 23);

INSERT INTO Person VALUES (1, 'Macdonald', 'Charlie', '23 Peachfield Road', 'CEFN EINION', 45);

In the above example, the first insert statement is committed because it does not return any error. However, the second insert statement is rolled back due to a duplicate primary key error.

Implicit Transaction Mode in SQL Server

In the implicit transaction mode, SQL Server begins transactions implicitly but waits for commit or rollback commands from the user. The database objects involved in the transaction remain locked until the commands are executed.

To enable or disable the implicit transaction mode, you can use the following syntax:

SET IMPLICIT_TRANSACTIONS { ON | OFF }

Here is an example:

SET IMPLICIT_TRANSACTIONS ON;

UPDATE Person
SET Lastname = 'Sawyer', Firstname = 'Tom'
WHERE PersonID = 2;

COMMIT TRAN;

In the above example, we enable the implicit transaction mode, update a row in the Person table, and then commit the transaction to make the data changes permanent.

Explicit Transaction Mode in SQL Server

In the explicit transaction mode, transactions must be explicitly defined using the BEGIN TRANSACTION, COMMIT TRANSACTION, and ROLLBACK TRANSACTION statements. All modifications within the transaction are persisted in the database when the transaction is committed, and undone when the transaction is rolled back.

Here is an example:

SET IMPLICIT_TRANSACTIONS OFF;

BEGIN TRAN;

INSERT INTO Person VALUES (3, 'Bunny', 'Bugs', '742 Evergreen Terrace', 'Springfield', 54);

COMMIT TRAN;

SELECT * FROM Person;

In the above example, we begin an explicit transaction, insert a new row into the Person table, commit the transaction, and then retrieve all rows from the table.

Differences between Auto-commit and Explicit Transactions

There are several differences between the auto-commit and explicit transaction modes in SQL Server. One notable difference is the log buffer flush mechanism. In auto-commit mode, the log buffer is flushed into the log file after each committed transaction, while in explicit mode, the log buffer reaches its maximum size before being flushed.

Here is a benchmark example:

DECLARE @Counter INT = 1;

WHILE @Counter <= 1000
BEGIN
    INSERT INTO InsertSomeRecord (RowNumber, RowNumberString)
    VALUES (@Counter, 'RowNumber=' + CAST(@Counter AS NVARCHAR(7)));

    SET @Counter = @Counter + 1;
END;

In the above example, when using auto-commit mode, the log flush process occurs 1000 times in random-sized chunks. However, in explicit mode, the log buffer reaches its maximum size and then flushes into the log file.

Based on your application and business requirements, you can choose the transaction mode that is most suitable for your needs.

Conclusion

In this article, we have discussed the different modes of transactions in SQL Server. Understanding transaction modes is crucial as they directly affect data integrity. Each transaction mode has its own characteristics and it is important to choose the appropriate mode based on your specific requirements.