INTRODUCTION
ACID properties are a set of principles that guarantee the reliability of database transactions. They ensure that data remains consistent and accurate, even when multiple operations are happening at the same time or when a system failure occurs. For this assignment, I used Oracle Live SQL to demonstrate these fundamental properties.
What are ACID Properties?
Atomicity: An "all-or-nothing" principle. A transaction either completes entirely or doesn't happen at all. If any part fails, the entire transaction is rolled back.
Consistency: A transaction brings the database from one valid state to another. It ensures that all data integrity rules (like constraints) are maintained.
Isolation: Concurrent transactions don't interfere with each other. Each transaction appears to be the only one running, even if many are happening simultaneously.
Durability: Once a transaction is committed, its changes are permanent and will survive a system crash or restart.
To begin, I created a simple Accounts table and inserted some sample data.
SQL
-- Initial setup
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Alice', 1000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (102, 'Bob', 500);
INSERT INTO Accounts (acc_no, name, balance) VALUES (103, 'Charlie', 2000);
SELECT * FROM Accounts;
Atomicity: The All-or-Nothing Transfer
To demonstrate atomicity, I simulated a failed money transfer from Alice to Bob. The transaction involves two steps: decreasing Alice's balance and increasing Bob's. I used a ROLLBACK to simulate a failure after the first step.
SQL
-- Before the transaction
SELECT * FROM Accounts WHERE acc_no IN (101, 102);
-- Start a transaction
SET TRANSACTION READ WRITE;
-- Step 1: Transfer $200 from Alice (101)
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 101;
-- Intentionally rollback
ROLLBACK;
-- After rollback, the balance returns to the original state
SELECT * FROM Accounts WHERE acc_no IN (101, 102);
As the screenshot shows, even though the UPDATE statement was executed, the ROLLBACK command ensured that no partial changes were saved. The balances for both Alice and Bob remained at their original values, proving the all-or-nothing nature of the transaction.
Consistency: Enforcing Data Rules
Consistency ensures that a transaction maintains the integrity of the database. I demonstrated this by adding a CHECK constraint to the Accounts table to prevent negative balances.
SQL
-- Drop the existing table and recreate it with a CHECK constraint
DROP TABLE Accounts;
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0)
);
-- Reinsert valid data
INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Alice', 1000);
-- ... (rest of the valid inserts)
-- Attempt to insert a record with a negative balance
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -100);
The INSERT statement with a negative balance was immediately rejected by the database. The screenshot below shows the specific error message, confirming that the CHECK constraint prevented the database from entering an invalid state.
Isolation: Concurrent Transactions
Isolation ensures that concurrent transactions don't affect each other. I used two separate Oracle Live SQL tabs (representing two different sessions) to demonstrate this.
Session 1 (The Updater):
In the first session, I started a transaction to update Alice's balance but did not commit it.
SQL
-- Session 1
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 101;
-- The transaction is still open...
Session 2 (The Reader):
In the second session, I ran a SELECT query on Alice's account. The database returned the old balance, as the changes from Session 1 were not yet committed and therefore not visible to other sessions.
SQL
-- Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
-- The result will show the original balance (1000)
Finally, I went back to Session 1 and committed the transaction. I then re-ran the query in Session 2, and this time, the new, updated balance was visible.
SQL
-- Back in Session 1
COMMIT;
-- Back in Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
-- The result will now show the new balance (900)
This demonstrates that the uncommitted changes from one transaction were isolated and invisible to another until the COMMIT command was executed.
Durability: Permanent Changes
Durability ensures that once a transaction is committed, the changes are permanent. I tested this by committing an update and then logging out and back into Oracle Live SQL (simulating a database restart) to check if the data persisted.
SQL
-- Update Charlie's account and commit
UPDATE Accounts SET balance = balance + 50 WHERE acc_no = 103;
COMMIT;
After committing the change, I started a new, fresh session. A simple SELECT query confirmed that the new balance for Charlie was still there, proving that the committed change was durable.
SQL
-- In a new session
SELECT * FROM Accounts WHERE acc_no = 103;
CONCLUSION
This exercise with Oracle Live SQL provided a practical demonstration of the ACID properties. It highlighted how these principles are fundamental to ensuring data integrity, reliability, and security in any database system, from a simple application to a large-scale enterprise system.
Top comments (0)