hi

nexk key locking of indexe happens only in RR scan or even for CS scan of indexe as per doc
The next-key locking for type-1 indexes during key insertions and key deletion might result in deadlocks. The following example shows how two transactions could deadlock. With type 2 indexes, such deadlocks do not occur.

Consider the following example of an index that contains 6 rows with the following values: 1 5 6 7 8 12.

Transaction 1 deletes the row with key value 8. The row with value 8 is locked in X mode. When the corresponding key from the index is deleted, the row with value 12 is locked in X mode.

WHY IT NEEDS TO LOCK THE ROW WITH VALUE 12 IN X MODE
regds
Paul

what exactly is the problem you are experiencing? Type 1 indexes should really be gone from any application by now, so not sure that is what you are having. Also, what platform are you running on?

Dave Nance

actually our db was migrated to db2 8.1 fix 12,
the db was restored in the new vesion
so the indexes which was created prior to version 8.1 was of type 1 indexes
now the problem was
we had a table in which there used to freq insert and delete whose indexes were of type 1 , which used to lock continusly
after convert to type 2 the locking was minimized
as type 1 indexes
Transaction 1 deletes the row with key value 8. The row with value 8 is locked in X mode. When the corresponding key from the index is deleted, the row with value 12 is locked in X mode.

whts the logic in locking 12 in X mode
kindly suggest
regds
paul