Understanding ACID principles in the relational databases
In the relational database world, ensuring data integrity and consistency is critical for any system that handles transactional data. The ACID principles – Atomicity, Consistency, Isolation, and Durability – are the foundational principles that guide how databases manage transactions to ensure data remains accurate and reliable. In this article, we will explain what ACID means in the context of relational databases and provide practical examples in the SQL Server environment to demonstrate how these principles work.
What are ACID principals?
ACID is an acronym that stands for four essential properties of database transactions:
- Atomicity
- Consistency
- Isolation
- Durability
These properties ensure that transactions in a relational database are processed reliably and that data integrity is maintained even in cases of system failures, crashes, or concurrent access.
Let’s take a deeper dive into each property:
1. Atomicity
Atomicity ensures that a transaction is treated as a single, indivisible unit. This means that all parts of the transaction must succeed or the entire transaction must fail. If any part of the transaction fails, the database will revert to its original state, ensuring no partial or inconsistent data is saved.
SQL Server Example of Atomicity
The transaction here is to transfer $100 from Alice’s account to Bob’s account. If any part of the transaction fails, both operations are rolled back to ensure atomicity.
--Step 1: Create the Accounts Table
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountName VARCHAR(100),
Balance DECIMAL(10, 2)
);
-- Insert sample data into Accounts table
INSERT INTO Accounts (AccountID, AccountName, Balance)
VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 300.00);
--Step 2: Atomic Transaction Example
BEGIN TRANSACTION;
-- Deduct $100 from Alice's account (AccountID = 1)
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;
-- Add $100 to Bob's account (AccountID = 2)
UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 2;
-- Check for errors, and if any error occurs, roll back the transaction
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION;
PRINT 'Transaction failed, rolled back';
RETURN;
END
-- Commit the transaction if everything is successful
COMMIT TRANSACTION;
PRINT 'Transaction succeeded, committed';
2. Consistency
Consistency ensures that a transaction takes the database from one valid state to another. A transaction must follow all database rules (such as constraints, triggers, and foreign keys), and the database must remain in a consistent state before and after the transaction.
SQL Server Example of Consistency
In this example, we’ll add a CHECK constraint to ensure that no account balance can go negative, maintaining consistency.
Imagine a database with a table for bank accounts. If we have a constraint that ensures account balances cannot be negative, a transaction that tries to deduct more money than is available should fail.
Here, we attempt to deduct more money from Alice’s account than is available. The database will prevent this operation, ensuring the consistency of the data.
--Step 1: Create the Accounts Table with a Constraint
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountName VARCHAR(100),
Balance DECIMAL(10, 2) CHECK (Balance >= 0) -- Constraint to ensure balance is non-negative
);
-- Insert sample data into Accounts table
INSERT INTO Accounts (AccountID, AccountName, Balance)
VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 300.00);
--Step 2: Consistency Violation Example
BEGIN TRANSACTION;
-- Attempt to deduct $600 from Alice's account (which only has $500)
UPDATE Accounts
SET Balance = Balance - 600
WHERE AccountID = 1;
-- Check for errors and roll back if any issue arises
IF @@ERROR <> 0
BEGIN
ROLLBACK TRANSACTION;
PRINT 'Transaction failed due to consistency violation';
RETURN;
END
-- Commit the transaction if all goes well
COMMIT TRANSACTION;
PRINT 'Transaction succeeded';
In this case, the constraint will prevent the balance from going below zero, so the transaction will fail, ensuring that the database remains consistent.
3. Isolation
Isolation ensures that transactions are executed independently of each other, even if they occur concurrently. The result of one transaction should not be visible to others until it is committed. This property prevents issues such as dirty reads, where one transaction reads uncommitted data from another.
SQL Server provides different isolation levels to control the level of isolation between transactions. The most common levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.
SQL Server Example of Isolation
For this example, we’ll use Transaction Isolation Levels to demonstrate how isolation works. We’ll use the READ COMMITTED isolation level, which prevents dirty reads.
Step 1: Create the Accounts Table
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountName VARCHAR(100),
Balance DECIMAL(10, 2)
);
-- Insert sample data into Accounts table
INSERT INTO Accounts (AccountID, AccountName, Balance)
VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 300.00);
Step 2: Isolation Level Demonstration
We will simulate two transactions where Transaction 1 deducts $100 from Alice’s account, and Transaction 2 tries to read or modify Alice’s balance during the process.
Transaction 1 (User 1):
BEGIN TRANSACTION;
-- User 1 starts by deducting $100 from Alice's account
UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 1;
-- User 1 waits before committing (simulating delay)
WAITFOR DELAY '00:00:10'; -- Simulates a 10-second delay
Transaction 2 (User 2):
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
-- User 2 attempts to read Alice's balance during User 1's transaction
SELECT Balance FROM Accounts WHERE AccountID = 1;
-- Commit User 2's transaction immediately
COMMIT TRANSACTION;
In this scenario, Transaction 2 will only see the committed value of Alice’s balance due to the READ COMMITTED isolation level. If Transaction 1 has not committed its change yet, Transaction 2 will wait until the first transaction finishes.
If Transaction 1 has not yet committed, Transaction 2 will be blocked or delayed based on the isolation level. For example, if the isolation level is SERIALIZABLE, Transaction 2 will have to wait until Transaction 1 finishes, ensuring the data integrity of Account A. If the isolation level is READ COMMITTED, Transaction 2 can read the uncommitted value but won’t be able to update until the transaction is complete.
4. Durability
Durability ensures that once a transaction is committed, its changes are permanent, even in the event of a system failure, crash, or power loss. Once a transaction completes, the changes are saved to disk and cannot be rolled back.
SQL Server Example of Durability
Imagine that a transaction commits a change, but before the changes are written to disk, the database server crashes. With durability, once SQL Server recovers, the changes made by the committed transaction will still be there.
To demonstrate this, you can perform the following steps:
--Step 1: Create the Accounts Table
CREATE TABLE Accounts (
AccountID INT PRIMARY KEY,
AccountName VARCHAR(100),
Balance DECIMAL(10, 2)
);
-- Insert sample data into Accounts table
INSERT INTO Accounts (AccountID, AccountName, Balance)
VALUES
(1, 'Alice', 500.00),
(2, 'Bob', 300.00);
--Step 2: Durability Demonstration
BEGIN TRANSACTION;
-- Deduct $50 from Alice’s account
UPDATE Accounts
SET Balance = Balance - 50
WHERE AccountID = 1;
-- Commit the transaction
COMMIT TRANSACTION;
-- Simulate a server crash by shutting down the SQL Server (for illustration purposes)
SHUTDOWN; -- In a real scenario, the server crash would be unexpected
Once SQL Server restarts, we check the table to verify that the $50 deduction from Alice’s account remains, showing that the transaction’s change was durable:
-- After SQL Server restarts, check Alice's balance
SELECT * FROM Accounts WHERE AccountID = 1;
The output should show that Alice’s balance is $450, reflecting the durability of the transaction even after the server shutdown.
The ACID properties—Atomicity, Consistency, Isolation, and Durability—are essential for ensuring the reliability, accuracy, and safety of transactions in relational databases like SQL Server. By following these principles, SQL Server can handle transactions in a way that ensures data integrity, even in cases of system failures, concurrent access, or data conflicts. Understanding and applying these concepts is crucial for database administrators and developers working in environments where transactional accuracy is critical.
Share on