會(huì)計(jì)學(xué)1DB數(shù)據(jù)庫鎖機(jī)制及問題1、DB2鎖策略簡介鎖是數(shù)據(jù)庫為了控制并發(fā)數(shù)據(jù)的完整性而引入的機(jī)制。

鎖定的對象鎖定的模式（強(qiáng)度）鎖定的長度第1頁/共38頁鎖定的對象數(shù)據(jù)庫表格空間表記錄第2頁/共38頁鎖定的模式（一）基本的鎖類型有兩種：排它鎖（Exclusivelocks記為X鎖）和共享鎖（Sharelocks記為S鎖）。排它鎖：若事務(wù)T對數(shù)據(jù)D加X鎖，則其它任何事務(wù)都不能再對D加任何類型的鎖，直至T釋放D上的X鎖；一般要求在修改數(shù)據(jù)前要向該數(shù)據(jù)加排它鎖，所以排它鎖又稱為寫鎖。共享鎖：若事務(wù)T對數(shù)據(jù)D加S鎖，則其它事務(wù)只能對D加S鎖，而不能加X鎖，直至T釋放D上的S鎖；一般要求在讀取數(shù)據(jù)前要向該數(shù)據(jù)加共享鎖，所以共享鎖又稱為讀鎖。第3頁/共38頁鎖定的模式（二）

--DB2數(shù)據(jù)庫表鎖的模式

第4頁/共38頁鎖定的模式（三）

--DB2數(shù)據(jù)庫行鎖的模式

第5頁/共38頁鎖定的模式（四）

--鎖的兼容性第6頁/共38頁鎖定的模式（五）

--DB2數(shù)據(jù)庫表鎖的兼容性第7頁/共38頁鎖定的模式（六）

--DB2數(shù)據(jù)庫行鎖的兼容性第8頁/共38頁鎖定的模式（七）

--各SQL語句產(chǎn)生表鎖的情況（假設(shè)缺省的隔離級(jí)別為CS）

與實(shí)際情況可能有出入，僅供參考第9頁/共38頁鎖定的長度（一）

--隔離級(jí)別（ISOLATIONLEVEL）寫鎖定（如X、W、NW、Z等）持續(xù)到事務(wù)結(jié)束（提交或是回滾）時(shí)釋放讀鎖定（如S、NS、U等）RepeatableRead(RR)

所有的讀鎖定在事務(wù)結(jié)束時(shí)才釋放ReadStability(RS)

在符合條件的記錄上所讀取得的讀鎖定，會(huì)持續(xù)到事務(wù)結(jié)束時(shí)才釋放。而不符合條件的記錄上的讀鎖定，則在讀取下一筆記錄時(shí)就立刻釋放CursorStability(CS)

目前所讀取的記錄上的讀鎖定，會(huì)在讀取下一筆記錄時(shí)就立刻釋放UncommittedRead(UR)

在讀取記錄時(shí)，不會(huì)取得讀鎖定。也就是一般所說的臟讀。缺省模式為CS第10頁/共38頁鎖定的長度（二）

第11頁/共38頁2、鎖表原因分析和場景模擬鎖超時(shí)死鎖鎖升級(jí)-911實(shí)例分析第12頁/共38頁鎖超時(shí)鎖等待 當(dāng)某應(yīng)用程序已經(jīng)取得一個(gè)對象上的鎖定（例如X鎖），而第2個(gè)程序企圖取得同一對象上無法并存的鎖定（例如S鎖定）時(shí)，則第2個(gè)程序的SQL指令就無法執(zhí)行下去，發(fā)生鎖定等待。鎖超時(shí)LOCKTIMEOUT參數(shù)

dbcfg中，指定一個(gè)應(yīng)用程序被允許的鎖等待的時(shí)間，單位是秒。缺省情況下，LOCKTIMEOUT是-1，意味著鎖等待時(shí)間無限期。建議修改。當(dāng)應(yīng)用程序等待時(shí)間超過LOCKTIMEOUT時(shí)間，仍未獲得鎖，則數(shù)據(jù)庫返回-911錯(cuò)誤，reasoncode為68。

第13頁/共38頁死鎖（一）死鎖 死鎖是因?yàn)閮蓚€(gè)并發(fā)的進(jìn)程或者線程同時(shí)各自占有一個(gè)資源上的鎖，又需要占有對方資源上的鎖，但又都各不相讓造成的。

相關(guān)參數(shù)DLCHKTIME參數(shù)

DB2檢查死鎖的間隔時(shí)間，單位是毫秒每隔DLCHKTIME這么長的時(shí)間，數(shù)據(jù)庫死鎖檢查器就會(huì)檢查有無死鎖存在。如有死鎖，會(huì)選擇回滾其中的某一個(gè)事務(wù)，讓另外一個(gè)事務(wù)完成交易?；貪L的事務(wù)返回返回-911錯(cuò)誤，reasoncode為2。

第14頁/共38頁死鎖（二）

--示例SessionA現(xiàn)在應(yīng)用程序A就擁有了一個(gè)EMPLOYEE表的行級(jí)別的排他鎖

SessionB現(xiàn)在應(yīng)用程序B就擁有了一個(gè)PROJECT表的行級(jí)別的排他鎖

db2connecttosampledb2+c"insertintoemployeevalues('000350','Truman','I','Jiang','B00','5892','1999-02-21','Engineer',19,'M','1978-06-17',60000,2000,6000)"

db2connecttosampledb2+c"insertintoprojectvalues('AD3300','DeadLockMonitor','B00','000350',7.00,'1982-07-21','1983-02-03','AD3111')"第15頁/共38頁死鎖（三）

--示例續(xù)SessionA應(yīng)用程序A需要PROJECT表上所有行的共享鎖，但是因?yàn)镻ROJECT表正在被應(yīng)用程序B以排他鎖的形式獨(dú)占，這時(shí)候應(yīng)用程序A就進(jìn)入一個(gè)鎖等待的狀態(tài)。

SessionB應(yīng)用程序B也進(jìn)入一個(gè)鎖等待的狀態(tài)。此時(shí)就出現(xiàn)了一個(gè)死鎖狀態(tài)。db2+c"selectprojnamefromproject"

db2+c"selectfirstnmefromemployee"

第16頁/共38頁鎖升級(jí)（一）鎖升級(jí) 每個(gè)鎖在內(nèi)存中都需要一定的內(nèi)存空間，為了減少鎖需要的內(nèi)存開銷，DB2提供了鎖升級(jí)的功能，將行鎖升級(jí)為表鎖，從而達(dá)到減少鎖的數(shù)量，降低鎖的內(nèi)存開銷的目的。

相關(guān)參數(shù)LOCKLIST參數(shù) 在一個(gè)數(shù)據(jù)庫全局內(nèi)存中用于鎖存儲(chǔ)的內(nèi)存。單位為頁（4K）MAXLOCKS參數(shù) 一個(gè)應(yīng)用程序允許得到的鎖占用的內(nèi)存所占locklist大小的百分比。

第17頁/共38頁鎖升級(jí)（二）鎖升級(jí)的觸發(fā)某個(gè)應(yīng)用程序請求的鎖所占用的內(nèi)存空間超出了maxlocks*locklist的乘積大小。這時(shí)，數(shù)據(jù)庫管理器將試圖通過為提出鎖請求的應(yīng)用程序申請表鎖，并釋放行鎖來節(jié)省空間。在一個(gè)數(shù)據(jù)庫中已被加上的全部鎖所占的內(nèi)存空間超出了locklist定義的大小。這時(shí)，數(shù)據(jù)庫管理器也將試圖通過為提出鎖請求的應(yīng)用程序申請表鎖，并釋放行鎖來節(jié)省空間。

鎖升級(jí)的后果增加可用的鎖的內(nèi)存空間降低程序的并發(fā)性 鎖住整個(gè)表，容易造成死鎖，或進(jìn)一步造成其他事務(wù)發(fā)生鎖等待的情況。第18頁/共38頁-911實(shí)例分析（一）出現(xiàn)-911錯(cuò)時(shí)，首先排除程序邏輯中的死鎖

相比死鎖，鎖等待導(dǎo)致的鎖超時(shí)情況更為常見第19頁/共38頁-911實(shí)例分析（二）

--①②--

--③上圖情況，文件生成插入的記錄，和文件裝載讀取的記錄，并無交集，因此從代碼邏輯上，無法直觀判斷出存在資源等待的情況。也可以排除死鎖。BAT_AflFileGeneinsertintoviw_bke_file_inf……;

…………………………Commit;BAT_FileCheck…………DECLARE***CURSORFORselect……fromviw_bke_file_infWhere……withur;…………第20頁/共38頁-911實(shí)例分析（三）從數(shù)據(jù)庫鎖操作的角度來分析：①BAT_AflFileGene模塊往文件信息視圖中插入記錄，新增記錄被加了X鎖。在③事務(wù)提交前，這個(gè)X鎖一直不會(huì)釋放。

②此時(shí)BAT_FileCheck輪詢文件信息視圖，進(jìn)行declare操作。首先會(huì)對該表加IS(IntentShare)鎖，然后開始讀取表格中記錄讀取每一條記錄前，先要取得該記錄的NS(NextKey)鎖，取得NS鎖后，才能比對該筆記錄是否符合where條件，進(jìn)行后繼操作。當(dāng)讀到BAT_AflFileGene新產(chǎn)生的那條記錄時(shí)，試圖獲得該記錄的NS鎖，但該記錄上已被BAT_AflFileGene模塊加上X鎖。這時(shí)BAT_FileCheck只能進(jìn)行鎖等待。當(dāng)超過數(shù)據(jù)庫設(shè)置的鎖超時(shí)時(shí)間，BAT_AflFileGene仍未執(zhí)行到③，則BAT_FileCheck就會(huì)報(bào)-911錯(cuò)。

第21頁/共38頁3、定位鎖問題的常見DB2命令查看和鎖相關(guān)的數(shù)據(jù)庫配置參數(shù)查看當(dāng)前并發(fā)應(yīng)用查看快照信息

查看執(zhí)行策略第22頁/共38頁查看和鎖相關(guān)的數(shù)據(jù)庫配置參數(shù)db2getdbcfg查看數(shù)據(jù)庫配置db2updatedbcfg更改數(shù)據(jù)庫配置db2getdbcfg|grep-ilock

Maxstorageforlocklist(4KB)(LOCKLIST)=10000Intervalforcheckingdeadlock(ms)(DLCHKTIME)=10000Percent.oflocklistsperapplication(MAXLOCKS)=100Locktimeout(sec)(LOCKTIMEOUT)=1 db2updatedbcfgusingLOCKTIMEOUT60第23頁/共38頁查看當(dāng)前并發(fā)應(yīng)用db2

list

applications

查看當(dāng)前是否有多個(gè)應(yīng)用在連接著數(shù)據(jù)庫，從而排查是否有并發(fā)的存在。db2listapplicationsAuthId

Application

Appl.

ApplicationId

DB

#of

Name

Handle

Name

Agents

BAT_EMVdb2sysc

867

*LOCAL.bat_emv.070731022004SAMDB1BAT_EMVdb2bp

456*LOCAL.bat_emv.0001B1021309SAMDB1BAT_EMVBAT_FileCheck894*LOCAL.bat_emv.0E0591022034BKEDB1BAT_EMVBAT_AflFileGen864*LOCAL.bat_emv.0606D1022003BKEDB1BAT_EMVBAT_knlCtrl

859*LOCAL.bat_emv.0206B1022002BKEDB1BAT_EMVdb2bp

901*LOCAL.bat_emv.0E0391012328BKEDB1

第24頁/共38頁查看快照信息（一） 用于-911發(fā)生前，分析鎖等待的狀態(tài)。打開鎖快照開關(guān)獲取當(dāng)前快照根據(jù)快照輸出文件，搜索被鎖表格信息，找出鎖住表格的應(yīng)用的ID

。分析見下頁。如有需要，強(qiáng)制停止鎖表的應(yīng)用。db2updatemonitorswitchesusinglockondb2getsnapshotforlockson數(shù)據(jù)庫名稱>輸出文件名稱db2"forceapplications(<ApplicationHandle>)"

第25頁/共38頁查看快照信息（二）續(xù)P22的實(shí)例,分析快照。先看BAT_AflFileGene模塊狀態(tài)Applicationhandle=1068ApplicationID

=C0A81CC4.DDC5.0A0699100810Sequencenumber=0001Applicationname=BAT_FileCheckCONNECTAuthorizationID=ZDZP_DBApplicationstatus=Lock-waitStatuschangetime=NotCollectedApplicationcodepage=1386Locksheld

=6Totalwaittime(ms)=16425Subsectionwaitingforlock=0

IDofagentholdinglock=189ApplicationIDholdinglock=C0A81CC4.DDC2.0105F9100602

Lockname

=0x00020020000000040000000052Lockattributes

=0x00000000Releaseflags

=0x00000001

Lockobjecttype=RowLockmode

=ExclusiveLock(X)Lockmoderequested

=NextKeyShare(NS)Nameoftablespaceholdinglock=USERSPACE1Schemaoftableholdinglock=ZDZP_DB

Nameoftableholdinglock=TBL_BKE_FILE_INF4Lockwaitstarttimestamp=04-19-200718:06:01.800533

第26頁/共38頁查看快照信息（三）再看BAT_FileCheck模塊狀態(tài)Applicationhandle=189ApplicationID=C0A81CC4.DDC2.0105F9100602Sequencenumber=0001Applicationname=BAT_AflFileGeneCONNECTAuthorizationID=ZDZP_DBApplicationstatus=UOWWaitingStatuschangetime=NotCollectedApplicationcodepage=1386Locksheld=29Totalwaittime(ms)=0ListOfLocks

LockName=0x00020020000000040000000052LockAttributes=0x00000000ReleaseFlags=0x40000000LockCount=18HoldCount=0LockObjectName=4

ObjectType=RowTablespaceName=USERSPACE1TableSchema=ZDZP_DB

TableName=TBL_BKE_FILE_INF4

Mode=X第27頁/共38頁查看執(zhí)行策略（一） 查看DB2的最優(yōu)化處理器（Optimizer）在選擇SQL指令的執(zhí)行計(jì)劃時(shí)，會(huì)采用何種鎖定方式。db2expln例如：db2expln-d<databasename>-u<username><password>(-c<schema>-p<packages>-version<version>/-f<filename>/-q<SQL>)-g–o<output>db2expln-dbkedb-ubat_emvbat_emv-cbat_emv-p"BAT1018-"-g-otyniu.out第28頁/共38頁查看執(zhí)行策略（二）|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||IndexPrefe