In database systems, ACID stands for Atomicity, Consistency, Isolation, and Durability. These four key properties ensure that database transactions are processed reliably — even in the case of errors, system crashes, or multiple users accessing the same data at once.
A transaction is a single logical unit of work that can include one or more SQL operations (like INSERT, UPDATE, or DELETE).
For example, transferring money between two bank accounts involves deducting an amount from one account and adding it to another. Both actions must succeed together — or fail completely — to maintain correctness.
The ACID properties guarantee this reliability:
Atomicity: Ensures that all steps in a transaction are treated as one single “all or nothing” operation.
Consistency: Ensures that the database remains in a valid state before and after the transaction.
Isolation: Ensures that transactions occurring at the same time do not interfere with each other.
Durability: Ensures that once a transaction is committed, its changes are permanent, even after a system crash.
Together, these properties form the foundation of trustworthy database systems, keeping your data accurate and secure.
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
);
INSERT INTO Accounts VALUES
(101, 'Alice', 1000),
(102, 'Bob', 800),
(103, 'Charlie', 1200);
⚙️ Step 2: Demonstrate Atomicity
Goal: Show that a failed transaction rolls back — no partial update remains
-- Start Transaction
START TRANSACTION;
-- Alice sends 500 to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Bob receives 500
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
-- Simulate an error or rollback
ROLLBACK;
-- Check balances again
SELECT * FROM Accounts;
Explanation:
After rollback, balances of Alice and Bob should remain the same (1000 and 800).
That shows Atomicity — either all steps complete or none do.
Step 3: Demonstrate Consistency
Goal: Database rules prevent invalid data.
Try to insert a record with a negative balance:
INSERT INTO Accounts VALUES (104, 'David', -500);
Step 4: Demonstrate Isolation
Goal: Two sessions should not interfere.
START TRANSACTION;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 101;
2️⃣ Session 2 (in another SQL tab/window)
SELECT * FROM Accounts WHERE acc_no = 101;
💾 Step 5: Demonstrate Durability
Goal: Data persists even after DB restart.
START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 103;
COMMIT;
Conclusion:
This experiment shows how ACID ensures reliability in SQL transactions — no data loss, corruption, or interference.
Top comments (0)