Row Level Locking when a Tablespace Scan is chosen - 12-06-2004 , 03:09 AM
The following briefly describes the DB2 characteristics of the
1) A set of Permanent Tables are used to hold 'transient' data that is
destroyed at the end of an online transaction. Data is collected from
various sources and then input to an online financial Quote.
2) When multiple threads concurrently run, the application design
ensures that all rows for a given thread are 'tagged' via a specific
3) This is used through the life of the DB2 thread to ensure that a
given 'User' Transaction will have no interest in another thread's
(i.e. Where Base_Timestamp = Threads Timestamp)
4) Therefore, across the set of Tables, a given thread will Insert
Rows, Update rows, Select Rows and finally (on producing the Quote),
Delete rows that match its own specific Base_Timestamp.
1) Various measures have been taken to maximise User concurrency. For
example, Row Level Locking.
2) However, a problem occurred which, although now resolved, the cause
is not fully understood.
3) Deadlocks were appearing due to Thread-2 appearing to have a IU
Lock on rows that 'belong' to Thread-1.
As stated above, the 'Where' clause specifies that Thread-2 should
only be accessing its own rows.
4) Trace evidence highlighted the following.
Thread-1 had an IX Lock on its own row to perform an Update.
Thread-2 had a IU Lock on Thread-1's row.
5) When executing, Thread-2 was not using an available Index, keyed on
Base_Timestamp. The access path chosen was instead a Tablespace Scan.
6) Circumstantial evidence suggests that when Thread-2 is trying to
filter rows that belong to its own Base_Timestamp, it was not
initially using a Share Lock.
7) Instead, it is using a IU Lock to evaluate each row on the Page -
even rows that belong to Thread-1. Hence the Deadlock.
The solution entailed creating artificial Runsats such that the
available (Type-2) Index is used by Thread-2 to identify its own rows
Is it plausible for Thread-2 to have behaved as described above.
That is, when using a Tablespace Scan, it opts for an Immediate IU
when filtering rows that belong to it prior to an Update?