By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

lock escalation

P: n/a
i am using db2 v8.1 windows 64 bit with fp3, i check db2diag.log ,
there is message stating

ADM5502W The escalation of "1540" locks on table "xxx.xxx" to lock
intent "X" was successful.

what does that mean?

i do have a sensitive updatable cursor to try to create a record lock
every time i read a row, then update the row, should the update
execute will release the lock? why there is a escalation happened?
will this has bad effect? will this relate to error 805? thanks
Nov 12 '05 #1
Share this Question
Share on Google+
8 Replies


P: n/a
Your update was holding 1540 locks, and so you've been 'upgraded' to a
table level lock. This will not cause a -805, but it will affect the
concurrency on your system.

xixi wrote:
i am using db2 v8.1 windows 64 bit with fp3, i check db2diag.log ,
there is message stating

ADM5502W The escalation of "1540" locks on table "xxx.xxx" to lock
intent "X" was successful.

what does that mean?

i do have a sensitive updatable cursor to try to create a record lock
every time i read a row, then update the row, should the update
execute will release the lock? why there is a escalation happened?
will this has bad effect? will this relate to error 805? thanks


Nov 12 '05 #2

P: n/a
"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...
i am using db2 v8.1 windows 64 bit with fp3, i check db2diag.log ,
there is message stating

ADM5502W The escalation of "1540" locks on table "xxx.xxx" to lock
intent "X" was successful.

what does that mean?

i do have a sensitive updatable cursor to try to create a record lock
every time i read a row, then update the row, should the update
execute will release the lock? why there is a escalation happened?
will this has bad effect? will this relate to error 805? thanks


Locks are released after a commit. If possible, try to commit more often
and/or use cursor stability isolation level. You can still maintain your
cursor position after a commit if you use cursor "with hold."

To allow more locks without escalating, you can increase the locklist size
and increase maxlocks. Not sure if these are the correct names for the
parms, but you can look them up in the manual.
Nov 12 '05 #3

P: n/a
hello,

our application use auto commit, so i will assume after every update,
the commit will execute immediately. our concurrencey type is cursor
stability. so why the lock still escalate? Since the application will
have update file process, i am afraid this will cause the problem.

thanks
Nov 12 '05 #4

P: n/a
The commit will be driven _after_ the update completes. During the
update, more and more locks are taken, until they are escalated to a
table level lock. Mark explained how to possibly avoid this in his
previous posting.

xixi wrote:
hello,

our application use auto commit, so i will assume after every update,
the commit will execute immediately. our concurrencey type is cursor
stability. so why the lock still escalate? Since the application will
have update file process, i am afraid this will cause the problem.

thanks


Nov 12 '05 #5

P: n/a
hello,

sorry i still confuse.

you said the commit will be driven _after_ the update completes. so
what does complete means ? our application will do read one row, lock
the row, then update this rows data, should it be auto commit
immediately and release the lock? in order to lock the row, the
concurrency type changes to read stability when read the row. (
because i need to have row lock when cursor stays on the row, so i set
the concurrency to read stability, open the sensitive updatable
resultset with query have where clause where id = xx, there is index
created on id), but right after update, i set the concurrency type
back to cursor stability. thanks
Nov 12 '05 #6

P: n/a
this time i have

ADM5503E The escalation of "875" locks on table "xxxx" to lock
intent "S" has failed. The SQLCODE is "-911".

what does it means? i don't see why we have 875 number of locks. when
i tried o insert a row on this table i get the sql error 911.
Nov 12 '05 #7

P: n/a
It means you couldn't escalate to a table level lock because of a deadlock.

xixi wrote:
this time i have

ADM5503E The escalation of "875" locks on table "xxxx" to lock
intent "S" has failed. The SQLCODE is "-911".

what does it means? i don't see why we have 875 number of locks. when
i tried o insert a row on this table i get the sql error 911.


Nov 12 '05 #8

P: n/a
Until you commit or roll back, your update will hold 1 row lock per row
updated...transactions can't be finished, and locks can't be released in
the middle of a statement.

xixi wrote:
hello,

sorry i still confuse.

you said the commit will be driven _after_ the update completes. so
what does complete means ? our application will do read one row, lock
the row, then update this rows data, should it be auto commit
immediately and release the lock? in order to lock the row, the
concurrency type changes to read stability when read the row. (
because i need to have row lock when cursor stays on the row, so i set
the concurrency to read stability, open the sensitive updatable
resultset with query have where clause where id = xx, there is index
created on id), but right after update, i set the concurrency type
back to cursor stability. thanks


Nov 12 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.