In this article, we will discuss the fundamental details of transactions in SQL Server and how they ensure data integrity and consistency.

Introduction

A transaction is a logical work unit that performs a single activity or multiple activities in a database. Transactions can consist of read, write, delete, or update operations, or a combination of these. The main idea behind transactions is to ensure that all operations within a transaction are either completed successfully or rolled back in case of any failure.

For example, when we withdraw money from an ATM, the ATM application performs multiple steps such as checking the account balance, deducting the money, and keeping a log of the transaction. If any of these steps fail, the entire transaction is rolled back to maintain data integrity.

ACID Properties

Transactions in SQL Server adhere to the ACID properties, which are:

• Atomicity: All operations within a transaction are treated as a single unit. If any operation fails, the entire transaction is rolled back.
• Consistency: After a transaction is completed, the data in the database is consistent according to the defined rules, constraints, cascades, and triggers.
• Isolation: Transactions are isolated from each other to prevent interference and ensure data integrity.
• Durability: The changes made by committed transactions are persisted in the database and are not lost even in case of power outages or other problems.

Transaction Modes

SQL Server supports three transaction modes:

1. Autocommit: This is the default transaction mode in SQL Server. Each T-SQL statement is evaluated as a separate transaction and is committed or rolled back based on its result.
2. Implicit: In this mode, SQL Server automatically starts an implicit transaction for every DML statement. The transaction needs to be explicitly committed or rolled back at the end of the statements.
3. Explicit: This mode allows you to define a transaction explicitly using the BEGIN TRANSACTION and COMMIT or ROLLBACK TRANSACTION statements.

Defining Transactions

To define an implicit transaction, you need to enable the IMPLICIT_TRANSACTIONS option. Here’s an example:

SET IMPLICIT_TRANSACTIONS ON

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

SELECT IIF(@@OPTIONS & 2 = 2, 'Implicit Transaction Mode ON', 'Implicit Transaction Mode OFF') AS 'Transaction Mode'
SELECT @@TRANCOUNT AS OpenTransactions

COMMIT TRAN

SELECT @@TRANCOUNT AS OpenTransactions

To define an explicit transaction, you can use the BEGIN TRANSACTION statement. Here’s an example:

BEGIN TRAN

UPDATE Person
SET Lastname = 'Lucky', Firstname = 'Luke'
WHERE PersonID = 1

SELECT @@TRANCOUNT AS OpenTransactions

COMMIT TRAN

SELECT @@TRANCOUNT AS OpenTransactions

You can also use the ROLLBACK TRANSACTION statement to undo all data modifications made by a transaction.

Save Points in Transactions

Savepoints allow you to rollback a specific part of a transaction instead of rolling back the entire transaction. To define a savepoint, you can use the SAVE TRANSACTION syntax. Here’s an example:

BEGIN TRANSACTION

INSERT INTO Person
VALUES ('Mouse', 'Micky', '500 South Buena Vista Street, Burbank', 'California', 43)

SAVE TRANSACTION InsertStatement

DELETE Person WHERE PersonID = 3

SELECT * FROM Person

ROLLBACK TRANSACTION InsertStatement

COMMIT

SELECT * FROM Person

Auto Rollback Transactions

In SQL Server, if one of the SQL statements within a transaction returns an error, all modifications made by the transaction are rolled back automatically. Here’s an example:

BEGIN TRAN

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

UPDATE Person
SET Age = 'MiddleAge'
WHERE PersonID = 7

SELECT * FROM Person

COMMIT TRAN

Marked Transactions

SQL Server allows you to mark and add a description to a specific transaction in the log files. This can be useful for identifying the exact time of data modifications. Here’s an example:

BEGIN TRAN DeletePerson WITH MARK 'MarkedTransactionDescription'

DELETE Person WHERE PersonID BETWEEN 3 AND 4

COMMIT TRAN DeletePerson

The logmarkhistory table in the msdb database stores details about each marked transaction.

Conclusion

Transactions are a vital part of relational database systems, ensuring data integrity and consistency. Understanding the different transaction modes and how to define and manage transactions in SQL Server is essential for building robust and reliable database applications.