DB2 锁升级示例1

 

厂商工程师反映系统无法登陆，一直停留在登陆界面。
用db2top查看，只发现数据库中有大量lock，当时不确定算不算异常，
后来lock信息没有，再看diag文件中，就有了锁升级的日志：

012-08-20-13.11.16.283971+480 E1667A534 LEVEL: Warning
PID : 8913288 TID : 200350 PROC : db2sysc 1
INSTANCE: db2sdin1 NODE : 001 DB : SX1
APPHDL : 1-6590 APPID: 10.20.0.201.59482.120820011850
AUTHID : SXZMUSER
EDUID : 200350 EDUNAME: db2agent (SX1) 1
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:3
MESSAGE : ADM5502W The escalation of "1" locks on table
"SXZMUSER.T_ZMC_CASEINFO" to lock intent "X" was successful.

2012-08-20-13.11.16.447937+480 E2202A3716 LEVEL: Warning
PID : 8913288 TID : 193369 PROC : db2sysc 1
INSTANCE: db2sdin1 NODE : 001 DB : SX1
APPHDL : 1-6587 APPID: 10.20.0.201.59479.120820011847
AUTHID : SXZMUSER
EDUID : 193369 EDUNAME: db2agent (SX1) 1
FUNCTION: DB2 UDB, data management, sqldEscalateLocks, probe:1
MESSAGE : ADM5501I DB2 is performing lock escalation. The affected application
is named "db2jcc_application", and is associated with the workload
name "SYSDEFAULTUSERWORKLOAD" and application ID
"10.20.0.201.59479.120820011847" at member "1". The total number of locks currently held is "21", and the target number of locks to hold
is "10". The current statement being executed is "select caseinfo0_.LIST_ID as LIST1_0_, caseinfo0_.CASECODE as CASECODE126_0_,
caseinfo0_.CASENAME as CASENAME126_0_, caseinfo0_.PARTYCODE as
PARTYCODE126_0_, caseinfo0_.BILL_ID as BILL5_126_0_,
caseinfo0_.BILL_CODE as BILL6_126_0_, caseinfo0_.VERSIONCODE as
VERSIONC7_126_0_, caseinfo0_.CASEYEAR as CASEYEAR126_0_,
caseinfo0_.CASENO as CASENO126_0_, caseinfo0_.CASEDATE as
CASEDATE126_0_, caseinfo0_.CASEPLACE as CASEPLACE126_0_,
caseinfo0_.PARTYNAME as PARTYNAME126_0_, caseinfo0_.PARTYMORECODE as
PARTYMO13_126_0_, caseinfo0_.PARTYMORENAME as PARTYMO14_126_0_,
caseinfo0_.CARCODE as CARCODE126_0_, caseinfo0_.CARNAME as
CARNAME126_0_, caseinfo0_.CASEKIND as CASEKIND126_0_,
caseinfo0_.CASEKINDNAME as CASEKIN18_126_0_, caseinfo0_.CASEVALUE as
CASEVALUE126_0_, caseinfo0_.PUNISHVALUE as PUNISHV20_126_0_,
caseinfo0_.UNDERTAKENPEOPLE as UNDERTA21_126_0_, caseinfo0_.DEPTCODE
as DEPTCODE126_0_, caseinfo0_.DEPTNAME as DEPTNAME126_0_,
caseinfo0_.CHECKDEPTCODE as CHECKDE24_126_0_, caseinfo0_.DISPOSALCODE
as DISPOSA25_126_0_, caseinfo0_.APPROVEDATE as APPROVE26_126_0_,
caseinfo0_.CASESOURCECODE as CASESOU27_126_0_, caseinfo0_.CASEBRIEF
as CASEBRIEF126_0_, caseinfo0_.TRAFFICTYPE as TRAFFIC29_126_0_,
caseinfo0_.CASETOOL as CASETOOL126_0_, caseinfo0_.ISSYSTEM as
ISSYSTEM126_0_, caseinfo0_.SYSTEMPARTYNAME as SYSTEMP32_126_0_,
caseinfo0_.MARKVALUE as MARKVALUE126_0_, caseinfo0_.ISCOMPLETE as
ISCOMPLETE126_0_, caseinfo0_.ISWSCOMPLETE as ISWSCOM35_126_0_,
caseinfo0_.COMPLETEDATE as COMPLET36_126_0_,
caseinfo0_.DISCUSSCONTENT as DISCUSS37_126_0_,
caseinfo0_.LAWSUITCONTENT as LAWSUIT38_126_0_,
caseinfo0_.CORRECTCONTENT as CORRECT39_126_0_,
caseinfo0_.DEPORTATIONCODE as DEPORTA40_126_0_, caseinfo0_.ISBIGCASE
as ISBIGCASE126_0_, caseinfo0_.CITYAPPROVE as CITYAPP42_126_0_,
caseinfo0_.CITYMAN as CITYMAN126_0_, caseinfo0_.CITYDATE as
CITYDATE126_0_, caseinfo0_.PROVINCEAPPROVE as PROVINC45_126_0_,
caseinfo0_.PROVINCEMAN as PROVINC46_126_0_, caseinfo0_.PROVINCEDATE
as PROVINC47_126_0_, caseinfo0_.REPORTEMPLOYEEID as REPORTE48_126_0_,
caseinfo0_.DISPOSEEMPLOYEEID as DISPOSE49_126_0_,
caseinfo0_.SAVEDEPTNAME as SAVEDEP50_126_0_, caseinfo0_.SAVEDEPTID as
SAVEDEPTID126_0_, caseinfo0_.WSCLASS as WSCLASS126_0_,
caseinfo0_.PARTY_LICECODE as PARTY53_126_0_, caseinfo0_.LABH as
LABH126_0_, caseinfo0_.CASE_REGTIME as CASE55_126_0_,
caseinfo0_.JBTSLIST_ID as JBTSLIST56_126_0_ from T_ZMC_CASEINFO
caseinfo0_ where caseinfo0_.LIST_ID=?".

2012-08-20-13.11.16.457790+480 I5919A438 LEVEL: Warning
PID : 8913288 TID : 170780 PROC : db2sysc 1
INSTANCE: db2sdin1 NODE : 001
APPHDL : 0-20855
EDUID : 170780 EDUNAME: db2agntp 1
FUNCTION: DB2 UDB, database monitor, sqm_snap_appl_locks, probe:10
MESSAGE : Snapshot cannot obtain lock information for application with app
handle 7138. Application in rollback.

2012-08-20-13.11.16.497087+480 I6358A438 LEVEL: Warning
PID : 8913288 TID : 170780 PROC : db2sysc 1
INSTANCE: db2sdin1 NODE : 001
APPHDL : 0-20855
EDUID : 170780 EDUNAME: db2agntp 1
FUNCTION: DB2 UDB, database monitor, sqm_snap_appl_locks, probe:10
MESSAGE : Snapshot cannot obtain lock information for application with app
handle 7105. Application in rollback.

2012-08-20-13.11.16.514391+480 I6797A438 LEVEL: Warning
PID : 8913288 TID : 170780 PROC : db2sysc 1
INSTANCE: db2sdin1 NODE : 001
APPHDL : 0-20855
EDUID : 170780 EDUNAME: db2agntp 1
FUNCTION: DB2 UDB, database monitor, sqm_snap_appl_locks, probe:10
MESSAGE : Snapshot cannot obtain lock information for application with app
handle 6460. Application in rollback.

发生锁升级后，数据库中锁的数量变少了，但是锁的级别却变高了，影响更大，这个例子中，锁升级成对表的排他锁（ lock intent "X" ）。锁升级后，系统很快就恢复正常了。我现在不清楚这里面的前因后果，猜测可能是升级锁后，该应用便可以独占的持有表，确保其顺利的将业务做完，最后释放所锁。

先记录一下，等以后彻底了解DB2的锁机制后，再来讨论这个问题。

 

转载于:https://www.cnblogs.com/cheneyyu/archive/2012/09/09/2677549.html