Messaging & transactions · Advanced

Locks, concurrency & isolation

Diagnose contention and protect correctness without unnecessarily serializing work.

Learn this topic first: Db2 for i: SQL, cursors, commitment and isolation →

6 explained questions · 5 practice MCQs

Questions and answers

1. How do object locks and record locks differ? (Intermediate)

Object locks coordinate operations on an object, such as use, allocation, or structural changes. Record locks protect particular records during data access. The granularity and compatibility rules differ.

Identify both the holder and requester and what each is doing. A file replacement blocked by an object lock is not the same incident as two jobs attempting to update one customer row. Choose diagnostic tooling appropriate to the type of lock and preserve the evidence before intervention.

2. How do common isolation levels differ? (Intermediate)

Uncommitted Read permits observations that may later roll back. Cursor Stability protects against reading uncommitted changes under its rules while allowing substantial concurrency. Read Stability and Repeatable Read provide stronger retention/protection, with different behavior around qualifying rows and phantoms. No Commit is a distinct IBM i option.

Choose according to the business invariant and verify Db2 for i’s exact semantics. Stronger isolation can increase lock duration and contention. Do not use terminology or defaults from another Db2 platform without checking the IBM i documentation.

3. What is a deadlock versus a lock timeout? (Intermediate)

A deadlock is a cycle of dependencies: each participant waits for a resource another participant holds. A timeout means a requested wait exceeded its limit; it does not prove a cycle exists.

Capture the jobs, resources, operation order, and diagnostics. Reduce deadlocks by acquiring resources in a consistent order and keeping transactions short. Use bounded retries only when the failure category is retryable and the entire business operation is safe to repeat.

4. How do you prevent lost updates with optimistic concurrency? (Advanced)

Read a version or original value, then update only if it still matches. If the affected-row count is zero, the record changed or disappeared and the application must reconcile or retry. The comparison and update must be one atomic database operation.

This avoids holding a lock while a user edits a screen. Use a dedicated version counter or other reliable token, and define how conflicts are shown. Do not simply overwrite with stale screen values.

Example

UPDATE app.orders SET status = ?, version = version + 1
WHERE id = ? AND version = ?;
-- Require exactly one affected row.
5. Does UPDATE always release every relevant lock? (Advanced)

For simple native-I/O record locking, updating a record can release the current update-read lock. Under commitment control, transaction locks may remain until COMMIT or ROLLBACK. SQL isolation and cursor attributes also affect lock behavior.

Always state the access mode and commitment context when answering. “UPDATE releases the lock” is incomplete without those assumptions. Diagnose the actual lock state in the job rather than inferring it solely from the last opcode.

6. Would you solve contention by ending the lock holder? (Advanced)

First determine whether the holder is progressing, waiting for another resource, or abandoned. Capture its transaction purpose and expected rollback impact. Ending it can trigger lengthy recovery or interrupt critical work.

Prefer normal completion or application-supported cancellation when possible. If intervention is necessary, coordinate with the owner and verify both data consistency and waiting jobs afterward. Repeated lock incidents require a design fix such as shorter transactions, consistent ordering, or reduced hot-row updates.

Practice checkpoint

  1. 1. Which situation proves a deadlock pattern?
    1. A waits alone for a slow query
    2. A waits for B while B waits for A
    3. A receives an empty result
    4. A compiles a module
  2. 2. Which isolation can allow dirty reads?
    1. All isolation levels forbid them
    2. Repeatable Read only
    3. Uncommitted Read
    4. Read Stability only
  3. 3. What confirms an optimistic update succeeded?
    1. The SELECT previously succeeded
    2. The screen stayed open
    3. The job name is unchanged
    4. Exactly one expected row was affected
  4. 4. Can transaction locks survive a native UPDATE?
    1. Yes, under commitment control
    2. No, never
    3. Only for display files
    4. Only without journaling
  5. 5. What helps reduce deadlocks?
    1. Random update order
    2. Consistent resource acquisition order
    3. Longer user think time inside transactions
    4. Unbounded retries
Show answer key and explanations

1. B — A waits for B while B waits for A A cycle of resource dependencies distinguishes deadlock from a simple long wait.

2. C — Uncommitted Read UR permits reading changes that may not commit.

3. D — Exactly one expected row was affected The version predicate can fail if another writer changed the row.

4. A — Yes, under commitment control Transaction lock lifetime can extend to the unit-of-work boundary.

5. B — Consistent resource acquisition order Consistent ordering reduces circular wait opportunities.

IBM documentation and further reading