Managing SQL Transactions
1. Starting Transactions
| Command | Use |
|---|---|
| BEGIN / START TRANSACTION | Start explicit tx |
| SET TRANSACTION ISOLATION LEVEL ... | Configure before first statement |
| READ ONLY / READ WRITE | Access mode hint |
| DEFERRABLE (PG) | Wait for serializable snapshot |
2. Committing Changes
| Command | Effect |
|---|---|
| COMMIT | Persist all changes; release locks |
| COMMIT AND CHAIN | Commit and start new tx immediately |
| 2-Phase Commit | PREPARE TRANSACTION 'id'; COMMIT PREPARED 'id'; |
3. Rolling Back Changes
| Command | Effect |
|---|---|
| ROLLBACK | Discard all changes since BEGIN |
| ROLLBACK TO SAVEPOINT s | Undo to savepoint, tx continues |
| ROLLBACK AND CHAIN | Rollback + new tx |
| Implicit rollback | On disconnect or fatal error |
4. Creating Savepoints
Example: SAVEPOINT
BEGIN;
INSERT INTO orders(...) VALUES (...);
SAVEPOINT before_items;
INSERT INTO order_items(...) VALUES (...);
-- something goes wrong with items
ROLLBACK TO SAVEPOINT before_items;
COMMIT; -- order committed, items not| Command | Detail |
|---|---|
| SAVEPOINT name | Mark point inside tx |
| RELEASE SAVEPOINT name | Discard the savepoint marker |
| ROLLBACK TO name | Undo to that point |
5. Understanding ACID Properties
| Property | Guarantee |
|---|---|
| Atomicity | All-or-nothing |
| Consistency | Valid → valid state per constraints |
| Isolation | Concurrent txs appear serial (per level) |
| Durability | Committed survives crash (via WAL) |
6. Setting Isolation Levels
| Level | Prevents | Allows |
|---|---|---|
| READ UNCOMMITTED | — | Dirty, non-repeatable, phantom reads |
| READ COMMITTED | Dirty reads | Non-repeatable, phantom |
| REPEATABLE READ | Dirty, non-repeatable | Phantom (PG: also phantom) |
| SERIALIZABLE | All anomalies | — |
Note: Postgres default is READ COMMITTED; MySQL InnoDB default is REPEATABLE READ. Postgres RR provides snapshot isolation; SERIALIZABLE adds SSI (predicate detection).
7. Handling Concurrency Issues
| Anomaly | Description |
|---|---|
| Dirty Read | Read uncommitted changes |
| Non-repeatable Read | Same row, different values on re-read |
| Phantom Read | Same query, different row set on re-read |
| Lost Update | Two writes, one overwrites the other |
| Write Skew | Two txs read overlapping set, write disjoint sets, violating invariant |
8. Using Optimistic Locking
| Mechanism | Detail |
|---|---|
| Version column | WHERE id=? AND version=? then bump |
| Timestamp | WHERE updated_at=? |
| Hash check | Compare row hash before write |
| Result | 0 rows updated → retry / merge / fail |
Example: Optimistic Lock
UPDATE accounts SET balance = ?, version = version + 1
WHERE id = ? AND version = ?;
-- if rowcount = 0, someone else updated → retry9. Using Pessimistic Locking
| Lock | Syntax |
|---|---|
| Row lock (exclusive) | SELECT ... FOR UPDATE |
| Row lock (shared) | SELECT ... FOR SHARE |
| Skip locked | FOR UPDATE SKIP LOCKED — queue patterns |
| No wait | FOR UPDATE NOWAIT — fail-fast |
| Table lock | LOCK TABLE t IN MODE ... |
| Advisory lock (PG) | pg_advisory_lock(id) |
10. Managing Deadlocks
| Strategy | Detail |
|---|---|
| Consistent lock order | Always acquire keys in same order (e.g., ascending id) |
| Short transactions | Reduce window for contention |
| Detect & retry | DB rolls back one victim; app catches SQLSTATE 40P01 / 1213 |
| Lock timeout | SET lock_timeout = '5s'; |
| Reduce isolation | READ COMMITTED has fewer lock conflicts |
Warning: Deadlocks are inevitable in concurrent systems — always implement automatic retry with exponential backoff.