databases
9 minMySQL InnoDB: Transactions, Indexes, and Reading EXPLAIN
Understand InnoDB's clustered primary key, secondary-index lookups, transaction behavior, and practical query-plan analysis.
Search the library
20 matching resultsArticles and interview answers
Q&A
Q&AWhy can a long PostgreSQL transaction delay vacuum cleanup?
An old transaction snapshot may still need row versions that newer transactions have replaced.
Read the concise answer
PostgreSQL MVCC keeps row versions visible to active snapshots. A long-running transaction can hold an old snapshot open, so vacuum cannot yet remove some dead versions. Bound transaction lifetimes and investigate idle-in-transaction sessions when table bloat grows.
databases
10 minRelational Databases and SQL Foundations: Model the Invariant First
Learn tables, keys, constraints, joins, transactions, and query shape before choosing a specific database engine.
Q&A
Q&AHow can write skew occur under snapshot or repeatable-read isolation?
Two transactions can read the same invariant and update disjoint rows, so row-level conflicts do not detect the violation.
Read the concise answer
If each transaction sees a snapshot where another row satisfies a rule, they may update different rows and both commit, leaving the combined invariant false. Use a constraint, lock a shared coordination row, or use serializable isolation with whole-transaction retry. Exact Repeatable Read semantics differ by engine.
java
9 minSpring Transaction Boundaries: Where to Commit and When to Split
A transaction is only as good as the boundary you choose. Learn how service-layer design, isolation levels, and idempotent writes affect correctness in Java systems.
Q&A
Q&AWhat is a safe application response to a database deadlock victim error?
Retry the whole idempotent transaction within a bounded policy, not just an arbitrary statement.
Read the concise answer
A deadlock detector aborts one transaction so another can progress. Capture lock and query evidence, then retry the whole business transaction only if its effects are safe to repeat. Use bounded attempts with jitter, consistent resource ordering, and idempotency for any external side effects.
databases
10 minPostgreSQL MVCC and Indexes: Reads, Vacuum, and Query Plans
Connect PostgreSQL's multi-version concurrency control to transaction visibility, vacuum work, index choice, and EXPLAIN output.
distributed systems
9 minIdempotency and the Transactional Outbox: Making Retries Safe
Learn why network retries create duplicate effects and how idempotency keys and an outbox close common reliability gaps.