why lock escalation happened

[xixi]

hi, we are using db2 udb v8.1 on 64 bit windows, this message is in
the db2diag.log file

ADM5502W The escalation of "38200" locks on table "xxx.xxx" to lock
intent "X" was successful.
currently i have locklist = 1075, maxlock=60, so based on the formula

(((locks held * 36)/ (locklist *4096)) *100) , i have the answer is
31, compare with maxlock 60, i should still have space for locks to
grow, so why this point the escalation happened?

[Matt Emmerton]

"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...

hi, we are using db2 udb v8.1 on 64 bit windows, this message is in
the db2diag.log file

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

currently i have locklist = 1075, maxlock=60, so based on the formula

(((locks held * 36)/ (locklist *4096)) *100) , i have the answer is
31, compare with maxlock 60, i should still have space for locks to
grow, so why this point the escalation happened?

Locks are 56 bytes on 64-bit DB2, not 36. This means that these 38200 locks
are consuming 48.5% of the lock list, not 31%.
(See
http://publib.boulder.ibm.com/infoce...n/r0000268.htm)

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.

While it is true that the 38200 locks held on table xxx.xxx only consume
48.5% of the lock list, these locks combined with all the other locks held
in the system exceeded the 60% threshold (set by MAXLOCKS)

Thus, DB2 chooses the table with the largest number of row locks and
escalates the row locks to table locks, in order to free up lock list.

--
Matt Emmerton

[Matt Emmerton]

"xixi" <da****@yahoo.com> wrote in message
news:c0**************************@posting.google.c om...

hi, we are using db2 udb v8.1 on 64 bit windows, this message is in
the db2diag.log file

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

currently i have locklist = 1075, maxlock=60, so based on the formula

(((locks held * 36)/ (locklist *4096)) *100) , i have the answer is
31, compare with maxlock 60, i should still have space for locks to
grow, so why this point the escalation happened?

Locks are 56 bytes on 64-bit DB2, not 36. This means that these 38200 locks
are consuming 48.5% of the lock list, not 31%.
(See
http://publib.boulder.ibm.com/infoce...n/r0000268.htm)

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.

While it is true that the 38200 locks held on table xxx.xxx only consume
48.5% of the lock list, these locks combined with all the other locks held
in the system exceeded the 60% threshold (set by MAXLOCKS)

Thus, DB2 chooses the table with the largest number of row locks and
escalates the row locks to table locks, in order to free up lock list.

--
Matt Emmerton

[Mark A]

> Lock escalation occurs when the TOTAL number of locks held by ALL

applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

[Mark A]

> Lock escalation occurs when the TOTAL number of locks held by ALL

applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

[Matt Emmerton]

"Mark A" <[e-mail]> wrote in message
news:rh**************@news.uswest.net...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

Sorry, my mistake.

--
Matt Emmerton

[Matt Emmerton]

"Mark A" <[e-mail]> wrote in message
news:rh**************@news.uswest.net...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

Sorry, my mistake.

--
Matt Emmerton

[xixi]

i agree, and so why the lock escalated?

"Mark A" <[e-mail]> wrote in message news:<rh**************@news.uswest.net>...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

[xixi]

i agree, and so why the lock escalated?

"Mark A" <[e-mail]> wrote in message news:<rh**************@news.uswest.net>...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

[Mark Yudkin]

"xixi" <da****@yahoo.com> wrote in message
news:c0*************************@posting.google.co m...

i agree, and so why the lock escalated?

Your application is taking out more locks than you're allowing (per
application or per instance). This may well be related to your other problem
(log space full): both being caused by not committing. Otherwise, increase
the lock list size. In any case (regardless of the explanation), use the DB2
monitor to monitor the lock list usage to see what's happening.
"Mark A" <[e-mail]> wrote in message

news:<rh**************@news.uswest.net>...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

[Mark Yudkin]

"xixi" <da****@yahoo.com> wrote in message
news:c0*************************@posting.google.co m...

i agree, and so why the lock escalated?

Your application is taking out more locks than you're allowing (per
application or per instance). This may well be related to your other problem
(log space full): both being caused by not committing. Otherwise, increase
the lock list size. In any case (regardless of the explanation), use the DB2
monitor to monitor the lock list usage to see what's happening.
"Mark A" <[e-mail]> wrote in message

news:<rh**************@news.uswest.net>...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

[Matt Emmerton]

"Mark A" <[e-mail]> wrote in message news:<rh**************@news.uswest.net>...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

"xixi" <da****@yahoo.com> wrote in message
news:c0*************************@posting.google.co m... i agree, and so why the lock escalated?

Because there are other locks held by your application (which aren't listed
in the db2diag.log escalation warning message) which makes the application's
usage of LOCKLIST exceed MAXLOCKS, and therefore DB2 escalates some row
locks to table locks to free up LOCKLIST.

--
Matt Emmerton

[Matt Emmerton]

"Mark A" <[e-mail]> wrote in message news:<rh**************@news.uswest.net>...

Lock escalation occurs when the TOTAL number of locks held by ALL
applications exceeds MAXLOCKS percent of the lock list.
<snip>
--
Matt Emmerton

That differs from what the Administration Guide: Performance says:

"When the number of locks held by any one application reaches this
percentage [defined in maxlocks] of the total lock list size, lock
escalation will occur for the locks held by that application. Lock
escalation also occurs if the lock list runs out of space."

"xixi" <da****@yahoo.com> wrote in message
news:c0*************************@posting.google.co m... i agree, and so why the lock escalated?

Because there are other locks held by your application (which aren't listed
in the db2diag.log escalation warning message) which makes the application's
usage of LOCKLIST exceed MAXLOCKS, and therefore DB2 escalates some row
locks to table locks to free up LOCKLIST.

--
Matt Emmerton