ACID Properties

Demonstrating ACID Properties in MySQL with Accounts Table

In this blog, we’ll explore the ACID properties—Atomicity, Consistency, Isolation, and Durability—using a simple Accounts table in MySQL.

1. Setting Up the Table

We start by creating a database and the Accounts table with three sample records.

CREATE DATABASE IF NOT EXISTS BankDB;
USE BankDB;

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

SELECT * FROM Accounts;

1. Atomicity

Atomicity ensures that a transaction is all or nothing. Let’s transfer money between accounts:

USE BankDB;

BEGIN;

UPDATE Accounts
SET balance = balance - 1000
WHERE acc_no = 101;

UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;

SELECT * FROM Accounts;

1. Consistency

Consistency ensures that invalid data cannot be inserted. We enforce a rule that balance cannot be negative.

USE BankDB;

ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);

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

Observation:

MySQL rejects the insert because it violates the constraint:

ERROR 3819 (HY000): Check constraint 'chk_balance' is violated.

1. Isolation

Isolation ensures that concurrent transactions do not interfere with each other.

Session 1 – Updating:

USE BankDB;

BEGIN;

UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 101;

Session 2 – Reading concurrently:

USE BankDB;

SELECT * FROM Accounts WHERE acc_no = 101;

Observation:

Session 2 cannot see uncommitted changes from Session 1.

After committing Session 1, Session 2 will see the updated balance.

1. Durability

Durability ensures that committed data persists even after a crash or restart.

USE BankDB;

BEGIN;

UPDATE Accounts
SET balance = balance + 200
WHERE acc_no = 102;

COMMIT;

SELECT * FROM Accounts;

Observation:

After committing and restarting the database, the updated balance remains.

Conclusion

We have successfully demonstrated all ACID properties in MySQL using the Accounts table:

Atomicity: Transactions are all-or-nothing.

Consistency: Database rules prevent invalid data.

Isolation: Concurrent transactions do not interfere.

Durability: Committed data persists.

Deliverables: SQL scripts and screenshots are included above for reference.