When would you use a transaction explicitly? | Interview Question

Q: When would you use a transaction explicitly?

**Target Audience:** Beginners who know `INSERT` and `UPDATE` and want to know when wrapping them in `BEGIN ... COMMIT` matters, and candidates who want concrete scenarios rather than a definition. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Notes on MySQL and drivers are cited. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First You are packing a parcel for a customer: write the label, put two keyboards and a mouse in the box, and tick them off the stock sheet. If the phone rings after you wrote the label but before you ticked off the stock, nobody is hurt: you are one pers

The problem

Question presented to candidate: "Most of the time our code just runs SQL statements one by one and it works. When would you actually reach for BEGIN and COMMIT yourself? Give me real cases from an app like an online shop, and tell me when you would avoid a transaction."

What a strong answer should cover:

By default each statement runs in its own transaction (autocommit), so a single statement is already atomic; an explicit transaction is for several statements that must succeed or fail together (order + items + stock)
Business-rule failures: check the row count of a conditional update and ROLLBACK when the rule fails, so no half-written order remains
Performance: many small writes in one transaction avoid one disk flush per commit, so bulk loads are much faster
Schema migrations: PostgreSQL and SQLite can roll back DDL; MySQL commits implicitly before most DDL
Read-then-write and consistent reports: a transaction alone at READ COMMITTED does not stop lost updates; pair it with an atomic UPDATE, SELECT ... FOR UPDATE or a stronger isolation level
When not to: never hold a transaction open across user think time or slow network calls; keep transactions short

Clarifying questions expected:

Which database and driver? (Some drivers open a transaction implicitly; others autocommit.)
Can the operation be expressed as one statement, or does it span several tables?

Code / implementation expected: Yes — an order placed as one transaction, and a rollback when a stock check fails.

Schema used by the examples:

truncate-vs-delete

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners who know INSERT and UPDATE and want to know when wrapping them in BEGIN ... COMMIT matters, and candidates who want concrete scenarios rather than a definition. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Notes on MySQL and drivers are cited.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

You are packing a parcel for a customer: write the label, put two keyboards and a mouse in the box, and tick them off the stock sheet. If the phone r

The solution is hidden so you can try first

14 min read · 13 sections

Run the code

Edit the query and run it against the tables for this question.

SQLPlace an order as one unit: order row, item rows and stock decrements commit together

SQLA stock check fails inside the transaction, so the app rolls everything back

PostgreSQL: a migration inside a transaction rolls back completely (transactional DDL)

BEGIN;
CREATE TABLE coupons (code TEXT PRIMARY KEY, percent_off INTEGER);
ALTER TABLE products ADD COLUMN sku TEXT;
ROLLBACK;
SELECT to_regclass('coupons') IS NULL AS coupons_table_gone,
       (SELECT COUNT(*) FROM information_schema.columns
        WHERE table_name = 'products' AND column_name = 'sku') AS sku_columns;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.