DB2 Locking and Concurrency Control

In any multi-user database environment, concurrency control is the mechanism that prevents transactions from interfering with each other. DB2 achieves this through locking — acquiring locks on data before reading or modifying it, and releasing them at appropriate points. Understanding how DB2 locking works is essential for building applications that are both correct and performant under concurrent load.

Why Locking Exists

Without locking, concurrent transactions could produce incorrect results:

• Dirty Read: Transaction A reads a row that Transaction B has modified but not yet committed — A reads uncommitted (possibly rolled-back) data
• Non-Repeatable Read: Transaction A reads a row twice; between the two reads, Transaction B updates and commits the row — A sees different values
• Phantom Read: Transaction A runs the same query twice; between the two reads, Transaction B inserts a new row that qualifies — A sees different result sets
• Lost Update: Transactions A and B both read a row, both modify it independently, and both write back — A's write is overwritten by B

Isolation levels control which of these anomalies DB2 prevents.

DB2 Isolation Levels

DB2 supports four isolation levels, from least to most restrictive:

Uncommitted Read (UR)

SELECT BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = '12345' WITH UR;

UR acquires no read locks. Reads uncommitted data from other transactions (dirty reads). Use only for inquiry-only transactions where approximate or slightly stale data is acceptable — for example, dashboard counts where exact accuracy is not critical. Never use UR in transactions that make decisions based on the data read.

Cursor Stability (CS) — Default

CS is the DB2 default. It holds a lock on the row currently being processed and releases it as soon as the cursor moves to the next row. Between FETCH calls the lock is held; when FETCH moves to the next row, the previous row's lock is released.

CS prevents dirty reads (you cannot read uncommitted data) but allows non-repeatable reads (a row you fetched earlier may have changed by the time you fetch it again). This is the appropriate level for most CICS online transactions.

Read Stability (RS)

RS holds locks on every row qualified by the query throughout the unit of work (until COMMIT or ROLLBACK). Other transactions can read the same rows but cannot update them. RS prevents dirty reads and non-repeatable reads but allows phantom reads (new rows matching the predicate can be inserted by other transactions between reads).

Repeatable Read (RR)

RR is the most restrictive isolation level. It holds locks on all rows scanned by the query (not just those that qualify), preventing updates AND new inserts that would match the query predicate. This eliminates all concurrency anomalies including phantoms. RR is rarely appropriate for OLTP applications because it severely limits concurrency.

Lock Types

DB2 uses a hierarchy of lock types:

S (Share) locks are acquired on rows being read (with CS or higher isolation). Multiple transactions can hold S locks on the same row simultaneously.

X (Exclusive) locks are acquired on rows being modified (INSERT, UPDATE, DELETE). Only one transaction can hold an X lock on a row; all others must wait.

Lock Granularity

DB2 locks at different granularities depending on the isolation level and tablespace LOCKSIZE setting:

Row-level locking (LOCKSIZE ROW): Locks individual rows. Maximum concurrency, highest overhead for queries that lock many rows.

Page-level locking (LOCKSIZE PAGE): Locks 4KB–32KB pages. One lock covers all rows on the page. Less overhead, less concurrency.

Tablespace-level locking (LOCKSIZE TABLESPACE): One lock covers the entire tablespace. Only one transaction at a time. Acceptable for single-user utilities; disastrous for OLTP.

-- z/OS: Set locksize in tablespace definition
CREATE TABLESPACE EMPTS01
    IN EMPDB
    LOCKSIZE ROW
    -- other options...

Lock Escalation

Lock escalation occurs automatically when a transaction acquires more row or page locks than the LOCKMAX threshold. DB2 converts all individual locks to a single tablespace lock. While cheaper for the lock manager, this blocks all other transactions from the tablespace.

Preventing escalation:

• Commit frequently in batch programs to release accumulated locks
• Reduce the scope of transactions
• Use CS isolation instead of RS/RR where data consistency requirements allow
• Increase LOCKMAX in the tablespace definition (z/OS)

Deadlocks — SQLCODE -911

A deadlock occurs when two or more transactions are in a circular wait:

Transaction A:   Holds lock on ROW_1, waiting for ROW_2
Transaction B:   Holds lock on ROW_2, waiting for ROW_1
→ Neither can proceed → Deadlock

DB2's IRLM detects deadlocks automatically and terminates the youngest transaction with SQLCODE -911. The victim transaction is completely rolled back.

Handling -911 in COBOL

       EXEC SQL
           UPDATE ACCOUNTS
           SET BALANCE = BALANCE - :WS-AMOUNT
           WHERE ACCOUNT_ID = :WS-ACCT-ID
       END-EXEC.

       EVALUATE SQLCODE
           WHEN 0
               CONTINUE
           WHEN -911
               ADD 1 TO WS-RETRY-COUNT
               IF WS-RETRY-COUNT <= 3
                   EXEC SQL ROLLBACK END-EXEC
                   PERFORM 5000-UPDATE-ACCOUNT   * retry
               ELSE
                   MOVE 'Max retries exceeded' TO WS-MSG
                   PERFORM 9900-ERROR
               END-IF
           WHEN OTHER
               PERFORM 9900-SQL-ERROR
       END-EVALUATE.

Preventing Deadlocks

The most effective deadlock prevention strategy is consistent lock acquisition order — all transactions that access the same set of rows should access them in the same order:

Always update ACCOUNTS before TRANSACTIONS (not the reverse)
Always lock lower account number before higher account number

Also:

• Keep transactions short — less time holding locks means less chance of deadlock
• Avoid user interaction (screen waits) while holding locks
• Use SELECT with FOR UPDATE to acquire X locks early

Lock Timeout — SQLCODE -904

When a transaction cannot acquire a lock within the timeout period (IRLM timeout), DB2 returns SQLCODE -904. Unlike -911, the transaction is NOT automatically rolled back — the failing SQL statement fails, but prior changes in the same transaction are intact.

The application must decide whether to rollback and retry or to proceed with partial success.

       WHEN -904
           DISPLAY 'Lock timeout - resource unavailable'
           EXEC SQL ROLLBACK END-EXEC
           PERFORM 9000-RETRY-LOGIC.

WITH UR / CS / RS / RR in SQL

Specify isolation level directly in a SELECT statement:

-- Dashboard count — UR is fine
SELECT COUNT(*) FROM ORDERS WHERE STATUS = 'PENDING' WITH UR;

-- Standard OLTP read — CS default
SELECT BALANCE FROM ACCOUNTS WHERE ACCOUNT_ID = :ws-acct;

-- Financial report requiring consistency — RS
SELECT * FROM LEDGER_ENTRIES WHERE PERIOD = '2026-Q1' WITH RS;

-- Auditing — must see stable snapshot — RR
SELECT * FROM AUDIT_TRAIL WHERE USER_ID = :ws-user WITH RR;

SKIP LOCKED DATA

DB2 10.5+ (LUW) and DB2 12 (z/OS) support SKIP LOCKED DATA, which allows a query to skip rows that are currently locked by other transactions rather than waiting:

SELECT EMPNO, STATUS FROM JOB_QUEUE
WHERE  STATUS = 'PENDING'
FETCH FIRST 1 ROW ONLY
FOR UPDATE
SKIP LOCKED DATA;

This is invaluable for queue processing where multiple workers should each claim a different pending job without contending with each other.

Monitoring Lock Activity

-- DB2 LUW: current lock waits
SELECT AGENT_ID, LOCK_OBJECT_TYPE, LOCK_MODE, LOCK_STATUS
FROM   TABLE(MON_GET_LOCKS(NULL, -2)) AS T;

-- DB2 LUW: lock wait events
SELECT * FROM TABLE(MON_GET_LOCK_WAIT_EVENT_DETAILS(NULL, -2)) AS T;

On z/OS, lock activity is reported through the IRLM statistics and DB2 trace (GTF, Omegamon).

Next Steps

With locking under control, the last performance concern is keeping statistics and physical organisation current. Read the DB2 RUNSTATS and REORG guide for the complete maintenance strategy. For the full DB2 learning path, visit the DB2 Mastery course hub.