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.

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

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.

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

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.

thanks a lot , i am reading the handbook