Chapter 8 Quiz: DB2 Locking, Concurrency, and Deadlock Resolution

Instructions: Select the best answer for each question. Some questions have multiple correct answers — select all that apply where indicated.

Question 1

What are the four levels of DB2's lock hierarchy, from coarsest to finest?

A) Database, tablespace, table, row B) Tablespace, table, page, row C) Subsystem, database, tablespace, page D) Database, table, page, column

Question 2

An X (Exclusive) lock is compatible with which other lock modes?

A) IS only B) S and IS C) No other lock modes D) U only

Question 3

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

A) To allow multiple programs to update the same row simultaneously B) To prevent conversion deadlocks when a program reads with intent to update C) To improve read performance by avoiding S locks D) To signal to DB2 that a utility is running

Question 4

Under Cursor Stability (CS) isolation, how long are read locks held?

A) Until the end of the unit of work (COMMIT/ROLLBACK) B) Until the cursor moves to the next row C) Until the program terminates D) Read locks are never acquired under CS

Question 5

Which isolation level allows dirty reads?

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

Question 6

A query scans 10,000 pages under RR isolation. Only 100 pages contain qualifying rows. How many page-level read locks are held at the end of the query (before COMMIT)?

A) 100 B) 1 (the current page) C) 10,000 D) 0 (RR uses tablespace-level locks)

Question 7

What happens when a DB2 connection's lock count on a tablespace exceeds LOCKMAX?

A) The connection is terminated B) DB2 escalates individual page/row locks to a tablespace-level lock C) DB2 issues a COMMIT on behalf of the application D) The IRLM returns -911 to the application

Question 8

A batch program updates 50,000 rows before committing. The tablespace has LOCKSIZE ROW and LOCKMAX 10,000. What will happen?

A) The program will complete normally with 50,000 row locks B) Lock escalation will occur when the lock count reaches 10,000 C) DB2 will automatically issue intermediate COMMITs D) The program will receive SQLCODE -904

Question 9

Which SQLCODE indicates a deadlock or timeout?

A) -803 B) -911 C) -904 D) -501

Question 10

After receiving SQLCODE -911 due to a deadlock, what is the state of the application's unit of work?

A) All changes since the last COMMIT are preserved B) Only the failed statement is rolled back C) The entire unit of work since the last COMMIT is rolled back D) The application must issue an explicit ROLLBACK

Question 11

Which IFCID trace record contains deadlock diagnostic information?

A) IFCID 0044 B) IFCID 0172 C) IFCID 0196 D) IFCID 0261

Question 12

What is the single most effective technique for preventing deadlocks between two programs that access the same tables?

A) Increasing RESOURCE TIMEOUT B) Using UR isolation for all queries C) Accessing resources in the same order in both programs D) Using LOCKSIZE TABLESPACE

Question 13 (Select ALL that apply)

Which of the following are valid strategies to prevent lock escalation?

A) COMMIT more frequently B) Increase LOCKMAX C) Use LOCK TABLE to take a tablespace lock proactively D) Switch from LOCKSIZE PAGE to LOCKSIZE ROW E) All of the above

Question 14

In a COBOL program, what is the purpose of declaring a cursor WITH HOLD?

A) It prevents lock escalation B) It keeps the cursor open across COMMITs C) It takes an exclusive lock on the entire result set D) It enables lock avoidance for the cursor

Question 15

What is "currently committed" semantics in DB2 12+?

A) A feature that forces all readers to wait for writers to commit B) A feature that returns the last committed version of a row when the current version is locked by another process C) A feature that automatically commits transactions after each statement D) A feature that prevents dirty reads under UR isolation

Question 16

In optimistic concurrency, what mechanism is used to detect that another program has modified a row between the initial read and the update?

A) The IRLM lock table B) A version column or timestamp compared before update C) DB2's deadlock detection D) The BIND ISOLATION parameter

Question 17

A wire transfer moves money from Account 500 to Account 200. Using the "lock lower account first" deadlock prevention strategy, which account should be locked first?

A) Account 500 (the source account) B) Account 200 (the target account and lower number) C) Either — the order doesn't matter for transfers D) Both should be locked simultaneously using LOCK TABLE

Question 18

What is the difference between a lock and a latch in DB2?

A) Locks protect data; latches protect internal DB2 control structures B) Locks are faster than latches C) Latches can cause deadlocks; locks cannot D) There is no difference — they are synonyms

Question 19

A batch program runs concurrently with online transactions. The batch program processes all rows in ACCT_MASTER and commits every 500 rows. The tablespace has LOCKSIZE ROW and LOCKMAX 10,000. Online transactions each touch 1-3 rows. Which statement is TRUE?

A) Deadlocks are impossible because the batch commits every 500 rows B) Deadlocks can still occur if online and batch access rows in different orders C) Lock escalation is impossible because 500 < 10,000 D) Both B and C are true

Question 20

You are designing the locking strategy for a table that is accessed by 5,000 concurrent online users and a nightly batch program that updates 80% of the rows. Which combination is most appropriate?

A) LOCKSIZE TABLESPACE for batch; LOCKSIZE ROW for online — run them in separate windows B) LOCKSIZE ROW with frequent COMMIT for batch; CS with currently committed for online reads; same-order access for both C) LOCKSIZE PAGE for both; RR isolation for batch; CS for online D) UR isolation for everything to eliminate lock contention entirely

Answer Key

1. B — Tablespace, table, page, row. Database is not a lock level. Columns are not lockable.

2. C — X is compatible with nothing. Any other lock holder blocks an X request, and an X holder blocks all other requests.

3. B — The U lock prevents conversion deadlocks. Without it, two programs holding S locks on the same resource can deadlock when both try to convert to X.

4. B — Under CS, read locks are held only while the cursor is positioned on the row/page. When the cursor moves, the previous read lock is released. Write locks are held until COMMIT.

5. C — UR (Uncommitted Read) is the only isolation level that allows dirty reads. It acquires no read locks.

6. C — RR holds read locks on ALL pages accessed, not just qualifying pages. All 10,000 scanned pages remain locked until COMMIT.

7. B — Lock escalation replaces the individual page/row locks with a single tablespace-level (or table-level) lock. The connection is not terminated, and DB2 does not COMMIT on the application's behalf.

8. B — The program will trigger lock escalation when it accumulates more than 10,000 row locks. Since it updates 50,000 rows before committing, escalation is guaranteed.

9. B — SQLCODE -911 indicates deadlock (reason 00C90088) or timeout (reason 00C9008E). -913 also indicates a resource unavailable condition (timeout). -803 is duplicate key; -904 is resource unavailable (different category); -501 is cursor not open.

10. C — After -911, DB2 rolls back the entire unit of work to the last COMMIT point. All uncommitted changes are lost, all cursors are closed, and the application must re-read any needed data before retrying.

11. B — IFCID 0172 contains deadlock trace records. IFCID 0044 tracks lock suspensions. IFCID 0196 tracks lock escalations. IFCID 0261 tracks global lock contention in data sharing.

12. C — Accessing resources in the same order eliminates the possibility of circular waits, which is the definition of a deadlock. Increasing timeout just delays the detection. UR sacrifices data integrity. LOCKSIZE TABLESPACE eliminates concurrency.

13. E — All listed strategies are valid. Frequent COMMIT (A) keeps lock count below LOCKMAX. Increasing LOCKMAX (B) raises the threshold. Proactive LOCK TABLE (C) avoids accumulating individual locks. Switching to ROW (D) changes the granularity but note: it increases lock count for the same number of row accesses, though it reduces contention per lock.

14. B — WITH HOLD keeps the cursor open and positioned after a COMMIT. Without it, COMMIT closes all open cursors. This is essential for batch programs that commit periodically.

15. B — Currently committed allows a reader under CS to see the last committed version of a row rather than waiting for a writer's X lock to be released. It effectively makes readers non-blocking with respect to writers.

16. B — Optimistic concurrency uses a version column (integer or timestamp) that is checked at update time. If the value has changed since the initial read, another program has modified the row and the update is aborted.

17. B — Account 200 is the lower number and must be locked first, regardless of whether it's the source or target. This ensures all programs accessing accounts 200 and 500 lock them in the same order.

18. A — Locks protect user data from concurrent application access and are managed by the IRLM. Latches protect DB2's internal control structures (buffer pool pages, log buffers, etc.) and are managed by DB2 internally.

19. D — Both B and C. Deadlocks can occur even with frequent commits if online and batch access rows in different orders (B is true). Lock escalation is prevented because the batch never holds more than 500 locks, which is well below LOCKMAX of 10,000 (C is true).

20. B — LOCKSIZE ROW with frequent COMMIT for batch minimizes lock duration and prevents escalation. CS with currently committed for online reads avoids blocking on batch's write locks. Same-order access prevents deadlocks. Option A requires a batch window (which we don't have). Option C uses RR for batch, which locks every row accessed — disaster for concurrency. Option D sacrifices data integrity.