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 COMMIT, ROLLBACK, and SAVEPOINT?

Accepted Answer

**Target Audience:** Beginners who have seen `BEGIN` and `COMMIT` and want to know exactly what each command does, and candidates preparing for the savepoint and error-recovery follow-ups.
**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. `SAVEPOINT`, `ROLLBACK TO SAVEPOINT` and `RELEASE SAVEPOINT` are standard SQL and also exist in MySQL InnoDB; SQL Server spells it `SAVE TRANSACTION

Question 3

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 4

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 5

What is the difference between OLTP and OLAP databases?

Accepted Answer

**Target Audience:** Beginners who hear "OLTP" and "OLAP" in architecture discussions, and candidates who need to explain why companies run a separate data warehouse.
**Difficulty:** Medium
**Engines:** The SQL examples run on SQLite 3.45.0 (sql.js 1.10.2, the engine this page uses) and returned the same rows on PostgreSQL 16.15; the plan comparison ran on PostgreSQL 16.15. Facts about warehouse products are cited from their documentation, not run here.

> **How to read this doc:** Every idea is

Question 6

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 7

What is the difference between DELETE, TRUNCATE, and DROP?

Accepted Answer

**Target Audience:** Beginners who need to remove data safely, and candidates preparing for the engine-specific follow-ups (transactions, locks, identity, space).
**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, including a two-session test on PostgreSQL. MySQL and SQL Server behaviour is cited from their manuals.

> **How to read this doc:** Every idea is explained in plain language

Question 8

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 9

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

Question 10

What is a deadlock and how do you prevent it?

Accepted Answer

**Target Audience:** Developers who have seen `deadlock detected` in a log and want to understand it from zero; candidates who need the precise detection and prevention story for an interview.
**Difficulty:** Hard
**Engines:** PostgreSQL 16.15 (two real `psql` sessions for the deadlock itself, the verifier for the plan and `pg_locks` examples) and SQLite 3.45 (sql.js 1.10.2 for the runnable examples, Python sqlite3 3.45.1 for the two-connection check). MySQL and SQL Server facts are cited, not r

SQLDECODED.

Easy