🔥ACID Properties with SQL Transactions

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 !!!