DBMS Interview Questions — Lost Update Race Conditions

Every serious backend or full-stack role has at least a handful of DBMS questions tucked into the interview. Why? Because how data is stored, retrieved, and protected is the backbone of almost every application ever built. A poorly understood database concept in production doesn't just cause slow queries — it causes data loss, race conditions, and outages at 3am. Interviewers use DBMS questions to quickly separate engineers who 'use' databases from engineers who 'understand' them.

The problem most candidates have isn't that they don't know SQL. It's that they've memorized definitions without ever connecting them to real scenarios. They can recite 'ACID stands for Atomicity, Consistency, Isolation, Durability' but freeze when asked 'What actually happens to your data if the server crashes mid-transaction?' That gap between definition and intuition is exactly what interviewers are probing for.

By the end of this article, you'll be able to answer intermediate DBMS interview questions with confident, scenario-grounded explanations. You'll understand the WHY behind normalization, indexing, transactions, and joins — and you'll have concrete examples to pull from the moment an interviewer says 'Tell me about...'

Why Lost Updates Are the Silent Killer in Concurrent DBMS

A lost update is a race condition where two concurrent transactions read the same row, modify it locally, and write back — the second write silently overwrites the first, discarding its change. This is not a theoretical corner case; it's a daily reality in any system with concurrent writes, from inventory decrements to balance transfers. The core mechanic: read-then-write without proper isolation. In practice, lost updates occur when transactions run at READ COMMITTED isolation or lower. The database allows both transactions to see the initial value, both compute a new value based on that stale snapshot, and both commit. The second commit clobbers the first. Serializable isolation prevents this by locking or aborting one transaction, but most production systems default to READ COMMITTED for performance. The fix is either explicit locking (SELECT ... FOR UPDATE) or optimistic concurrency with version columns. You use lost update awareness everywhere: e-commerce stock, financial ledgers, booking systems. Ignoring it means corrupted data that's invisible until reconciliation fails. Senior engineers treat every read-then-write as a potential lost update until proven otherwise.

ACID Properties — What They Actually Guarantee (and When They Fail)

ACID is the most asked-about DBMS concept in interviews, and the most superficially answered. Let's fix that.

Atomicity means a transaction is all-or-nothing. If you're transferring $500 from Account A to Account B, that's two writes. Atomicity guarantees that if the second write fails, the first is rolled back — you never end up with money disappearing into thin air.

Consistency means the database moves from one valid state to another. If your schema says an order must have a valid customer_id, a transaction that violates that constraint is rejected entirely.

Isolation is the subtle one. It controls what a transaction can see about other in-flight transactions. SQL standard defines four isolation levels — Read Uncommitted, Read Committed, Repeatable Read, and Serializable — each trading performance for safety. Most databases default to Read Committed.

Durability means once a transaction is committed, it survives crashes. The database writes to a transaction log (WAL — Write-Ahead Log) before confirming success, so even if the server dies immediately after your commit, the data is recoverable on restart.

The key interview insight: ACID is a contract, not a guarantee that nothing goes wrong. It's a guarantee about what the system will do to handle things going wrong.

-- Scenario: Bank transfer between two accounts
-- This demonstrates Atomicity — both updates happen or neither does

-- Setup: create accounts table with a balance constraint
CREATE TABLE bank_accounts (
    account_id   INT PRIMARY KEY,
    holder_name  VARCHAR(100) NOT NULL,
    balance      DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)  -- Consistency constraint
);

INSERT INTO bank_accounts VALUES (1, 'Alice Johnson', 1000.00);
INSERT INTO bank_accounts VALUES (2, 'Bob Martinez',  500.00);

-- Begin a transaction (Atomicity wrapper)
BEGIN TRANSACTION;

    -- Step 1: Deduct from Alice
    UPDATE bank_accounts
    SET    balance = balance - 500.00
    WHERE  account_id = 1;

    -- Step 2: Credit Bob
    UPDATE bank_accounts
    SET    balance = balance + 500.00
    WHERE  account_id = 2;

    -- If EITHER update fails (e.g. Alice's balance would go negative,
    -- violating CHECK constraint), the whole block rolls back.
    -- Neither account changes. That's Atomicity + Consistency working together.

COMMIT;  -- Durability: this is now written to the WAL and survives a crash

-- Verify the final state
SELECT account_id, holder_name, balance FROM bank_accounts;

-- Now demonstrate a ROLLBACK scenario
BEGIN TRANSACTION;

    UPDATE bank_accounts
    SET    balance = balance - 2000.00  -- Alice only has 500 now; CHECK will fail
    WHERE  account_id = 1;

ROLLBACK;  -- We manually roll back, or the DB engine does it on constraint violation

-- Alice's balance is unchanged — Atomicity protected us
SELECT account_id, holder_name, balance FROM bank_accounts;

Normalization vs Denormalization — Choosing the Right Tradeoff

Normalization is the process of organizing your schema to eliminate redundant data and protect against update anomalies. Denormalization is the deliberate reversal of that — adding redundancy back in to speed up reads. The interview question isn't 'what is normalization?' — it's 'when do you normalize and when do you denormalize?'

Normalization follows 'Normal Forms'. First Normal Form (1NF) eliminates repeating groups — every cell holds one atomic value. Second Normal Form (2NF) removes partial dependencies — every non-key column depends on the whole primary key, not just part of it. Third Normal Form (3NF) removes transitive dependencies — non-key columns shouldn't depend on other non-key columns.

Here's the real-world intuition: if you store a customer's city and zip code in the orders table, you'll have to update both every time a customer moves — and you risk them going out of sync. 3NF says: put zip code in a customers table and reference it by customer_id. Now city and zip are always consistent.

Denormalization makes sense in read-heavy analytical systems (data warehouses, reporting dashboards) where joins across 10 normalized tables are too slow. You pre-join the data at write time so reads are instant. The tradeoff: writes become slower and you need application logic to keep redundant data in sync.

The rule of thumb: normalize by default in OLTP systems (transactions), denormalize deliberately in OLAP systems (analytics).

-- =============================================
-- BAD: Unnormalized orders table (violates 3NF)
-- =============================================
-- Problem: customer_city depends on customer_id, NOT on order_id.
-- If a customer moves, you update 100 rows instead of 1.
CREATE TABLE orders_unnormalized (
    order_id       INT PRIMARY KEY,
    customer_id    INT,
    customer_name  VARCHAR(100),   -- redundant — should live in customers table
    customer_city  VARCHAR(100),   -- transitive dependency: city -> customer, not order
    product_name   VARCHAR(100),
    order_total    DECIMAL(10, 2)
);

-- =============================================
-- GOOD: Normalized to 3NF — two separate tables
-- =============================================
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    customer_city VARCHAR(100) NOT NULL
    -- city lives here — update once, reflects everywhere
);

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    customer_id   INT NOT NULL REFERENCES customers(customer_id),  -- FK enforces integrity
    product_name  VARCHAR(100) NOT NULL,
    order_total   DECIMAL(10, 2) NOT NULL
);

-- Insert sample data
INSERT INTO customers VALUES (1, 'Alice Johnson', 'New York');
INSERT INTO orders   VALUES (101, 1, 'Laptop', 1299.99);
INSERT INTO orders   VALUES (102, 1, 'Mouse',    29.99);

-- Join to reconstruct the full picture — this is the cost of normalization
-- (the join), but the benefit is zero data duplication
SELECT
    o.order_id,
    c.customer_name,
    c.customer_city,
    o.product_name,
    o.order_total
FROM   orders    o
JOIN   customers c ON o.customer_id = c.customer_id;

-- If Alice moves to Boston — ONE update, zero inconsistency
UPDATE customers SET customer_city = 'Boston' WHERE customer_id = 1;

Indexing Deep Dive — Why Indexes Speed Up Reads but Hurt Writes

An index is a separate data structure — usually a B-Tree — that the database builds and maintains alongside your table. It stores a sorted copy of one or more columns, with pointers back to the actual rows. Without an index, a query like WHERE email = 'alice@example.com' does a full table scan — reading every single row. With an index on email, the database does a binary search on the B-Tree and jumps directly to the matching row.

So why not index every column? Because every index you create is a structure that must be updated on every INSERT, UPDATE, and DELETE. If your orders table has 8 indexes, inserting one order requires 8 separate B-Tree updates. In a write-heavy system, that overhead adds up fast and can make inserts painfully slow.

There are two important index types you'll encounter in interviews. A clustered index physically reorders the table data to match the index order — there can only be one per table (SQL Server/MySQL InnoDB use the primary key as the clustered index by default). A non-clustered index is a separate structure with pointers to rows — you can have many, and they don't affect the storage order of the table.

Composite indexes follow the 'left-prefix rule': an index on (last_name, first_name) speeds up queries filtering on last_name alone, or both columns — but NOT on first_name alone. The database can only use the leftmost columns in the index definition.

-- Setup: large employees table to demonstrate index impact
CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,          -- clustered index by default in most DBs
    department_id INT NOT NULL,
    last_name     VARCHAR(100) NOT NULL,
    first_name    VARCHAR(100) NOT NULL,
    email         VARCHAR(200) UNIQUE,      -- UNIQUE automatically creates an index
    hire_date     DATE NOT NULL,
    salary        DECIMAL(10, 2)
);

-- Without any extra indexes, this query does a full table scan (slow at scale)
-- EXPLAIN shows 'Seq Scan' or 'Full Table Scan' — red flag in production
EXPLAIN SELECT * FROM employees WHERE last_name = 'Johnson';

-- Create a non-clustered index on last_name
CREATE INDEX idx_employees_last_name
    ON employees (last_name);  -- B-Tree built; now searches on last_name are O(log n)

-- After the index: EXPLAIN shows 'Index Scan' — jumps directly to matching rows
EXPLAIN SELECT * FROM employees WHERE last_name = 'Johnson';

-- Composite index: useful when you frequently filter by department AND hire_date
CREATE INDEX idx_employees_dept_hire
    ON employees (department_id, hire_date);

-- This query USES the composite index (left-prefix rule satisfied)
SELECT * FROM employees
WHERE  department_id = 3
AND    hire_date > '2022-01-01';           -- Both columns in index, in order

-- This query CANNOT use the composite index efficiently
-- (skips department_id, starts at second column — violates left-prefix rule)
SELECT * FROM employees
WHERE  hire_date > '2022-01-01';           -- Falls back to full scan

-- Check index usage and fragmentation (PostgreSQL syntax)
SELECT
    indexname,
    idx_scan,       -- how many times this index was used
    idx_tup_read    -- rows read through this index
FROM   pg_stat_user_indexes
WHERE  tablename = 'employees';

Joins, Keys, and the Difference Between WHERE and HAVING

Joins are the mechanism for combining rows from two or more tables based on a related column. The confusion isn't usually about syntax — it's about understanding which join to use and what happens to unmatched rows.

INNER JOIN returns only rows where the join condition is satisfied in both tables. If a customer has no orders, they don't appear in the result. LEFT JOIN returns all rows from the left table and matching rows from the right — unmatched right-side columns are NULL. This is critical for questions like 'find all customers who have never placed an order' (WHERE order_id IS NULL after a LEFT JOIN).

Cross joins and self-joins come up less often in interviews but signal depth. A self-join joins a table to itself — useful for hierarchical data like an employees table where manager_id references another employee_id in the same table.

The WHERE vs HAVING distinction is a classic interview trap. WHERE filters rows before aggregation happens. HAVING filters groups after aggregation. If you put an aggregate function like COUNT() in a WHERE clause, you'll get a syntax error — because WHERE runs before GROUP BY has created any groups to count. HAVING runs after GROUP BY, so aggregates are fair game.

Primary keys uniquely identify a row and cannot be NULL. Foreign keys enforce referential integrity — a foreign key column in one table must match an existing primary key in another table (or be NULL). Without this constraint, you can end up with orphaned records: orders pointing to customer_ids that no longer exist.

-- Setup: customers and orders tables
CREATE TABLE customers (
    customer_id   INT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id),  -- foreign key
    amount      DECIMAL(10, 2) NOT NULL,
    order_year  INT NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice Johnson'), (2, 'Bob Martinez'), (3, 'Carol Wu');
INSERT INTO orders VALUES
    (101, 1, 250.00, 2023),
    (102, 1, 450.00, 2023),
    (103, 2, 125.00, 2022),
    (104, 2, 800.00, 2023);
-- Carol Wu (customer_id=3) has NO orders — useful for LEFT JOIN demo

-- =============================================
-- INNER JOIN: only customers WITH orders
-- =============================================
SELECT c.customer_name, o.order_id, o.amount
FROM   customers c
INNER JOIN orders o ON c.customer_id = o.customer_id;
-- Carol Wu is NOT in this result

-- =============================================
-- LEFT JOIN: ALL customers, even those without orders
-- =============================================
SELECT c.customer_name, o.order_id, o.amount
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Carol Wu appears with NULL for order_id and amount

-- =============================================
-- Find customers who have NEVER placed an order
-- =============================================
SELECT c.customer_name
FROM   customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE  o.order_id IS NULL;  -- NULL means no matching order row was found

-- =============================================
-- WHERE vs HAVING — the classic interview trap
-- =============================================

-- WHERE filters individual rows BEFORE grouping
SELECT   customer_id, SUM(amount) AS total_spent
FROM     orders
WHERE    order_year = 2023           -- filter rows: only 2023 orders enter the group
GROUP BY customer_id;

-- HAVING filters groups AFTER aggregation
SELECT   customer_id, SUM(amount) AS total_spent
FROM     orders
GROUP BY customer_id
HAVING   SUM(amount) > 500;          -- filter groups: only big spenders

-- WRONG: This throws an error — you can't use aggregate in WHERE
-- SELECT customer_id FROM orders WHERE SUM(amount) > 500 GROUP BY customer_id;
-- ERROR: aggregate functions are not allowed in WHERE

Transactions, Isolation Levels, and Concurrency Control

A transaction groups multiple SQL statements into a single logical unit. When you COMMIT, all changes become permanent. When you ROLLBACK, all changes are undone. But what happens when two transactions run at the same time? That's where isolation levels and concurrency control kick in.

Most modern databases use Multi-Version Concurrency Control (MVCC). Instead of locking every row a transaction reads, MVCC keeps older versions of rows. Each transaction sees a snapshot of the data as of its start time. Readers never block writers, and writers never block readers. That's the magic. But MVCC isn't free — it increases storage and makes the transaction log grow.

A deadlock occurs when two transactions each hold a lock the other needs. The database automatically kills one transaction to break the cycle. The fix is always application-side: access tables in the same order, keep transactions short, and avoid user interaction inside transactions.

-- =============================================
-- Example: Lost Update under Read Committed
-- =============================================
CREATE TABLE account_balance (
    account_id INT PRIMARY KEY,
    balance    DECIMAL(10, 2) NOT NULL CHECK (balance >= 0)
);
INSERT INTO account_balance VALUES (1, 1000.00);

-- Transaction A
BEGIN TRANSACTION;
SELECT balance FROM account_balance WHERE account_id = 1;  -- reads 1000
-- ... some processing ...
UPDATE account_balance SET balance = balance - 200 WHERE account_id = 1;

-- Transaction B (concurrent)
BEGIN TRANSACTION;
SELECT balance FROM account_balance WHERE account_id = 1;  -- also reads 1000
UPDATE account_balance SET balance = balance - 300 WHERE account_id = 1;

-- If Transaction B commits after A, the final balance is 700 (only B's change)
-- Transaction A's deduction is lost. This is a lost update.

-- Fix: Use SELECT ... FOR UPDATE to lock the row
BEGIN TRANSACTION;
SELECT balance FROM account_balance WHERE account_id = 1 FOR UPDATE;  -- acquires row lock
-- Transaction B will block here until A commits
UPDATE account_balance SET balance = balance - 200 WHERE account_id = 1;
COMMIT;

-- =============================================
-- Deadlock scenario (avoid this)
-- =============================================
CREATE TABLE table_a (id INT PRIMARY KEY, val INT);
CREATE TABLE table_b (id INT PRIMARY KEY, val INT);
INSERT INTO table_a VALUES (1, 10);
INSERT INTO table_b VALUES (1, 20);

-- Transaction 1 (started first)
BEGIN;
UPDATE table_a SET val = val + 1 WHERE id = 1;
-- now try to update table_b
UPDATE table_b SET val = val + 1 WHERE id = 1;  -- blocked if Transaction 2 holds lock on table_b

-- Transaction 2 (concurrent)
BEGIN;
UPDATE table_b SET val = val + 1 WHERE id = 1;
-- now try to update table_a
UPDATE table_a SET val = val + 1 WHERE id = 1;  -- DEADLOCK!

-- One of them will be killed by the database; the other succeeds.
-- Prevention: always update table_a before table_b in both transactions.

Why Views Are Leaky Abstractions That Can Wreck Query Performance

Views are sold as a clean abstraction. They aren't. A view is just a saved SELECT statement — the database re-executes it every time you query the view. No caching. No magic. When you nest views inside views, you get a query that the optimizer cannot flatten into a single execution plan. The result? A 10-row filter triggers a 10-million-row scan. I've seen devs cry over 45-second queries that should take 2ms. The fix? Materialized views if your DB supports them (PostgreSQL, Oracle, Snowflake). Or just write the damn query yourself. Views have legitimate uses (row-level security, column masking, legacy compatibility). But if you are using a view to "simplify" a join that runs every 5 seconds, you are building a performance time bomb. Always EXPLAIN your view-based queries in production before you ship.

-- A seemingly innocent view
CREATE VIEW customer_orders AS
SELECT c.id, c.name, o.total, o.order_date
FROM customers c
JOIN orders o ON c.id = o.customer_id;

-- This nested query triggers a full table scan on orders
SELECT * FROM customer_orders
WHERE order_date > '2025-01-01'
AND total > 1000;

-- EXPLAIN output shows: Seq Scan on orders (filters applied AFTER join)
-- Not pushed down to the base tables

The Difference Between WHERE and HAVING That Junior Devs Always Get Wrong

WHERE filters rows before grouping. HAVING filters groups after aggregation. That sounds simple. But in production, I see people using HAVING for everything because they learned it in a tutorial that never showed a WHERE clause with a GROUP BY. Here is the practical impact: HAVING cannot use indexes on columns that are not in the GROUP BY or the aggregate. WHERE can use any index. So if you write 'HAVING status = 'active'' on a 100M-row table, you just killed performance because the database must scan every group after aggregating. The rule? Filter raw rows with WHERE. Filter aggregated results with HAVING. Example: find customers with more than 5 orders. WHERE filters out inactive customers before grouping. HAVING then checks the count. That cuts the aggregation workload by 80%. Stop guessing. Start using both correctly.

-- WRONG: Filtering row-level data with HAVING
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING status = 'shipped' AND COUNT(*) > 5;

-- RIGHT: WHERE filters rows, HAVING filters groups
SELECT customer_id, COUNT(*) as order_count
FROM orders
WHERE status = 'shipped'
GROUP BY customer_id
HAVING COUNT(*) > 5;

-- Performance difference: 2.3s vs 0.4s on 10M order table