SQL Server ACID Properties

Introduction

In the world of databases, reliability and consistency are of utmost importance. When dealing with critical and sensitive data, organizations must ensure that their database systems maintain data integrity, even in the face of failures or concurrent transactions. This is where the concept of ACID properties comes into play.

SQL Server ACID properties

ACID

ACID stands for Atomicity, Consistency, Isolation, and Durability, and it represents a set of principles that ensure the reliability and robustness of transactions in a database management system, such as SQL Server. In this article, we'll explore each of the ACID properties with examples to better understand their significance.

Atomicity

Atomicity refers to the indivisibility or all-or-nothing nature of a transaction. It means that a transaction should be treated as a single unit of work, and either all the operations within the transaction are executed successfully or none of them are. If any part of the transaction fails, the entire transaction is rolled back, and the database returns to its original state.

To better understand atomicity, let's consider an example.

Suppose we have a database with two tables: "Customers" and "Orders." The "Customers" table contains information about the customers, and the "Orders" table contains information about orders placed by those customers. We want to update both tables in a single transaction.

Let's say we want to perform the following two operations within a transaction:

Insert a new customer into the "Customers" table.
Insert a new order for that customer into the "Orders" table.

The SQL code for the transaction might look like this.

BEGIN TRANSACTION;

    -- Step 1: Insert a new customer into the "Customers" table
    INSERT INTO Customers (CustomerID, FirstName, LastName, Email)
    VALUES ('C001', 'Amit', 'Mohanty', '[email protected]');

    -- Step 2: Insert a new order for the customer into the "Orders" table
    INSERT INTO Orders (OrderID, CustomerID, OrderDate, TotalAmount)
    VALUES ('O001', 'C001', '2023-07-21', 100.00);

COMMIT TRANSACTION;

In this example, the transaction is started using the BEGIN TRANSACTION; statement. Within the transaction, we perform two operations: inserting a new customer and inserting a new order for that customer. Once both operations have been executed without any errors, we commit the transaction using COMMIT TRANSACTION.

Now, suppose an error occurs after inserting the new customer (Step 1), and the second operation (Step 2) is not executed. In this case, because of atomicity, the transaction will be rolled back, and no changes will be made to the database. This ensures that the database remains in a consistent state.

Atomicity guarantees that either all the steps within the transaction are successful or the database remains unchanged. There is no partial completion of the transaction, which helps maintain data integrity and consistency in the database.

Consistency

The consistency property ensures that a database transitions from one valid state to another valid state after a transaction is executed. It means that a transaction must maintain the integrity of the data and not violate any defined rules or constraints.

To explain consistency, let's consider an example involving a simple banking database. We have two tables: "Accounts" to store account information and "Transactions" to store transaction records.

The "Accounts" table structure.

border="1" cellspacing="1" width="100%"> AccountNumber AccountHolder Balance 101 Amit 1000.00 102 Deepak 1500.00 103 Swarup 2000.00

The "Transactions" table structure.

TransactionID	AccountNumber	Amount	TransactionType	TransactionDate
1	101	-200.00	Withdrawal	2023-07-15 10:00:00.000
2	102	300.00	Deposit	2023-07-15 10:30:00.000
3	103	-100.00	Withdrawal	2023-07-15 11:00:00.000

Let's say we have two transactions happening concurrently.

Transaction 1

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance - 100 WHERE AccountNumber = 101;
    INSERT INTO Transactions (AccountNumber, Amount, TransactionType, TransactionDate)
    VALUES (101, -100, 'Withdrawal', GETDATE());
COMMIT;

Transaction 2

BEGIN TRANSACTION;
    UPDATE Accounts SET Balance = Balance + 150 WHERE AccountNumber = 102;
    INSERT INTO Transactions (AccountNumber, Amount, TransactionType, TransactionDate)
    VALUES (102, 150, 'Deposit', GETDATE());
COMMIT;

These transactions are trying to withdraw 100 from Amit's account and deposit 150 to Deepak's account concurrently.

Now, let's discuss different consistency levels.

Read Uncommitted: This level allows dirty reads, meaning a transaction can read uncommitted changes made by other transactions.
Example

-- Session 1
BEGIN TRANSACTION
UPDATE Employees SET Salary = Salary + 1000 WHERE Department = 'IT'
-- Session 2
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT SUM(Salary) FROM Employees WHERE Department = 'IT'
-- The result may include the uncommitted changes made by Session 1

Read Committed: This level prevents dirty reads. A transaction can only read committed changes made by other transactions.
Example

-- Session 1
BEGIN TRANSACTION
UPDATE Employees SET Salary = Salary + 1000 WHERE Department = 'HR'
-- Session 2
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SELECT SUM(Salary) FROM Employees WHERE Department = 'HR'
-- The result will not include the uncommitted changes made by Session 1

Repeatable Read: This level ensures that if a transaction reads a certain set of data, it will get the same data if it reads again during the same transaction. However, it allows non-repeatable reads.
Example

-- Session 1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION
SELECT SUM(Salary) FROM Employees WHERE Department = 'Finance'
-- Session 2
-- Inserts new rows into the Finance department
INSERT INTO Employees (Name, Department, Salary) VALUES ('Abhilash Mohanty', 'Finance', 5000)
-- The SELECT statement in Session 1 may return a different result in subsequent reads

Serializable: This level provides the highest consistency. It ensures that a transaction cannot read or modify data that has been read or modified by another transaction until the first transaction is completed.
Example

-- Session 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION
SELECT SUM(Salary) FROM Employees WHERE Department = 'Sales'
-- Session 2
-- Inserts new rows into the Sales department
INSERT INTO Employees (Name, Department, Salary) VALUES ('Deepak Mohapatra', 'Sales', 4500)
-- Session 2 will be blocked until Session 1 completes its transaction

Let's say both transactions are executed at the "Read Committed" isolation level.

If Transaction 1 executes first, it will update Amit's balance and insert a withdrawal transaction. Then, if Transaction 2 executes, it will update Deepak's balance and insert a deposit transaction. The changes are made separately, and each transaction is unaware of the other's actions until they are committed.

After both transactions are committed, the database remains consistent.

Accounts table

AccountNumber	AccountHolder	Balance
101	Amit	900.00
102	Deepak	1650.00
103	Swarup	2000.00

Transactions table

TransactionID	AccountNumber	Amount	TransactionType	TransactionDate
1	101	-200.00	Withdrawal	2023-07-15 10:00:00.000
2	102	300.00	Deposit	2023-07-15 10:30:00.000
3	103	-100.00	Withdrawal	2023-07-15 11:00:00.000
4	101	-100.00	Withdrawal	2023-07-21 10:00:00.000
5	102	150	Deposit	2023-07-21 10:30:00.000

By maintaining consistency through isolation levels and transaction handling, SQL Server ensures that the data remains accurate and reliable even in a multi-user, concurrent environment.

Isolation

Isolation ensures that multiple transactions can operate concurrently without interfering with each other. Each transaction should be executed in isolation as if it were the only transaction running on the database. This property prevents conflicts between transactions and maintains data integrity.

To understand isolation better, consider two transactions running simultaneously. Transaction A transfers 200 from account '101' to account '103', while Transaction B transfers 150 from account '103' to account '102'.

-- Transaction A
BEGIN TRANSACTION;

    UPDATE BankAccounts
       SET Balance = Balance - 200
     WHERE AccountNumber = '101';

    UPDATE BankAccounts
       SET Balance = Balance + 200
     WHERE AccountNumber = '103';

COMMIT TRANSACTION;

-- Transaction B
BEGIN TRANSACTION;

    UPDATE BankAccounts
       SET Balance = Balance - 150
     WHERE AccountNumber = '103';

    UPDATE BankAccounts
       SET Balance = Balance + 150
     WHERE AccountNumber = '102';

COMMIT TRANSACTION;

With proper isolation, both transactions will execute without affecting each other. For instance, if Transaction A is rolled back due to a failure, it won't impact the outcome of Transaction B.

Durability

Durability guarantees that once a transaction is committed, its changes are permanent and will survive any subsequent failures, including power outages or crashes. The committed data becomes a permanent part of the database, ensuring long-term data reliability.

To understand durability better, let's consider an example.

Suppose we have a simple SQL Server database with a table called "Customers" that stores customer information, and we want to add a new customer record to the database.

Initial State of the "Customers" table.

CustomerID	CustomerName	Age	Email
1	Amit Mohanty	30	[email protected]

The user initiates a transaction to add a new customer:

BEGIN TRANSACTION;

    INSERT INTO Customers (CustomerName, Age, Email)
    VALUES ('Abhilash Mishra', 28, '[email protected]');

The transaction is executed and waits for confirmation. Before the transaction is committed, the system encounters a power outage.

Without the durability property, the changes made by the transaction might be lost, and the database could revert to its previous state when it comes back online. However, thanks to the durability property, SQL Server ensures that the changes are preserved and committed to the database permanently.

After the system recovers from the power outage, the database will have the following state.

CustomerID	CustomerName	Age	Email
1	Amit Mohanty	30	[email protected]
2	Abhilash Mishra	28	[email protected]

Conclusion

ACID properties play a vital role in ensuring the reliability and consistency of database transactions. Whether it's processing financial transactions, handling customer data, managing inventory, or adhering to the ACID properties, transactions ensure that data remains reliable and accurate, even in the face of system failures or concurrent operations. SQL Server, like many other mature database management systems, is built with ACID in mind, making it a reliable choice for various critical applications.

Hope this article will help the readers.

Happy Coding!