Your app vs. the database

Most database bugs never show up on your laptop. You click through the app alone, one request at a time, and everything is correct. Production runs the same code hundreds of times in parallel, holds connections for too long, retries, crashes halfway, and reads from replicas. This guide is about the mechanisms behind that: connections and pools, transactions, isolation levels, Spring's @Transactional and propagation, locking, JPA's hidden queries, and what to check in every application that talks to a database.

Examples use Java, Spring and JPA/Hibernate on PostgreSQL, with MySQL InnoDB notes where it behaves differently. The ideas carry over to any stack. Facts were checked against the PostgreSQL 18, Spring Framework 7.0, Hibernate ORM 6.6, HikariCP and MySQL 8.4 documentation in September 2026.

The guide has levels. Chapters and blocks carry a chip — L1 essentials, L2 practitioner, L3 expert, L4 operations. Choose your depth with the switch in the bottom-left corner; deeper material hides until you want it. The map below jumps anywhere and ticks off what you've read.

Every query travels over a connection: a network session with the database server. Opening one costs a TCP handshake, authentication and a server-side session, so applications keep a connection pool. In Spring Boot that's HikariCP by default. A request borrows a connection, runs its statements, and returns it.

The pool is small on purpose. HikariCP's maximumPoolSize defaults to 10. When all ten are borrowed, the next getConnection() call blocks for up to connectionTimeout — default 30 seconds — and then throws. So the question is never "how fast is my query" alone. It is "how long does each request hold a connection".

Notice what broke: not the database. The database was idle while requests queued. The pool ran dry because connections were held while the code waited on something else.

A transaction groups statements into one unit: BEGIN, several statements, COMMIT — or ROLLBACK, which undoes all of them. The classic promise is ACID:

Atomicity handles crashes. Isolation handles neighbours. When two transactions interleave, each one can be correct alone and the combination still wrong. The SQL standard names three phenomena — dirty read, non-repeatable read, phantom read — and PostgreSQL's documentation adds a fourth, the serialization anomaly. Two more are what bites applications in practice: the lost update and write skew (a kind of serialization anomaly).

The SQL standard defines four isolation levels by which phenomena they must prevent. Databases may give you more than the standard requires, and they do — differently. That's why "Repeatable Read" in PostgreSQL and in MySQL are not the same thing.

// Spring: per transaction
@Transactional(isolation = Isolation.REPEATABLE_READ)
public void closeMonth() { … }

-- SQL: first statement inside the transaction
BEGIN;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

Spring doesn't rewrite your method. By default (proxy mode) it wraps the bean in a proxy. When another bean calls your method, the call hits the proxy first: it begins a transaction (for JDBC: takes a connection and switches auto-commit off), calls your code, then commits — or rolls back if an exception escapes. That design explains almost every @Transactional surprise.

When a @Transactional method calls another one (through a proxy), the inner method's propagation decides: join the running transaction, start a new one, use a savepoint, or run without one. Spring's docs distinguish the physical transaction (the real database transaction on one connection) from logical transaction scopes (one per annotated method).

Back to the lost update. Alice and Bob both try to buy the last ticket. Read Committed alone lets both succeed. Here are the standard fixes, from simplest to most specific.

@Entity
class Ticket {
  @Id Long id;
  int stock;
  @Version long version;          // optimistic locking
}

interface TicketRepo extends JpaRepository<Ticket, Long> {
  @Lock(LockModeType.PESSIMISTIC_WRITE)            // SELECT … FOR UPDATE
  @Query("select t from Ticket t where t.id = :id")
  Ticket lockById(Long id);

  @Modifying
  @Query("update Ticket t set t.stock = t.stock - 1 where t.id = :id and t.stock > 0")
  int takeOne(Long id);                            // 1 = sold, 0 = sold out
}

Inside a transaction, Hibernate keeps a persistence context (the first-level cache): every entity you load is tracked. Change a field of a managed entity and, as Hibernate's guide puts it, "there is no need to call a particular method to make your modifications persistent" — dirty checking writes it at flush time.

Flushes happen in AUTO mode: before commit, before a JPQL/HQL query that overlaps the queued changes, and — with the JPA EntityManager — before every native SQL query. So a SELECT in your code can trigger an UPDATE first.

Every statement is a network round trip. Inserting 1,000 rows one by one costs 1,000 of them. JDBC batching sends many statements at once; in Hibernate you enable it with hibernate.jdbc.batch_size (its guide suggests a value between 10 and 50).

Paging: OFFSET vs keyset

PostgreSQL's docs: "The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient." Page 5,000 of an infinite scroll reads and throws away 100,000 rows. Keyset pagination remembers where the last page ended:

-- OFFSET: cost grows with the page number
SELECT * FROM event ORDER BY id LIMIT 20 OFFSET 100000;
-- keyset: an index seek, same cost on every page
SELECT * FROM event WHERE id > :lastSeenId ORDER BY id LIMIT 20;

PostgreSQL (and InnoDB) use multiversion concurrency control. An UPDATE doesn't overwrite a row in place; it creates a new version. Each statement reads from a snapshot — the set of transactions committed at a certain moment — and picks the version that snapshot can see. So plain reads never wait for writers. The only lock that blocks a plain SELECT is ACCESS EXCLUSIVE.

The difference between Read Committed and Repeatable Read is just when the photo is taken: at the start of each statement, or at the first statement of the transaction.

Write skew slips through Snapshot Isolation because the two transactions write different rows. PostgreSQL's Serializable level uses Serializable Snapshot Isolation: it tracks what each transaction read (predicate locks, shown as SIReadLock in pg_locks) flags read/write dependencies among concurrent Serializable transactions, and aborts one when a combination of them could lead to a serialization anomaly. These predicate locks never block and cannot cause deadlocks.

The retry loop — outside the transaction

PostgreSQL's advice: retry the complete transaction, "including all logic that decides which SQL to issue and/or which values to use". It also suggests retrying deadlocks (40P01) and, in some cases, unique violations (23505). In Spring, that means the retry wraps the proxy, never the other way round:

// caller bean — NOT @Transactional
public void bookWithRetry(Cmd cmd) {
  for (int attempt = 1; ; attempt++) {
    try {
      bookingService.book(cmd);           // @Transactional(isolation = SERIALIZABLE): a fresh transaction each time
      return;
    } catch (ConcurrencyFailureException e) {   // parent of Spring's lock / deadlock / serialization failures
      if (attempt == 3) throw e;
      sleep(backoffWithJitter(attempt));
    }
  }
}

Every UPDATE locks the rows it changes until commit. If T1 locks A then wants B, while T2 locks B then wants A, each waits for the other forever. PostgreSQL waits deadlock_timeout (default 1 s) before running its relatively expensive deadlock check, then aborts one of the transactions with SQLSTATE 40P01.

A transaction only covers the database. The moment your method also sends a message, an email or an HTTP request, you have a dual write: two systems that can't commit together. Whatever order you pick, some crash point leaves them disagreeing.

@Transactional
public void placeOrder(Order o) {
  orders.save(o);
  outbox.save(new OutboxEvent("OrderPlaced", o.getId(), toJson(o)));   // same transaction
}
// a separate relay (poller or CDC) reads the outbox table, publishes, marks rows as sent

A transaction holds its connection — and its row locks — from BEGIN to COMMIT. Everything slow inside it multiplies: an HTTP call, a file upload, a user prompt, a loop that calls another service per row. PostgreSQL's docs call holding transactions open while waiting for user input "a bad idea".

Timeouts: your safety net

PostgreSQL's docs say setting statement_timeout, lock_timeout or transaction_timeout in postgresql.conf is not recommended "because it would affect all sessions". Set them per role, per session or per transaction instead.

Replicas (PostgreSQL hot standby) take read load off the primary, but "the data on the standby takes some time to arrive from the primary server", so the same query on both "might therefore return differing results". The docs call standby data eventually consistent.

Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock — the one lock that blocks even plain SELECTs. The statement itself may be instant. The danger is getting the lock: it must wait for every open transaction that touched the table, and while it waits, it blocks others.

pg_stat_activity shows one row per server process: its state, current query and when the transaction started. The state to hunt for is idle in transaction: "the backend is in a transaction, but is not currently executing a query" — typically your app holding a connection while doing something else.

-- open transactions, oldest first
SELECT pid, state, now() - xact_start AS xact_age, wait_event_type, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL
ORDER BY xact_start;

-- who blocks whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 60)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;

Each card is a question interviewers like because the "obvious" answer is wrong. Say your answer out loud before you open it.

Cheat sheet