DBzTech-Technology Dossier

In high-volume enterprise systems running on IBM Db2, concurrency is not just a database concept — it is a production stability factor.

When multiple users access the same data at the same time, DB2 maintains consistency using locks.
But what many developers overlook is this:

Isolation level determines the locking behavior.

It defines:

• What gets locked
• When it gets locked
• How long it stays locked
• Whether uncommitted data can be read

To make this practical, let’s use a hotel booking system example.

The Hotel Booking System Scenario

In one instance:

• Customer A searches for sea-facing rooms
• Customer B tries to book a room
• The Manager updates room rates
• Background jobs generate reports

Same SQL. Same tables. Same business logic.

SELECT * FROM ROOMS
WHERE VIEW = 'SEA'
AND DATE BETWEEN '2020-03-01' AND '2020-03-05';

But depending on the isolation level, DB2’s locking behavior changes dramatically.

1️⃣ Repeatable Read (RR) – Maximum Locking

Customer A searches for sea-facing rooms for specific dates.

DB2 scans 100 rooms but 10 rooms qualify.

Under RR:

• All 100 scanned rooms are locked
• Locks are held until COMMIT
• Other users cannot modify any scanned rows
• Phantom rows are prevented

Even rooms that do not qualify may still be locked because they were scanned.

What Happens in the Hotel

• Manager cannot change rates of any scanned sea-facing room.
• Customer B cannot book those rooms.
• System concurrency drops significantly.

RR = Maximum consistency, minimum concurrency.

2️⃣ Read Stability (RS) – Controlled Locking

Customer A runs the same search.

DB2 scans 100 rooms. 10 rooms qualify.

Under RS:

• Only the 10 qualifying rooms are locked
• Locks are held until COMMIT
• Non-qualifying scanned rooms are not locked

What Happens in the Hotel

• Manager can update rates of rooms not in the result list.
• Customer B can book rooms not shown to Customer A.
• The displayed list remains stable.

RS = Balance between integrity and concurrency.

3️⃣ Cursor Stability (CS) – Default in DB2

CS is the default isolation level in DB2.

Customer A views sea-facing rooms one at a time.

Under CS:

• Only the currently fetched row is locked.
• Lock is released when cursor moves to next row.
• Prevents dirty reads only.

What Happens in the Hotel

If Customer A is viewing Room 101:

• Manager cannot update Room 101
• Customer B cannot book Room 101

Once Customer A moves to Room 102:

• Room 101 becomes available again

This is why CS is ideal for:

• CICS transactions
• High-volume booking systems
• Real-time applications

CS = Short-lived locks, high throughput.

4️⃣ Uncommitted Read (UR) – Minimal Locking

Customer A searches for sea-facing rooms.

Under UR:

• No read locks are taken.
• Other users can update data freely.
• Dirty reads are possible.

What Happens in the Hotel

• Manager can change rates anytime
• Customer B can book the same room simultaneously
• Customer A may see uncommitted or rolled-back data

UR = Performance over precision.

Isolation Level vs Locking Behavior Summary

To Summarize,

Isolation level determines the locking behavior — and locking behavior determines system performance.