What Is a Database Transaction?
3 days ago
- #SQL
- #database
- #transactions
- Database transactions are sequences of actions performed as a single, atomic operation in SQL databases.
- Transactions in MySQL and Postgres begin with `begin;` and end with `commit;` or `rollback;`.
- Postgres uses multi-versioning of rows with `xmin` and `xmax` to manage consistent reads.
- MySQL uses an undo log to reconstruct past versions of rows for consistent reads.
- Isolation levels in databases include Serializable, Repeatable Read, Read Committed, and Read Uncommitted.
- Serializable is the strictest isolation level, ensuring transactions appear to run sequentially.
- MySQL handles conflicting writes with row-level locking, which can lead to deadlocks.
- Postgres uses Serializable Snapshot Isolation with predicate locks for optimistic conflict resolution.
- Phantom reads, non-repeatable reads, and dirty reads are phenomena that different isolation levels allow or prevent.
- Understanding transactions and isolation levels is crucial for effective database management.