ACID property with SQL transactions

Introduction

When working with databases, ensuring the reliability and correctness of transactions is essential. This is where the ACID properties come into play. ACID stands for Atomicity, Consistency, Isolation, and Durability which are the core principles that guarantee trustworthy database transactions.
In this blog we are to focus on these ACID properties:

we will start by creating a database called acid_test then create a table named Accounts with the following structure:

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

Insert 3 Sample Rows
INSERT INTO Accounts VALUES (1, 'Alice', 5000);
INSERT INTO Accounts VALUES (2, 'Bob', 3000);
INSERT INTO Accounts VALUES (3, 'Charlie', 7000);

Displaying the data

Exploring ACID Properties

1. Atomicity: All or Nothing Transaction

BEGIN TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
-- Suppose an error happens here or we decide to rollback
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;

ROLLBACK; -- Cancel the transaction midway

Both updates are part of one transaction.
If any step fails or we explicitly roll back, no partial update remains.
Balances for Alice and Bob remain unchanged.

2. Consistency: Data Integrity Constraints

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

The database rejects the insert due to the CHECK (balance >= 0) constraint.
This enforces consistency, ensuring data adheres to business rules.

3. Isolation: Concurrent Transactions

Open two database sessions:

Session 1: Update Alice's balance.
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
-- Do NOT commit yet

Session 2: Read Alice's balance.
SELECT balance FROM Accounts WHERE acc_no = 1;

Session 2 may see the old balance (before update), depending on the isolation level.
This prevents dirty reads and shows how isolation controls concurrent transaction visibility.

4. Durability: Persisting Committed Data

After committing a transaction:
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;
COMMIT;

Now, restart the database server.
Query the Accounts table again:
SELECT * FROM Accounts WHERE acc_no = 3;

The data can still be viewed.

In conclusion ACID
By understanding and leveraging these principles, developers and database administrators can build applications that maintain data integrity even under complex and concurrent operations .Huge thanks to @santhoshnc for the guidance.