ACID stands for Atomicity, Consistency, Isolation, Durability – the four key properties that ensure reliable database transactions. In this tutorial, we’ll demonstrate ACID concepts using MySQL 8.0 with practical examples.
We’ll use a simple Accounts table:
CREATE TABLE Accounts(
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);
Screenshot:
1️⃣ Atomicity
Goal: Ensure transactions are all-or-nothing.
1.Start a transaction to transfer money from Alice to Bob.
2.Rollback midway and verify no partial updates occur.
-- Start transaction
START TRANSACTION;
-- Deduct 1000 from Alice
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
-- Simulate an error (e.g., invalid operation)
-- For demonstration, we rollback instead of committing
ROLLBACK;
-- Check balances
SELECT * FROM Accounts;
Expected Output (balances unchanged):
+--------+---------+---------+
| acc_no | name | balance |
+--------+---------+---------+
| 101 | Alice | 5000 |
| 102 | Bob | 3000 |
| 103 | Charlie | 7000 |
+--------+---------+---------+
The rollback ensures no partial update occurs.
screenshot:
2️⃣ Consistency
Goal: Ensure the database remains in a valid state.
1.Try inserting a record with a negative balance.
2.Define a CHECK constraint to reject invalid balances.
ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- Attempt invalid insert
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);
Expected Result:
1.MySQL rejects the insert with an error due to the constraint.
2.Database remains consistent.
Screenshot:
3️⃣ Isolation
Goal: Ensure concurrent transactions do not interfere.
Open two MySQL sessions:
Session 1: Update Alice’s balance.
Session 2: Read Alice’s balance before Session 1 commits.
Session 1:
START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 101;
-- Do not commit yet
Session 2:
SELECT * FROM Accounts WHERE acc_no = 101;
Expected Output:
Session 2 does not see uncommitted changes (depending on isolation level).
Commit Session 1:
COMMIT;
Screenshot:
4️⃣ Durability
Goal: Ensure changes persist even after a crash.
Commit a transaction:
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;
-Restart MySQL server.
-Check balances:
SELECT * FROM Accounts;
Expected Output:
-The update remains permanent.
Screenshot:
+--------+---------+---------+
| acc_no | name | balance |
+--------+---------+---------+
| 101 | Alice | 5000 |
| 102 | Bob | 3500 |
| 103 | Charlie | 7000 |
+--------+---------+---------+
✅ Full SQL Script
You can save the following in ACID_demo.sql and run in MySQL CLI:
CREATE DATABASE IF NOT EXISTS ACID_demo;
USE ACID_demo;
CREATE TABLE Accounts(
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);
ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- Atomicity demo
START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
ROLLBACK;
-- Isolation demo (run in two sessions)
-- Session 1
START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 101;
-- Session 2: SELECT * FROM Accounts WHERE acc_no = 101;
-- Then commit Session 1
COMMIT;
-- Durability demo
START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;
SELECT * FROM Accounts;
Troubleshooting Tips
1.Rollback not working? Ensure you’re in a transaction using START TRANSACTION;.
2.Negative balances accepted? Add CHECK (balance >= 0) constraint.
3.Isolation behavior varies: MySQL default is REPEATABLE READ; change with SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;.
4.Durability: MySQL uses InnoDB engine for ACID support.
Top comments (0)