Database Transactions (ACID)
A database transaction is a unit of work that is treated as "a whole". It often consists of multiple operations (reads and writes). To ensure data integrity, relational databases typically adhere to the ACID properties.
1. The ACID Properties
Atomicity
"All or Nothing." The entire transaction takes place at once or doesn't happen at all. If any part of the transaction fails, the entire transaction fails, and the database state is left unchanged (Rolled Back).
- Example: Transferring money. If debiting Account A succeeds but crediting Account B fails, the debit must be undone.
Consistency
"Valid State." The database must move from one valid state to another valid state. The transaction must adhere to all defined rules, constraints, cascades, and triggers.
- Example: If the database schema says "Balance cannot be negative", a transaction that results in a negative balance will fail.
Isolation
"Private Workspace." Multiple transactions occurring at the same time must not affect each other's execution. The intermediate state of a transaction is invisible to other transactions.
- Example: If two people try to buy the last ticket simultaneously, they shouldn't both see it as "Available" and successfully buy it.
Durability
"Written in Stone." Once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors.
- Example: Once the bank says "Transfer Complete", your money is safe even if the server catches fire 1 second later (thanks to transaction logs).
2. Isolation Levels
Isolation is a spectrum. Higher isolation means better consistency but lower performance (locking).
- Read Uncommitted: Lowest level. Transactions can see uncommitted changes from others (Dirty Reads). Fast but dangerous.
- Read Committed: A transaction only sees changes that have been committed. Prevents Dirty Reads. (Default in PostgreSQL, SQL Server).
- Repeatable Read: Ensures that if you read a row twice in a transaction, you get the same result. Prevents Non-Repeatable Reads. (Default in MySQL).
- Serializable: Highest level. Transactions are executed as if they were run serially (one after another). Prevents Phantom Reads. Slowest.
3. Example (SQL)
BEGIN TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE id = 1;
UPDATE Accounts SET balance = balance + 100 WHERE id = 2;
-- If everything is okay:
COMMIT;
-- If something goes wrong:
-- ROLLBACK;programming/database-basics programming/cap-theorem programming/saga-pattern