467,926 Members | 1,863 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,926 developers. It's quick & easy.

locks and related

Hello,

My question is a follow up on a recent thread (db2_evaluncommitted)

If I have a table with say a million rows and a predicate of my update
clause matches say 8000 records,
does db2 only lock 8000 records or does it lock records that it evaluates
based on the query plan.
(db2_evaluncommitted=yes, isolation level --> cursor stability

I see locks in the order of 30,000 - 50,000 if there were no statistics
collected on the transaction table

once I executed the run stats, the locks were pretty close to around 8500.
(which is what I expect)

I was assuming only rows that match the predicate will be locked
irrespective of a runstats but i dont see it
that way based on my above observation? can anyone explain me how the
locking mechanism works?
Thanks,
Sumanth

Apr 4 '06 #1
  • viewed: 1219
Share:
1 Reply
Do an explain on the current query. Remove the statistics you added and
redo the explain. If you have different access plans, they you may be
able to account for the difference in locks.

Phil Sherman

Sumanth wrote:
Hello,

My question is a follow up on a recent thread (db2_evaluncommitted)

If I have a table with say a million rows and a predicate of my update
clause matches say 8000 records,
does db2 only lock 8000 records or does it lock records that it evaluates
based on the query plan.
(db2_evaluncommitted=yes, isolation level --> cursor stability

I see locks in the order of 30,000 - 50,000 if there were no statistics
collected on the transaction table

once I executed the run stats, the locks were pretty close to around 8500.
(which is what I expect)

I was assuming only rows that match the predicate will be locked
irrespective of a runstats but i dont see it
that way based on my above observation? can anyone explain me how the
locking mechanism works?
Thanks,
Sumanth

Apr 5 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Yannick Turgeon | last post: by
reply views Thread by Bruce Pullen | last post: by
4 posts views Thread by Alex Callea | last post: by
8 posts views Thread by utagger | last post: by
6 posts views Thread by CJ | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.