Advanced SQL Interview Questions for Backend Developer

Advanced-level SQL interview questions for Backend Developer positions.

1. Explain optimistic vs pessimistic locking strategies.

Answer: Pessimistic locking locks rows when reading (SELECT FOR UPDATE) - guarantees no conflicts but blocks other transactions. Optimistic locking uses version columns, checks at write time - better for read-heavy workloads with rare conflicts.

-- Pessimistic: Lock row until transaction completes
BEGIN;
SELECT * FROM inventory WHERE id = 1 FOR UPDATE;
-- Other transactions wait here
UPDATE inventory SET quantity = quantity - 1 WHERE id = 1;
COMMIT;

-- Optimistic: Version check at write time
UPDATE inventory 
SET quantity = quantity - 1, version = version + 1
WHERE id = 1 AND version = 5;
-- If affected_rows = 0, another transaction modified it

2. How would you handle database migrations in a production environment?

Answer: 1) Use migration tools (Flyway, Prisma Migrate). 2) Make migrations backward-compatible. 3) Separate deploy from migrate. 4) Test on staging first. 5) Have rollback plan. 6) For large tables, use online DDL or create new table and swap.

-- Safe column addition (non-blocking)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Safe column rename (two-step deployment)
-- Step 1: Add new column, update code to write to both
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
UPDATE users SET full_name = name;

-- Step 2 (after code deployed): Remove old column
ALTER TABLE users DROP COLUMN name;