DB数据库锁机制及问题.pptx

1.1.DB2DB2锁策略简介锁策略简介2.2.锁表原因分析和场景模拟锁表原因分析和场景模拟3.3.定位锁问题的常见定位锁问题的常见DB2DB2命令命令4.4.相关编程建议相关编程建议1 1、DB2DB2锁策略简介锁策略简介锁是数据库为了控制并发数据的完整性而引入的机制。锁定的对象锁定的模式（强度）锁定的长度锁定的对象数据库表格空间表记录锁定的模式（一）锁定的模式（一）基本的锁类型有两种：排它锁（Exclusivelocks记为X锁）和共享锁（Sharelocks记为S锁）。排它锁：若事务T对数据D加X锁，则其它任何事务都不能再对D加任何类型的锁，直至T释放D上的X锁；一般要求在修改数据前要向该数据加排它锁，所以排它锁又称为写锁。共享锁：若事务T对数据D加S锁，则其它事务只能对D加S锁，而不能加X锁，直至T释放D上的S锁；一般要求在读取数据前要向该数据加共享锁，所以共享锁又称为读锁。锁定的模式（二）锁定的模式（二）-DB2数据库表锁的模式 锁定的模式（三）锁定的模式（三）-DB2数据库行锁的模式 锁定的模式（四）锁定的模式（四）-锁的兼容性锁定的模式（五）锁定的模式（五）-DB2数据库表锁的兼容性锁定的模式（六）锁定的模式（六）-DB2数据库行锁的兼容性锁定的模式（七）锁定的模式（七）-各SQL语句产生表锁的情况（假设缺省的隔离级别为CS）与实际情况可能有出入，仅供参考锁定的长度（一）锁定的长度（一）-隔离级别（ISOLATION LEVEL）写锁定（如X、W、NW、Z等）持续到事务结束（提交或是回滚）时释放读锁定（如S、NS、U等）Repeatable Read(RR)所有的读锁定在事务结束时才释放Read Stability(RS)在符合条件的记录上所读取得的读锁定，会持续到事务结束时才释放。而不符合条件的记录上的读锁定，则在读取下一笔记录时就立刻释放Cursor Stability(CS)目前所读取的记录上的读锁定，会在读取下一笔记录时就立刻释放Uncommitted Read(UR)在读取记录时，不会取得读锁定。也就是一般所说的脏读。缺省模式为CS锁定的长度（二）锁定的长度（二）2 2、锁表原因分析和场景模拟、锁表原因分析和场景模拟锁超时死锁锁升级-911实例分析锁超时锁超时锁等待当某应用程序已经取得一个对象上的锁定（例如X锁），而第2个程序企图取得同一对象上无法并存的锁定（例如S锁定）时，则第2个程序的SQL指令就无法执行下去，发生锁定等待。锁超时LOCKTIMEOUTLOCKTIMEOUT参数参数db cfg中，指定一个应用程序被允许的锁等待的时间，单位是秒。缺省情况下，LOCKTIMEOUT是-1，意味着锁等待时间无限期。建议修改。当应用程序等待时间超过LOCKTIMEOUT时间，仍未获得锁，则数据库返回-911错误，reason code为68。死锁（一）死锁（一）死锁死锁是因为两个并发的进程或者线程同时各自占有一个资源上的锁，又需要占有对方资源上的锁，但又都各不相让造成的。相关参数DLCHKTIMEDLCHKTIME参数参数DB2检查死锁的间隔时间，单位是毫秒每隔DLCHKTIME这么长的时间，数据库死锁检查器就会检查有无死锁存在。如有死锁，会选择回滚其中的某一个事务，让另外一个事务完成交易。回滚的事务返回返回-911错误，reason code为2。死锁（二）死锁（二）-示例SessionA现在应用程序A就拥有了一个EMPLOYEE表的行级别的排他锁SessionB现在应用程序B就拥有了一个PROJECT表的行级别的排他锁db2connecttosampledb2+cinsertintoemployeevalues(000350,Truman,I,Jiang,B00,5892,1999-02-21,Engineer,19,M,1978-06-17,60000,2000,6000)db2connecttosampledb2+cinsertintoprojectvalues(AD3300,DeadLockMonitor,B00,000350,7.00,1982-07-21,1983-02-03,AD3111)死锁（三）死锁（三）-示例续SessionA应用程序A需要PROJECT表上所有行的共享锁，但是因为PROJECT表正在被应用程序B以排他锁的形式独占，这时候应用程序A就进入一个锁等待的状态。SessionB应用程序B也进入一个锁等待的状态。此时就出现了一个死锁状态。db2+cselectprojnamefromprojectdb2+cselectfirstnmefromemployee锁升级（一）锁升级（一）锁升级每个锁在内存中都需要一定的内存空间，为了减少锁需要的内存开销，DB2提供了锁升级的功能，将行锁升级为表锁，从而达到减少锁的数量，降低锁的内存开销的目的。相关参数LOCKLISTLOCKLIST参数参数在一个数据库全局内存中用于锁存储的内存。单位为页（4K）MAXLOCKSMAXLOCKS参数参数一个应用程序允许得到的锁占用的内存所占locklist大小的百分比。锁升级（二）锁升级（二）锁升级的触发某个应用程序请求的锁所占用的内存空间超出了maxlocks*locklist的乘积大小。这时，数据库管理器将试图通过为提出锁请求的应用程序申请表锁，并释放行锁来节省空间。在一个数据库中已被加上的全部锁所占的内存空间超出了locklist定义的大小。这时，数据库管理器也将试图通过为提出锁请求的应用程序申请表锁，并释放行锁来节省空间。锁升级的后果增加可用的锁的内存空间降低程序的并发性锁住整个表，容易造成死锁，或进一步造成其他事务发生锁等待的情况。-911-911实例分析（一）实例分析（一）出现-911错时，首先排除程序逻辑中的死锁 相比死锁，锁等待导致的锁超时情况更为常见-911-911实例分析（二）实例分析（二）-上图情况，文件生成插入的记录，和文件装载读取的记录，并无交集，因此从代码逻辑上，无法直观判断出存在资源等待的情况。也可以排除死锁。BAT_AflFileGeneinsert into viw_bke_file_inf;Commit;BAT_FileCheckDECLARE*CURSOR FORselect from viw_bke_file_inf Where with ur;-911-911实例分析（三）实例分析（三）从数据库锁操作的角度来分析：BAT_AflFileGene模块往文件信息视图中插入记录，新增记录被加了X锁。在事务提交前，这个X锁一直不会释放。此时BAT_FileCheck轮询文件信息视图，进行declare操作。首先会对该表加IS(IntentShare)锁，然后开始读取表格中记录读取每一条记录前，先要取得该记录的NS(NextKey)锁，取得NS锁后，才能比对该笔记录是否符合where条件，进行后继操作。当读到BAT_AflFileGene新产生的那条记录时，试图获得该记录的NS锁，但该记录上已被BAT_AflFileGene模块加上X锁。这时BAT_FileCheck只能进行锁等待。当超过数据库设置的锁超时时间，BAT_AflFileGene仍未执行到，则BAT_FileCheck就会报-911错。3 3、定位锁问题的常见、定位锁问题的常见DB2DB2命令命令查看和锁相关的数据库配置参数查看当前并发应用 查看快照信息查看执行策略查看和锁相关的数据库配置参数db2 get db cfg查看数据库配置db2 update db cfg更改数据库配置db2getdbcfg|grep-ilockMaxstorageforlocklist(4KB)(LOCKLIST)=10000Intervalforcheckingdeadlock(ms)(DLCHKTIME)=10000Percent.oflocklistsperapplication(MAXLOCKS)=100Locktimeout(sec)(LOCKTIMEOUT)=1db2updatedbcfgusingLOCKTIMEOUT60查看当前并发应用db2listapplications查看当前是否有多个应用在连接着数据库，从而排查是否有并发的存在。db2listapplicationsAuthIdApplicationAppl.ApplicationIdDB#ofNameHandleNameAgents-BAT_EMVdb2sysc867*LOCAL.bat_emv.070731022004SAMDB1BAT_EMVdb2bp456*LOCAL.bat_emv.0001B1021309SAMDB1BAT_EMVBAT_FileCheck894*LOCAL.bat_emv.0E0591022034BKEDB1BAT_EMVBAT_AflFileGen864*LOCAL.bat_emv.0606D1022003BKEDB1BAT_EMVBAT_knlCtrl859*LOCAL.bat_emv.0206B1022002BKEDB1BAT_EMVdb2bp901*LOCAL.bat_emv.0E0391012328BKEDB1查看快照信息（一）用于-911发生前，分析锁等待的状态。打开锁快照开关获取当前快照根据快照输出文件，搜索被锁表格信息，找出锁住表格的应用的ID。分析见下页。如有需要，强制停止锁表的应用。db2updatemonitorswitchesusinglockondb2getsnapshotforlockson数据库名称输出文件名称db2forceapplications()查看快照信息（二）续P22的实例,分析快照。先看BAT_AflFileGene模块状态Applicationhandle=1068ApplicationID=C0A81CC4.DDC5.0A0699100810Sequencenumber=0001Application name =BAT_FileCheckCONNECTAuthorizationID=ZDZP_DBApplication status =Lock-waitStatuschangetime=NotCollectedApplicationcodepage=1386Locksheld=6Totalwaittime(ms)=16425Subsectionwaitingforlock=0ID of agent holding lock =189ApplicationIDholdinglock=C0A81CC4.DDC2.0105F9100602Lock name =0 x00020020000000040000000052Lockattributes=0 x00000000Releaseflags=0 x00000001Lock object type =Row Lock mode =Exclusive Lock(X)Lock mode requested =Next Key Share(NS)Nameoftablespaceholdinglock=USERSPACE1Schemaoftableholdinglock=ZDZP_DBName of table holding lock =TBL_BKE_FILE_INF4Lockwaitstarttimestamp=04-19-200718:06:01.800533查看快照信息（三）再看BAT_FileCheck模块状态Application handle =189ApplicationID=C0A81CC4.DDC2.0105F9100602Sequencenumber=0001Application name =BAT_AflFileGeneCONNECTAuthorizationID=ZDZP_DBApplicationstatus=UOWWaitingStatuschangetime=NotCollectedApplicationcodepage=1386Locksheld=29Totalwaittime(ms)=0ListOfLocksLock Name =0 x00020020000000040000000052LockAttributes=0 x00000000ReleaseFlags=0 x40000000LockCount=18HoldCount=0LockObjectName=4Object Type =RowTablespaceName=USERSPACE1TableSchema=ZDZP_DBTable Name =TBL_BKE_FILE_INF4Mode =X查看执行策略（一）查看DB2的最优化处理器（Optimizer）在选择SQL指令的执行计划时，会采用何种锁定方式。db2expln例如：db2expln-d-u(-c-p-version/-f/-q)-godb2expln-dbkedb-ubat_emvbat_emv-cbat_emv-pBAT1018-g-otyniu.out查看执行策略（二）|AccessTableName=BAT_EMV.TBL_BKE_FILE_INF4ID=2,37|IndexScan:Name=BAT_EMV.IND_BKE_FI4_2ID=3|RegularIndex(NotClustered)|IndexColumns:|1:FILE_DT(Ascending)|2:INS_ID_CD(Ascending)|3:FILE_TP_INDEX(Ascending)|4:FILE_TFR_DIR(Ascending)|5:FILE_PROC_ST(Ascending)|#Columns=15|#KeyColumns=0|StartKey:BeginningofIndex|StopKey:EndofIndex|DataPrefetch:None|IndexPrefetch:None|Lock Intents|Table:Intent Share|Row :Next Key Share|SargableIndexPredicate(s)|#Predicates=2UNION4 4、相关编程建议、相关编程建议编程建议（一）编程建议（一）尽量缩短事务长度在数据一致性逻辑许可情况下，尽快提交事务，不要在事务中加入不必要的执行时间过长的代码，比如大的代码循环和远程调用，或者一些没有用处的SELECT语句。否则在提交前，锁一直得不到释放，容易产生锁超时或锁升级的情况。例如P22的实例 编程建议（二）编程建议（二）使用正确的隔离级别系统缺省的ISOLATIONLEVEL为CS。程序员可以在BIND或PREP时指定该程序缺省的ISOLATIONLEVEL，也可以在每个SQL语句中指定相应的ISOLATIONLEVEL。UR这一ISOLATIONLEVEL无需任何读锁，因此它的效率最高。我们建议，如果业务允许的话，尽可能多用这种ISOLATIONLEVEL。编程建议（三）编程建议（三）避免逻辑中的死锁如果多个应用程序访问同一资源，最好以相同的次序访问。这样，即使前一个访问资源的应用程序会延迟其他应用程序的访问，也不会导致死锁的发生 编程建议（四）编程建议（四）创建和使用合理的索引如果SQL语句没有走到索引，则最优化处理器只能使用表格索引的方式，即遍历这张表格，来查找记录。遍历过程中，会将所有的记录依次加锁。如果SQL语句走到索引，则最优化处理器会选择采用索引搜索，也就是会借助索引结构来找记录。只会将索引上的记录加锁。这样可以有效的避免锁等待。编程建议（五）编程建议（五）其他(非编程)尽量避免锁升级，正确调整参数LOCKLIST,MAXLOCKS也可能存在系统表被锁的情况，可对系统表做runstats 谢谢！