Loading...
System Design and AI Engineering, by Roni Das. 928 interactive lessons.
Start LearningA DBMS (database management system) stores data and lets many users read and change it safely. Interviews test keys, normalization, SQL, indexing, transactions and concurrency. Here are 58 questions with short, simple answers. Every SQL example was run on a real database, and the real output is shown.
58 questions · 8 sections · answers from the course
Give the definition in one sentence first. Then give a tiny example, such as an employees table or a bank transfer. For freshers, the example is what shows you really understand it.
Interviews for freshers usually start here. Keep these answers short and give one example each.
A DBMS (database management system) is software that stores data and lets many users read and change it safely. Examples are PostgreSQL, MySQL, Oracle and MongoDB.
It handles the hard parts for you. It finds data fast and stops two users from breaking each other's changes. It also keeps data safe after a crash.
An RDBMS (relational DBMS) is a DBMS that stores data in tables. A table has rows (records) and columns (fields). Tables are linked by keys, and you query them with SQL.
DBMS is the wider word. It also covers systems that do not use tables, such as document or key-value stores. PostgreSQL and MySQL are RDBMSs.
With plain files, your own code must handle searching and two users writing at once. It must also handle crashes halfway through a write, and who may see what. A DBMS does all of this for you.
It links tables through keys (ids that point to other rows), so data is not repeated. It also checks rules, such as "every order must belong to a real customer".
The schema is the design: which tables exist, their columns, types and rules. It changes rarely. The instance is the data stored in those tables at one moment. It changes all the time.
Think of the schema as an empty form, and the instance as all the filled-in copies.
It describes a database at three levels. The external level is what each user or app sees, such as a view. The conceptual level is the full logical design: tables, columns, keys. The internal level is how data is stored on disk.
Data independence means you can change one level without breaking the level above. Physical independence: add an index or move files, and the tables stay the same. Logical independence: add a column, and old views still work.
An ER (entity-relationship) model is a drawing of the data before you build tables. An entity is a thing, like Student. An attribute is a detail of it, like name. A relationship links entities, like Student enrols in Course.
In an ER diagram, an entity is a rectangle, an attribute is an oval, and a relationship is a diamond.
Cardinality says how many rows on one side can link to rows on the other. 1:1: one person has one passport. 1:N: one department has many employees. M:N: many students take many courses.
In tables, 1:N becomes a foreign key on the "many" side, such as dept_id in employees. M:N needs a third table, called a junction table, such as enrolments(student_id, course_id).
A weak entity cannot be identified by its own columns alone. It needs its owner. Example: an employee's dependent, such as a child. Two employees can each have a child called Riya.
So its key is the owner's key plus its own partial key: (emp_id, dependent_name). In an ER diagram it is drawn as a double rectangle.
They are groups of SQL commands. DDL (data definition) builds structure: CREATE, ALTER, DROP. DML (data manipulation) works on rows: SELECT, INSERT, UPDATE, DELETE.
DCL (data control) handles permissions: GRANT, REVOKE. TCL (transaction control) handles transactions, which are groups of statements that succeed or fail together: COMMIT, ROLLBACK, SAVEPOINT. Some books put SELECT in its own group, DQL (data query).
DELETE removes the rows you choose with WHERE, one row at a time, and runs row triggers. TRUNCATE empties the whole table in one step, so it is much faster. DROP removes the whole table, including its structure.
Can TRUNCATE be undone with ROLLBACK? It depends on the database. We tested two, and they behave differently. BEGIN and ROLLBACK are explained in the transactions section.
-- PostgreSQL 18.6
BEGIN;
TRUNCATE accounts;
ROLLBACK;
SELECT COUNT(*) FROM accounts; -- 2 (the rows came back)
-- MySQL 9.7.1, InnoDB
START TRANSACTION;
TRUNCATE TABLE accounts;
ROLLBACK;
SELECT COUNT(*) FROM accounts; -- 0 (TRUNCATE had already committed)A view is a saved SELECT query that you can use like a table. It stores no data. Each time you read it, the query runs again.
A materialized view stores the result on disk, so reads are fast. But the stored result gets old when the tables change. You must refresh it, for example with REFRESH MATERIALIZED VIEW in PostgreSQL.
A key is a column, or a group of columns, used to identify rows or link tables. Keys are a kind of constraint: a rule the database checks on every write. PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL and CHECK are all constraints.
A primary key is the column that identifies each row. It must be unique and can never be NULL. NULL means the value is missing or unknown. A table has at most one primary key.
Example: emp_id in an employees table. Two employees can share a name, but never an emp_id.
A super key is any set of columns that identifies a row uniquely. It may include extra columns it does not need. For example, (emp_id, name) is a super key.
A candidate key is a minimal super key: remove any column and it stops being unique. emp_id and email can both be candidate keys. You pick one as the primary key. The others are alternate keys.
A foreign key is a column that points to the primary key (or another unique column) of another table. The database refuses a value that does not exist there. This rule is called referential integrity.
Below, department 9 does not exist, so the insert fails. The second insert fails on the primary key. The DELETE fails because Sales still has employees. With ON DELETE CASCADE in the table design, those employees would be deleted too.
-- departments(dept_id PRIMARY KEY, name)
-- employees(emp_id PRIMARY KEY, name, salary,
-- dept_id REFERENCES departments)
INSERT INTO employees VALUES (7, 'Neha', 70000, 9);
-- ERROR: insert or update on table "employees" violates
-- foreign key constraint "employees_dept_id_fkey"
-- DETAIL: Key (dept_id)=(9) is not present in table "departments".
INSERT INTO employees VALUES (1, 'Neha', 70000, 1);
-- ERROR: duplicate key value violates unique constraint "employees_pkey"
-- DETAIL: Key (emp_id)=(1) already exists.
DELETE FROM departments WHERE dept_id = 2; -- Sales still has employees
-- ERROR: update or delete on table "departments" violates foreign key
-- constraint "employees_dept_id_fkey" on table "employees"
-- DETAIL: Key (dept_id)=(2) is still referenced from table "employees".No. SQLite checks foreign keys only after you run PRAGMA foreign_keys = ON. You must run it every time you connect.
Say this if the interviewer asks a follow-up question. It shows you have tried a real database, not just read about one.
-- SQLite 3.43.2, a smaller table for this test:
-- employees(emp_id, name, dept_id REFERENCES departments)
INSERT INTO employees VALUES (1, 'Neha', 9); -- no department 9
SELECT COUNT(*) FROM employees; -- 1 (accepted!)
PRAGMA foreign_keys = ON;
INSERT INTO employees VALUES (2, 'Ravi', 9);
-- Runtime error near line 9: FOREIGN KEY constraint failed (19)Both stop duplicate values. A primary key can never be NULL, and a table has only one. A table can have many UNIQUE columns, and they can hold NULL.
How many NULLs a UNIQUE column allows depends on the database. PostgreSQL allows many, as the run below shows. SQL Server allows only one.
CREATE TABLE users_demo (id INT PRIMARY KEY, phone TEXT UNIQUE);
INSERT INTO users_demo VALUES (1, NULL), (2, NULL);
-- INSERT 0 2 (2 rows added: two NULL phones are allowed)
INSERT INTO users_demo VALUES (NULL, '98450');
-- ERROR: null value in column "id" of relation "users_demo"
-- violates not-null constraintA composite key uses two or more columns together. Example: (order_id, item) in an order_lines table. Neither column is unique alone, but the pair is.
A surrogate key is an id with no business meaning, such as an auto-increment number. A natural key has real meaning, such as an email address. Real values can change or be typed wrong. A surrogate id never needs to change.
Normalization means splitting tables so that each fact is stored in one place. The figure takes one small orders table through each normal form.
Normalization is the process of splitting data into tables so that no fact is repeated. Say Asha's city is stored in ten rows. You must update all ten. Miss one, and the table shows two cities for her.
Repeated data also causes three classic problems, called anomalies. Update anomaly: you must change many rows. Insert anomaly: you cannot add a customer who has no order yet. Delete anomaly: deleting the last order also deletes the customer.
What we measured: In our course lab, one million orders took 141.7 MB as one flat table and 80.5 MB normalized. A flat table keeps everything in one table. Renaming one product category changed 200,000 rows in the flat table and 1 row in the normalized schema.
X → Y means: if you know X, you know Y. Example: emp_id → name. One emp_id always gives the same name.
Normal forms are rules about these dependencies. 2NF and 3NF both ask: does each column depend on the key, or on something else?
A table is in 1NF when every cell holds one single value. No lists, and no repeating groups of columns like item1, item2, item3.
Example: "Pen, Book" in one items cell breaks 1NF. The fix is one row per item.
A table is in 2NF when it is in 1NF, and every non-key column depends on the whole key. Part of the key is not enough. Strictly, "key" here means every candidate key. This only matters when a key has two or more columns.
Example: in order_lines with the key (order, item), the customer depends on the order alone. So customer moves to an orders table.
A table is in 3NF when it is in 2NF, and no non-key column depends on another non-key column. Non-key means not part of any candidate key. That kind of chain is called a transitive dependency.
Example: in orders, city depends on the customer, not on the order. So city moves to a customers table. Remember it like this. Every column depends on the key (1NF), the whole key (2NF), and nothing but the key (3NF).
BCNF (Boyce-Codd normal form) is a stricter 3NF. Its rule: if columns X decide column Y, then X must be a super key. That means X alone identifies the row. Cases where Y is already part of X do not count.
3NF allows one exception: Y may be part of some candidate key. BCNF removes that exception. Example: a table (student, subject, teacher). Each teacher teaches one subject, so teacher → subject. Each student has one teacher per subject, so (student, subject) → teacher. The keys are (student, subject) and (student, teacher). Subject is part of a key, so the table is in 3NF. But teacher is not a super key, so it is not in BCNF. The fix is two tables: (teacher, subject) and (student, teacher).
Denormalization means copying some data back into a table on purpose. The goal is to avoid a join on a very busy query. Examples are a comment count stored on a post, or a reporting table.
The price is that every copy must be kept in sync. Normalize first. Denormalize only when you have measured that a query is slow. Then plan how the copy stays correct.
What we measured: In our normalization lab, a small query was slow because of a missing index, not the join. One customer's order count took 0.647 ms without an index on customers.email, and 0.038 ms with one. A query over all orders still cost 1.34 times more with the join.
Most DBMS interviews include a few SQL questions. The examples below use two small tables. departments: 1 Engineering, 2 Sales, 3 HR. employees: Asha 90000, Ravi 120000 and Meera 120000 in Engineering. Karan 60000 and Priya 75000 in Sales. Arjun 50000 has no department. Every result was produced by actually running the query.
A join combines rows from two tables using a matching column. INNER JOIN keeps only rows that match on both sides. LEFT JOIN keeps every row from the left table, with NULL where nothing matches.
RIGHT JOIN is the mirror of LEFT JOIN. FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN pairs every row with every row. A self join joins a table to itself, for example employee to manager.
SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON d.dept_id = e.dept_id
ORDER BY e.emp_id;
-- Asha | Engineering
-- Ravi | Engineering
-- Meera | Engineering
-- Karan | Sales
-- Priya | Sales (5 rows: Arjun has no department)
SELECT d.name AS dept, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
ORDER BY d.dept_id, e.name;
-- Engineering | Asha
-- Engineering | Meera
-- Engineering | Ravi
-- Sales | Karan
-- Sales | Priya
-- HR | (6 rows: HR is kept; the empty cell is NULL)An aggregate function turns many rows into one value: COUNT, SUM, AVG, MIN and MAX. Without GROUP BY, you get one row for the whole table.
GROUP BY splits the rows into groups first, and the function runs once per group. Every other column in SELECT must be in the GROUP BY.
SELECT COUNT(*), SUM(salary), MIN(salary), MAX(salary) FROM employees;
-- 6 | 515000 | 50000 | 120000 (one row for the whole table)
SELECT dept_id, COUNT(*) AS people, MAX(salary) AS highest
FROM employees
GROUP BY dept_id
ORDER BY dept_id;
-- dept_id | people | highest
-- 1 | 3 | 120000
-- 2 | 2 | 75000
-- NULL | 1 | 50000 (Arjun: no department)WHERE filters single rows before they are grouped. HAVING filters groups after GROUP BY. So only HAVING can use results like COUNT(*) or AVG(salary).
-- ::int rounds the average to a whole number (PostgreSQL syntax)
SELECT dept_id, COUNT(*) AS people, AVG(salary)::int AS avg_salary
FROM employees
WHERE salary > 55000 -- filters rows, before grouping
GROUP BY dept_id
HAVING COUNT(*) >= 2 -- filters groups, after grouping
ORDER BY dept_id;
-- dept_id | people | avg_salary
-- 1 | 3 | 110000
-- 2 | 2 | 67500This is one of the most common SQL questions for freshers. The trap is a tie. LIMIT 1 OFFSET 1 returns the second row, which may have the same salary as the first.
DENSE_RANK() is a window function: it gives each row a value computed over other rows, without merging them. It gives equal salaries the same rank, with no gaps. So rank 2 is the second highest distinct salary. Change 2 to N for the Nth highest.
-- salaries: 120000, 120000, 90000, 75000, 60000, 50000
SELECT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;
-- 120000 (wrong: Ravi and Meera share the top salary)
SELECT DISTINCT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) AS ranked
WHERE rnk = 2;
-- 90000 (right)
-- the classic fresher answer, with a subquery
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- 90000 (right, but only for the 2nd highest)NULL means the value is missing or unknown. Comparing anything with unknown gives unknown, not true, so the row is not returned. Use IS NULL or IS NOT NULL instead.
SELECT COUNT(*) FROM employees WHERE dept_id = NULL; -- 0
SELECT COUNT(*) FROM employees WHERE dept_id IS NULL; -- 1 (Arjun)Both stack the results of two queries. UNION removes duplicate rows, which costs extra work. UNION ALL keeps every row and is faster. Use UNION ALL when duplicates are fine or impossible.
SELECT dept_id FROM employees WHERE salary > 100000
UNION
SELECT dept_id FROM employees WHERE name = 'Asha';
-- 1 (duplicates removed)
-- the same two queries with UNION ALL:
-- 1
-- 1
-- 1 (all 3 rows kept)A subquery is a query inside another query. A correlated subquery uses a column from the outer query, so it runs again for each outer row.
Example: find employees paid more than their department's average. The inner query needs the outer row's dept_id. A join or a window function is often faster.
-- who earns more than the average of their own department?
SELECT e.name, e.salary, e.dept_id
FROM employees e
WHERE e.salary > (
SELECT AVG(salary) FROM employees x
WHERE x.dept_id = e.dept_id -- uses the outer row
)
ORDER BY e.emp_id;
-- Ravi | 120000 | 1
-- Meera | 120000 | 1
-- Priya | 75000 | 2A stored procedure is a named block of SQL saved in the database, which you call by name. A trigger is code that runs by itself when a row is inserted, updated or deleted.
Both keep logic close to the data. Too many triggers make behaviour hard to see and debug.
An index is a separate, sorted structure that helps the database find rows without reading the whole table. It works like the index at the back of a book.
Without an index, the database reads every row to find a match. This is called a full table scan, or sequential scan. With an index, it jumps close to the right rows.
The planner is the part of the database that picks how to run a query. EXPLAIN shows its choice. Below, the same query changes from Seq Scan to Index Scan once the index exists.
-- people: 200,000 rows
EXPLAIN (COSTS OFF) SELECT * FROM people WHERE email = 'user4242@mail.com';
-- Seq Scan on people (no index yet: reads every row)
CREATE INDEX idx_people_email ON people (email);
EXPLAIN (COSTS OFF) SELECT * FROM people WHERE email = 'user4242@mail.com';
-- Index Scan using idx_people_email on peopleA B-tree is a sorted tree of pages. A page is a fixed-size block, 8 KB in PostgreSQL. The root page points to pages below it, and the leaf pages hold the keys with each row's address.
Each page holds hundreds of keys, so the tree stays very short. A lookup reads one page per level, then fetches the row.
What we measured: In our course lab, a PostgreSQL B-tree on 10,000 rows had 2 levels. At 1 million and at 10 million rows, it had 3. The 10 million row index was 214.2 MB and still only 3 levels deep.
A clustered index decides the physical order of the rows in the table, so a table can have only one. InnoDB is MySQL's default storage engine, the part that stores the tables. In InnoDB, the table itself is stored in primary key order.
A non-clustered (secondary) index is a separate structure that points back to the rows. A table can have many. PostgreSQL has no clustered index that stays sorted. Its CLUSTER command sorts the table one time, and new rows are not kept in order.
When the query matches a large part of the table, one full read in order is cheaper than many jumps. When you wrap the column in a function, such as upper(email), a plain index on email cannot help.
Also when a composite index does not start with the column you filter on. An index on (customer_id, region) is sorted by customer_id first.
What we measured: In our course lab, one index on (customer_id, region) answered a query on customer_id in 0.054 ms. A query that filtered on region, without customer_id, took 37.269 ms.
EXPLAIN (COSTS OFF) SELECT * FROM people WHERE id <= 100;
-- Index Scan using people_pkey on people (100 rows: index used)
EXPLAIN (COSTS OFF) SELECT * FROM people WHERE id > 20000;
-- Seq Scan on people (180,000 rows: index skipped)
EXPLAIN (COSTS OFF) SELECT * FROM people WHERE upper(email) = 'USER4242@MAIL.COM';
-- Gather -> Parallel Seq Scan on people (function on the column)Every index must be updated on every INSERT, UPDATE and DELETE. It also uses disk and memory. An index that no query uses is pure cost.
Index the columns your common queries filter, join or sort on. Check the plan with EXPLAIN before and after.
What we measured: In our course lab, 200,000 inserts took 160 ms with no index and 751 ms with five indexes.
A transaction is a group of SQL statements that must succeed or fail together. ACID is the set of four promises a transaction gives you.
A transaction starts with BEGIN. COMMIT saves all its changes together. ROLLBACK cancels all of them together.
The classic example is a bank transfer. Money must leave A and reach B, or neither must happen.
-- accounts: A = 1000, B = 1000
BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 'A';
UPDATE accounts SET balance = balance + 500 WHERE id = 'B';
ROLLBACK;
SELECT * FROM accounts;
-- A | 1000
-- B | 1000 (both changes undone together)Atomicity: all or nothing, so no half-finished transfer. Consistency: every transaction leaves the data valid under your rules, such as "balance cannot go below zero".
Isolation: transactions running at the same time do not disturb each other. Durability: once COMMIT returns, the change survives a crash or power cut.
Mostly with a write-ahead log (WAL). Before changing the data files, the database writes the change to a log on disk. The log is append-only: new records are only added at the end. A commit is safe once its log record is on disk.
After a crash, the database replays the log, so committed work is not lost. Work that never committed must not count. MySQL InnoDB undoes it with its undo log. SQL Server uses the undo records in its transaction log. PostgreSQL leaves it on disk, but invisible, because it was never marked committed.
With the rules you declare: primary keys, foreign keys, UNIQUE, NOT NULL and CHECK constraints. A statement that breaks a rule is refused, and nothing is written.
-- balance INT NOT NULL CHECK (balance >= 0)
UPDATE accounts SET balance = balance - 1500 WHERE id = 'A';
-- ERROR: new row for relation "accounts" violates
-- check constraint "accounts_balance_check"
-- DETAIL: Failing row contains (A, -500).A savepoint is a marker inside a transaction. ROLLBACK TO SAVEPOINT undoes only the work after the marker. The rest of the transaction continues.
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
SAVEPOINT before_fee;
UPDATE accounts SET balance = balance - 50 WHERE id = 'A';
ROLLBACK TO SAVEPOINT before_fee; -- undo only the fee
COMMIT;
SELECT balance FROM accounts WHERE id = 'A';
-- 900 (1000 - 100; the 50 was undone)No, and interviewers like this question. ACID consistency means one database keeps your declared rules. CAP consistency means all copies of the data on different servers agree on the latest value.
These questions check whether you understand what happens when two transactions run at the same time. The concurrency results below come from two real sessions on one PostgreSQL 18.6 server. The default-level check and the lost-update test also ran on MySQL 9.7.1.
The SQL standard names four levels, from weakest to strongest. READ UNCOMMITTED allows dirty reads, which means reading data another transaction has not committed yet.
READ COMMITTED stops dirty reads. REPEATABLE READ also stops non-repeatable reads: reading the same row twice and getting two values. SERIALIZABLE also stops phantom reads: a repeated range query finds new rows.
Databases add their own details. PostgreSQL never allows dirty reads, even at READ UNCOMMITTED. Its REPEATABLE READ also stops phantom reads.
It depends on the database. PostgreSQL defaults to READ COMMITTED. MySQL's InnoDB engine defaults to REPEATABLE READ. We checked both instead of trusting memory.
-- PostgreSQL 18.6
SHOW default_transaction_isolation;
-- read committed
-- MySQL 9.7.1
SELECT @@transaction_isolation;
-- REPEATABLE-READTwo transactions read the same value, change it, and write it back. The second write replaces the first, so one change silently disappears.
Fix it in one of three ways. Let the database do the subtraction: SET balance = balance - 800. Or lock the row first with SELECT ... FOR UPDATE. Or, in PostgreSQL, use REPEATABLE READ or SERIALIZABLE, which cancels the second writer.
Careful: MySQL's default REPEATABLE READ does not stop this case. Its UPDATE reads the latest committed row and writes anyway. We ran the same steps on MySQL 9.7.1: both commits worked, and the balance was 200. Use FOR UPDATE there.
-- balance of A = 1000. Two sessions each withdraw 800.
-- Both read 1000 first. Then each writes back 1000 - 800.
-- READ COMMITTED
T1> UPDATE accounts SET balance = 1000 - 800 WHERE id = 'A';
T2> UPDATE accounts SET balance = 1000 - 800 WHERE id = 'A'; -- waits
T1> COMMIT;
T2> COMMIT;
-- final balance: 200
-- 1600 was paid out, but the balance fell only 800. One update is lost.
-- REPEATABLE READ: the same two UPDATEs, then
T1> COMMIT;
T2: ERROR: could not serialize access due to concurrent update
-- T2 is cancelled. Its app must read again and retry.
-- READ COMMITTED, but written as balance = balance - 800,
-- with CHECK (balance >= 0)
T1> COMMIT;
T2: ERROR: new row for relation "accounts" violates check constraint
T2: DETAIL: Failing row contains (A, -600).MVCC (multi-version concurrency control) keeps several versions of a row. An UPDATE keeps the old version for readers that still need it. PostgreSQL writes a new row version. MySQL InnoDB and Oracle keep the old version in an undo log. Each query sees a snapshot: a frozen picture of the committed data at one moment. At REPEATABLE READ, the whole transaction keeps one snapshot.
So readers do not block writers, and writers do not block readers. The cost is old versions that must be cleaned up later. In PostgreSQL, VACUUM removes row versions nobody needs. InnoDB does this with a job called purge.
Snapshot isolation means each transaction reads from one fixed snapshot of the data. It does not stop write skew. In write skew, two transactions each check a rule and see it is safe. Then each changes a different row. Together, they break the rule.
In PostgreSQL, REPEATABLE READ is snapshot isolation. SERIALIZABLE catches write skew by cancelling one transaction, which your code must then retry.
-- Rule: at least one doctor must stay on call. Both are on call.
T1> SELECT count(*) FROM doctors WHERE on_call; -- 2
T2> SELECT count(*) FROM doctors WHERE on_call; -- 2
T1> UPDATE doctors SET on_call = false WHERE name = 'Alice';
T2> UPDATE doctors SET on_call = false WHERE name = 'Bob';
T1> COMMIT;
T2> COMMIT;
-- REPEATABLE READ: both commit. Doctors still on call: 0
-- SERIALIZABLE, the same steps:
T2: ERROR: could not serialize access due to read/write
dependencies among transactions
T2: HINT: The transaction might succeed if retried.
-- Doctors still on call: 1 (T2 was cancelled, so Bob stays on call)A deadlock happens when two transactions each hold a lock the other one needs. Both wait forever unless the database steps in.
PostgreSQL checks for this after waiting one second (the deadlock_timeout setting). Then it cancels one transaction with an error. MySQL's InnoDB detects deadlocks automatically and rolls one transaction back.
T1> BEGIN;
T2> BEGIN;
T1> UPDATE accounts SET balance = balance - 100 WHERE id = 'A';
T2> UPDATE accounts SET balance = balance - 100 WHERE id = 'B';
T1> UPDATE accounts SET balance = balance + 100 WHERE id = 'B'; -- waits for T2
T2> UPDATE accounts SET balance = balance + 100 WHERE id = 'A'; -- waits for T1
T1: ERROR: deadlock detected
T1: DETAIL: Process 51488 waits for ShareLock on transaction 863;
blocked by process 51487.
Process 51487 waits for ShareLock on transaction 862;
blocked by process 51488.
-- PostgreSQL's own wording. It means: T1 waits for T2 to finish,
-- and T2 waits for T1 to finish.
-- T1 is rolled back. Only T2's transfer (B to A) happens:
-- A = 1100, B = 900.
All four must be true together. Mutual exclusion: only one transaction can hold the lock. Hold and wait: it keeps its lock while waiting for another. No preemption: the database cannot force a transaction to give up its lock. Circular wait: the waiting forms a loop.
In practice, stop the loop from forming. Lock rows in the same order everywhere, for example by id. Keep transactions short. And always retry a transaction that fails with a deadlock error.
Pessimistic locking locks the row before changing it, with SELECT ... FOR UPDATE. Others wait. It suits rows that many users fight over.
Optimistic locking takes no lock. It adds a version column and updates only if the version has not changed. If zero rows change, someone else won, so re-read and try again.
-- seats(id, holder, version). Seat 7 is free at version 1.
-- Asha and Ravi both read version 1, then both try to book.
UPDATE seats SET holder = 'Asha', version = version + 1
WHERE id = 7 AND version = 1;
-- UPDATE 1 (Asha wins)
UPDATE seats SET holder = 'Ravi', version = version + 1
WHERE id = 7 AND version = 1;
-- UPDATE 0 (version is now 2, so Ravi's app must re-read)For working engineers and product companies, the interview often moves from one database to many servers.
SQL (relational) databases store data in tables with a fixed schema, support joins, and give ACID transactions across tables. Examples: PostgreSQL, MySQL.
NoSQL is a group of non-table models: document, key-value, wide-column and graph. They are usually built to spread data across many servers. Many give up joins and some transaction guarantees for that.
Two common myths are false. "SQL does not scale": Instagram stores its main data in PostgreSQL. "NoSQL means no schema": Cassandra needs you to define tables and columns first.
Key-value stores, like Redis and DynamoDB, fetch a value by its key. Document stores, like MongoDB, store JSON-like documents. Wide-column stores, like Cassandra, group columns into families.
Graph databases, like Neo4j, store nodes and the links between them, for questions like "friends of friends".
CAP is about data copied across servers. When the network between them breaks, you must choose. That break is called a partition. It is not the same as table partitioning below. Either refuse some requests to stay consistent (CP), or answer with maybe-old data to stay available (AP).
"Pick any two of three" is misleading. Network partitions will happen, so the real choice is only what to do during one.
BASE stands for Basically Available, Soft state, Eventually consistent. It is the looser promise many distributed databases make instead of ACID.
Eventually consistent means: if writes stop, all copies will agree after a while. Until then, two users may briefly see different values.
Replication keeps full copies of the same data on several servers. It helps with more reads and with surviving a server failure. Sharding splits the data, so each server holds only some rows.
Sharding helps when the data or the writes are too big for one server. Large systems often use both: each shard has its own copies (replicas).
Partitioning splits one big table into smaller pieces, often on the same server. Examples are one partition per month, or ranges of user ids.
Sharding is partitioning across different servers. It adds harder problems: queries that touch many shards, and transactions across shards.
Start from the data and the queries, not the product name. Money, orders and bookings need transactions and constraints, so start with SQL.
Pick a NoSQL store when one access pattern dominates and must scale very far. Examples are a cache, a chat message log, or a feed. Say the trade-off you are accepting out loud.
Every answer on this page comes from our System Design course: 766 lessons on databases, caching, scaling, messaging and reliability, each drawn step by step. You learn why the answer is right, which is what an interviewer checks with the second question. 18 lessons are free to read, with no card needed.
course 1
From absolute beginner to principal engineer, drawn step by step.