Top 9 Senior Interview Questions

Atomicity

All operations in a transaction succeed or all are rolled back. No partial writes.

How it works: DB maintains a write-ahead log (WAL). On commit, changes are flushed to WAL first. On rollback or crash, WAL allows undoing partial work.

-- Transfer $100: both statements must succeed or both rolled back
BEGIN;
  UPDATE account SET balance = balance - 100 WHERE id = 1;
  UPDATE account SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- If UPDATE #2 fails: UPDATE #1 is reversed. Balance never disappears.

Consistency

A transaction moves the database from one valid state to another. Constraints (NOT NULL, UNIQUE, CHECK, FK) are enforced at commit.

Note: Consistency is partly the DB's job (constraints) and partly the application's job (business rules). The DB can't enforce "balance ≥ 0" unless you add a CHECK constraint.

Isolation

Concurrent transactions don't see each other's intermediate states. The degree depends on the isolation level (see Q2).

Without isolation: two withdrawals running simultaneously could both read the same balance, both subtract, and result in insufficient funds going negative.

Durability

Once committed, data survives crashes. Guaranteed by flushing the WAL to disk before acknowledging the commit. Even if the server crashes a millisecond after COMMIT, the data is safe.

The three problems isolation solves

• Dirty read: reading uncommitted data from another transaction (that might roll back).
• Non-repeatable read: re-reading the same row gets different values (another transaction committed a change between reads).
• Phantom read: re-running a query returns different rows (another transaction inserted/deleted rows matching the WHERE clause).

Isolation levels

* PostgreSQL's REPEATABLE READ also prevents phantoms (uses MVCC snapshots).

Practical choices

• READ COMMITTED: most services — reads see only committed data, acceptable for normal queries.
• REPEATABLE READ: reports that read the same data multiple times and need consistency.
• SERIALIZABLE: financial transfers, inventory allocation — where phantom reads would cause real business errors. Use sparingly — lower throughput.

In JPA

@Transactional(isolation = Isolation.READ_COMMITTED) // default
@Transactional(isolation = Isolation.SERIALIZABLE)   // for critical ops

What an index does

An index is a separate data structure (usually B-tree) that maps column values to row locations. Speeds up reads at the cost of write overhead (every INSERT/UPDATE/DELETE must maintain the index).

Index types

Composite index — leftmost prefix rule

CREATE INDEX idx_order ON orders(customer_id, status, created_at);

-- USES the index (leftmost prefix matched):
WHERE customer_id = 5
WHERE customer_id = 5 AND status = 'PENDING'
WHERE customer_id = 5 AND status = 'PENDING' AND created_at > '2024-01-01'

-- Does NOT use the index (skips customer_id):
WHERE status = 'PENDING'
WHERE created_at > '2024-01-01'

Covering index

-- Query only needs customer_id and order_total:
SELECT customer_id, order_total FROM orders WHERE customer_id = 5;

-- Covering index: all needed columns are IN the index — no table access:
CREATE INDEX idx_covering ON orders(customer_id) INCLUDE (order_total);
-- "Index-only scan" — significantly faster on high-volume reads.

EXPLAIN ANALYZE

EXPLAIN ANALYZE
SELECT o.id, c.name, o.total
FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'PENDING' AND o.created_at > NOW() - INTERVAL '7 days';

What to look for

Common optimisation checklist

• Add index on columns in WHERE, JOIN ON, ORDER BY.
• Replace SELECT * with explicit columns (enables covering indexes).
• Rewrite correlated subqueries as JOINs.
• Use keyset pagination instead of OFFSET for large pages:

  -- SLOW for large offsets (scans and discards rows):
  SELECT * FROM orders ORDER BY id LIMIT 20 OFFSET 10000;
  
  -- FAST: keyset pagination (uses index directly):
  SELECT * FROM orders WHERE id > :lastSeenId ORDER BY id LIMIT 20;

• Partition large tables by date range — old partitions rarely queried are skipped.

The problem

// 1 query to load 100 orders:
List<Order> orders = orderRepo.findAll();

// Then for EACH order, a separate query for its items (lazy loading):
orders.forEach(o -> o.getItems().size()); // triggers N more queries
// Result: 1 + 100 = 101 queries. With 1000 orders: 1001 queries.

Detection

• Enable Hibernate SQL logging: spring.jpa.show-sql=true and spring.jpa.properties.hibernate.format_sql=true.
• Use Hibernate Statistics or p6spy to count queries per request.
• Symptom in production: fast single-object endpoints, very slow list endpoints.

Fix 1 — JOIN FETCH

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
List<Order> findWithItems(@Param("status") String status);
// 1 query with JOIN — all data fetched at once

Fix 2 — @EntityGraph

@EntityGraph(attributePaths = {"items", "customer"})
List<Order> findByStatus(String status);
// Declarative fetch plan — no JPQL needed

Fix 3 — DTO projection (best for read-only)

@Query("SELECT new com.example.OrderSummary(o.id, o.total, c.name) FROM Order o JOIN o.customer c")
List<OrderSummary> getOrderSummaries();
// Returns exactly what you need, no entity overhead, no lazy loading

Fix 4 — @BatchSize

@BatchSize(size = 50)
@OneToMany List<Item> items;
// Hibernate loads items in batches of 50 → 1 + ceil(N/50) queries instead of 1+N

Pessimistic locking

Lock the row immediately when reading. Other transactions must wait.

-- Database level:
SELECT * FROM account WHERE id = 1 FOR UPDATE;  -- row locked until COMMIT

-- JPA:
Account acc = em.find(Account.class, id, LockModeType.PESSIMISTIC_WRITE);

Use when: high contention and cost of failure is high (balance deduction, seat booking, inventory reservation). Lock guarantees you won't conflict.

Optimistic locking

No lock held. Add a version column. At commit, check that version hasn't changed — if it has, another transaction beat you, retry.

@Entity
class Account {
    @Version
    private int version;  // auto-incremented by Hibernate on every update
}

-- Hibernate generates:
UPDATE account SET balance = 900, version = 2 WHERE id = 1 AND version = 1;
-- If 0 rows updated → version changed → OptimisticLockException → retry

Use when: low contention and retry is cheap (profile updates, viewing data). High throughput — no blocking.

Decision guide

What is a deadlock

Two (or more) transactions each hold a lock the other needs. Neither can proceed. The DB detects this via a wait-for graph and kills one transaction (the "victim") to break the cycle.

-- Transaction A:                    Transaction B:
BEGIN;                               BEGIN;
UPDATE account SET ... WHERE id=1;   UPDATE account SET ... WHERE id=2;
-- (waits for B's lock on id=2)        -- (waits for A's lock on id=1)
UPDATE account SET ... WHERE id=2;   UPDATE account SET ... WHERE id=1;
-- DEADLOCK: DB kills one transaction

Prevention

• Consistent lock ordering: always acquire locks in the same order (e.g., lower account_id first). Eliminates cyclic dependency.
• Keep transactions short: acquire locks, do the work, release quickly. Don't hold locks across external calls or user input.
• Lock timeouts: SET lock_timeout = '3s' — transaction fails fast instead of waiting indefinitely.

// Always lock in consistent order by account ID:
Long firstId  = Math.min(fromId, toId);
Long secondId = Math.max(fromId, toId);
em.find(Account.class, firstId,  LockModeType.PESSIMISTIC_WRITE);
em.find(Account.class, secondId, LockModeType.PESSIMISTIC_WRITE);

Handling

// Deadlock = transient error — safe to retry with backoff:
@Retryable(
    retryFor = { DeadlockLoserDataAccessException.class },
    maxAttempts = 3,
    backoff = @Backoff(delay = 100, multiplier = 2)
)
@Transactional
public void transfer(Long from, Long to, BigDecimal amount) { ... }

Why connection pooling

Opening a DB connection is expensive (~50–200ms: TCP handshake, authentication, session setup). A pool pre-creates N connections and lends them to threads. Connection acquired in microseconds.

HikariCP key config

spring:
  datasource:
    hikari:
      maximum-pool-size: 20         # max connections to DB
      minimum-idle: 5               # keep 5 warm connections always
      connection-timeout: 3000      # fail fast if no connection available (3s)
      idle-timeout: 600000          # close idle connections after 10min
      max-lifetime: 1800000         # recycle connections before DB kills them (30min)
      keepalive-time: 30000         # send keepalive query to prevent idle timeout

How to size the pool

HikariCP's advice: pool_size = (core_count * 2) + effective_spindle_count. For most services: 10–20 connections per pod is the sweet spot.

• More connections ≠ more throughput. DB CPU/disk is the bottleneck, not connection count.
• With 10 pods × 20 connections = 200 connections to the DB. Stay within the DB's max_connections limit.
• Use PgBouncer or RDS Proxy in front of the DB to multiplex connections at scale.

Connection pool exhaustion — a common production issue

// Symptoms: requests timing out, HikariCP timeout exceptions in logs
// Root cause: threads holding connections too long (slow queries, forgotten transactions)
// Debug: monitor pool metrics:
management.metrics.enable.hikari=true
// Grafana: hikaricp_connections_active, hikaricp_connections_pending
// If pending > 0 for more than a second → pool is too small OR queries are too slow

Flyway vs Liquibase — choosing the right tool

Flyway fundamentals

-- File naming: V{version}__{description}.sql
-- V1__create_users_table.sql
-- V2__add_email_index.sql
-- R__seed_lookup_data.sql   (repeatable — reruns when content changes)

-- Flyway tracks applied migrations in the schema_history table:
-- installed_rank | version | description | type | script | checksum | success

-- CRITICAL: Never modify an applied migration file.
-- Flyway recalculates the checksum at startup and throws:
-- FlywayException: Validate failed: Migration checksum mismatch
-- Fix: create a new migration that corrects the schema.

# application.yml — Spring Boot Flyway config
spring:
  flyway:
    enabled: true
    locations: classpath:db/migration
    baseline-on-migrate: false   # true only for existing DBs first run
    validate-on-migrate: true    # checksum check at startup (keep true)
    out-of-order: false          # true allows V5 to run after V6 (careful)
    schemas: public
    # In multi-pod deployments, Flyway uses a DB advisory lock
    # so only one pod runs migrations even if multiple start simultaneously

The expand-contract pattern — zero-downtime migrations

Zero-downtime deployment means the new application version and the old application version must be able to run against the same database simultaneously during rolling deployment. Most migrations break this if done naively.

Expand-Contract (aka Parallel Change) splits a breaking schema change into three safe phases:

-- Example: renaming column "username" to "display_name"

-- PHASE 1: Deploy V5 migration — add new column (nullable, backward compatible)
ALTER TABLE users ADD COLUMN display_name VARCHAR(255);

-- Old app: reads/writes username (doesn't see display_name)
-- New app: reads display_name if not null, else username (backward compat read)
--          writes to BOTH columns during transition

-- PHASE 2: Deploy V6 migration — backfill
UPDATE users SET display_name = username WHERE display_name IS NULL;

-- PHASE 3: Deploy V7 migration — after all old pods are gone
ALTER TABLE users ALTER COLUMN display_name SET NOT NULL;
ALTER TABLE users DROP COLUMN username;

Common migration pitfalls

• Adding NOT NULL without a default — fails instantly on a table with existing rows. Always add the column as nullable, backfill, then add the constraint.
• Adding an index without CONCURRENTLY (PostgreSQL) — CREATE INDEX takes a full table lock. Use CREATE INDEX CONCURRENTLY — it takes no exclusive lock but cannot run inside a transaction, so it needs a dedicated Flyway migration with -- flyway:nonTransactional.
• Large backfill in one UPDATE — locks rows for the duration, causes timeouts. Batch it: UPDATE users SET x = y WHERE id BETWEEN 1 AND 10000, repeat in a loop.
• Modifying applied migrations — Flyway's checksum validation catches this at startup, which is good. Fix forward, never rewrite history.

-- Safe index creation in Flyway (PostgreSQL)
-- V8__add_users_email_idx.sql
-- flyway:nonTransactional  -- tell Flyway not to wrap in a transaction
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_users_email ON users(email);