ACID properties ensure reliability and correctness of database transactions.
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:
Atomicity ensures that a transaction is treated as a single, indivisible unit of work.
Either all the operations within a transaction are completed successfully, or none are applied.
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:
Consistency ensures that a transaction brings the database from one valid state to another, maintaining all defined rules, constraints, and relationships.
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:
Isolation ensures that concurrent transactions do not interfere with each other.
Each transaction should execute as if it were the only one running, preventing issues like dirty reads, lost updates, or uncommitted data access.
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:
Durability guarantees that once a transaction has been committed, its results are permanently saved in the database, even if the system crashes or power fails.
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:
The ACID properties — Atomicity, Consistency, Isolation, and Durability — form the foundation of reliable transaction management in database systems.
They ensure that all transactions are accurate, consistent, isolated, and permanent, protecting data integrity and maintaining trust in the system. Together, these principles make database operations robust, even in cases of errors, concurrent access, or failures.
I would like to thank @santhoshnc for his guidance and support in completing this DBMS assignment.
dbms #sql #oracle #plsql #transactions #acid #database #assignment #learning
Top comments (0)