DEV Community

Cover image for ACID Properties with SQL Transactions in DBMS
Jaswant Karun
Jaswant Karun

Posted on

ACID Properties with SQL Transactions in DBMS

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);

Run it.
👉 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:

Atomicity: Transactions are “All or Nothing”.

Consistency: Database enforces valid data.

Isolation: Concurrent transactions do not interfere.

Durability: Committed changes are permanent.

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

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

Top comments (0)