實(shí)驗(yàn)13數(shù)據(jù)庫(kù)并發(fā)控制實(shí)驗(yàn)?zāi)夸浺粚?shí)驗(yàn)?zāi)康亩尘爸R(shí)三實(shí)驗(yàn)示例四實(shí)驗(yàn)內(nèi)容與要求（選做）

了解并掌握數(shù)據(jù)庫(kù)的保護(hù)措施——并發(fā)控制機(jī)制，重點(diǎn)以SQLServer2005或2008為平臺(tái)加以操作實(shí)踐，要求認(rèn)識(shí)典型并發(fā)問(wèn)題的發(fā)生現(xiàn)象并掌握其解決方法。一、實(shí)驗(yàn)?zāi)康亩?、背景知識(shí)二、背景知識(shí)2.1事務(wù)二、背景知識(shí)2.2加鎖技術(shù)二、背景知識(shí)下表(表13-1)顯示了最常見(jiàn)的鎖模式的兼容性。二、背景知識(shí)二、背景知識(shí)二、背景知識(shí)二、背景知識(shí)表13-2資源加鎖粒度表二、背景知識(shí)二、背景知識(shí)二、背景知識(shí)下表(表13-3)顯示了不同隔離級(jí)別允許的并發(fā)副作用。表13-3SQLServer2005支持的四種隔離級(jí)別二、背景知識(shí)三、實(shí)驗(yàn)示例

三、實(shí)驗(yàn)示例

丟失修改是指A、B事務(wù)在同時(shí)讀到修改基準(zhǔn)數(shù)據(jù)后，A事務(wù)修改數(shù)據(jù)，緊接著B(niǎo)事務(wù)也修改數(shù)據(jù)并覆蓋A事務(wù)的修改，使A事務(wù)的修改丟失，從而產(chǎn)生兩次修改行為而只有一次修改數(shù)據(jù)保留的錯(cuò)誤情況。丟失修改是并發(fā)控制首要解決的并發(fā)問(wèn)題，因?yàn)槠渲苯佑绊憯?shù)據(jù)的正確性。

以下兩存儲(chǔ)過(guò)程同時(shí)運(yùn)行時(shí)，則會(huì)發(fā)生丟失修改(檢驗(yàn)時(shí)發(fā)現(xiàn)數(shù)量字段的值較原值改變了，說(shuō)明中間有修改丟失)。解決的辦法是：避免先SELECT再UPDATE操作的事務(wù)設(shè)計(jì)，若必要如此安排事務(wù)，則可以在事務(wù)一開(kāi)始就對(duì)數(shù)據(jù)加獨(dú)占鎖如：把事務(wù)中的SELECT語(yǔ)句改為“SELECT@sl=數(shù)量FROMsalesWITH(tablockx)WHERE客戶代號(hào)='A0001'”或在SELECT語(yǔ)句前加“UPDATEsalesSET客戶代號(hào)=客戶代號(hào)WHERE客戶代號(hào)='A0001'”語(yǔ)句使得事物一開(kāi)始就能獨(dú)占加鎖表；或指定更高的事務(wù)隔離級(jí)別如REPEATABLEREAD、SNAPSHOT（說(shuō)明：需類(lèi)似如下命令A(yù)LTERDATABASEjxglSETALLOW_SNAPSHOT_ISOLATIONON，設(shè)置數(shù)據(jù)庫(kù)為允許快照）或SERIALIZABLE（可能會(huì)發(fā)生死鎖的）。1、丟失修改(LOSTUPDATES)三、實(shí)驗(yàn)示例

CREATEPROCEDUREmodi_mASDECLARE@iintDECLARE@slintSETTRANSACTIONISOLATIONLEVELREADCOMMITTEDSELECT@i=1WHILE(@i<=2000)BEGIN--2000可調(diào)節(jié)BEGINTRANSELECT@sl=數(shù)量FROMsalesWHERE客戶代號(hào)='A0001'WAITFORDELAY'00:00:00.002'--可調(diào)節(jié)延遲時(shí)間UPDATEsalesSET數(shù)量=@sl-1WHERE客戶代號(hào)='A0001'COMMITTRANSELECT@i=@i+1END三、實(shí)驗(yàn)示例

另一存儲(chǔ)過(guò)程modi_a由以上存儲(chǔ)過(guò)程modi_m修改“UPDATEsalesSET數(shù)量=@sl-1WHERE客戶代號(hào)=‘A0001’”語(yǔ)句為“UPDATEsalesSET數(shù)量=@sl+1WHERE客戶代號(hào)=‘A0001’”而得，其它語(yǔ)句相同。sales為表名，其含有客戶代號(hào)、數(shù)量等字段。先可利用“CREATETABLEsales(客戶代號(hào)char(5)PRIMARYKEY,數(shù)量intnull)”命令創(chuàng)建該表，再利用“INSERTINTOsalesVALUES('A0001',0)”命令插入初始記錄。

接著可創(chuàng)建兩存儲(chǔ)過(guò)程如圖13-1所示。然后可同時(shí)打開(kāi)兩個(gè)查詢子窗體，在兩子窗體中分別運(yùn)行兩存儲(chǔ)過(guò)程來(lái)模擬事務(wù)地并行執(zhí)行。如圖13-2所示，先按前面子窗體的運(yùn)行按鈕（或按F5），再快速移動(dòng)鼠標(biāo)到后一子窗體并按運(yùn)行按鈕（或按F5），這樣它們能并行執(zhí)行，以下其它事務(wù)地并行運(yùn)行方法相同，將不再贅述?？啥啻芜\(yùn)行，觀察事務(wù)并發(fā)執(zhí)行情況及并行運(yùn)行結(jié)果（運(yùn)行第三個(gè)查詢窗體，查看“數(shù)量”字段的值）。三、實(shí)驗(yàn)示例

可以看到，同時(shí)運(yùn)行modi_a與modi_m，即實(shí)現(xiàn)對(duì)“數(shù)量”字段加1減1各2000次操作后，“數(shù)據(jù)”字段為-4（多次運(yùn)行會(huì)得到不同的非0數(shù)）而不是0（本應(yīng)該為0），說(shuō)明發(fā)生了丟失修改現(xiàn)象。如果把存儲(chǔ)過(guò)程modi_a改成如下（另一存儲(chǔ)過(guò)程也作相應(yīng)修改，略）：CREATEPROCEDUREmodi_aASDECLARE@iintDECLARE@slintSETTRANSACTIONISOLATIONLEVELREADCOMMITTEDSELECT@i=1WHILE(@i<=2000)BEGIN--2000可調(diào)節(jié)

BEGINTRANSELECT@sl=數(shù)量FROMsalesWITH(tablockx)WHERE客戶代號(hào)=‘A0001’--加獨(dú)占鎖

WAITFORDELAY'00:00:00.002'--可調(diào)節(jié)延遲時(shí)間

UPDATEsalesSET數(shù)量=@sl+1WHERE客戶代號(hào)='A0001'COMMITTRANSELECT@i=@i+1END三、實(shí)驗(yàn)示例

圖13-1創(chuàng)建存儲(chǔ)過(guò)程modi_a與modi_m三、實(shí)驗(yàn)示例

圖13-2并發(fā)運(yùn)行存儲(chǔ)過(guò)程modi_a與modi_m的子窗體情況三、實(shí)驗(yàn)示例

2、臟讀(dirtyread)三、實(shí)驗(yàn)示例

CREATEPROCEDUREdirt_wrollASDECLARE@iintDECLARE@slintSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTEDSELECT@i=1WHILE(@i<=16000)BEGIN--16000可調(diào)節(jié)SELECT@i=@i+1BEGINTRANSELECT@sl=數(shù)量FROMsalesWHERE客戶代號(hào)='A0001'UPDATEsalesSET數(shù)量=@sl+1WHERE客戶代號(hào)='A0001'ROLLACKTRANWAITFORDELAY'00:00:00.000'--可調(diào)節(jié)延遲時(shí)間END三、實(shí)驗(yàn)示例

CREATEPROCEDUREdirt_rASDECLARE@iintDECLARE@slintSETTRANSACTIONISOLATIONLEVELREADUNCOMMITTEDSELECT@i=1WHILE(@i<=60000)BEGIN--60000可調(diào)節(jié)SELECT@i=@i+1BEGINTRANSELECT@sl=數(shù)量FROMsalesWHERE客戶代號(hào)='A0001'IF(@sl<>1000)raiserror('發(fā)生了臟讀！',16,1)/*假設(shè)原數(shù)量值為1000*/COMMITTRANEND三、實(shí)驗(yàn)示例

并行運(yùn)行以上兩個(gè)存儲(chǔ)過(guò)程，會(huì)出現(xiàn)如圖13-3所示的發(fā)生了臟讀的提示。修改兩存儲(chǔ)過(guò)程中的設(shè)置隔離級(jí)別語(yǔ)句“SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED”為“SETTRANSACTIONISOLATIONLEVELREADCOMMITTED”（或其它更高隔離級(jí)別）后繼續(xù)運(yùn)行兩存儲(chǔ)過(guò)程。問(wèn)題得到解決。圖13-3發(fā)生了臟讀現(xiàn)象三、實(shí)驗(yàn)示例

3、不可重讀(UNREPEATABLEREAD)三、實(shí)驗(yàn)示例

CREATEPROCEDURErep_rASDECLARE@iintDECLARE@slintDECLARE@s2intSETTRANSACTIONISOLATIONLEVELREADCOMMITTED/*或設(shè)置READUNCOMMITTED*/SELECT@i=1WHILE(@i<=3000)BEGIN--3000可調(diào)節(jié)SELECT@i=@i+1BEGINTRANSELECT@sl=數(shù)量FROMsalesWHERE客戶代號(hào)='A0001'WAITFORDELAY'00:00:00.001'--可調(diào)節(jié)延遲時(shí)間SELECT@s2=數(shù)量FROMsalesWHERE客戶代號(hào)='A0001'IF(@sl<>@s2)raiserror('發(fā)生不可重復(fù)讀！',16,1)COMMITTRANend三、實(shí)驗(yàn)示例

CREATEPROCEDURErep_wASDECLARE@iintDECLARE@slintDECLARE@s2intSETTRANSACTIONISOLATIONLEVELREADCOMMITTED/*或設(shè)置READUNCOMMITTED*/SELECT@i=1WHILE(@i<=1000)BEGIN--1000可調(diào)節(jié)BEGINTRANSELECT@sl=數(shù)量FROMsalesWHERE客戶代號(hào)='A0001'WAITFORDELAY'00:00:00.002'--可調(diào)節(jié)延遲時(shí)間UPDATEsalesSET數(shù)量=@sl+1WHERE客戶代號(hào)='A0001'COMMITTRANSELECT@i=@i+1End三、實(shí)驗(yàn)示例

圖13-4發(fā)生了不可重讀現(xiàn)象三、實(shí)驗(yàn)示例

4、幻影問(wèn)題(phantom)三、實(shí)驗(yàn)示例

CREATEPROCEDUREhuany_IAS/*變量定義(略)*/DECLARE@iintSETTRANSACTIONISOLATIONLEVELREPEATABLEREAD--此處隔離級(jí)別與幻影關(guān)系不大DELETEFROMsalesWHERE(客戶代號(hào)='A1111')SELECT@i=1WHILE(@i<=1000)BEGINBEGINTRAN--1000可調(diào)節(jié)INSERTINTOsales(客戶代號(hào),數(shù)量)VALUES('A1111',10000)COMMITTRANWAITFORDELAY'00:00:00.001'--可調(diào)節(jié)延遲時(shí)間SELECT@i=@i+1END三、實(shí)驗(yàn)示例

CREATEPROCEDUREhuany_uAS/*變量定義(略)*/DECLARE@iintDECLARE@jintSETTRANSACTIONISOLATIONLEVELREPEATABLEREADSELECT@i=1WHILE(@i<=300)BEGINBEGINTRAN--300可調(diào)節(jié)UPDATEsalesSET數(shù)量=數(shù)量+3WHERE客戶代號(hào)='A1111'SELECT@j=0SELECT@j=count(*)FROMsalesWHERE客戶代號(hào)='A1111'and數(shù)量=10000IF(@j>0)raiserror('發(fā)生了幻影現(xiàn)象！',16,1)COMMITTRANWAITFORDELAY'00:00:00.001'--可調(diào)節(jié)延遲時(shí)間SELECT@i=@i+1END三、實(shí)驗(yàn)示例

圖13-5發(fā)生了幻影現(xiàn)象三、實(shí)驗(yàn)示例

圖13-5發(fā)生了幻影現(xiàn)象三、實(shí)驗(yàn)示例

三、實(shí)驗(yàn)示例

5、搶答問(wèn)題

CREATEPROCEDUREqiangxian1ASSETTRANSACTIONISOLATIONLEVELREADCOMMITTEDWHILE(@i<=5000)BEGINBEGINTRAN--5000可調(diào)節(jié)UPDATEsaleSET數(shù)量=數(shù)量+10000WHERE客戶代號(hào)=@i/*先加鎖成功者修改*/IF(@@error<>0)ROLLACKTRANELSECOMMITTRANSELECT@i=@i+1END三、實(shí)驗(yàn)示例

三、實(shí)驗(yàn)示例

6、編號(hào)產(chǎn)生問(wèn)題CREATEPROCEDUREbhscASSETTRANSACTIONISOLATIONLEVELREADCOMMITTEDWHILE(@i<=1500)BEGINBEGINTRAN--1500可調(diào)節(jié)SELECT@sl=max(客戶代號(hào))+1FROMsaleINSERTINTOsale(客戶代號(hào),數(shù)量)VALUES(@sl,@i)COMMITTRANSELECT@i=@i+1END

解決的辦法有：①設(shè)計(jì)編號(hào)產(chǎn)生事務(wù)一開(kāi)始就加獨(dú)占鎖；②設(shè)計(jì)編號(hào)產(chǎn)生事務(wù)，其中采用插入后即查詢重復(fù)編號(hào)情況，若發(fā)現(xiàn)重復(fù)能進(jìn)行反復(fù)嘗試再插入；③利用一般數(shù)據(jù)庫(kù)具有的IDENTITY字段來(lái)保障編號(hào)的唯一性(篇幅限不作進(jìn)一步說(shuō)明)。下面的存儲(chǔ)過(guò)程體現(xiàn)了前兩種方法，當(dāng)多個(gè)此存儲(chǔ)過(guò)程同時(shí)運(yùn)行時(shí)不會(huì)再發(fā)生編號(hào)重復(fù)現(xiàn)象。三、實(shí)驗(yàn)示例

6、編號(hào)產(chǎn)生問(wèn)題CREATEPROCEDUREbhsc2ASSETTRANSACTIONISOLATIONLEVELREADCOMMITTEDWHILE(@i<=1500)BEGINBEGINTRAN--1500可調(diào)節(jié)SELECT@sl=max(客戶代號(hào))+1FROMsaleWITH(tablockx)/*手工加獨(dú)占鎖*/INSERTINTOsale(客戶代號(hào),數(shù)量)VALUES(@sl,@i)SELECT@kk=count(*)FROMsaleWHERE客戶代號(hào)=@slIF@kk>=2BEGINROLLACKTRANcontinueEND/*能反復(fù)嘗試再插入*/ELSECOMMITTRANSELECT@i=@i+1END三、實(shí)驗(yàn)示例

三、實(shí)驗(yàn)示例

7、手工加鎖下并發(fā)事務(wù)讀寫(xiě)沖突三、實(shí)驗(yàn)示例

7、手工加鎖下并發(fā)事務(wù)讀寫(xiě)沖突圖13-6并發(fā)事務(wù)手工加鎖運(yùn)行情況三、實(shí)驗(yàn)示例

7、手工加鎖下并發(fā)事務(wù)讀寫(xiě)沖突CREATEPROCEDUREpminAS/*變量定義(略)*/SETTRANSACTIONISOLATIONLEVELREADCOMMITTED/*可設(shè)置不同隔離級(jí)別*/SELECT@i=1WHILE(@i<=500)/*事務(wù)數(shù)暫設(shè)為500，可調(diào)節(jié)*/BEGINBEGINTRANSELECT@sl=數(shù)量FROMsalesWITH(updlock)WHERE客戶代號(hào)='A0001'/*可采用不同手工加鎖，請(qǐng)通過(guò)幫助學(xué)習(xí)與使用*/WAITFORDELAY'00:00:00.003'/*延遲，可調(diào)節(jié)*/UPDATEsalesSET數(shù)量=@sl-1WHERE客戶代號(hào)='A0001'IF(@@error<>0)ROLLACKTRANELSECOMMITTRANSELECT@i=@i+1END

另一存儲(chǔ)過(guò)程padd由以上存儲(chǔ)過(guò)程pmin修改“UPDATEsalesSET數(shù)量=@sl-1WHERE客戶代號(hào)='A0001'”語(yǔ)句為“UPDATEsalesSET數(shù)量=@sl+1WHERE客戶代號(hào)='A0001'”而得，其它語(yǔ)句相同。

三、實(shí)驗(yàn)示例

8、事務(wù)處理技術(shù)在應(yīng)用開(kāi)發(fā)工具中運(yùn)用三、實(shí)驗(yàn)示例

ImportsSystemImportsSystem.DataImportsSystem.Data.SqlClientPublicClassForm1InheritsSystem.Windows.Forms.Form//變量定義(略)//其他過(guò)程定義(略)PrivateSubBtn8_Click(ByValsenderAsSystem.Object,ByValeAsSystem.EventArgs)HandlesBtn8.ClickDimmyConnectionAsNewSqlConnection("Server=localhost;uid=sa;pwd=sa;database=pubs")//ADO事務(wù)連接對(duì)象的定義myConnection.Open()DimmyCommandAsNewSqlCommand()DimmyTransAsSqlTransaction,ssAsString,icon,jAsInteger,drAsSqlDataReaderForj=1To100三、實(shí)驗(yàn)示例

myTrans=myConnection.BeginTransaction(IsolationLevel.Readcommitted)//ADO連接事務(wù)對(duì)象的創(chuàng)建myCommand.Connection=myConnectionmyCommand.Transaction=myTrans//事務(wù)開(kāi)始TrymyCommand.CommandText="UPDATEauthorsSETcontract=con