Hi,

I am facing a serious locking problem and need to solve it urgently.

In session 1 I run the following sql:
INSERT INTO table_a (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) VALUES (4538947, 4476205, 20557, 4476224, 226, 1001498, 2650, 0, 2650, 0);

execution plan shows tablescan on table_a
shapshot for locks shows 1 row lock(w) and 1 table lock(ix) on table_a

In session 1 I run the following sql:
update table_a set col10=4539505 where col1=4538947 AND col10=0;

execution plan shows indexscan (unique idex on col1) on table_a
shapshot for locks shows 1 row lock(x) and 1 table lock(ix) on table_a

Then in session 2 I run the following sql:
INSERT INTO table_a (col1, col2, col3, col4, col5, col6, col7, col8, col9, col10) VALUES (4538971, 4476205, 20557, 4476224, 1210729, 1001498,
33320, 0, 33320, 0);

Session 2 hangs.
shapshot for locks shows 1 row lock(x) and 1 table lock(ix) on table_a
for session 1, and another row lock(x) and 1 table lock(ix) on table_a for session 2.

After I commit or rollback session 1, session 2 resumes.
Execution plan shows tablescan.
Shapshot for locks shows 1 row lock(w) and 1 table lock(ix) on table_a for session 2.

I think after session 1 issued an update statement, the updated row is locked exclusively. When session 2 inserts a new row, the optimizer choose to use tablescan. However, it cannot read the row locked by session 1.

If the assumption is right, I have several questions:
1. I have specified 'volatile' in creating table_a, and indexes exist for table_a, why the optimizer still use tablescan in insert statement?
2. How can I solve this locking problem?

Many thanks for any suggestion.

ps: no effect by using 'with ur/cs' in the sqls.

Give information on the db2 version/OS ...
Also the table and index definiton, including any related tables/indexes..
If possible post the execution plan ..

This will help the forum to give a better answer ..

Cheers
Sathyaram

OS: Win2K
DB2: V7.2 FP7

attached:
1. actual sql, table and index definition,
2. execution plan and
3. snapshot report(captured when application 26 hangs, application 18 run first then application 26)

Thanks a lot!

The statement is not doing tablescan on any 'physical' table .. (The table scan is on the GENROW) .. All locks seem to be OK ...

I think it is likely to be because of Next-Key Share locks (V7 Type 1 indexes) ... take an application snapshot to see why the session B is waiting (I thought lock snapshot shows this info, but doesn't seem to be there) at the tail end of the B's snapshot

HTH

Sathyaram

Thanks for your reply.

I generate the application snapshot but the content are quite complicated to me. attached 28.txt(session A) and 32.txt(session B).

When will the next-key share lock hold? after insert or update? I tried different sequence of those sqls and found that if session A issue inserts statements only, session B can also issue insert statements, but once session A issues update statement, session B cannot issue insert or update statement anymore. It seems that the problem is caused by update by session A, but not insert.

The lock wait is because of the Next Key Weak Exclusive lock confilicts ....

Next Key locks is a property of Type 1 indexes where a lock is obtained on an adjecent row when inserting into an index ...

The lock-wait is caused by the index

CREATE INDEX "SM_SET "."EV_STK_ENT_IDX_2" ON "SM_SET "."EV_STOCKS_ENTITLE"
("TRADE_LEG_OID" ASC)
PCTFREE 10 ALLOW REVERSE SCANS;

Google for 'Next Key Weak Exclusive" for more information

HTH

Sathyaram

Thanks for your info.

Since other sqls need that index. Is it possible to avoid this problem but not dropping that index?

also, when I test the same case but using AIX, the optimizer will use EV_STK_ENT_IDX_2 instead of pk_ev_stk_ent in update sql. That cause another performance problem. are there any method to force or direct the optimizer to use a particular index?

You need not drop the index ... Find a balance between having the overhead of frequent commits and concurrency ... Have the update as nearer to the commit point as possible ...
In almost all cases, I would allow the optimizer to do what it thinks best .... DB2 UDB optimizer is quiet sophisticated to pick up the best access path if you provide it with proper information on statistics, bufferpool size, num of concurrent applications etc. For complex SQLs or joins, if you see a problem with the access path, get in touch with IBM ...

HTH

Sathyaram

Hi Sathyaram,

Your info are really useful to me. Thanks a lot!

However, I have 1 more question. There are several indexes on the table, but why only the existence of EV_STK_ENT_IDX_2 causes this problem?

EV_STK_ENT_IDX_2 is a non-unique index while other indexes are all unique. Is it true that NW will not be acquired if there are only unique indexes associated with a table?

Because that is the index with the updated column TRADE_LEG_OID

Version 8 has introducted Type-2 indexes ... Tnerefore you will not have this problem

Cheers
Sathyaram

Thank you very much for your help.

As frequent commit has big impact on performance, I really hope there are other ways to solve this problem.

Again, thanks.