Transactions, Multiple Datasources & Query Tuning — Interview Questions

How to use this lesson

These are the "you've been in production" questions. The strongest answers show:

• you know where transaction boundaries belong;
• you know why @Transactional sometimes silently does nothing;
• you know how to change a schema while the old code is still running;
• you have a methodical approach to a slow query: measure, explain, fix, verify.

Q1. What mechanisms does Spring Boot provide for transaction management?

Short answer: Spring's PlatformTransactionManager abstraction (JpaTransactionManager, DataSourceTransactionManager, JtaTransactionManager, and the reactive ReactiveTransactionManager), used in two ways:

1. Declarative: @Transactional on methods or classes. An AOP proxy begins the transaction before the method, and commits or rolls back afterwards.
2. Programmatic: TransactionTemplate (or TransactionManager directly), for fine-grained boundaries inside a method.

Boot auto-configures the right transaction manager for your data access stack.

Key points to cover:

• Propagation:
  • REQUIRED (the default) joins an existing transaction.
  • REQUIRES_NEW suspends the current one and starts a new one, which is useful for audit logs that must persist even if the caller rolls back.
  • NESTED uses savepoints.
  • There's also MANDATORY, SUPPORTS, NOT_SUPPORTED and NEVER.
• Isolation: READ_COMMITTED (the usual database default), REPEATABLE_READ and SERIALIZABLE.
• Rollback rules: by default, rollback happens only on unchecked exceptions and Errors. Checked exceptions commit, unless you set rollbackFor = Exception.class.
• readOnly = true (Hibernate skips dirty checking, and some drivers route the query to a replica), and timeout.

Learn it in depth → @Transactional Deep Dive

Q2. Can transaction management be managed externally, or must it be inside the application?

Short answer: Both are possible:

• Local, application-managed transactions (a single resource, handled by Spring's JpaTransactionManager) are the norm, and the fastest.
• Externally coordinated transactions use JTA/XA: a transaction manager (Atomikos, Narayana, or an application server's) coordinates two-phase commit across several XA resources (two databases, or a database plus a JMS broker). Spring plugs in through JtaTransactionManager, and your @Transactional code doesn't change.

Key points to cover:

• XA brings latency, operational complexity, in-doubt transactions and limited support (many cloud databases and Kafka don't support XA).
• Across microservices, you don't use distributed transactions. You use sagas, the outbox pattern and idempotency, which give eventual consistency.

Learn it in depth → Two-Phase Commit

Q3. You're designing an e-commerce application that needs precise control over transactions. What approach would you take?

Short answer:

• Keep the transaction boundaries at the service layer, and small: one business operation, one transaction.
• Use declarative @Transactional for most methods, and programmatic TransactionTemplate where one method needs several independent commits.
• Never hold a database transaction open across a remote call. Reserve stock and create the order as PENDING and commit; call the payment gateway outside the transaction; then commit the result in a second transaction.
• Protect against races with optimistic locking (@Version), or SELECT … FOR UPDATE for stock.
• Across services (inventory, payment, shipping), use a saga with compensations, and the outbox pattern for reliable events.

@Service
public class CheckoutService {
    private final TransactionTemplate tx;
    private final PaymentGateway payments;
    // ...
    public OrderId checkout(CheckoutRequest req) {
        Order order = tx.execute(s -> {                                 // transaction 1: reserve + PENDING order
            inventory.reserve(req.items());                             // @Version on Stock detects races
            return orders.save(Order.pending(req));
        });
        ChargeResult charge = payments.charge(order.total(), req.paymentToken(), order.idempotencyKey());   // no transaction held
        tx.executeWithoutResult(s -> {                                  // transaction 2: final state + outbox event
            if (charge.approved()) { order.markPaid(charge.reference()); outbox.add(new OrderPaid(order.id())); }
            else { order.markFailed(charge.reason()); inventory.release(req.items()); }
            orders.save(order);
        });
        return order.id();
    }
}

Common trap: the source calls @Transactional "programmatic". It's declarative. Programmatic means TransactionTemplate or PlatformTransactionManager in code.

Q4. How do you configure and connect to multiple databases in Spring Boot?

Short answer:

1. Define one set of beans per database: DataSource, LocalContainerEntityManagerFactoryBean and PlatformTransactionManager (or a JdbcTemplate).
2. Mark one set @Primary, so auto-wiring without a qualifier works.
3. Point each set at its own repository and entity packages with @EnableJpaRepositories(basePackages, entityManagerFactoryRef, transactionManagerRef).
4. Inject with @Qualifier, and use @Transactional("reportingTransactionManager") for the secondary database.

@Configuration
@EnableJpaRepositories(basePackages = "com.shopin.reporting.repo",
        entityManagerFactoryRef = "reportingEmf", transactionManagerRef = "reportingTx")
class ReportingDbConfig {
    @Bean @ConfigurationProperties("app.datasource.reporting")
    DataSourceProperties reportingProps() { return new DataSourceProperties(); }

    @Bean DataSource reportingDataSource() { return reportingProps().initializeDataSourceBuilder().build(); }

    @Bean LocalContainerEntityManagerFactoryBean reportingEmf(EntityManagerFactoryBuilder b) {
        return b.dataSource(reportingDataSource()).packages("com.shopin.reporting.domain").persistenceUnit("reporting").build();
    }
    @Bean PlatformTransactionManager reportingTx(@Qualifier("reportingEmf") EntityManagerFactory emf) {
        return new JpaTransactionManager(emf);
    }
}
// The primary database gets the same trio, annotated @Primary, pointing at com.shopin.orders.*

Key points to cover:

• Defining your own DataSource switches off Boot's auto-configured one, so the primary must be defined explicitly too.
• A @Transactional method using the primary transaction manager does not cover writes to the secondary database. Writes to both aren't atomic without JTA, so design for that (the outbox pattern, or idempotent retries).
• For read/write splitting or multi-tenant routing to the same schema, use AbstractRoutingDataSource (a lookup key from a thread-local), usually wrapped in LazyConnectionDataSourceProxy.

Q5. How do you achieve multiple database connections? (The short version)

Short answer: Separate configuration classes, each producing its own DataSource, EntityManagerFactory and TransactionManager; @Primary on the default; @Qualifier at injection points; and separate repository packages bound through @EnableJpaRepositories. Each DataSource has its own connection pool (HikariCP), so size each pool for its own workload.

Learn it in depth → Connection Pooling

Q6. You need to migrate an application to a new database schema with no downtime. How would you plan it?

Short answer: Use expand/contract (parallel change), with versioned migrations (Flyway or Liquibase), and backward-compatible steps, so that the old and new versions of the application can run at the same time during a rolling deployment.

1. Expand: add the new columns or tables (nullable, or with defaults), and add indexes concurrently or online. Nothing breaks the old code.
2. Dual-write: deploy an application version that writes both the old and new structures, and still reads the old one.
3. Backfill: migrate the existing rows in small batches, throttled, and resumable. Verify with counts and checksums.
4. Switch reads: deploy a version that reads the new structure, behind a feature flag so you can roll back instantly. Keep dual-writing.
5. Stop the old writes, once you're confident.
6. Contract: in a later release, drop the old columns or tables.

Key points to cover:

• Never rename or drop in one step. A rename is "add new, copy, switch, drop old".
• Avoid long table locks: know which DDL your database runs online. For example, Postgres's ADD COLUMN with a constant default is instant, but CREATE INDEX without CONCURRENTLY locks writes.
• Each step must be independently deployable and reversible.

Q7. You have a complex query that runs slowly. How would you optimise it?

Short answer: Measure first, then fix the biggest cost.

1. Find it and measure it: use APM traces, pg_stat_statements or the slow query log, and Hibernate statistics. Is it one slow query, or an N+1 pattern of many small ones?
2. Read the execution plan (EXPLAIN ANALYZE). Look for sequential scans on large tables, bad row estimates, nested loops over big sets, sorts spilling to disk, and filtering after joins.
3. Fix at the database:
   • Add a composite or covering index matching the WHERE/JOIN/ORDER BY columns.
   • Rewrite non-sargable predicates: no functions on indexed columns, no leading % wildcards.
   • Replace OR chains or correlated subqueries with joins, EXISTS or UNION ALL.
   • Use keyset pagination instead of a big OFFSET.
   • Update the statistics.
4. Fix in the application:
   • Fetch only the needed columns (DTO projections), not whole entity graphs.
   • Use JOIN FETCH or @EntityGraph for N+1 problems.
   • Batch the IN lists, and set the fetch size for large reads.
   • Use a native query when JPQL generates poor SQL.
5. Architectural options:
   • Cache stable results (@Cacheable or Redis).
   • Precompute with a materialised view or summary table.
   • Move reporting queries to a read replica or an analytics store.
6. Verify: compare the before and after plans and latencies, under realistic data volumes, and add a regression test or dashboard.

Learn it in depth → Query Execution Plans

Follow-up questions this topic invites — and their answers

Q: Why doesn't @Transactional work on a private method, or when it's called from the same class? A: It's applied by a proxy. Private methods can't be proxied (in Spring 6, with class-based proxies, only public and protected methods are intercepted), and this.method() calls bypass the proxy entirely. Move the method to another bean, or use TransactionTemplate.

Q: What happens if a REQUIRES_NEW method throws inside an outer transaction? A: The inner transaction rolls back. If the exception propagates, the outer one rolls back too, unless the outer code catches it. If the inner method catches an exception from a REQUIRED (joined) call, the shared transaction is already marked rollback-only, and the outer commit fails with UnexpectedRollbackException.

Q: How do you avoid long-running transactions in batch jobs? A: Process in chunks, each with its own transaction (Spring Batch chunk processing, or TransactionTemplate per batch). Use StatelessSession or clear the persistence context periodically, and avoid remote calls inside transactions.

Q: How would you do read/write splitting to a replica? A: Use an AbstractRoutingDataSource that chooses "replica" when the current transaction is read-only (TransactionSynchronizationManager.isCurrentTransactionReadOnly()), wrapped in LazyConnectionDataSourceProxy, so the choice is made after the transaction attributes are known. Beware replication lag for read-after-write flows.