Lesson 02 · Transactions & Pooling

Beyond the 1Z0-830 exam

A correct database app needs more than single statements: it needs transactions (all-or-nothing units of work) and, for performance, connection pooling. Both are everyday production concerns and both show up in integration tests.

Objectives

After this lesson you will be able to:

• Group statements into a transaction with commit/rollback.
• Explain isolation levels and the anomalies they prevent.
• Describe why and how a connection pool is used.

Transactions — atomic units of work

A JDBC Connection starts in auto-commit mode: every statement commits immediately. To make several statements atomic, turn auto-commit off and control the boundary yourself:

boolean prior = conn.getAutoCommit();
conn.setAutoCommit(false);          // begin transaction
try {
    insert(n1, e1);
    insert(n2, e2);                 // if this throws...
    conn.commit();                  // all-or-nothing: both rows persist
} catch (SQLException e) {
    conn.rollback();                // ...neither row persists
    throw e;
} finally {
    conn.setAutoCommit(prior);      // restore the connection's mode
}

This is ACID atomicity: the unit of work either fully happens or not at all. The lab demonstrates a rollback — a duplicate-email insert aborts the pair, leaving the original row untouched.

Gotcha

Forgetting setAutoCommit(false) means each statement auto-commits, so a later failure leaves partial data with nothing to roll back. And always restore the connection's prior mode (and never leave a transaction open) before returning a pooled connection — the next borrower inherits its state.

Isolation levels

When transactions run concurrently, isolation controls which of each other's changes they can see. Higher isolation prevents more anomalies but reduces concurrency:

Level	Prevents	Still allows
READ_UNCOMMITTED	nothing	dirty reads, non-repeatable reads, phantoms
READ_COMMITTED (common default)	dirty reads	non-repeatable reads, phantoms
REPEATABLE_READ	+ non-repeatable reads	phantom reads
SERIALIZABLE	all anomalies (as if serial)	(lowest concurrency)

• Dirty read — seeing another transaction's uncommitted change.
• Non-repeatable read — re-reading a row and getting a different value (it was updated/committed meanwhile).
• Phantom read — re-running a range query and getting new rows.

Set with conn.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED). Most apps run at the database's default (READ_COMMITTED on Postgres/Oracle/SQL Server) and raise it only where a specific anomaly would cause a bug.

Connection pooling

Opening a database connection is expensive (network handshake, auth, session setup) — far too costly to do per request. A connection pool keeps a bounded set of live connections and lends them out:

HikariConfig cfg = new HikariConfig();
cfg.setJdbcUrl("jdbc:postgresql://localhost/app");
cfg.setMaximumPoolSize(10);
try (HikariDataSource ds = new HikariDataSource(cfg)) {
    try (Connection conn = ds.getConnection()) {   // borrow from the pool
        // ...use it...
    }                                               // close() RETURNS it to the pool, not the DB
}

HikariCP is the de-facto standard. The key mental shift: with a pool, conn.close() returns the connection to the pool rather than physically closing it — so try-with-resources is still correct and still essential (a "leak" now means the pool drains and requests block).

SDET note

In tests you usually don't need a pool — a single connection per test is simpler and clearer (as the lab does). Pooling matters for load/performance tests (Module 19) and when integration tests exercise the same DataSource wiring as production. A common integration-test pattern is to begin a transaction in setup and roll back in teardown, so every test leaves the database pristine without manual cleanup.

Key Takeaways

• A connection defaults to auto-commit; for atomicity, setAutoCommit(false) then commit()/rollback(), and restore the mode in finally.
• Transactions give ACID atomicity — all-or-nothing units of work.
• Isolation levels (READ_UNCOMMITTED → SERIALIZABLE) trade concurrency for preventing dirty/non-repeatable/phantom reads; most apps use READ_COMMITTED.
• Connection pools (HikariCP) reuse expensive connections; with a pool, close() returns the connection, so try-with-resources is still required.
• A neat test pattern: transaction-per-test, rolled back in teardown for a pristine DB.

Lesson Quiz

Lesson Quiz · Transactions & Pooling0 / 5

1. How do you make two INSERTs atomic in JDBC?

   • ANothing — they're atomic by default
   • BsetAutoCommit(false), run both, then commit() (or rollback() on error)
   • COpen two connections
   • DUse a ResultSet

2. A 'dirty read' is…

   • AReading committed data
   • BSeeing another transaction's uncommitted changes
   • CA slow query
   • DReading a deleted row

3. Which isolation level prevents the most anomalies?

   • AREAD_UNCOMMITTED
   • BREAD_COMMITTED
   • CREPEATABLE_READ
   • DSERIALIZABLE

4. Why use a connection pool?

   • ATo prevent SQL injection
   • BOpening DB connections is expensive; a pool reuses a bounded set of live connections
   • CTo run migrations
   • DTo sort results

5. With a connection pool, what does conn.close() do?

   • APhysically closes the TCP connection to the DB
   • BReturns the connection to the pool for reuse
   • CNothing
   • DRolls back the transaction permanently

---

Next: Test Databases.