Transactions
28 bites tagged Transactions — interview questions with model answers, and 60-second explainers.
Atomic order creation with Sequelize transactions
Wrap dependent writes in sequelize.transaction, pass the transaction to each query, let managed transactions auto-commit or roll back. atomicity and transaction handling.
SQL isolation levels and the anomalies they prevent
Read Uncommitted allows dirty reads; Read Committed blocks them; Repeatable Read blocks non-repeatable reads; Serializable blocks phantoms. the isolation-anomaly mapping.
Transaction isolation levels and their tradeoffs
Isolation levels control which concurrency anomalies (dirty/non-repeatable reads, phantoms) are allowed; higher levels mean stronger consistency but more blocking and less concurrency. isolation tradeoffs.
How do you keep consistency without multi-document transactions?
A Saga runs a sequence of local transactions, each with a compensating action to undo on failure, coordinated via choreography or orchestration. maintaining integrity across services without distributed ACID.
Lifecycle of a single row update
Buffer manager faults the page in, the row is modified in memory marking the page dirty, a WAL record is written, and commit fsyncs the log while the dirty page is flushed later by a checkpoint. end-to-end write path.
Database deadlocks and how engines resolve them
Define a deadlock as mutual waiting on locks, name detection plus victim rollback, and prevention by consistent lock ordering. understanding circular lock waits. confusing a deadlock with a slow query or simple lock wait.
The lost update anomaly explained
Both transactions read the same value, each adds one, the second overwrite erases the first. read-modify-write race awareness. thinking the database auto-serializes plain reads, or that the final value is always correct.
The ACID properties of transactions
Define Atomicity, Consistency, Isolation, Durability and why each matters. foundational transaction guarantees. confusing Consistency with Isolation or thinking durability means in-memory only.
Read Committed versus Serializable isolation levels
Name the four levels, map each anomaly (dirty read, non-repeatable read, phantom) to the level that blocks it. grasp of concurrency anomalies versus consistency cost. claiming Serializable blocks only phantoms.
Explain IndexedDB transactions and readonly vs readwrite modes
Every operation needs a transaction; readonly allows concurrent readers, readwrite is exclusive; they auto-commit when idle. Understanding of IndexedDB's transactional model and concurrency.
Design DB transaction middleware and identify the background-task pitfall
Tests request-scoped DB lifecycle awareness. Strong answer: middleware closes the session on response, yet BackgroundTasks run afterward, so sharing that session causes crashes or leaks. Red flag: saying background tasks can reuse the request transaction.
How do you atomically create an order and update inventory?
Tests transaction boundaries and SQLAlchemy 2.0 session lifecycle in FastAPI. A strong answer wraps both writes in session.begin(), flushes to catch constraint errors early, and uses exceptions to trigger rollback.
What is an sqflite transaction? Provide a practical example.
Tests atomicity in SQLite and isolate safety. A strong answer defines all-or-nothing execution, gives a fund-transfer example updating both, and warns sqflite transactions are not cross-isolate safe. Red flag: omitting rollback or multi-isolate writes.
Explain ACID properties and why they matter for banking or e-commerce
Define each as a failure-handling guarantee; show how partial commits cause double-spending. Mapping ACID to real failure modes in finance. Vague definitions that skip Isolation levels or Durability details.
IndexedDB Transactions: The Gatekeepers of Data
An IndexedDB transaction is a short-lived container for all database operations, ensuring data integrity. Use it for any read or write. The main footgun: transactions auto-commit if idle, so you must queue all requests synchronously without waiting.
Sequelize Transactions: All-or-Nothing Database Writes
A Sequelize transaction is a safety wrapper for database queries, ensuring they all succeed or none do. Use it for multi-step operations like creating a user and profile.
Phantom Reads: When New Rows Appear Mid-Transaction
A phantom read occurs when a transaction repeats a query and finds new rows that match its search criteria, inserted by another committed transaction. It's common in reporting jobs that need a stable set of data.
Two-Phase Commit (2PC): All or Nothing, Together
Two-Phase Commit (2PC) ensures a distributed transaction is atomic: all participants either commit or abort together. A coordinator first asks all nodes to prepare (vote), then issues a final commit or abort.
ARIES Recovery Algorithm
ARIES is a widely-used database recovery algorithm, found in systems like IBM Db2 and SQL Server. It enables high performance by supporting a 'no-force, steal' policy, ensuring data integrity after a crash. The main footgun is not understanding this policy.
Serializable Snapshot Isolation: True Serializability Without Heavy Locking
SSI upgrades Snapshot Isolation to true serializability. It optimistically lets transactions run, but aborts one if a dangerous read-write dependency arises. This prevents subtle data corruption in systems like PostgreSQL without heavy locking.
Write Skew: The Phantom Anomaly of Snapshot Isolation
Write skew is when two transactions read the same data, make decisions, and then update *different* data, violating a business rule. It's common in booking systems or when enforcing multi-row constraints under Snapshot Isolation.
Snapshot Isolation: A 'Photo' of Your Database
Snapshot Isolation gives a transaction a private 'photo' of the database from when it started, ensuring consistent reads. It's used in high-concurrency systems to prevent readers from blocking writers. The footgun is that it doesn't prevent all anomalies.
Timestamp Concurrency Control: No Locks, Just Time
Timestamp-based concurrency control bets that transaction conflicts are rare, using timestamps to order operations instead of locking data. It's used where lock overhead is high, but the footgun is that frequent conflicts can cause transaction starvation.
Strict Two-Phase Locking (S2PL): Safety Over Speed
Strict Two-Phase Locking (S2PL) forces a transaction to hold all its locks until it fully commits or aborts. This prevents cascading aborts in databases but at the cost of concurrency, as other transactions are blocked for longer periods.
Get Transactions bites daily.
Five a day, five minutes, offline. With quizzes so it sticks.
Open testing — you’ll join as an early tester.