我们这里的数据库每天utc19:30开始将MHAPP表空间中7天以前的数据挪至MHHISTORY表空间中,将MHHISTORY表空间中90天之前的数据删除,同时每天把昨天的数据export到本地目录。
每天晚上19:30开始,数据库出现锁升级,数据库参数采用的都是默认参数,如下:
$ db2 get db cfg|grep 'LOCK'
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(777376)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(66)
Lock timeout (sec) (LOCKTIMEOUT) = -1
Block non logged operations (BLOCKNONLOGGED) = NO
Lock timeout events (MON_LOCKTIMEOUT) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
$ db2 get db cfg|grep MAXLOCKS
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(66)
$ db2 get db cfg|grep MAX
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(66)
Max number of active applications (MAXAPPLS) = AUTOMATIC(323)
Max DB files open per application (MAXFILOP) = 61440
Percent max primary log space by transaction (MAX_LOG) = 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
所升级开始后,holded的锁和target number of locks 总维持在50%,然后ecaliscation 的锁数量又会比holed的锁数量少6个,请问这是什么原因呢?
2018-07-27-19.33.13.266796 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:20887 Appid:172.20.2.2.34731.180815082849
data management sqldEscalateLocks Probe:1 Database:ZBBB
ADM5501I DB2 is performing lock escalation. The affected application is named
"db2jcc_application", and is associated with the workload name
"SYSDEFAULTUSERWORKLOAD" and application ID "172.20.2.2.34731.180815082849" at
member "0". The total number of locks currently held is "10098408", and the
target number of locks to hold is "5049204". The current statement being
executed is "delete from (select reftime,row_number() over(order by reftime) rn
from MHHISTORY.BULL01_ALL) where reftime<'2018-4-28 00:00:00 ' and
rn<=20000". Reason code: "1"
^^
2018-07-27-19.33.13.279160 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:20887 Appid:172.20.2.2.34731.180815082849
data management sqldEscalateLocks Probe:3 Database:ZBBB
ADM5502W The escalation of "10098402" locks on table "MHHISTORY.BULL01_ALL" to
lock intent "X" was successful.
^^
2018-07-27-19.33.57.632442 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:20887 Appid:172.20.2.2.34731.180815082849
data management sqldEscalateLocks Probe:1 Database:ZBBB
ADM5501I DB2 is performing lock escalation. The affected application is named
"db2jcc_application", and is associated with the workload name
"SYSDEFAULTUSERWORKLOAD" and application ID "172.20.2.2.34731.180815082849" at
member "0". The total number of locks currently held is "9893767", and the
target number of locks to hold is "4946883". The current statement being
executed is "delete from (select reftime,row_number() over(order by reftime) rn
from MHHISTORY.BULL01_ALL) where reftime<'2018-4-28 00:00:00 ' and
rn<=20000". Reason code: "1"
^^
2018-07-27-19.33.57.632997 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:20887 Appid:172.20.2.2.34731.180815082849
data management sqldEscalateLocks Probe:3 Database:ZBBB
ADM5502W The escalation of "9893761" locks on table "MHHISTORY.BULL01_ALL" to
lock intent "X" was successful.
^^
2018-07-27-19.37.46.083092 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:30034 Appid:172.20.2.2.35793.180815084544
data management sqldEscalateLocks Probe:1 Database:ZBBB
ADM5501I DB2 is performing lock escalation. The affected application is named
"db2jcc_application", and is associated with the workload name
"SYSDEFAULTUSERWORKLOAD" and application ID "172.20.2.2.35793.180815084544" at
member "0". The total number of locks currently held is "10282374", and the
target number of locks to hold is "5141187". The current statement being
executed is "delete from (select reftime,row_number() over(order by reftime) rn
from MHHISTORY.RPT01_CAC) where reftime<'2018-4-28 00:00:00 ' and rn<=20000".
Reason code: "1"
^^
2018-07-27-19.37.46.083651 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:30034 Appid:172.20.2.2.35793.180815084544
data management sqldEscalateLocks Probe:3 Database:ZBBB
ADM5502W The escalation of "10282368" locks on table "MHHISTORY.RPT01_CAC" to
lock intent "X" was successful.
^^
2018-07-27-19.38.40.179714 Instance:db2inst1 Node:000
PID:6553838(db2agent (ZBBB) 0) TID:30034 Appid:172.20.2.2.35793.180815084544
data management sqldEscalateLocks Probe:1 Database:ZBBB
ADM5501I DB2 is performing lock escalation. The affected application is named
"db2jcc_application", and is associated with the workload name
"SYSDEFAULTUSERWORKLOAD" and application ID "172.20.2.2.35793.180815084544" at
member "0". The total number of locks currently held is "11113510", and the