Interview Preparation· 60 min

Spring Boot, SQL, and Backend Scenarios

Practice framework, data, security, debugging, and production scenario questions.

Java interview lesson
Question 34 · Not reviewed

Interview prep · backend scenarios

Can you explain this clearly in an interview?

Use the strong answer as your reference, explain it aloud in your own words, then choose how soon you want to review it again.

Interview prompt

Spring Boot, SQL, and Backend Scenarios

Explain it aloud in your own words

Strong answer

Scenario rounds give you a symptom and watch how you investigate. A good answer narrows the problem with evidence (metrics, logs, query plans, thread dumps), forms a hypothesis that fits all the symptoms, fixes the cause rather than the symptom, and adds a guard so it does not regress. Most Spring Boot production incidents involve a short list of causes: proxy surprises, transaction boundaries, N+1 or missing indexes, pool exhaustion, missing timeouts, and unsafe retries.

Key points

  • @Transactional only applies through the proxy: public method, called from another bean, unchecked exception rolls back.
  • Connection pool exhaustion usually means slow queries or a remote call inside a transaction, not a pool that is too small.
  • Read the execution plan before adding an index, and check that the index matches the predicate and sort.
  • Make write endpoints idempotent so client and gateway retries do not create duplicates.

Interviewer looks for

Narrate the investigation step by step, state what evidence would confirm or reject each hypothesis, and finish with the long-term fix plus how you would prevent recurrence.

Java example

1// Scenario: "Orders sometimes save without their audit row."
2@Service
3class OrderService {
4  @Transactional
5  public void place(Order order) {
6    repository.save(order);
7    this.audit(order); // self-invocation: REQUIRES_NEW is ignored
8  }
9
10  @Transactional(propagation = Propagation.REQUIRES_NEW)
11  public void audit(Order order) { auditRepository.save(AuditEntry.of(order)); }
12}
13
14-- Scenario: "Top customers report is slow."
15SELECT c.id, c.name, SUM(o.total) AS revenue
16FROM customers c
17JOIN orders o ON o.customer_id = c.id
18WHERE o.created_at >= now() - interval '30 days'
19GROUP BY c.id, c.name
20ORDER BY revenue DESC
21LIMIT 10;  -- check EXPLAIN ANALYZE; index orders (created_at, customer_id)

Topic interview questions

Revise this topic like an interviewer

These questions are tied to this lesson. Read each question aloud, then compare your explanation with the strong answer.

5 questions
Q1Basic

A method annotated with @Transactional does not roll back after an exception. What could be wrong?

Strong answer

Common causes are self-invocation that bypasses the proxy, a non-public method, a checked exception (which does not roll back by default unless rollbackFor is set), the exception being caught and swallowed, or the bean not being managed by Spring. Check each against the code path.

Q2Intermediate

The API becomes slow under load and logs show connection pool timeouts. How do you investigate?

Strong answer

Look at pool metrics (active, pending, acquire time) alongside query latency and thread dumps. Usually connections are held too long because of slow queries, N+1 patterns, or remote calls inside transactions. Fix those, add query and statement timeouts, and only then size the pool against database capacity.

Q3Intermediate

Write a SQL query to find the second-highest salary in each department.

Strong answer

Use a window function: SELECT department_id, salary FROM (SELECT department_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees) ranked WHERE rnk = 2. DENSE_RANK handles ties so duplicates of the top salary do not hide the second distinct value.

Q4Advanced

Users report being charged twice when the payment page times out. How would you fix it?

Strong answer

Make the charge endpoint idempotent: require an idempotency key, store it with a unique constraint and the result in the same transaction, and return the stored result on retries. Also pass an idempotency key to the payment provider and reconcile pending payments asynchronously.

Q5Advanced

A query that was fast last month is now slow. What do you check?

Strong answer

Compare EXPLAIN ANALYZE output now and before: data growth, changed plan, stale statistics, a missing or unused index because of a function or type mismatch on the column, lock contention, or a new filter. Fix with the right index or query rewrite, refresh statistics, and add a regression check on query latency.

How well do you know this?

Your review choice is saved locally and synced to your learner profile when signed in.

34 / 35