When working with databases, ACID properties are the foundation that ensures data reliability and consistency.
In this blog, we’ll explore each property with SQL examples using a simple Accounts table.
🏦 Step 1: Create Table and Insert Sample Data
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
INSERT INTO Accounts VALUES (101, 'Alice', 5000);
INSERT INTO Accounts VALUES (102, 'Bob', 3000);
INSERT INTO Accounts VALUES (103, 'Charlie', 7000);
SELECT * FROM Accounts;
🅰 Atomicity
Definition: Atomicity ensures a transaction is all or nothing. If one part fails, the whole transaction is rolled back.
Example: Transfer money from Alice → Bob, but rollback before completion.
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
ROLLBACK;
SELECT * FROM Accounts;
🅲 Consistency
Definition: Consistency ensures that data follows defined rules and constraints.
Example: Prevent negative balance using a CHECK constraint.
ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
INSERT INTO Accounts VALUES (104, 'David', -500);
🅸 Isolation
Definition: Isolation ensures that concurrent transactions do not interfere with each other.
Example: Run two sessions at once.
SESSION 1 :
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 103;
SESSION 2:
SELECT * FROM Accounts WHERE acc_no = 103;
🅳 Durability
Definition: Durability ensures once a transaction is committed, it remains even after a system crash.
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;
🚀 Conclusion
*Through this demo, we saw how ACID properties guarantee:
*Atomicity → no partial updates.
*Consistency → rules are enforced.
*Isolation → transactions don’t clash.
*Durability → committed changes survive crashes.
Thanks for @santhoshnc sir guiding me !!!
Top comments (0)