我们有一个db2 10.5的数据库,有一个表test.执行sql:
delete from test where id>'201412093' and id<'201412094' and curdate='20141209';
索引情况:
id 、curdate (注意是随机顺序索引,非正常的顺序索引)
数据情况:
20141209日之前的数据大概有一亿多条,大于20141209的数据大概3000万条。
20141209日当天的数据大概只剩下不到1000条(其中大部分数据都清理完了)。
为什么这个sql会造成锁升级哪?
ADM5501I DB2 is performing lock escalation. The affected application is named
"db2bp", and is associated with the workload name "SYSDEFAULTUSERWORKLOAD" and
application ID "192.168.0.34.50418.141211170003" at member "1". The total number
of locks currently held is "195768", and the target number of locks to hold is
"97884". The current statement being executed is "delete from test
where curdate = '20141209' and id > '1412093' and id <
'1412094'". Reason code: "0"
收起