Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007
    Posts
    246

    Unanswered: Next key locking

    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

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    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

  3. #3
    Join Date
    Oct 2007
    Posts
    246
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •