ACID Properties & SQL Transactions

Summary

This post walks through creating an Accounts table, inserting seed data, updating balances with transactions, and demonstrating a CHECK constraint violation. It also ties each step to ACID properties with clear visuals.

What you’ll learn

• Create/insert into an Accounts table and verify results with SELECT
• Run UPDATE + COMMIT cycles and validate row changes
• Trigger and understand a CHECK constraint error for data integrity
• Map each step to ACID: Atomicity, Consistency, Isolation, Durability

1) Setup: create and insert

CREATE TABLE Accounts (
acc_no NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
balance NUMBER(12,2) CONSTRAINT chk_balance CHECK (balance >= 0)
);

INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Alice', 4000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (102, 'Bob', 4000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (103, 'Charlie', 2000);
COMMIT;

SELECT * FROM Accounts;

2) Transactions: update and commit

-- Apply bonus to 101 and 102
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;
COMMIT;

SELECT * FROM Accounts;

3) Quick verification: single account

SELECT balance FROM Accounts WHERE acc_no = 101;

4) Constraint demo: negative balance (expected error)

-- Integrity check: should fail
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);

ACID refresher with visuals

Atomicity: A transaction is all‑or‑nothing—either the whole unit commits or none persists.

Consistency: Only valid states reach the database; constraints enforce rules at the boundary.

Isolation: Concurrent work shouldn’t interfere; each transaction sees a consistent snapshot.

Durability: After COMMIT, data survives failures and session ends.

-- Create + seed
CREATE TABLE Accounts (
acc_no NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
balance NUMBER(12,2) CONSTRAINT chk_balance CHECK (balance >= 0)
);

INSERT INTO Accounts (acc_no, name, balance) VALUES (101, 'Alice', 4000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (102, 'Bob', 4000);
INSERT INTO Accounts (acc_no, name, balance) VALUES (103, 'Charlie', 2000);
COMMIT;

SELECT * FROM Accounts;

-- Updates
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;
COMMIT;

-- Verifications
SELECT balance FROM Accounts WHERE acc_no = 101;
SELECT * FROM Accounts;

-- Constraint violation demo (will error)
INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);

Credits

Thank you, @santhoshnc Sir, for the assignment and guidance—this write‑up is the direct result of that mentorship.