Transactions and Data Integrity
A transaction is a group of SQL statements that either all succeed or all fail together. Transactions are what make databases safe for critical operations like bank transfers, order processing, and medical records.
Key Concepts
ACID Properties
Databases guarantee ACID: Atomicity (all or nothing — no partial transactions), Consistency (data always follows its rules), Isolation (concurrent transactions do not interfere), Durability (committed data survives crashes). These four properties make databases trustworthy.
BEGIN, COMMIT, ROLLBACK
Wrap operations in a transaction: BEGIN; UPDATE accounts SET balance=balance-100 WHERE id=1; UPDATE accounts SET balance=balance+100 WHERE id=2; COMMIT. If anything fails, ROLLBACK undoes all changes. The money never disappears.
Constraints
Constraints enforce rules at the database level: NOT NULL (field required), UNIQUE (no duplicates), CHECK (value must pass condition), FOREIGN KEY (must reference valid row). Even if application code has a bug, the database rejects invalid data.
🆕 Transaction Simulator
Simulate a bank transfer. See how transactions protect your data!
✅ Check Your Understanding
1. What does ACID stand for in databases?
2. What does ROLLBACK do?
3. Why are database constraints valuable?