Explain Locking, Isolation Levels, and Deadlocks in IBM Mainframes

36 / 60

DB2advanced

Locking, Isolation Levels, and Deadlocks

Understand DB2's locking model, the practical effect of each isolation level, and how deadlocks arise and get resolved.

DB2 locking is where concurrency theory meets real production incidents. The isolation level decides how much your transaction is protected, and what it costs everyone else.

Isolation levels are how tightly you reserve a shared meeting room: Uncommitted Read is walking in without knocking and reading whatever's currently on the whiteboard, even mid-erase; Repeatable Read is locking the whole room for your entire meeting so nobody else can touch anything until you leave; Cursor Stability is holding the door only while you're actually looking at one specific note. A deadlock is two people each holding a door the other needs open, until security (DB2's lock manager) steps in and asks one of them to leave.

Key Concepts

The four levels.

    RR  Repeatable Read    locks every row read, held to commit.
                           Nothing you read can change. Most blocking.
    RS  Read Stability     locks the rows you qualified, held to commit.
                           New rows can still appear.
    CS  Cursor Stability   locks the row only while the cursor sits on
                           it. The usual default.
    UR  Uncommitted Read   no locks taken, none waited for.
                           Can read uncommitted data.

CS is the default because of what it releases. The lock is held while the cursor is positioned on the row and dropped when it moves on.

So a browse through ten thousand rows holds one lock at a time rather than ten thousand. Other transactions are barely affected.

The trade is that a row read earlier may have changed by the time you come back to it — which matters if you read, decide, then update.

UR is for reporting and nothing else. No locks means no waiting and no blocking anyone. It also means reading rows another transaction has written and may yet roll back.

For "roughly how many orders were placed today" that is fine. For an account balance it is not.

A deadlock is a cycle.

    transaction A:  locks row 1, then wants row 2
    transaction B:  locks row 2, then wants row 1

Neither can proceed. DB2's detector finds the cycle, picks a victim and rolls it back with -911. The other continues.

-911

A timeout is not a deadlock, though it reports similarly. There is no cycle — one transaction simply waited longer than IRLM's threshold for a lock somebody else was holding.

    deadlock  a genuine cycle; one side is chosen and rolled back
    timeout   no cycle; the waiter gave up

The distinction changes the fix. A deadlock means access ordering. A timeout means something is holding a lock too long — usually a transaction that stayed open across a slow operation.

Both come back as -911, so any code that can hit them needs a retry loop. -911 means DB2 has already rolled you back, so retrying the whole unit of work is safe and is the expected response.

-911

Prevention is consistent ordering. Look at the cycle again: A took 1 then 2, B took 2 then 1. If both had taken the lower key first, B would simply have waited and then proceeded.

Sorting keys before updating them removes the entire class, and it is a one-line change.

And keep the unit of work short. Locks are held until commit, so a transaction that reads, calls something slow, then writes is holding locks across the slow part.

What the interviewer is probing.1. "What are the isolation levels in DB2?" Probing: breadth. Stalls: "Just one." Moves up: repeatable read, read stability, cursor stability and uncommitted read — in decreasing order of locking.

2. "What is lock escalation?" Probing: the mechanism. Stalls: "More locks." Moves up: DB2 replacing many page or row locks with a table space lock when a threshold is crossed, which suddenly blocks everyone else.

3. "How do you avoid deadlocks?" Probing: the discipline. Stalls: "Retry." Moves up: access tables in a consistent order, commit frequently in batch, and keep units of work short.

4. "Why does commit frequency matter in batch?" Probing: the operational point. Stalls: "It does not." Moves up: a job that never commits holds locks for hours and blocks online work — and a failure then means rolling back everything.