Question 1

When would you use a transaction explicitly?

Accepted Answer

**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 `📌 Inte

Question 2

What are transaction isolation levels?

Accepted Answer

**Target Audience:** Beginners who know what a transaction is and want to understand what "isolation level" means, and candidates who need the anomaly names, the four standard levels and the real engine defaults.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (all two-session timelines were run on it with two concurrent `psql` sessions) and SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples use). MySQL, SQL Server and Oracle facts are cited from their documentation. The Postgre

Question 3

What are ACID properties?

Accepted Answer

**Target Audience:** Beginners who have heard "ACID" and want to know what each letter promises in practice, and candidates preparing for the follow-ups ("which letter is weakest by default?", "what does durable actually cost?").
**Difficulty:** Medium
**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. The concepts apply to every transactional database (MySQL InnoDB, SQL Server, Oracle); engine differences a

Question 4

UPSERT with INSERT … ON CONFLICT — how do you insert or update atomically without races?

Accepted Answer

**Target Audience:** Backend developers who write "create or update" code and candidates who will be asked why the naive version is unsafe.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (concurrency tests, the batch example) and SQLite 3.45.0 through sql.js 1.10.2 (the runnable examples on this page). MySQL and SQL Server syntax notes are cited from their documentation, not run.

> **How to read this doc:** Every idea is explained in plain language first. Then a `📌 Interview term:` callo

Question 5

How do you paginate efficiently in SQL?

Accepted Answer

**Target Audience:** Developers building list screens, feeds and APIs who have used `LIMIT`/`OFFSET` and want to know why deep pages get slow and rows repeat; candidates preparing for a pagination design question.
**Difficulty:** Medium
**Engines:** SQLite 3.45.0 (sql.js 1.10.2, the runnable examples) and PostgreSQL 16.15 (plans and timings), both run for this page. `LIMIT`/`OFFSET` works on both; SQL Server uses `OFFSET ... FETCH NEXT ...`.

> **How to read this doc:** Each idea is explained in

Question 6

Transaction isolation levels in Postgres — what anomalies does each level prevent?

Accepted Answer

**Target Audience:** Developers who know the four standard isolation levels and want to know exactly how PostgreSQL behaves at each one, and candidates preparing for "what does your app have to do at SERIALIZABLE?".
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15. Every two-session timeline on this page was run with two concurrent `psql` sessions against a scratch database seeded from the schema above, and the values and error messages quoted are what those sessions printed. The two runnabl

Question 7

Serializable isolation pitfalls — write skew, retry storms, and when advisory locks beat SSI?

Accepted Answer

**Target Audience:** Developers who know what a transaction is and have heard of isolation levels, meeting write skew and SERIALIZABLE retries for the first time; and candidates who must defend a locking strategy in a design interview.
**Difficulty:** Hard
**Engines:** PostgreSQL 16.15 (two real `psql` sessions for every anomaly and error, `pgbench` 16 for the retry numbers, the verifier for the examples) and SQLite via sql.js 1.10.2 for the runnable examples. MySQL is mentioned only where cited

SQLDECODED.

Easy