DB2 lock types and durations

How Db2 locking is organized

IRLM (Internal Resource Lock Manager) owns transaction locks. An application that reads or writes data typically:

• Takes an intent lock on the table space or partition (IS or IX)
• Takes an S, U, or X lock on each page or row it actually touches
• Keeps X locks on changed data until commit
• Releases S locks according to isolation (CS vs RS vs RR)

If LOCKSIZE is TABLESPACE, Db2 skips the child locks and takes a gross S or X on the space (or partition). That is cheaper in CPU and deadly for concurrency.

Lock modes

S, U, and X on pages and rows

S (share) — read. Many S locks can coexist. Nobody else gets X.

U (update) — “I am reading and I might update.” Only one U is allowed with any number of S locks. U exists so two updatable cursors do not deadlock as often: both would want to convert S to X; U already reserved the conversion slot. The owner still cannot change the row until the lock converts to X.

X (exclusive) — the owner may change the data. Used for INSERT, UPDATE, DELETE of that page or row. Held until commit for those changes.

Intent locks: IS, IX, SIX

Intent locks sit on the parent (table, partition, table space) when LOCKSIZE is PAGE or ROW.

• IS — intent share: I will read children. Others may read and change other rows.
• IX — intent exclusive: I will change children. Others may also IS/IX and lock different pages or rows.
• SIX — share with intent exclusive: I have a share on the parent (so I can read without child S locks) and I will X-lock children I change. Others may read, not write.

Compatibility is a table, not a slogan. IS is compatible with IS and IX. S on the parent is not compatible with IX. When a process holds IX and needs to read the whole table without child locks, conversion toward SIX or S is how Db2 advertises that.

Lock sizes: row, page, table, table space, partition

On partitioned table spaces, the parent lock is usually a partition lock, not one lock covering every partition. That is partition independence: REORG PART 5 drains part 5 while part 6 still takes claims. Table locks appear on segmented (non-UTS) multi-table spaces when Db2 locks one table inside the space.

1
2
3
4
5
6
7
CREATE TABLESPACE HRTS IN HRDB
  LOCKSIZE PAGE
  LOCKMAX SYSTEM;
 
ALTER TABLESPACE HRDB.HRTS
  LOCKSIZE ROW
  LOCKMAX 0;

LOCK TABLE IN SHARE MODE / EXCLUSIVE MODE is the SQL hammer: take a gross S or X regardless of LOCKSIZE, held until commit.

Durations

Bind ACQUIRE(USE vs ALLOCATE) and RELEASE(COMMIT vs DEALLOCATE) change when some parent locks are taken and dropped. RELEASE(DEALLOCATE) can keep table-space intent locks across COMMITs in a reused CICS thread — faster, more blocking of DDL and utilities. Match RELEASE to the workload; do not copy DEALLOCATE onto a long-running batch cursor without thinking about drains.

Lock conversion, promotion, and escalation

Conversion (mode promotion) is S or U becoming X on the same page or row when you UPDATE. The parent IS may convert to IX.

Escalation is different: Db2 releases a large number of page, row, LOB, or XML locks on one table or space and takes an S or X on the table, table space, or set of partitions instead. It fires when the process exceeds LOCKMAX for that space (or NUMLKTS when LOCKMAX is SYSTEM). LOCKMAX 0 turns escalation off. Escalation is suspended during ALTER/CREATE/DROP/GRANT/ REVOKE.

If about a quarter of escalations cause timeouts or deadlocks, IBM suggests raising LOCKMAX or committing more often rather than living with surprise table-space X locks. NUMLKUS is a different ceiling: total locks per user across spaces; exceeding it is SQLCODE -904, not escalation.

Contention, SKIP LOCKED DATA, and lock avoidance

Lock contention is waiting because an incompatible lock is already held. Timeout and deadlock are how waits end badly; they have their own page. Application knobs:

• SKIP LOCKED DATA — with CS or RS and row/page locks, skip incompatibly locked rows. No warning. Ignored for UR and RR.
• Lock avoidance — CS CURRENTDATA(NO) may skip an S lock when Db2 can prove the page is committed.
• Currently committed — skip uncommitted inserts / read last committed image of deletes instead of waiting.

Global locking, data sharing, and GBP

In a data-sharing group, transaction locks are global L-locks. IRLM on each member uses the coupling-facility lock structure so member A’s X lock is visible to member B. P-locks (physical) protect interest in a cached page in the group buffer pool (GBP); they are not the same as your SQL X lock but they serialize page coherency.

False contention is two different resources hashing to the same lock-table entry: IRLM thinks they collide when the data rows do not. Bigger lock structure (or a larger lock table portion) reduces hash collisions. Global contention is real cross-member wait (XES and IRLM levels). Tuning GBP, lock structure size, and commit frequency matters more in data sharing than in a standalone subsystem.

Deadlocks (preview)

Two processes each hold a lock the other needs. IRLM detects the cycle on the deadlock interval and picks a victim (SQLCODE -911 or -913). Row locks reduce “false” deadlocks that were really two rows on one page. Consistent access order (always EMP then DEPT) prevents many real deadlocks. Full treatment is on the timeouts and deadlocks page.

Explain It Like I'm Five

A lock is a sticky note on a toy. S means “I’m looking; you can look too.” U means “I’m looking and I called dibs on taking it next.” X means “I’m using it; wait.” IS/IX are notes on the toy box: “someone inside is looking” versus “someone inside might take a toy.” LOCKSIZE is whether the note goes on one toy, one shelf, or the whole box. Escalation is ripping off a hundred toy notes and slapping one giant X on the box. Commit is taking all your notes off. Data sharing is two playrooms sharing one whiteboard of notes so both rooms see who has the red truck.

Exercises

1. Why is U used on an updatable CS cursor instead of S?
2. Sketch IS + X(row) versus X(table space) for one UPDATE of a single employee.
3. When would you set LOCKSIZE ROW on a table space that is currently PAGE?
4. LOCKMAX 1000 versus LOCKMAX 0 — what happens when a process needs 1001 row locks?
5. In one sentence each, define false contention and lock avoidance.