Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Location
    Shenzhen
    Posts
    8

    Unanswered: a problem with locks of db2

    Note : Since i am not an english native speaker, please forgive me if you read this with a little bit diffuculty , thanks.

    I found a problem with locks of db2, and i cann't resolve it. please help me
    First I created a table
    db2 create table t1(c1 int,c2 int) , and the table had row lock.
    then i inserted 100 rows into the t1, (1,1),(2,2) and the others (98) were (3,3).
    then i updated db cfg:LOCKTIMEOUT=10, MAXLOCKS=22.


    and then i opened 2 db2cmd windows,

    in the first db2cmd windows, i typed:
    root:/>db2 +c "update t1 set c2=11 where c1=1"
    DB20000I The SQL command completed successfully.
    but i didnot submit it.

    in the 2nd db2cmd windows , i typed the following command ,but after 10 sec ,it returned a timeout error :
    root:/>db2 "select c1,c2 from t1 where c1=2"
    SQL0911N The current transaction has been rolled back because of a deadlock
    or timeout. Reason code "68". SQLSTATE=40001
    68 transaction rolled back due to lock timeout.

    Obviously , the "where" sub-clause of the 2nd command was different from that of the first. but the command "update" locked the rows in which the other command selected.

    then i typed "get snapshot for locks on sample" , got the output list below:
    ================================================== ===
    root:/>db2 "get snapshot for locks on sample"

    Database Lock Snapshot

    Database name = SAMPLE
    Database path = /home/db2inst5/db2inst5/NODE0000/SQ
    L00002/
    Input database alias = SAMPLE
    Locks held = 4
    Applications currently connected = 2
    Agents currently waiting on locks = 0
    Snapshot timestamp = 04-27-2004 15:35:51.505416

    Application handle = 4
    Application ID = NF000001.O067.040397202800
    Sequence number = 0003
    Application name = db2bp
    Authorization ID = DB2INST5
    Application status = UOW Waiting
    Status change time = 04-27-2004 15:28:22.623350
    Application code page = 819
    Locks held = 0
    Total wait time (ms) = 0


    Application handle = 3
    Application ID = NF000001.O066.0E0E17202726
    Sequence number = 0001
    Application name = db2bp
    Authorization ID = DB2INST5
    Application status = UOW Waiting
    Status change time = 04-27-2004 15:27:47.695375
    Application code page = 819
    Locks held = 4
    Total wait time (ms) = 0

    List Of Locks
    Lock Name = 0x0002000D000000040000000052
    Lock Attributes = 0x00000020
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 4
    Object Type = Row
    Tablespace Name = USERSPACE1
    Table Schema = DB2INST5
    Table Name = T1
    Mode = X

    Lock Name = 0x0002000D000000000000000054
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 13
    Object Type = Table
    Tablespace Name = USERSPACE1
    Table Schema = DB2INST5
    Table Name = T1
    Mode = IX

    Lock Name = 0x41414141414A485253334E4441
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal P Lock
    Mode = S

    Lock Name = 0x434F4E544F4B4E3153544E4441
    Lock Attributes = 0x00000000
    Release Flags = 0x40000000
    Lock Count = 1
    Hold Count = 0
    Lock Object Name = 0
    Object Type = Internal P Lock
    Mode = S
    ================================================== ===
    from the output , we can find:
    when the 1st commanded "update" a row , the row held the X lock, but when the 2nd commnd executed "select", it held the table's IS lock.
    Since X lock and IS lock are not compatible, it must wait for the application to release the locks.

    So, there arises a problem: every time when DB2 updates a row in the table without commit or rollback, no "select" command can be executed even the "where" clause is different . The only way to change this condition is that the isolation level is "Uncommitted read".

    hu..... complete , it took me 1 hour to write this ,oh, poor English.

  2. #2
    Join Date
    Mar 2004
    Location
    Netherlands
    Posts
    183
    Hi,

    Because DB2 locked the first record (I believe) in the first session (because it is not commited) other sessions cannot access this row. (It seems you understood this).
    The second session is accessing other rows, that's why it should work, you thought. But actually because the table has no index DB2 must evaluate all the rows (first read the record before you can access the values). Due to a lock on the first row, it cannot read this record. So it cannot access it. And a lock timeout will occur. Adding an index will make the table more easy to access in parallel, because it doesn't have to scan the whole table.

    Hope this helps,

    Rob Prop

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I agree with adding an index to help with the locking problem. However, if there are only 100 rows, DB2 may not use the index because it is faster to scan the entire table. To alleviate this problem, you can update the catalog stats and increase the card value to a high number, which should encourage DB2 to use the index. You can check this with a visual explain.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Apr 2004
    Location
    Shenzhen
    Posts
    8
    thank you very much ,RobP and Marcus_A. i understand it now and resolved this problem .

    but i still have a puzzle about "To alleviate this problem, you can update the catalog stats and increase the card value to a high number, which should encourage DB2 to use the index. " where can i update the catalog stats ? i cann't find it out.

    could you explain it to me ? thanks a lot

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The DB2 Catalog contains tables store information about the metadata, including statistics that are updated after the runstats command. DB2 uses these statistics to choose an access path. See the appendix D in the SQL Reference Vol 1 which shows the DB2 catalog views (this is for version 8, may be elsewhere in other DB2 versions).

    One of the views of the DB2 catalog is SYSCAT.TABLES and the CARD column indicates the number of rows in the table. Another column that may need to be updated is FULLKEYCARD on the SYSCAT.INDEXES view. These are a user updateable columns, but be warned that they will be overwritten when runstats is executed.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Apr 2004
    Location
    Shenzhen
    Posts
    8
    thanks a lot , i am reading the handbook

Posting Permissions

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