Chapter 26: Quiz — Locking, Concurrency, and Isolation Levels

Test your understanding of DB2 locking, isolation levels, and concurrency control.

Question 1

Which lock mode is acquired on a row when a transaction reads it under CS isolation using a regular SELECT (not FOR UPDATE)?

A) X (Exclusive) B) U (Update) C) S (Share) D) No lock is acquired

Question 2

Under which isolation level can a transaction read uncommitted data?

A) Cursor Stability (CS) B) Read Stability (RS) C) Repeatable Read (RR) D) Uncommitted Read (UR)

Question 3

What is the primary difference between a deadlock and a lock timeout?

A) A deadlock always involves more than two transactions; a timeout involves exactly two B) A deadlock is a circular wait that cannot resolve itself; a timeout is a linear wait that exceeds a threshold C) A deadlock always results in SQLCODE -912; a timeout results in SQLCODE -911 D) A deadlock is detected immediately; a timeout requires a background process to detect

Question 4

Lock escalation on LUW is triggered when:

A) A transaction's locks exceed MAXLOCKS percent of LOCKLIST B) A transaction acquires more than 1,000 row locks C) The total number of locks in the system exceeds LOCKLIST D) A deadlock is detected between two transactions

Question 5

Which isolation level prevents phantom reads?

A) Uncommitted Read (UR) B) Cursor Stability (CS) C) Read Stability (RS) D) Repeatable Read (RR)

Question 6

What is the purpose of the U (Update) lock mode?

A) To indicate that a row is being updated B) To prevent the S-to-X upgrade deadlock pattern C) To allow multiple transactions to update the same row simultaneously D) To combine the properties of S and X locks

Question 7

Currently Committed semantics on LUW applies to which isolation level(s)?

A) UR only B) CS only C) CS and RS D) All isolation levels

Question 8

When a transaction under RS isolation reads 1,000 qualifying rows, how many row locks does it hold at any point before COMMIT?

A) 0 B) 1 (only the current row) C) Up to 1,000 (all qualifying rows) D) All rows examined, including non-qualifying rows

Question 9

What does SKIP LOCKED DATA do when a qualifying row is locked by another transaction?

A) Waits for the lock to be released B) Returns an error C) Silently skips the row and continues to the next qualifying row D) Returns the previously committed version of the row

Question 10

On z/OS, which parameter controls the lock escalation threshold per tablespace?

A) LOCKLIST B) MAXLOCKS C) NUMLKTS D) IRLMRWT

Question 11

A funds transfer transaction reads account A, reads account B, debits A, and credits B. What is the minimum isolation level needed to ensure the balance of account A does not change between the read and the debit?

A) UR B) CS C) RS D) RR

Question 12

Two transactions each hold an S lock on the same row. Transaction A wants to upgrade to X. Transaction B also wants to upgrade to X. What happens?

A) Transaction A upgrades first, then Transaction B B) Both transactions upgrade simultaneously C) A deadlock occurs D) Both transactions time out

Question 13

Which of the following is the most effective strategy for preventing lock escalation in a batch job?

A) Increasing CPU allocation B) Committing frequently to release locks C) Using RR isolation D) Disabling the lock manager

Question 14

On LUW, which configuration parameter controls how long a transaction waits for a lock before timing out?

A) DLCHKTIME B) LOCKLIST C) LOCKTIMEOUT D) MAXLOCKS

Question 15

Under CS isolation without Currently Committed, what happens when Transaction B tries to read a row that Transaction A has updated but not committed?

A) Transaction B reads the previously committed value B) Transaction B reads the uncommitted value C) Transaction B waits until Transaction A commits or rolls back D) Transaction B receives an error

Question 16

Which SQLCODE indicates that a transaction was rolled back due to a deadlock?

A) -803 B) -904 C) -911 with reason code 2 D) -911 with reason code 68

Question 17

An IX (Intent Exclusive) lock at the table level indicates that:

A) The entire table is exclusively locked B) The transaction intends to modify some rows in the table C) The transaction is reading the entire table D) The table is being reorganized

Question 18

For Meridian Bank's internal monitoring dashboard that shows approximate account statistics, which isolation level is most appropriate?

A) UR B) CS C) RS D) RR

Question 19

Which of the following is a correct deadlock prevention strategy?

A) Always use UR isolation B) Access tables and rows in a consistent order across all transactions C) Set LOCKTIMEOUT to 0 D) Use table-level locking for all tables

Question 20

What is the key advantage of optimistic locking over pessimistic locking in a web application?

A) It provides stronger consistency guarantees B) Locks are not held during user think-time C) It prevents all concurrency anomalies D) It eliminates the need for COMMIT statements

Answer Key

  1. C — Under CS, a share (S) lock is acquired on the current row and released when the cursor moves.

  2. D — Uncommitted Read (UR) allows reading data that has been modified but not yet committed.

  3. B — A deadlock is a circular dependency (A waits for B, B waits for A) that can never resolve. A timeout is simply waiting longer than the configured threshold.

  4. A — On LUW, escalation triggers when a single application's locks exceed MAXLOCKS percent of the total LOCKLIST memory.

  5. D — Repeatable Read (RR) locks all examined rows (including non-qualifying ones), preventing both non-repeatable reads and phantom inserts within the scanned range.

  6. B — The U lock prevents the classic deadlock where two transactions both hold S locks and try to upgrade to X. Only one transaction can hold U at a time, serializing the upgrade path.

  7. B — Currently Committed applies only to CS isolation. Under RS and RR, the reader must still wait because those levels require lock-based stability guarantees.

  8. C — RS holds share locks on all qualifying rows until COMMIT. Non-qualifying rows that were examined but did not meet the predicate are not locked (unlike RR).

  9. C — SKIP LOCKED DATA silently omits locked rows from the result set, allowing the query to proceed without waiting.

  10. C — NUMLKTS (Number of Locks per Tablespace) is the z/OS IRLM parameter that controls per-tablespace escalation. LOCKLIST and MAXLOCKS are LUW parameters.

  11. C — RS (Read Stability) holds the share lock on read rows until COMMIT, ensuring the balance does not change. CS would release the lock when the cursor moves, allowing another transaction to modify the balance.

  12. C — Deadlock. Neither can upgrade because the other holds an S lock. S is compatible with S, but neither can get X while the other holds S. This is the classic upgrade deadlock that the U lock mode prevents.

  13. B — Committing frequently releases accumulated locks, keeping the total below the escalation threshold. RR would make escalation worse.

  14. C — LOCKTIMEOUT specifies the number of seconds a transaction will wait for a lock. DLCHKTIME is the deadlock detection interval.

  15. C — Under traditional CS (without Currently Committed), the reader waits for the writer to commit or roll back. With CC enabled, the reader would see the previously committed value.

  16. C — SQLCODE -911 with reason code 2 indicates a deadlock rollback. Reason code 68 indicates a timeout.

  17. B — IX signals intent to acquire exclusive (X) locks on individual rows within the table. It is a table-level intent lock that allows concurrent IX from other transactions.

  18. A — UR is appropriate because the dashboard shows approximate statistics, precision is not critical, and the query must never block OLTP transactions.

  19. B — Consistent lock ordering prevents circular waits, which is the definition of a deadlock. UR avoids locks but sacrifices consistency. Setting LOCKTIMEOUT to 0 resolves waits but does not prevent deadlocks.

  20. B — Optimistic locking does not hold database locks during user think-time (which can be seconds to minutes in a web application). This prevents long-held locks from blocking other users.