ACID properties ensure reliability and correctness of database transactions.
Step 1: Create Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);
Insert Sample Data
INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Ravi', 5000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (102, 'Meena', 7000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (103, 'Kumar', 6000);
1️⃣ Atomicity:
A transaction is all-or-nothing.
Either all operations in a transaction are executed successfully, or none are applied.
Ensures no partial updates occur in case of errors.
Scenario: Transfer ₹1000 from Ravi to Meena. If an error occurs, rollback to prevent partial updates.
_
-- Deduct ₹1000 from Ravi_
UPDATE Accounts
SET balance = balance - 1000
WHERE acc_no = 101;
_-- Simulate an error_
INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'ErrorTest', 1000);
-_- Add ₹1000 to Meena_
UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;
_-- Rollback transaction_
ROLLBACK;
-- Verify final balances
SELECT * FROM Accounts;
_Explanation:
_
If an error occurs during transfer, ROLLBACK ensures no partial changes remain.
Atomicity ensures transactions are all-or-nothing.
2️⃣Consistency:
A transaction must bring the database from one valid state to another.
Database rules (constraints, triggers) are preserved.
Prevents invalid data like negative balances or duplicate primary keys.
Scenario: Database should reject invalid data like negative balance.
-- Fix existing negative balances if any
UPDATE Accounts
SET balance = 0
WHERE balance < 0;
-- Add CHECK constraint
ALTER TABLE Accounts
ADD CONSTRAINT chk_balance CHECK (balance >= 0);
-- Test invalid insert
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'Anita', -500);
_Explanation:
_
Constraint prevents negative balances, maintaining data consistency.
Anita’s balance is set to 0 to satisfy the constraint.
3️⃣Isolation:
Transactions operate independently of each other.
Changes made in one transaction are not visible to others until committed.
Ensures concurrent transactions do not interfere incorrectly.
Scenario: Observe concurrent transactions.
_Session 1: Update Kumar’s balance_
UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 103;
-- Do not commit yet
_Session 2: Read Kumar’s balance_
SELECT * FROM Accounts
WHERE acc_no = 103;
Explanation:
Session 2 does not see uncommitted changes from Session 1.
Isolation ensures concurrent transactions do not interfere incorrectly.
4️⃣Durability:
Once a transaction is committed, its changes are permanent, even in the event of a system crash or failure.
Guarantees the persistence of committed data.
Scenario: Commit a transaction and ensure changes persist after DB restart.
UPDATE Accounts
SET balance = balance + 200
WHERE acc_no = 101;
COMMIT;
-- After reconnecting/restarting DB
SELECT * FROM Accounts;
Explanation:
Changes persist permanently after commit.
Durability ensures committed data survives failures.
Conclusion:
_Atomicity: _Transactions are “all or nothing”.
Consistency: Database enforces valid data.
Isolation: Concurrent transactions do not interfere.
Durability: Committed changes are permanent.
I would like to thank @santhoshnc for his guidance and support in completing this DBMS assignment.
Top comments (0)