Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2006
    Posts
    2

    Unanswered: Next key locking and deadlocks in a table with one row of data

    I am developing an application and can't get passed the deadlock exceptions that I can reliably create with a multi-threaded test. I am running DB 7.2.0 on WinXP.

    I know there's been a lot of discussion about how NS locks and type-1 indexes can cause deadlocks and here's a great description of the problem: http://publib.boulder.ibm.com/infoce...n/c0009699.htm.

    I've tried the DB2_RR_TO_RS solution and it doesn't work for me and I'm not sure I'm having the same problem. My application opens a transaction for writing, executes an INSERT and (only if it fails) executes a SELECT using the same primary key value used in the INSERT. Then the transaction is committed (or rolled back if the insert had failed).

    The summary of what my test executes and what locks are held is below (with the application id and lock object names). The data being inserted and queried by these threads is all exactly the same and the row already exists for the given data (at least for the indexed data):

    Executing INSERT... (Thread3) LOCAL.DB2.060214020932
    Executing INSERT... (Thread2) LOCAL.DB2.060214020947
    INSERT done: false (Thread3)
    Executing SELECT... (Thread3)
    Executing INSERT... (Thread4) LOCAL.DB2.060214020948
    Executing INSERT... (Thread8) LOCAL.DB2.060214020949
    Executing INSERT... (Thread9) LOCAL.DB2.060214020950

    LOCAL.DB2.060214020932 Holds
    15179780 NS (Row)
    15179781 X (Row)
    237459371 X (Key Value)

    LOCAL.DB2.060214020947 Holds
    15179782 X (Row)

    LOCAL.DB2.060214020947 Wants
    237459371 X (Key Value)

    LOCAL.DB2.060214020932 Wants
    15179782 X (Row)

    All the threads above failed with deadlock exceptions.

    As you can see 932 (Thread 3) has lock that 947 (Thread 2) wants and 947 has a lock that 932 wants and thus the deadlock. I'm not much of a DBA so I don't understand the difference here between a "Row" lock and a "Key Value" lock. I also don't understand how Thread 2 was able to lock anything. When Thread 3 executes the insert, it should lock the only row in the table. When Thread 2 executes its insert, it should block waiting for the lock but not only does it block, but its given an X lock on something that Thread 3 later wants. I don't get that. It's obvious that a deadlock can occur if you've got two transactions that operate on two or more pieces of data that are shared between the two, but in this case, there's only one piece of data. A single key value being inserted (attempted) into the table.

    I've tried everything I can think of including changing the isolation level of the queries. I was able to get around the problem by removing the index and replacing it with a unique constraint but that's not really a solution I want.

    Here's a fuller lock snapshot but I've chopped out stuff I don't think is necessary. I can provide the whole thing as well as a statement and transaction log.

    Application handle = 56
    Application ID = *LOCAL.DB2.060214020932
    Sequence number = 0001
    Application name = java.exe
    Authorization ID = DB2USER
    Application status = Lock-wait
    Status change time = Not Collected
    Application code page = 1252
    Locks held = 6
    Total wait time (ms) = Not Collected

    List Of Locks
    Lock Object Name = 15179780
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = DB2USER
    Table Name = ACE_LOCKS
    Mode = NS
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 237459371
    Node number lock is held at = 0
    Object Type = Key Value
    Tablespace Name = USERSPACE1
    Table Schema = DB2USER
    Table Name = ACE_LOCKS
    Mode = X
    Status = Granted
    Lock Escalation = NO

    Lock Object Name = 15179781
    Node number lock is held at = 0
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = DB2USER
    Table Name = ACE_LOCKS
    Mode = X
    Status = Granted
    Lock Escalation = NO


    Application handle = 57
    Application ID = *LOCAL.DB2.060214020947
    Sequence number = 0001
    Application name = java.exe
    Authorization ID = DB2USER
    Application status = Lock-wait
    Status change time = Not Collected
    Application code page = 1252
    Locks held = 4
    Total wait time (ms) = Not Collected

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

    Lock Object Name = 3339
    Node number lock is held at = 0
    Object Type = Table
    Tablespace Name = USERSPACE1
    Table Schema = DB2USER
    Table Name = ACE_LOCKS
    Mode = IX
    Status = Granted
    Lock Escalation = NO

  2. #2
    Join Date
    Feb 2006
    Posts
    2
    Oops, a little typo. When 932 wants lock 15179782, its requested it for NS mode:

    60) Deadlocked Connection ...
    Appl Id: *LOCAL.DB2.060214020947
    Appl Seq number: 0001
    Appl Id of connection holding the lock: *LOCAL.DB2.060214020932
    Seq. no. of connection holding the lock: 0001
    Lock wait start time: 02-13-2006 20:09:47.157637
    Requesting lock as part of escalation: FALSE
    Deadlock detection time: 02-13-2006 20:09:54.994691
    Table of lock waited on : ACE_LOCKS
    Schema of lock waited on : DB2USER
    Tablespace of lock waited on : USERSPACE1
    Type of lock: Keyvalue
    Mode of lock: X
    Mode application requested on lock: X
    Node lock occured on: 0
    Lock object name: 237459371
    Application Handle: 57

    61) Deadlocked Connection ...
    Appl Id: *LOCAL.DB2.060214020932
    Appl Seq number: 0001
    Appl Id of connection holding the lock: *LOCAL.DB2.060214020947
    Seq. no. of connection holding the lock: 0001
    Lock wait start time: 02-13-2006 20:09:47.168766
    Requesting lock as part of escalation: FALSE
    Deadlock detection time: 02-13-2006 20:09:54.994691
    Table of lock waited on : ACE_LOCKS
    Schema of lock waited on : DB2USER
    Tablespace of lock waited on : USERSPACE1
    Type of lock: Row
    Mode of lock: X
    Mode application requested on lock: NS
    Node lock occured on: 0
    Lock object name: 15179782
    Application Handle: 56

Posting Permissions

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