Top 8 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