DB2锁问题处理最佳实践

DB2锁问题处理最佳实践徐明伟北京普远天成科技有限公司技术总监DTCC2012DTCC2012议题12345DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB29.7锁机制深入分析DB2锁案例分享2DTCC2012DTCC2012为什么需要锁•一致性机制–事务–日志–锁–隔离级别•锁–维护数据一致性–控制并发性•锁分类–锁的对象（表、行、表空间、索引）–锁的模式（S,X等）3DTCC2012DTCC2012锁导致问题/锁现象•锁的几种现象–锁等待–锁超时–死锁–锁升级–锁转换•锁产生的问题–系统运行慢–应用回滚4DTCC2012DTCC2012议题12345DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB29.7锁机制深入分析DB2锁案例分享5DTCC2012DTCC2012锁问题监控和定位•锁问题监控定位工具Snapshot快照deadlockeventmonitorwithdetailshistorydb2pd(8.2后)db2pdcfg(9.1)db2_capture_locktimeout(9.5)NewLockingeventmonitor(9.7)•锁是症状，不是根源6DTCC2012DTCC2012锁快照监控•通过getsnapshotfordatabaseondb或sysibmadm.snapdb–死锁、锁等、锁超时、锁升级等统计信息DatabaseSnapshotDatabasename=CRMDBFirstdatabaseconnecttimestamp=10/10/201120:50:28.421301Snapshottimestamp=03/07/201211:10:08.120847Locksheldcurrently=1Lockwaits=471967Timedatabasewaitedonlocks(ms)=2039693347Locklistmemoryinuse(Bytes)=1740480Deadlocksdetected=470Lockescalations=0Exclusivelockescalations=0Agentscurrentlywaitingonlocks=0LockTimeouts=24782快照时间数据库连接时间锁等数量发生锁等的时间死锁数量锁升级数量锁超时数量7DTCC2012DTCC2012db2pd监控锁等•db2pd–ddb_name-lockswait–tra–app–dyn–定位引起锁等的事务、应用和动态语句8DTCC2012DTCC2012db2pdcfg捕获锁超时(9.1版本)•db2pd结合db2cos回调脚本捕获锁超时或死锁•改写db2cos回调脚本LOCKTIMEOUT)echoLockTimeoutCaught$logfileif[!-n$database]thendb2pd-inst$logfileelsedb2pd-db$database-locks-tra-app-dyn$logfilefi;;•db2pdcfg–catchlocktimeoutcount=1•当发生锁超时时，会调用db2cos脚本9DTCC2012DTCC2012db2_capture_locktimeout捕获锁超时(9.1fp4-9.5)•设置db2_capture_locktimeout注册变量–db2setDB2_CAPTURE_LOCKTIMEOUT=ON•创建死锁事件监控器–db2“createeventmonitordlockevmfordeadlockswithdetailshistorywritetofile‘/home/db2inst1/locks’”请求锁模式请求的SQL语句锁请求信息10DTCC2012DTCC2012锁拥有者信息当前锁模式占有锁的SQL语句11DTCC2012DTCC2012Deadlockeventmonitor监控死锁•创建deadlock事件监控器–db2createeventmonitordlockevmfordeadlockswithdetailshistorywritetofile'/home/db2inst1/deadlock'“Deadlockid死锁的表表上已有的锁模式当前请求的锁模式死锁语句12DTCC2012DTCC2012Deadlockeventmonitor监控死锁(2)Deadlockid锁的表请求的锁类型死锁语句13DTCC2012DTCC2012锁事件监控器(9.7版本)•9.7引入了新的锁事件监控器•用同一个锁事件监控器就可捕获死锁、锁超时和锁等待•采用UE表存取锁事件结果，使得分析更简单•锁事件监控器的使用包含三个步骤：–1.创建锁事件监控器–2.设置数据收集的类型和级别–3.格式化并分析数据14DTCC2012DTCC2012创建锁事件监控器createeventmonitorlockevmonforlockingwritetounformattedeventtable(tablelocks)创建锁事件监控器seteventmonitorlockevmonstate=1创建锁事件监控器•UnformattedEvent(UE)表–解释说明15DTCC2012DTCC2012设置锁事件参数•捕获死锁事件–db2updatedbcfgusingmon_deadlockhist_and_values•捕获锁超时事件–db2updatedbcfgusingmon_locktimeouthist_and_values•捕获锁等待事件–db2updatedbcfgusingmon_lw_thresh5000000–db2updatedbcfgusingmon_lockwaithist_and_values•要监控的锁事件信息详细程度:–Without_hist：收集基本事件信息–With_hist：一个事务里最多收集250个活动–Hist_and_values：收集活动和值–None：不收集事件16DTCC2012DTCC2012格式化锁事件输出结果•一旦捕获了锁事件，下一步就要对锁数据进行分析•对UE表数据的格式化有三种方法：–Db2evmonfmttool产生文本格式输出报告–EVMON_FORMAT_UE_TO_XML_UDF函数产生XML格式输出报告–EVMON_FORMAT_UE_TO_TABLE存储过程将UE数据格式化成关系表数据分析处理变得简单17DTCC2012DTCC2012死锁举例Application#1updatet1setname='t11...'whereid=100updatet2setname='t2new...'whereid=200Application#2updatet2setname='t2...'whereid=200select*fromemployeeupdatet1setname='t222'whereid=100(911error)模拟一个死锁场景18DTCC2012DTCC2012使用EVMON_FORMAT_UE_TO_TABLE格式化callEVMON_FORMAT_UE_TO_TABLES('LOCKING',NULL,NULL,NULL,NULL,NULL,'RECREATE_FORCE',-1,'select*fromlocksorderbyevent_timestamp‘)LOCK_ACTIVITY_VALUESINST97T2012-04-03-22.22.42.368824LOCK_EVENTINST97T2012-04-03-22.22.41.600176LOCK_PARTICIPANTSINST97T2012-04-03-22.22.41.921119LOCK_PARTICIPANT_ACTIVITIESINST97T2012-04-03-22.22.42.11235819DTCC2012DTCC2012查看死锁和参与者信息XMLIDEVENT_TYPEEVENT_TIMESTAMPMEMBERDL_CONNSROLLED_BACK_PARTICIPANT_NO--------------------------------------------------------------------------------------------------------db2LockEvent_4…DEADLOCK2012-04-03-21.27.19.660632022Selectxmlid,event_id,event_type,event_timestamp,member,dl_conns,rolled_back_participant_nofromlock_event查看锁信息XMLIDPARTICIPANT_NOPARTICIPANT_TYPEPARTICIPANT_NO_HOLDING_LKAPPLICATION_HANDLE-------------------------------------------------------------------------------------db2LockEvent_4…1Requester27db2LockEvent_4…2Requester160Selectxmlid,participant_no,participant_type,participant_no_holding_lk,application_handlefromlock_participantswherexmlidlikedb2LockEvent_1%'查看死锁参与者信息20DTCC2012DTCC2012查看死锁参与者SQL活动信息ACTIVITY_IDACTIVITY_TYPEUOW_IDSTMT_TYPESTMT_TEXT---------------------------------------------------------------------------------------------------------------------1past42updatet1setname='t11...'whereid=1002current42updatet2setname='t2new...'whereid=2001past12updatet2setname='t2...'whereid=2002past12select*fromemployee3current12updatet1setname='t222'whereid=100selectactivity_id,activity_type,uow_id,stmt_type,substr(stmt_text,1,100)asstmt_textfromlock_participant_activitieswherexmlidlike'db2LockEvent_4%'查看死锁参与者SQL活动信息21DTCC2012DTCC2012议题12345DB2锁概述DB2锁问题监控和定位DB2锁问题调优DB29.7锁机制深入分析DB2锁案例分享22DTCC2012DTCC2012性能调优关键•锁是症状，SQL是问题的根源•I/O最关键–减少I/O–最大化I/O效率–存储规划–物理设计•CPU2大杀手–表扫描–排序•Memory命中率可能会骗人23DTCC2012DTCC2012锁的调优(1)—应用层•写优秀的SQL语句•创建合适的索引避免表扫描•选择合适的隔离级别:UR,CS(CC),RS,RR•事务尽可能频繁的提交•在事务结尾执行insert/update/delete24DTCC2012DTCC2012锁的调优(2)—数据库层•调优locklist和maxlocks数据库参数–9.1后设为automatic•Locktimeout锁超时参数–OLTP系统,建议设置为15-30秒–D