ACID Properties with SQL Transactions in DBMS

Introduction: Understanding ACID Properties through SQL Transactions

In the world of database management, ensuring data reliability and consistency is paramount. This is where the ACID properties Atomicity, Consistency, Isolation, and Durability come into play. These principles form the backbone of transaction management, guaranteeing that database operations are executed safely, even in the face of errors, system crashes, or concurrent access.

In this blog, we’ll explore how to demonstrate each of the ACID properties practically using SQL transactions in MySQL. Starting from creating a simple database, we'll walk through examples that showcase how ACID principles maintain data integrity and provide a robust framework for handling complex operations.

Let’s dive in by setting up our demo environment and then uncover the power of ACID in action.

Demonstrating ACID Properties with SQL Transactions:-

Creating a Database in MySql

CREATE DATABASE acid_demo;
USE acid_demo;

Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT).

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
) ENGINE=InnoDB;

Insert 3 sample rows.

INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Alice', 5000),
(2, 'Bob', 3000),
(3, 'Charlie', 7000);

Output: 3 rows inserted.

Check the table:

SELECT * FROM Accounts;

Output:
acc_no | name | balance
1 | Alice | 5000
2 | Bob | 3000
3 | Charlie | 7000

Atomicity (Rollback vs Commit)

Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

ROLLBACK:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;
ROLLBACK;
SELECT * FROM Accounts;

Output:
balances remain unchanged (Alice=5000, Bob=3000).
This proves atomicity: either all updates happen, or none.
COMMIT:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;
COMMIT;
SELECT * FROM Accounts;

Output: Alice=4500, Bob=3500.
Committed → permanent update.

Consistency

Try inserting a wrong record:

INSERT INTO Accounts (acc_no, name, balance) VALUES (4, 'David', -500);

Output: Error – CHECK constraint failed.
Database rejects invalid data → consistency is preserved.

isolation (needs 2 query tabs)

Open two query tabs in Workbench:

Tab A = Session 1

Tab B = Session 2

In Tab A,

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 1;
-- Do not commit yet
SELECT balance FROM Accounts WHERE acc_no = 1;

Tab A sees the reduced balance (2500).

In Tab B,

SELECT balance FROM Accounts WHERE acc_no = 1;

If using default isolation (REPEATABLE READ), Tab B still sees old committed value (4500), not Tab A’s uncommitted update.

Now, back in Tab A:

ROLLBACK;

Both sessions see Alice back to 4500.
This shows isolation.

Durability

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
COMMIT;

Check:

SELECT * FROM Accounts WHERE acc_no = 3;

Charlie’s balance increases (7500).

Now restart MySQL server
Reconnect, run again:

USE acid_demo;
SELECT * FROM Accounts WHERE acc_no = 3;

Balance is still 7500.
This proves durability: committed changes survive restarts.

Conclusion: The Power of ACID in Reliable Database Transactions

ACID properties are fundamental to ensuring that database transactions are processed reliably and securely. By guaranteeing atomicity, consistency, isolation, and durability, databases can handle complex operations without risking data corruption or loss even under concurrent access or unexpected failures.

Through practical examples in MySQL, we've seen how these principles work behind the scenes to protect data integrity and provide a stable environment for applications. Whether you're a developer, database administrator, or tech enthusiast, understanding and leveraging ACID properties is essential for building trustworthy and efficient data-driven systems.

Embracing ACID transactions means embracing confidence in your data and that’s a critical step toward scalable, robust applications.