SQLMentor // learn db2

Transactions

Difficulty: Advanced · ~10 min read

Overview

A transaction is a unit of work that DB2 either commits in full or rolls back in full — there's no partial state. This is the foundation of trusting a database to handle money, orders, or anything that must remain consistent under concurrent users.

The four classic ACID guarantees:

Property Meaning
Atomicity All statements in the transaction succeed, or none of them do
Consistency The database moves from one valid state to another (constraints hold)
Isolation Concurrent transactions can't see each other's partial state
Durability Once committed, the change survives crashes

DB2 wraps every statement in an implicit transaction. You finish it with COMMIT (make changes permanent) or ROLLBACK (discard them).

DB2 supports four isolation levels, each trading consistency against concurrency:

Level Code Sees Holds locks until
Uncommitted Read UR dirty + committed reads end of read
Cursor Stability CS (default) only committed; current row locked row read complete
Read Stability RS only committed; rows you read stay stable end of transaction
Repeatable Read RR full serializable end of transaction

Syntax

-- Implicit transaction starts; finish with one of:
COMMIT;
ROLLBACK;

-- Savepoints inside a transaction
SAVEPOINT sp1 ON ROLLBACK RETAIN CURSORS;
ROLLBACK TO SAVEPOINT sp1;
RELEASE SAVEPOINT sp1;

-- Set isolation level for the session
SET CURRENT ISOLATION = CS;     -- UR | CS | RS | RR

-- Or per-statement
SELECT * FROM orders WITH UR;   -- override on a specific query

Examples

Example 1: A simple atomic transfer

-- Two statements that must succeed or fail together
UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

If the second UPDATE fails (e.g. account 2 doesn't exist), nothing has been persisted yet — issue ROLLBACK and the first update vanishes.

Example 2: Rolling back after an error

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- Discover a problem
ROLLBACK;
-- balance for account 1 is unchanged

Example 3: Savepoints — partial rollback

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
SAVEPOINT sp_after_debit ON ROLLBACK RETAIN CURSORS;

UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
INSERT INTO transfer_log VALUES (1, 2, 100, CURRENT_TIMESTAMP);

-- Logging failed for some reason → undo only the credit + log
ROLLBACK TO SAVEPOINT sp_after_debit;
-- account 1's debit is preserved; account 2's credit and the log row are gone

-- Could now retry with a different log table, or just COMMIT
COMMIT;

Savepoints let you build retry logic without losing earlier work in the transaction.

Example 4: Reading with different isolation

-- Default (CS): only see committed changes
SELECT balance FROM accounts WHERE account_id = 1;

-- UR: see uncommitted changes from other sessions (dirty read)
SELECT balance FROM accounts WHERE account_id = 1 WITH UR;

-- RR: ensure no other session's commit can change what we see, even on re-read
SET CURRENT ISOLATION = RR;
SELECT SUM(balance) FROM accounts;        -- snapshot
-- ... do other work ...
SELECT SUM(balance) FROM accounts;        -- guaranteed same result
COMMIT;

Example 5: Demonstrating isolation phenomena

Phenomenon UR CS RS RR
Dirty read (see another session's uncommitted change) possible impossible impossible impossible
Non-repeatable read (a row you read changes if you re-read it) possible possible impossible impossible
Phantom read (new rows appear that match your WHERE on re-read) possible possible possible impossible

Higher isolation = less concurrency. Pick the lowest level your business logic tolerates.

Example 6: Locking + WAIT

SET CURRENT LOCK TIMEOUT = 10;     -- wait up to 10 seconds for locks

UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
-- If another session is holding a lock, DB2 waits up to 10 s
-- then raises SQLCODE -911 with reason code 68 (lock timeout)

Without LOCK TIMEOUT, the default is to wait indefinitely. For interactive apps, set a short timeout.

Example 7: Autocommit on / off

# DB2 CLP defaults to autocommit ON:
db2 -c   "INSERT INTO ..."        # COMMIT issued automatically
db2 +c   "INSERT INTO ..."        # autocommit OFF — must commit manually
db2 +c   "INSERT INTO ...; COMMIT;"

Most clients (JDBC, ODBC) start with autocommit on. Switch it off when you need multi-statement transactions.

Example 8: Long-running transactions and locking

Long transactions are dangerous — they hold locks and bloat the transaction log. A few defensive habits:

  • Commit as often as your business logic allows
  • Use WITH HOLD cursors so you can commit inside a batch loop
  • Avoid mixing user thinking time inside a transaction (let the user click, then start a fast tx)

Notes & Tips

  • DB2's default isolation CS (Cursor Stability) is right for most OLTP — it sees only committed data, locks rows briefly during reads, and releases locks after the cursor moves on.
  • For reporting where you can tolerate slightly stale data, WITH UR runs fastest and creates almost no locking pressure on writers. Use it for dashboards over OLTP tables.
  • RR (serializable) holds locks on every row you've read until the transaction ends — this means lots of contention. Reach for RR only when business logic truly needs it (e.g. compute a sum that must not shift mid-transaction).
  • Deadlocks are normal. DB2 detects them and rolls back one transaction with SQLCODE -911 reason code 2. Your app should be ready to retry the failed transaction.
  • Logical units of work that fit inside one statement (MERGE, multi-row UPDATE) are naturally atomic — no BEGIN TRAN / COMMIT needed.
  • Inspect long-running transactions with db2pd -db <dbname> -applications and -transactions.

Practice Exercises

  1. Write a transfer routine that uses a savepoint so a failure in the audit-log insert doesn't roll back the debit/credit pair.
  2. Run two CLP sessions side by side. In one, start a transaction that updates a row but doesn't commit. In the other, read that row under UR, then under CS, and explain the different behaviour.
  3. Set CURRENT LOCK TIMEOUT = 5 and observe DB2's SQLCODE -911 when a competing session holds a row for longer.
  4. Open a WITH HOLD cursor, iterate 10 rows, COMMIT after each, and verify the cursor stays open.
  5. Use db2pd -db <dbname> -locks to inspect what locks your transaction is holding.

Quick Quiz

Q1. What does ACID stand for?

Show answer

Atomicity, Consistency, Isolation, Durability — the four guarantees a transactional database makes. Atomicity = all-or-nothing; Consistency = constraints always hold; Isolation = concurrent transactions don't see each other's partial state; Durability = once committed, the change survives crashes.

Q2. When would you choose WITH UR for a query?

Show answer

For reporting and dashboards over OLTP tables where dirty reads are acceptable and locking the readers would hurt the writers. WITH UR issues almost no locks — the trade-off is you might see uncommitted ("in-flight") changes from other transactions. Never use it for queries whose result will drive business decisions like billing.

Q3. What's the difference between ROLLBACK and ROLLBACK TO SAVEPOINT sp?

Show answer

ROLLBACK (no savepoint) undoes the entire transaction back to its start and ends it. ROLLBACK TO SAVEPOINT sp undoes only the work done after the savepoint was set — the transaction continues, and you can still commit it or roll back further. Useful for "try-this-then-retry" patterns inside a longer transaction.

Next Up

Transactions guarantee correctness. Next we get the database to be fast as well — performance tuning.