A service inserts an order row and then updates a stock row, running on a connection left in autocommit mode. Explain what autocommit does to those two statements, what can go wrong, and how the transaction scope should be set instead.
answer
- Autocommit = one transaction per statement
- First statement already durable when the second fails
- Atomicity and isolation both lost
- Same connection or the write escapes the transaction
- Scope = business operation; external calls outside
basics
~20 sIn autocommit each statement is its own transaction, committed the moment it succeeds. So the insert is durable even if the update then fails, leaving an order with no stock deducted, and nothing can roll it back. Both statements must run inside one explicit transaction on the same connection.
solid answer
~50 sAutocommit means the connection commits after every statement, so there is no multi-statement unit of work. The order insert commits immediately; if the stock update then fails — constraint violation, deadlock victim, timeout, process crash — the insert stays. There is nothing to roll back, because the transaction that held it already ended. That breaks atomicity for any invariant spanning two statements, and it also breaks isolation: another session can observe the order in the window before the stock row moves, so readers see states your business rules say cannot exist. The fix is to make the unit of work explicit: begin a transaction, run both statements **on the same connection**, commit once, roll back on error. In application terms that means a transaction boundary around the whole use case rather than around each repository call — and being aware that connection pools hand out connections whose autocommit state must be restored on return, otherwise one component's setting leaks into the next borrower.
code
sql · 9 lines-- autocommit ON: two independent transactions
INSERT INTO orders (...) VALUES (...); -- committed immediately
UPDATE stock SET qty = qty - 1 WHERE sku = ?; -- if this fails, order stays
-- explicit scope: one atomic unit
BEGIN;
INSERT INTO orders (...) VALUES (...);
UPDATE stock SET qty = qty - 1 WHERE sku = ?;
COMMIT; -- both visible together, or neithergo deeper
Say that autocommit commits after every statement, so the first write survives the second one's failure, and that both belong in one explicit transaction.
Add the isolation window other sessions can observe, the same-connection requirement, and the rule that the boundary tracks the business operation.
Discuss escaped writes via non-transaction-aware connections, pool state leakage, keeping external calls out of the boundary, and outbox-style deferral of side effects.
Set conventions for where boundaries live in the architecture, how invariants are grouped, and when cross-service work needs compensation rather than a longer transaction.
## What autocommit actually is Every statement executes inside a transaction — there is no such thing as a statement outside one. Autocommit is a *connection setting* that says: implicitly begin a transaction before each statement and commit it as soon as the statement succeeds. It is the default in most drivers and interactive clients, which is why so many applications are accidentally running without transactions at all. With autocommit on, a two-statement sequence is two independent transactions: 1. `INSERT INTO orders ...` → begins, succeeds, **commits**. The row is durable and visible to everyone. 2. `UPDATE stock SET qty = qty - 1 ...` → begins, and may fail. ## What goes wrong **Atomicity is gone.** If step 2 fails for any reason — a check constraint, a deadlock in which this session is the victim, a lock timeout, a network drop, the application process being killed — the order row remains. You cannot roll it back, because its transaction committed. Recovery is now a manual or compensating operation: a cleanup job that finds orders with no stock movement, which is code you would not have needed. **Isolation is gone.** Even in the success case there is a window in which another session sees an order whose stock has not been deducted. Reports, downstream consumers, and concurrent business rules all read a state the domain considers impossible. A single transaction makes both changes appear together at commit. **Error handling becomes ambiguous.** With one transaction, a failure has one outcome: nothing happened. With autocommit, failure means "some prefix of my statements happened", and the prefix depends on where it broke — the hardest kind of state to reason about or test. ## Setting the scope correctly The unit of work should match the **business operation**, not the code structure. "Place an order" is one transaction; the three repository calls it makes are not three transactions. Practically: - Turn autocommit off (or use the framework's declarative transaction boundary) for the duration of the use case, run the statements, commit once, and roll back on any exception. - **All statements must use the same connection.** This is the most common real bug: a framework opens a transaction on connection A, while some component fetches its own connection B from the pool and writes there. B's writes are outside the transaction and commit independently, so a rollback leaves them behind. Anything that bypasses the ambient transaction context — a raw data source lookup, a second session, an async task started mid-method — is a candidate. - **Restore the connection's state before returning it to the pool.** A component that flips autocommit off and returns the connection without restoring it hands the next borrower a connection that silently never commits. Well-behaved pools reset this; hand-rolled code often does not. ## Scope discipline: tight, but not too tight Two failure modes sit either side of the right answer. *Too wide* is a transaction opened at the start of a request handler and held while the code calls a payment provider, waits on a queue, or renders a response. The transaction now lasts as long as the slowest external dependency, holding every lock it took and occupying a pooled connection. External calls belong outside the transaction; if their result must be recorded, write it in a separate short transaction afterwards, or record the intent inside the transaction (an outbox row) and dispatch after commit. *Too narrow* is one transaction per statement — autocommit by another name — where the invariant spans statements. The test is simple: **if two writes must both happen or neither, they share a transaction.** The practical rule is: gather your inputs and do your validation and computation first, then open the transaction, perform the writes, commit, and do the side effects afterwards. That keeps the transaction to the shortest span that still covers the invariant. ## Read-only work Single-statement reads under autocommit are usually fine. But a report reading several tables that must agree with each other still needs one transaction (and often a repeatable-read snapshot), otherwise each query sees a different point in time and the totals do not reconcile. Consistency requirements, not write-versus-read, decide whether a transaction is needed.
- The framework wraps the method in a transaction, yet one write survived a rollback. What is the usual cause?That write ran on a different connection than the transaction. Fetching a connection directly from the pool, using a second session or template that is not transaction-aware, or spawning an asynchronous task inside the method all escape the ambient transaction, so those statements commit independently under their own autocommit. The fix is to route every write through the same transaction-bound connection, and to start background work only after commit.
- Do read-only operations ever need an explicit transaction?Yes, when several reads must agree with one another. A report that queries orders, then payments, then inventory under autocommit sees three different points in time, so the numbers may not reconcile. Wrapping them in one transaction — often at a repeatable-read snapshot — gives all of them a single consistent view. A single standalone read gains nothing from an explicit transaction.
saying these in an interview costs you the question
- Believing statements run 'outside a transaction' when autocommit is on, rather than one transaction each
- Assuming a rollback can undo a statement whose autocommit transaction already committed
- Placing the transaction boundary per repository call instead of per business operation
- Holding the transaction open across an external HTTP or payment call
- Turning autocommit off on a pooled connection and never restoring it