SQL Transaction および Isolation リファレンス
リファレンス 用 SQL transactions, isolation levels, および concurrency control.
ACID Properties
Atomicity: all operations commit or none do. Consistency: transactions move the database between valid states. Isolation: concurrent transactions appear serial. Durability: committed data survives crashes. Transactions begin with BEGIN/START TRANSACTION and end with COMMIT or ROLLBACK. SAVEPOINT creates partial rollback points. Autocommit mode runs each statement as a transaction. Nested transactions are typically emulated with savepoints. Proper transaction design prevents data corruption.
- Atomicity
- All or nothing
- Isolation
- Appears serial
- SAVEPOINT
- Partial rollback
rollback on errorconcurrency controlnested emulationIsolation Levels
Read Uncommitted: allows dirty reads. Read Committed: no dirty reads, allows non-repeatable reads. Repeatable Read: no non-repeatable reads, allows phantoms. Serializable: full isolation, no anomalies. Snapshot Isolation (MVCC): readers see a consistent snapshot, no phantoms. Each level prevents specific anomalies: dirty read, non-repeatable read, phantom, write skew. Higher isolation reduces concurrency. PostgreSQL defaults to Read Committed; MySQL/InnoDB to Repeatable Read. SERIALIZABLE has performance costs; choose based on application needs.
- Read Committed
- No dirty reads
- Repeatable Read
- No non-repeatable
- Serializable
- Full isolation
PostgreSQL defaultMySQL defaulthighest cost