DB Transaction Handling Skill

Phase 1 — Discovery

Ask only what context doesn't reveal:

• Database engine? Isolation level semantics differ. Postgres uses MVCC and doesn't implement READ UNCOMMITTED (treats it as READ COMMITTED). MySQL's REPEATABLE READ prevents phantom reads via gap locks; Postgres's doesn't (requires SERIALIZABLE).
• What consistency problem are you solving? Lost update, phantom read, write skew, or double-spend? The isolation level needed depends on which anomaly you're preventing.
• ORM or raw SQL? ORMs often silently open transactions per query or wrap saves incorrectly — the fix location differs.
• Is this a high-contention path? Optimistic locking is better than pessimistic for low-contention reads. Pessimistic (SELECT FOR UPDATE) is better when contention is high and retries are expensive.
• Any long-running operations inside the transaction? External HTTP calls, file I/O, or sleep inside a transaction is almost always a bug.

Phase 2 — Isolation Level Selection

The anomaly → isolation level mapping (the non-obvious part)

Most teams default to READ COMMITTED everywhere. It prevents dirty reads but not: