Greetings Folks,
I am trying to understand how DB2 locks data and can't believe my eyes.
Have read the literature about the difference isolation levels and have
a general understanding about intent, update, share and exclusive
locks.
I am working on the sample database tables and using the default
isolation level of CS.
Situation:
-Session1 does a simple update on the Org table.
CLI Session 1 (autocommit off): UPDATE org SET location = 'Montreal'
WHERE deptnumb = 10
-the db2pd lock trace:
Locks:
Address TranHdl Lockname Type Mode Sts
Owner Dur HldCnt Att Rlse
0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G 2
1 0 0 0x0
0x020CA598 2 02001100000000000000000054 Table .IX G 2
1 0 0 0x0
0x020CA458 2 02001100040000000000000052 Row ..X G 2
1 0 0 0x0
As we can see, a Intent Exclusive table lock on table org and an
exclusive row lock (on deptnumb 10) is acquired. Everything is fine and
dandy so far.
-Session2 tries to so a simple update on a different row of table Org.
CLI Session 2 (autocommit off): UPDATE org SET location = 'Toronto'
WHERE deptnumb = 15
BOOM! Session is blocked!
Looking at the lock trace again:
Address TranHdl Lockname Type Mode Sts
Owner Dur HldCnt Att Rlse
0x020CA318 2 53514c4332453036bd4a32c841 Internal P ..S G 2
1 0 0 0x0
0x020CA390 3 53514c4332453036bd4a32c841 Internal P ..S G 3
1 0 0 0x0
0x020CA598 2 02001100000000000000000054 Table .IX G 2
1 0 0 0x0
0x020CA840 3 02001100000000000000000054 Table .IX G 3
1 0 0 0x0
0x020CA458 2 02001100040000000000000052 Row ..X G 2
1 0 0 0x0
0x020CA5C0 3 02001100040000000000000052 Row ..U W 0
1 0 0 0x0
0x020CAAC0 3 01000000010000000100140056 Internal V ..S G 3
1 0 0 0x0
We can see from the lock trace that an update lock is in waiting status
and there is another table IX lock showing up.
Question: If I am updating a another row in session 2 why does session2
still get blocked?
On page 64 (table) of Administration Guide: Performance manual, I quote
the explanation for IX table locks: "The lock owner and concurrent
applications can read and update data. Other concurrent applications
can both read and update the table.
So clearly the first session's IX table lock is not going to block the
2nd session.
What could be the reason? Thank you for your time.