DB2 Locking Scheme Problem.

Vikky

Hi All:

I have a problem with DB2 locking scheme. I am dealing with two
processes modifying the DBMS (here IBM DB2 7.1 FP8 on Solaris)
simulteneously.

P1: Huge transactional data being processed and updated. [Batch
Engines]
P2: A very low intensive thread carry out the requests from the UI.
[UI]

I intend to run both the processes simultaneously with maximum
concurrency.

But this does not happen since the locks behave differently. I have a
senario where P1(WRITER) while creating/updating rows in a table (say:
Tab) acquires 'X' lock on 'Tab' and does not allow the P2(READER) to
atleast even read. This makes P2 block on P1.

The following is the LOCK snapshot while the READER is blocked on the
WRITER:

At this moment
LOCKLIST=500
MAXLOCKS=10
LOCKTIMEOUT=-1
DBHEAP=1200
Total Locks in DB: (4*1024)/(72) = 56 locks/page and hence 56*500=2800
locks.
Hence ROW locks should be esclated to TABLE locks only if any one
application held more than 10% of 2800 locks which is 280 locks.
READER (Acquired 4 locks)
~~~~~~~

Application handle = 266
Application ID = 954AA18B.89E7.040922190825
Sequence number = 0001
Application name = java
Authorization ID = QE02
Application status = Lock-wait
Status change time = Not Collected
Application code page = 819
Locks held = 3
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 464
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_PERSON
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
WRITER: (Acquired 12 locks)
~~~~~~~

Application handle = 111
Application ID = 954AA18B.89EB.040922190832
Sequence number = 0001
Application name = java
Authorization ID = QE02
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 819
Locks held = 12
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 523
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_PERSON
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 27937
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_OBJECT
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 456
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_OBJECT
Mode = IX
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 6187
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_DATA_OBJECT
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 458
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_DATA_OBJECT
Mode = IX
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 779
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_PERSON
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 778
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_PERSON
Mode = X
Status = Granted
Lock Escalation = NO

Lock Object Name = 464
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = QE02
Table Name = SC_PERSON
Mode = IX
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Possible query dimensions in my application:
1. Actions: 'MODIFY' and 'SELECT'
2. Isolation Level: 'CS'
3. LOCKSIZE: 'ANY', 'PAGE', 'TABLE'.

Rules: Taken from "DB2 Developers Guide" by Craig S. Mullins. 4th
edition.
1. MODIFY + CS + [ANY | PAGE]: will result in acquiring 'IS' lock on
tablespace and 'IX' on the table.
2. MODIFY + CS + [TABLE]: 'IS' lock on tablespace & 'X' on the table.
3. SELECT + CS + [ANY | PAGE]: 'IS' lock on tablespace & 'IS' on the
table.
4. SELECT + CS + [TABLE]: 'IS' lock on tablespace & 'S' on the table.

Note: In none of my queries LOCKSIZE is 'TABLE' hence removing the
possibility of rule 2 and 4. Hence only 1 and 3 apply.
Writer/Reader senario:
~~~~~~~~~~~~~~~~~~~~~~~
When the WRITER first gets its plan approved, it acquires 'IS' lock on
tablespace and 'IX' lock on table. Now when READER comes just to read
some other rows of the same table it tries to acqurie 'IS' lock on the
tablespace and gets it (since 2 'IS' locks dont conflict). It then
tries to acquire 'IS' lock on the table for reading which again should
acquire, since an 'IS' lock does not conflict with 'IX' lock. This
implies that both processes should proceed without being blocked.

Can anyone please reason as to why DB2 behaves like this. Am I missing
something here ?

Waiting for your replies.

Thank You,

-Vikash.

Nov 12 '05 #1

pz

Vikky wrote:

Writer/Reader senario:
~~~~~~~~~~~~~~~~~~~~~~~
When the WRITER first gets its plan approved, it acquires 'IS' lock on
tablespace and 'IX' lock on table. Now when READER comes just to read
some other rows of the same table it tries to acqurie 'IS' lock on the
tablespace and gets it (since 2 'IS' locks dont conflict). It then
tries to acquire 'IS' lock on the table for reading which again should
acquire, since an 'IS' lock does not conflict with 'IX' lock. This
implies that both processes should proceed without being blocked.

Can anyone please reason as to why DB2 behaves like this. Am I missing
something here ?

You're probably missing what happens after the writer updates one of the
rows. At this point the writer gets an X lock on that row until commit
time. Now when the reader attempts to access that locked row it can't,
therefore a lock wait.

If your business scenario allows it you can run the reader with UR
isolation lever; this should solve your problem. Alternatively, make
writer commit changes immediately.

Hope this helps.

Nov 12 '05 #2