Listen to this Post
ACID stands for Atomicity, Consistency, Isolation, and Durability—four key properties that ensure reliable database transactions.
1. Atomicity
- Ensures a transaction is treated as a single, indivisible unit.
- If any part fails, the entire transaction is rolled back.
- Example: A bank transfer must complete both debit and credit; otherwise, it reverts.
SQL Command:
BEGIN TRANSACTION; UPDATE accounts SET balance = balance - 100 WHERE user_id = 1; UPDATE accounts SET balance = balance + 100 WHERE user_id = 2; COMMIT; -- If both succeed -- ROLLBACK; -- If any fails
2. Consistency
- Ensures data transitions from one valid state to another.
- Enforces constraints (e.g., no negative balances).
- Example: Preventing overdrafts in banking apps.
SQL Constraint Example:
ALTER TABLE accounts ADD CONSTRAINT chk_balance CHECK (balance >= 0);
3. Isolation
- Concurrent transactions don’t interfere.
- Achieved via locking or Multi-Version Concurrency Control (MVCC).
- Example: Two users booking the same seat—only one succeeds.
PostgreSQL Isolation Level:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
4. Durability
- Committed transactions survive crashes.
- Achieved via write-ahead logging (WAL).
Linux Command to Force Write to Disk:
sync
You Should Know:
Database Recovery Commands
- MySQL:
-- Check transaction logs SHOW ENGINE INNODB STATUS;
-
PostgreSQL:
pg_resetwal -f /var/lib/postgresql/data
Testing Atomicity
- Bash Script to Simulate a Crash:
!/bin/bash echo "Simulating crash after transaction start..." kill -9 $(pgrep mysql)
Isolation Levels in Practice
-
Read Uncommitted (Risk of Dirty Reads):
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
-
Repeatable Read (Default in MySQL):
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Durability with fsync
- Linux Command to Disable fsync (For Testing Only!):
echo "fsync = off" >> /etc/postgresql/postgresql.conf
What Undercode Say:
ACID compliance is critical for financial, healthcare, and e-commerce systems. Always:
– Use `BEGIN TRANSACTION` and `COMMIT` explicitly.
– Test isolation levels under high concurrency.
– Monitor WAL logs for durability.
Expected Output:
Transaction committed successfully. No dirty reads detected. Database recovered after crash.
Prediction:
As distributed databases grow, ACID compliance will evolve with hybrid models (e.g., Google Spanner’s “External Consistency”).
Relevant Course:
Advanced Database Systems – Stanford
References:
Reported By: Bonagirisandeep What – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
