lock escalation

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

re: lock escalation

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:
[color=blue]
> 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[/color]

re: lock escalation

"xixi" <dai_xi@yahoo.com> wrote in message
news:c0f33a17.0310170823.761c74d8@posting.google.c om...[color=blue]
> 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[/color]

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.

re: lock escalation

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

re: lock escalation

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:[color=blue]
> 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[/color]

re: lock escalation

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

re: lock escalation

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.

re: lock escalation

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

xixi wrote:[color=blue]
> 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.[/color]

re: lock escalation

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:
[color=blue]
> 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[/color]