Transactions & Data Integrity
Learn how ACID transactions protect your data when things go wrong — from bank transfers to e-commerce orders.
Transaction Simulator
Simulate bank transfer transactions. Click each scenario to see how COMMIT and ROLLBACK protect account balances.
What Is a Transaction?
A transaction is a sequence of SQL statements treated as a single logical unit of work. Either all statements succeed and are committed, or none of them take effect. This protects your data from partial failures.
Classic example: transferring $200 from Alice to Bob requires two UPDATE statements. If the first succeeds but the second fails (network error, server crash), Alice loses money and Bob gains nothing — a catastrophe. Transactions prevent this.
Every database transaction must satisfy four properties: Atomicity (all or nothing), Consistency (data stays valid before and after), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).
COMMIT and ROLLBACK
A transaction begins with START TRANSACTION (or BEGIN). Changes are tentative until you either:
COMMIT— make all changes permanentROLLBACK— undo all changes since the transaction began
In MySQL, auto-commit is ON by default, meaning each individual statement is its own transaction. Turn it off with SET autocommit = 0 or use START TRANSACTION explicitly.
SAVEPOINT
SAVEPOINTs let you create intermediate checkpoints within a transaction. You can roll back to a savepoint without undoing the entire transaction — useful for complex multi-step operations.
Constraints and Data Integrity
Database constraints enforce rules at the schema level, automatically rejecting bad data:
NOT NULL— column must have a valueUNIQUE— no duplicate values allowedPRIMARY KEY— uniquely identifies each row, implies NOT NULL + UNIQUEFOREIGN KEY— references a valid row in another tableCHECK— custom condition must be true (e.g.,salary > 0)DEFAULT— provides a fallback value when none is given
Locking and Deadlocks
Databases use locks to isolate concurrent transactions. A row-level lock prevents other transactions from modifying the same row while it's being updated. If two transactions each lock a row the other needs, a deadlock occurs. Modern databases detect deadlocks and automatically roll back one transaction.
Check Your Understanding
1. Which ACID property guarantees that either ALL statements in a transaction succeed, or NONE of them take effect?
2. You run START TRANSACTION, then two UPDATEs, then discover an error. Which command undoes all changes since START TRANSACTION?
3. A column has a FOREIGN KEY constraint referencing customers(id). What happens if you INSERT a row with a customer_id that doesn't exist in the customers table?
4. Which constraint ensures that no two rows in a table have the same value in a given column, but still allows NULL values?