DEV Community

Cover image for ACID Properties with SQL Transactions in DBMS
Jerlin vanessa Vincent paul
Jerlin vanessa Vincent paul

Posted on

ACID Properties with SQL Transactions in DBMS

When working with relational databases, transactions are the building blocks that ensure reliability. They follow the ACID properties:

Atomicity → All or nothing

Consistency → Valid state before & after

Isolation → Transactions don’t interfere

Durability → Changes survive crashes

In this blog, we’ll explore ACID with SQL scripts using an Accounts table.

Creating a Database in MySql

CREATE DATABASE acid_demo;
USE acid_demo;

Step 1: Setup the Accounts Table

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, 'Sarah', 5000),
(2, 'Jessie', 3000),
(3, 'Benson', 7000);

Run it.
Output: 3 rows inserted

Check the table:

SELECT * FROM Accounts;

Output:
acc_no | name | balance
1 | Sarah| 5000
2 | Jessie | 3000
3 | Benson | 7000

Atomicity

Definition: A transaction is atomic, meaning either all operations succeed or none do.

Example: Transfer 500 from Sarah to Jessie, then rollback

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 (Sarah=5000, Jessie=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: Sarah=4500, Jessie=3500.
Committed → permanent update.

Consistency

Definition: A transaction must bring the database from one valid state to another. Rules like constraints must never be violated.

Example: Try inserting negative balance

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

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

Isolation

Definition: Transactions executing at the same time should not interfere with each other.

Example: Two sessions

Session 1 (updating):

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

Session 1 sees the reduced balance (2500).

Session 2 (reading at same time):

SELECT balance FROM Accounts WHERE acc_no = 1;

ROLLBACK;

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

Durability

Definition: Once a transaction is committed, its changes persist even if the system crashes.

Example: Commit, restart DB, check again

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

Check:

SELECT * FROM Accounts WHERE acc_no = 3;

Benson’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.

🚀 Wrap Up

We demonstrated the ACID properties using SQL:

🔹 Atomicity → Rollback prevents partial updates

🔹 Consistency → Constraints keep data valid

🔹 Isolation → Transactions run independently

🔹 Durability → Committed changes survive crashes

These principles ensure that databases remain reliable, safe, and trustworthy, even under concurrent workloads or unexpected failures.

Thanks @santhoshnc Sir for his guidance and support and for Everything

dbms #MySql #oracle #transactions #acid #database #learning

Top comments (0)