Question 1

FOR UPDATE vs FOR UPDATE NOWAIT vs FOR UPDATE SKIP LOCKED — which to use when?

Accepted Answer

**Target Audience:** Developers who know basic transactions and want to understand locking reads from zero; candidates who need a crisp "which one when" answer.
**Difficulty:** Medium
**Engines:** PostgreSQL 16.15 (two real `psql` sessions for every locking behaviour, the verifier for the examples), SQLite via sql.js 1.10.2 for the runnable query and to confirm it has no `FOR UPDATE`. MySQL and SQL Server facts are cited, not run.

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

Question 2

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 3

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 4

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.

Medium

FOR UPDATE vs FOR UPDATE NOWAIT vs FOR UPDATE SKIP LOCKED — which to use when?

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

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

Hard

What is a deadlock and how do you prevent it?