1、南昌工程學(xué)院計(jì)算機(jī)系第第8章章 游標(biāo)和事務(wù)游標(biāo)和事務(wù)報(bào)告報(bào)告 人：譚德坤人：譚德坤Email:8.1 游標(biāo)( Cursor)8.2 事務(wù)(Transaction) 游標(biāo)的概念 聲明游標(biāo) 打開(kāi)游標(biāo) 數(shù)據(jù)處理 關(guān)閉游標(biāo) 釋放游標(biāo) 事務(wù)的概念 事務(wù)的模式 事務(wù)控制本章知識(shí)點(diǎn) 前面介紹的數(shù)據(jù)檢索方法可以得到數(shù)據(jù)庫(kù)中有關(guān)表的數(shù)據(jù)，但這些數(shù)據(jù)是作為一個(gè)結(jié)果集得到的，用戶(hù)可以把這個(gè)結(jié)果集保存到一個(gè)文件里，或生成一個(gè)新表以便于以后使用。這種查詢(xún)是非常重要的。但這種查詢(xún)形式有一個(gè)很大的缺點(diǎn)，它不能對(duì)結(jié)果集中每一行的數(shù)據(jù)進(jìn)行處理。使用游標(biāo)可以實(shí)現(xiàn)對(duì)查詢(xún)結(jié)果集中的數(shù)據(jù)逐行處理。為何引入游標(biāo)？為何引入游標(biāo)？8.1.1

2、 游標(biāo)的概念游標(biāo)的概念 游標(biāo)是SQL Server提供的一種機(jī)制，它能夠?qū)σ粋€(gè)結(jié)果集進(jìn)行逐行處理，其工作方式類(lèi)似于指針，可以指向結(jié)果集中的任意位置以此對(duì)指定位置的數(shù)據(jù)進(jìn)行處理。可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序中或其它地方。Transact-SQL游標(biāo)遵循ANSI-92標(biāo)準(zhǔn)。使用游標(biāo)的優(yōu)點(diǎn)使用游標(biāo)的優(yōu)點(diǎn) 從游標(biāo)定義可以得知游標(biāo)具有如下的優(yōu)點(diǎn)，正是這些優(yōu)點(diǎn)使得游標(biāo)在實(shí)際編程應(yīng)用中具有重要作用。 允許程序?qū)τ刹樵?xún)語(yǔ)句SELECT返回的行集合中的每一行數(shù)據(jù)執(zhí)行相同或不同的操作，而不是對(duì)整個(gè)行集合執(zhí)行同一個(gè)操作 提供對(duì)基于游標(biāo)位置的表中的行進(jìn)行刪除和更新的能力 游標(biāo)實(shí)際上作為面向集合的數(shù)據(jù)庫(kù)管理系

3、統(tǒng)（RDBMS）和面向行的程序設(shè)計(jì)之間的橋梁，使這兩種處理方式通過(guò)游標(biāo)溝通起來(lái)使用游標(biāo)的步驟 使用游標(biāo)要遵循聲明游標(biāo)打開(kāi)游標(biāo)讀取游標(biāo)關(guān)閉游標(biāo)刪除游標(biāo)的順序。 5步！8.1.2 聲明游標(biāo)declare 游標(biāo)名游標(biāo)名 cursor local | global forward_only | scroll static | keyset | dynamic | fast_forward read_only | scroll_locks | optimisticfor select語(yǔ)句語(yǔ)句 for update of 列名列名 ,.n參數(shù)說(shuō)明： 游標(biāo)名：游標(biāo)命名必須符合標(biāo)識(shí)符規(guī)則，不能超過(guò)30 個(gè)字符

4、。 select語(yǔ)句：定義結(jié)果集的標(biāo)準(zhǔn)select語(yǔ)句，且不許用compute、compute by、for browse和into子句。 local：游標(biāo)的作用域是局部的創(chuàng)建它的批處理、存儲(chǔ)過(guò)程或觸發(fā)器。 global：游標(biāo)的作用域是全局的。在由連接執(zhí)行的任何存儲(chǔ)過(guò)程或批處理中，都可以引用該游標(biāo)名稱(chēng)。該游標(biāo)僅在連接斷開(kāi)時(shí)自動(dòng)釋放。參數(shù)說(shuō)明：forward_only 只進(jìn)。僅支持next。Scroll 滾動(dòng) 支持：next、prior、first、last、absolute、relativestatic 靜態(tài) open時(shí)在tempdb創(chuàng)建臨時(shí)表復(fù)本保存結(jié)果集。只提取，不許修改dynamic 動(dòng)

5、態(tài) 記錄值、順序等在每次提取時(shí)都可能因其他用戶(hù)的更改而變動(dòng)。不支持 absolute 提取選項(xiàng)。keyset 鍵集 open時(shí)在tempdb創(chuàng)建keyset表，記錄結(jié)果集中每條記錄的關(guān)鍵字段值和順序。參數(shù)說(shuō)明：fast_forward 快速向前 優(yōu)化的 forward_only、read_only.與scroll、for_update、 forward_only互斥read_only 只讀 在 update 或 delete 語(yǔ)句的 where current of 子句中不能引用游標(biāo)scroll_locks 滾動(dòng)鎖定 當(dāng)滾動(dòng)記錄指針提取當(dāng)前記錄時(shí)，系統(tǒng)將會(huì)鎖定該行，確保游標(biāo)更新或刪除的成功O

6、ptimistic 樂(lè)觀(guān) 行自從被讀入游標(biāo)以來(lái)，如果已修改該行，嘗試進(jìn)行的更新或刪除將失敗for update of 列,.更新 指定可更新的列。默認(rèn)所有列例例8-1 定義一個(gè)游標(biāo)，其數(shù)據(jù)為表定義一個(gè)游標(biāo)，其數(shù)據(jù)為表salers中中的全部數(shù)據(jù)的全部數(shù)據(jù)DECLARE myCursor CURSORFor select * from Salers語(yǔ)法格式：OPEN GLOBAL 游標(biāo)名游標(biāo)名 當(dāng)游標(biāo)被打開(kāi)時(shí)，行指針會(huì)指在第一行之前 打開(kāi)游標(biāo)后,如果 error=0表示打開(kāi)操作成功 打開(kāi)游標(biāo)后,可用 cursor_rows返回游標(biāo)記錄數(shù)-m 游標(biāo)被異步填充.-m是鍵集中當(dāng)前的行數(shù)-1 游標(biāo)為動(dòng)態(tài)。

7、符合條件記錄的行數(shù)不斷變化0 沒(méi)有符合的記錄、游標(biāo)沒(méi)打開(kāi)、關(guān)閉或釋放n 游標(biāo)已完全填充。n是在游標(biāo)中的總行數(shù)8.1.3 打開(kāi)游標(biāo)8.1.4從打開(kāi)的游標(biāo)中提取數(shù)據(jù) FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | RELATIVE n FROM cursor_name INTO variable_name ,.n NEXT：提取上次提取行之后的行，即向下移動(dòng)。如：提取上次提取行之后的行，即向下移動(dòng)。如果果 FETCH NEXT 為對(duì)游標(biāo)的第一次提取操作，則返回結(jié)為對(duì)游標(biāo)的第一次提取操作，則返回結(jié)果集中的第一行。果集中的第一行。NEXT 為默認(rèn)的游標(biāo)

8、提取選項(xiàng)。為默認(rèn)的游標(biāo)提取選項(xiàng)。 PRIOR：提取上次提取行之前的行。如果：提取上次提取行之前的行。如果 FETCH PRIOR 為對(duì)游標(biāo)的第一次提取操作，則沒(méi)有行返回并且為對(duì)游標(biāo)的第一次提取操作，則沒(méi)有行返回并且游標(biāo)置于第一行之前。游標(biāo)置于第一行之前。 LAST：提取游標(biāo)中的最后一行并將其作為當(dāng)：提取游標(biāo)中的最后一行并將其作為當(dāng)前行。前行。ABSOLUTE n ：如果：如果 n 為正數(shù)，則提取游標(biāo)中從為正數(shù)，則提取游標(biāo)中從第第 1 行開(kāi)始的第行開(kāi)始的第 n 行。如果行。如果 n 為負(fù)整數(shù)，則提為負(fù)整數(shù)，則提取游標(biāo)中的倒數(shù)第取游標(biāo)中的倒數(shù)第 n 行。行。 RELATIVE n ：如果：如果 n

9、 為正數(shù)，則提取所上次為正數(shù)，則提取所上次提取行之后的第提取行之后的第 n 行。如果行。如果 n 為負(fù)數(shù)，則提取為負(fù)數(shù)，則提取上次所提取行之前的第上次所提取行之前的第 n 行。如果行。如果 n 為為 0，則，則同一行被再次提取。同一行被再次提取。 cursor_name ：要從中進(jìn)行提取的游標(biāo)的名稱(chēng)：要從中進(jìn)行提取的游標(biāo)的名稱(chēng)。 INTO variable_name,.n：允許將提取操：允許將提取操作的列數(shù)據(jù)放到局部變量中。作的列數(shù)據(jù)放到局部變量中。注意：注意： FETCH語(yǔ)句每次只能提取一行數(shù)據(jù)。因?yàn)門(mén)ransact-SQL游標(biāo)不支持塊（多行）提取操作。 用fetch_status返回執(zhí)行F

10、ETCH操作之后，當(dāng)前游標(biāo)指針的狀態(tài)。狀態(tài)值如下： 0 表示行已成功地讀取。 -1 表示讀取操作已超出了結(jié)果集。 -2 表示行在表中不存在。注意：注意： 查詢(xún)結(jié)果用into子句寫(xiě)入局部變量，須先聲明局部變量的類(lèi)型和寬度，且與select 語(yǔ)句中相應(yīng)列的順序、類(lèi)型和寬度一致。 第一次執(zhí)行fetch next，則將獲取游標(biāo)中的第一行數(shù)據(jù)。 打開(kāi)游標(biāo)后第一次執(zhí)行fetch prior，則得不到任何數(shù)據(jù)。數(shù)據(jù)處理(2) 修改數(shù)據(jù) 語(yǔ)法格式：語(yǔ)法格式：update from 表或視圖表或視圖 set 列名列名=表達(dá)式表達(dá)式, where current of 游標(biāo)名游標(biāo)名功能：修改當(dāng)前行指定字段的值功能

11、：修改當(dāng)前行指定字段的值數(shù)據(jù)處理(3) 刪除數(shù)據(jù) 語(yǔ)句格式：delete from 表或視圖表或視圖 where current of 游標(biāo)名游標(biāo)名 功能： 刪除游標(biāo)名指定的當(dāng)前行數(shù)據(jù)。8.1.5關(guān)閉游標(biāo) 當(dāng)提取完游標(biāo)結(jié)果集中的數(shù)據(jù)時(shí)，應(yīng)及時(shí)調(diào)當(dāng)提取完游標(biāo)結(jié)果集中的數(shù)據(jù)時(shí)，應(yīng)及時(shí)調(diào)用用CLOSE語(yǔ)句關(guān)閉游標(biāo)，以便釋放游標(biāo)所占用語(yǔ)句關(guān)閉游標(biāo)，以便釋放游標(biāo)所占用的系統(tǒng)資源。的系統(tǒng)資源。 CLOSE cursor_name cursor_name 是要被關(guān)閉的游標(biāo)名。是要被關(guān)閉的游標(biāo)名。8.1.6 釋放（刪除）游標(biāo)由于關(guān)閉游標(biāo)時(shí)并沒(méi)有刪除游標(biāo)，因此，游標(biāo)仍由于關(guān)閉游標(biāo)時(shí)并沒(méi)有刪除游標(biāo)，因此，游標(biāo)仍然

12、占用著系統(tǒng)資源。則將其關(guān)閉后，還需要使然占用著系統(tǒng)資源。則將其關(guān)閉后，還需要使用用DEALLOCATE語(yǔ)句來(lái)釋放游標(biāo)所占用的系語(yǔ)句來(lái)釋放游標(biāo)所占用的系統(tǒng)資源。統(tǒng)資源。 DEALLOCATE cursor_name 使用游標(biāo)修改數(shù)據(jù) UPDATE語(yǔ)句和語(yǔ)句和DELETE語(yǔ)句也支持游標(biāo)操語(yǔ)句也支持游標(biāo)操作，它們可以通過(guò)游標(biāo)修改或刪除游標(biāo)基表中作，它們可以通過(guò)游標(biāo)修改或刪除游標(biāo)基表中的當(dāng)前數(shù)據(jù)行。這樣，就可以通過(guò)游標(biāo)更新和的當(dāng)前數(shù)據(jù)行。這樣，就可以通過(guò)游標(biāo)更新和刪除數(shù)據(jù)表中的數(shù)據(jù)。刪除數(shù)據(jù)表中的數(shù)據(jù)。 UPDATE table_nameSET 子句WHERE CURRENT OF cursor_na

13、me table_name table_name：為需要修改的數(shù)據(jù)表名。：為需要修改的數(shù)據(jù)表名。 cursor_namecursor_name：為游標(biāo)名。：為游標(biāo)名。例如：例如： Update SalersSet sex=女Where CURRENT OF myCursor將游標(biāo)將游標(biāo)“myCursor”中當(dāng)前行中的中當(dāng)前行中的“sex”列列的值修改為的值修改為“女女”。 DELETE FROM table_nameWHERE CURRENT OF cursor_name table_name：為需要修改的數(shù)據(jù)表名。：為需要修改的數(shù)據(jù)表名。 cursor_name：為游標(biāo)名。：為游標(biāo)名。例如：

14、例如：DELETE FROM SalersWhere CURRENT OF myCursor將游標(biāo)將游標(biāo)“myCursor”中當(dāng)前行中刪除。中當(dāng)前行中刪除。 USE Student GO DECLARE student_cur3 CURSOR FOR SELECT student_Name FROM student_Info OPEN student_cur3 GO一個(gè)完整的實(shí)例 FETCH NEXT FROM student_cur3 GO UPDATE student_Info SET student_Name = liu WHERE CURRENT OF student_cur3 GO

15、CLOSE student_cur3 DEALLOCATE student_cur3 GOdeclare 游標(biāo)名游標(biāo)名 cursor for select語(yǔ)句語(yǔ)句(2) open 游標(biāo)名游標(biāo)名(3) 使用使用移動(dòng)讀取：移動(dòng)讀取： fetch 游標(biāo)名游標(biāo)名 into 變量名變量名,刪除數(shù)據(jù)：刪除數(shù)據(jù)： delete from 表或視圖名表或視圖名 where current of 游標(biāo)名游標(biāo)名修改數(shù)據(jù)：修改數(shù)據(jù)： update from 表或視圖名表或視圖名 set 列名列名=表達(dá)式表達(dá)式, where current of 游標(biāo)名游標(biāo)名(4) close 游標(biāo)名游標(biāo)名(5) deallocat

16、e 游標(biāo)名游標(biāo)名是從查詢(xún)記錄集中逐條逐行地移動(dòng)讀取、修改是從查詢(xún)記錄集中逐條逐行地移動(dòng)讀取、修改或刪除記錄的處理機(jī)制。或刪除記錄的處理機(jī)制。靜態(tài)靜態(tài)(Static) / 動(dòng)態(tài)動(dòng)態(tài)(Dynamic)只進(jìn)只進(jìn)(Forward Only)/ 滾動(dòng)滾動(dòng)(Scroll)游標(biāo)小結(jié)8.2 事事 務(wù)務(wù) 事務(wù)是SQL Server中的一個(gè)邏輯單元，其中包括一系列的操作，這些語(yǔ)句將被作為一個(gè)整體進(jìn)行處理。通過(guò)事務(wù)，SQL Server能將邏輯相關(guān)的一組操作綁定在一起，以便服務(wù)器保持?jǐn)?shù)據(jù)的完整性。8.2.1 事務(wù)的基本概念事務(wù)的基本概念 事務(wù)是一種機(jī)制，是一個(gè)操作序列，它包含了一組數(shù)據(jù)庫(kù)操作命令，所有的命令作為一個(gè)

17、整體一起向系統(tǒng)提交或撤消操作請(qǐng)求，即要么都執(zhí)行，要么都不執(zhí)行。因此，事務(wù)是一個(gè)不可分割的工作邏輯單元，類(lèi)似于操作系統(tǒng)中的原語(yǔ)。在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，事務(wù)是作為最小的控制單元來(lái)使用的。 事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作。一個(gè)邏輯工作單元必須有四個(gè)屬性，稱(chēng)為ACID（原子性、一致性、隔離性和持久性）屬性，只有這樣才能成為一個(gè)事務(wù)。原子性：事務(wù)必須是原子工作單元。對(duì)于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。一致性：事務(wù)在完成時(shí)，必須使所有的數(shù)據(jù)都保持一致?tīng)顟B(tài)。在相關(guān)數(shù)據(jù)庫(kù)中，所有規(guī)則都必須應(yīng)用于事務(wù)的修改，以保持所有數(shù)據(jù)的完整性。事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。隔離

18、性：由并發(fā)事務(wù)所作的修改必須與任何其他并發(fā)事務(wù)所作的修改隔離。事務(wù)查看數(shù)據(jù)時(shí)數(shù)據(jù)所處的狀態(tài)，要么是另一并發(fā)事務(wù)修改它之前的狀態(tài)，要么是另一事務(wù)修改它之后的狀態(tài)，事務(wù)不會(huì)查看中間狀態(tài)的數(shù)據(jù)。這稱(chēng)為可串行性，因?yàn)樗軌蛑匦卵b載起始數(shù)據(jù)，并且重播一系列事務(wù)，以使數(shù)據(jù)結(jié)束時(shí)的狀態(tài)與原始事務(wù)執(zhí)行的狀態(tài) 相同。持久性：事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。通常在程序中用BEGIN TRANSACTION命令來(lái)標(biāo)識(shí)一個(gè)事務(wù)的開(kāi)始，用COMMIT TRANSACTION命令標(biāo)識(shí)事務(wù)結(jié)束。這兩個(gè)命令之間的所有語(yǔ)句被視為一體，只有執(zhí)行到COMMIT TRANSACTION命

19、令時(shí)，事務(wù)中對(duì)數(shù)據(jù)庫(kù)的更新操作才算確認(rèn)。和BEGINEND命令類(lèi)似，這兩個(gè)命令也可以進(jìn)行嵌套，即事務(wù)是可以嵌套執(zhí)行這兩個(gè)命令的。1語(yǔ)法語(yǔ)法 BEGIN TRAN SACTION transaction_name | tran_name_variable WITH MARK description 2參數(shù)參數(shù) transaction_name：給事務(wù)分配的名稱(chēng)。 tran_name_variable：用戶(hù)定義的、含有有效事務(wù)名稱(chēng)的變量的名稱(chēng)。必須用 char、varchar、nchar或nvarchar數(shù)據(jù)類(lèi)型聲明該變量。 WITH MARK description：指定在日志中標(biāo)記事務(wù)。des

20、cription 是描述該標(biāo)記的字符串。如果使用了 WITH MARK，則必須指定事務(wù)名。WITH MARK 允許將事務(wù)日志還原到命名標(biāo)記。 SQL Server以下面幾種事務(wù)模式運(yùn)行：顯式事務(wù)隱性事務(wù)自動(dòng)提交事務(wù)分布式事務(wù)8.2.2 顯式事務(wù)顯式事務(wù) 顯示事務(wù)是指由用戶(hù)通過(guò)Transact-SQL事務(wù)語(yǔ)句而定義的事務(wù)。 Transact-SQL事務(wù)語(yǔ)句包括以下語(yǔ)句： BEGIN TRANSACTION語(yǔ)句：標(biāo)記一個(gè)本地事務(wù)的開(kāi)始。 COMMIT TRANSACTION語(yǔ)句：標(biāo)記一個(gè)顯示事務(wù)或隱式事務(wù)的結(jié)束，說(shuō)明事務(wù)已經(jīng)成功執(zhí)行，并將事務(wù)內(nèi)全部被修改的數(shù)據(jù)保存到數(shù)據(jù)庫(kù)中。 COMMIT WOR

21、K語(yǔ)句：標(biāo)記一個(gè)事務(wù)的結(jié)束。 ROLLBACK TRANSACTION語(yǔ)句：回滾顯示事務(wù)或隱式事務(wù)到事務(wù)的起始位置，或事務(wù)內(nèi)部的存儲(chǔ)點(diǎn)。 ROLLBACK WORK語(yǔ)句：回滾顯示事務(wù)到事務(wù)的起始位置。8.2.3 隱式事務(wù)隱式事務(wù) 隱式事務(wù)是指在當(dāng)前事務(wù)提交或回滾后，SQL Server自動(dòng)開(kāi)始的事務(wù)。 在回滾之后，SQL Server又自動(dòng)開(kāi)始一個(gè)新的事務(wù)。 在隱式模式下，當(dāng)執(zhí)行下面任何一個(gè)語(yǔ)句時(shí)，可以使SQL Server重新啟動(dòng)一個(gè)事務(wù)。所有CREATE語(yǔ)句ALTER TABLE語(yǔ)句所有DROP語(yǔ)句TRUNCATE TABLE語(yǔ)句GRANT語(yǔ)句REVOKE語(yǔ)句INSERT語(yǔ)句UPDATE語(yǔ)

22、句DELETE語(yǔ)句SELECT語(yǔ)句OPEN語(yǔ)句FETCH 要關(guān)閉隱式事務(wù)模式前，執(zhí)行SET IMPLICIT_TRANSACTIONS OFF語(yǔ)句即可。8.2.4 自動(dòng)事務(wù)模式自動(dòng)事務(wù)模式 自動(dòng)事務(wù)是一種能夠自動(dòng)執(zhí)行并能自動(dòng)回滾的事務(wù)。在自動(dòng)事務(wù)模式下，當(dāng)一個(gè)語(yǔ)句成功執(zhí)行后，它被自動(dòng)提交，而當(dāng)它執(zhí)行過(guò)程中產(chǎn)生錯(cuò)誤則被自動(dòng)回滾。 8.2.5 分布式事務(wù)分布式事務(wù) 分布式事務(wù)跨越兩個(gè)或多個(gè)稱(chēng)為資源管理器的服務(wù)器。 8.3 鎖鎖 定定 如果在數(shù)據(jù)庫(kù)不一致性狀態(tài)時(shí)讀取數(shù)據(jù)，就有可能產(chǎn)生不一致性問(wèn)題。解決這類(lèi)問(wèn)題的常用方法就是對(duì)此數(shù)據(jù)源進(jìn)行了鎖定，在SQL Server中可以對(duì)以下的對(duì)象進(jìn)行鎖定。數(shù)據(jù)行

23、（Row）：數(shù)據(jù)頁(yè)中的單行數(shù)據(jù)。索引行（Key）：索引頁(yè)中的單行數(shù)據(jù)即索引的鍵值。頁(yè)（Page）：頁(yè)是SQL Server 存取數(shù)據(jù)的基本單位，其大小為8KB。盤(pán)區(qū)（Extent）：一個(gè)盤(pán)區(qū)由8 個(gè)連續(xù)的頁(yè)組成。表（Table）。數(shù)據(jù)庫(kù)（Database）。8.3.1 鎖定粒度鎖定粒度 SQL Server 2000具有多粒度鎖定，可以根據(jù)事務(wù)所執(zhí)行的任務(wù)靈活選擇所鎖定的資源粒度。為了最小化鎖的成本，SQL Server 2000自動(dòng)地將適當(dāng)級(jí)別的鎖分配給任務(wù)。 8.3.2 鎖模式鎖模式 SQL Server 使用以下資源鎖模式。 1共享鎖 共享（S）鎖允許并發(fā)事務(wù)讀取（SELECT）一個(gè)資源

24、。資源上存在共享（S）鎖時(shí)，任何其他事務(wù)都不能修改數(shù)據(jù)。 一旦已經(jīng)讀取數(shù)據(jù)，便立即釋放資源上的共享（S）鎖，除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享（S）鎖。2更新鎖更新鎖 更新（U）鎖可以防止通常形式的死鎖。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄，獲取資源（頁(yè)或行）的共享（S）鎖，然后修改行，此操作要求鎖轉(zhuǎn)換為排它（X）鎖。如果兩個(gè)事務(wù)獲得了資源上的共享模式鎖，然后試圖同時(shí)更新數(shù)據(jù)，則一個(gè)事務(wù)嘗試將鎖轉(zhuǎn)換為排它（X）鎖。 若要避免這種潛在的死鎖問(wèn)題，請(qǐng)使用更新（U）鎖。 3排它鎖排它鎖 排它（X）鎖可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪(fǎng)問(wèn)。其他事務(wù)不能讀取

25、或修改排它（X）鎖鎖定的數(shù)據(jù)。4意向鎖意向鎖 意向鎖表示SQL Server需要在層次結(jié)構(gòu)中的某些底層資源上獲取共享（S）鎖或排它（X）鎖。 5架構(gòu)鎖架構(gòu)鎖 執(zhí)行表的數(shù)據(jù)定義語(yǔ)言（DDL）操作（例如添加列或除去表）時(shí)，使用架構(gòu)修改（Sch-M）鎖。6大容量更新鎖大容量更新鎖 當(dāng)將數(shù)據(jù)大容量復(fù)制到表，且指定了 TABLOCK 提示或者使用 sp_tableoption 設(shè)置了 table lock on bulk 表選項(xiàng)時(shí)，將使用大容量更新（BU）鎖。大容量更新（BU）鎖允許進(jìn)程將數(shù)據(jù)并發(fā)地大容量復(fù)制到同一表，同時(shí)防止其他不進(jìn)行大容量復(fù)制數(shù)據(jù)的進(jìn)程訪(fǎng)問(wèn)該表。8.3.3 顯示鎖定信息顯示鎖定信息

26、可以通過(guò)企業(yè)管理器或存儲(chǔ)過(guò)程來(lái)查看鎖。 1用企業(yè)管理器查看鎖 在企業(yè)管理器中選擇目錄樹(shù)窗口中管理文件夾下當(dāng)前活動(dòng)中的鎖/對(duì)象節(jié)點(diǎn)，如圖8-1所示，則可以查看當(dāng)前鎖定的進(jìn)程，選擇同級(jí)的鎖/對(duì)象節(jié)點(diǎn)下的相應(yīng)子節(jié)點(diǎn)，則可以查看當(dāng)前鎖定的對(duì)象。 圖圖8-1 8-1 查看鎖查看鎖圖圖8-2 8-2 鎖屬性對(duì)話(huà)框鎖屬性對(duì)話(huà)框2用系統(tǒng)存儲(chǔ)過(guò)程用系統(tǒng)存儲(chǔ)過(guò)程Sp_lock 查看鎖查看鎖 sp_lock spid SQL Server 的進(jìn)程編號(hào)spid 可以在master.dbo.sysprocesses 系統(tǒng)表中查到，spid 是INT類(lèi)型的數(shù)據(jù)，如果不指定spid，則顯示所有的鎖。8.3.4 死鎖及其處理

27、死鎖及其處理 當(dāng)某組資源的兩個(gè)或多個(gè)線(xiàn)程之間有循環(huán)相關(guān)性時(shí)，將發(fā)生死鎖。死鎖是在多用戶(hù)或多進(jìn)程狀況下，為使用同一資源而產(chǎn)生的無(wú)法解決的爭(zhēng)用狀態(tài)，通俗地講就是兩個(gè)用戶(hù)各占用一個(gè)資源，兩人都想使用對(duì)方的資源，但同時(shí)又不愿放棄自己的資源，就一直等待對(duì)方放棄資源。如果不進(jìn)行外部干涉，就將一直耗下去。 死鎖會(huì)造成資源的大量浪費(fèi)，甚至?xí)瓜到y(tǒng)崩潰。 盡量避免并發(fā)地執(zhí)行涉及到修改數(shù)據(jù)的語(yǔ)句。 要求每個(gè)事務(wù)一次就將所有要使用的數(shù)據(jù)全部加鎖，否則就不予執(zhí)行。 預(yù)先規(guī)定一個(gè)封鎖順序，所有的事務(wù)都必須按這個(gè)順序?qū)?shù)據(jù)執(zhí)行封鎖。例如，不同的過(guò)程在事務(wù)內(nèi)部對(duì)對(duì)象的更新執(zhí)行順序應(yīng)盡量保持一致。 每個(gè)事務(wù)的執(zhí)行時(shí)間不可太長(zhǎng)

28、，對(duì)程序段長(zhǎng)的事務(wù)可考慮將其分割為幾個(gè)事務(wù)。8.4 監(jiān)監(jiān) 測(cè)測(cè) SQL Server 2000 提供多種工具，監(jiān)測(cè)SQL Server 實(shí)例的性能以及在數(shù)據(jù)庫(kù)中發(fā)生的用戶(hù)活動(dòng)。通過(guò)監(jiān)測(cè)可以使用戶(hù)確定數(shù)據(jù)庫(kù)應(yīng)用程序是否按預(yù)期有效地運(yùn)行，即使應(yīng)用程序、數(shù)據(jù)庫(kù)和環(huán)境發(fā)生了變化。 8.4.1 系統(tǒng)性能評(píng)估及監(jiān)測(cè)系統(tǒng)性能評(píng)估及監(jiān)測(cè) 系統(tǒng)性能評(píng)估是指通過(guò)有效的網(wǎng)絡(luò)流量、磁盤(pán)I/O和CPU時(shí)間，可獲得最短的響應(yīng)時(shí)間和最大的吞吐量，從而獲得最佳性能。 響應(yīng)時(shí)間是這樣度量的：以可視的形式確認(rèn)正在處理查詢(xún)并將結(jié)果集中的首行返回給用戶(hù)所需的時(shí)間長(zhǎng)度；吞吐量是服務(wù)器在給定時(shí)間內(nèi)處理的查詢(xún)總數(shù)的度量值。隨著用戶(hù)數(shù)的增加

29、，對(duì)服務(wù)器資源的競(jìng)爭(zhēng)也隨之增加，轉(zhuǎn)而導(dǎo)致響應(yīng)時(shí)間增加和總體吞吐量減少。 影響系統(tǒng)性能有以下幾個(gè)方面的因素： 系統(tǒng)資源（硬件）Microsoft Windows NT 4.0 和 Windows 2000 操作系統(tǒng)數(shù)據(jù)庫(kù)應(yīng)用程序客戶(hù)端應(yīng)用程序網(wǎng)絡(luò) 監(jiān)測(cè)以下幾個(gè)方面，便可以基本解決問(wèn)題： SQL Server 存儲(chǔ)過(guò)程或用戶(hù)應(yīng)用程序提交的批處理 SQL 語(yǔ)句用戶(hù)活動(dòng)（如阻塞鎖或死鎖）硬件活動(dòng)（如磁盤(pán)使用） 可以使用SQL 事件探查器監(jiān)視和解決 Transact-SQL問(wèn)題以及與應(yīng)用程序有關(guān)的問(wèn)題。可以使用系統(tǒng)監(jiān)視器監(jiān)視硬件問(wèn)題和其他與系統(tǒng)有關(guān)的問(wèn)題。8.4.2 性能監(jiān)測(cè)工具性能監(jiān)測(cè)工具 監(jiān)測(cè)應(yīng)用程

30、序、SQL Server 實(shí)例或操作系統(tǒng)環(huán)境一般按照下面的流程操作。 確定監(jiān)視目標(biāo)。 為要執(zhí)行的監(jiān)視類(lèi)型選擇合適的工具。 使用工具監(jiān)視 SQL Server 或系統(tǒng)環(huán)境并分析捕獲的數(shù)據(jù)。 識(shí)別要監(jiān)視的事件。 事件確定所要監(jiān)視和捕獲的活動(dòng)。選擇要監(jiān)視的事件取決于要監(jiān)視的目標(biāo)和原因。例如，當(dāng)監(jiān)視磁盤(pán)活動(dòng)時(shí)，沒(méi)必要監(jiān)視 SQL Server 鎖。 確定要捕獲的事件數(shù)據(jù)。 事件數(shù)據(jù)描述事件發(fā)生時(shí)的每個(gè)實(shí)例。 具體選擇什么工具取決于監(jiān)測(cè)類(lèi)型和要監(jiān)測(cè)的事件類(lèi)型。SQL 事件探查器 SQL事件探查器能夠監(jiān)測(cè)服務(wù)器和數(shù)據(jù)庫(kù)活動(dòng)，如死鎖數(shù)、嚴(yán)重錯(cuò)誤、跟蹤存儲(chǔ)過(guò)程和Transact-SQL語(yǔ)句或登錄活動(dòng)等。系統(tǒng)監(jiān)

31、視器 通過(guò)使用預(yù)定義對(duì)象和計(jì)數(shù)器或用戶(hù)定義的計(jì)數(shù)器監(jiān)測(cè)事件，監(jiān)測(cè)服務(wù)器的性能和活動(dòng)。系統(tǒng)監(jiān)測(cè)器（Microsoft Windows NT4.0 中的性能監(jiān)測(cè)器）收集事件數(shù)而不是關(guān)于事件的數(shù)據(jù)（例如內(nèi)存使用、活動(dòng)事務(wù)數(shù)、阻塞的鎖數(shù)或 CPU 活動(dòng)）。 當(dāng)前活動(dòng)窗口（SQL Server 企業(yè)管理器） 以圖形顯示有關(guān)當(dāng)前正在 SQL Server 實(shí)例上運(yùn)行的進(jìn)程、被阻塞的進(jìn)程、鎖和用戶(hù)活動(dòng)的信息。 錯(cuò)誤日志 除了在別處可用的信息外，還包含有關(guān) SQL Server 中的事件的額外信息。可以利用錯(cuò)誤日志中的信息解決與 SQL Server 有關(guān)的問(wèn)題。 sp_who 報(bào)告有關(guān)當(dāng)前 SQL Serv

32、er 用戶(hù)和進(jìn)程的快照信息，包括當(dāng)前正在執(zhí)行的語(yǔ)句以及該語(yǔ)句是否被阻塞。 sp_lock 有關(guān)鎖的報(bào)表快照信息，包括對(duì)象 ID、索引 ID、鎖的類(lèi)型和鎖應(yīng)用的類(lèi)型或資源。這是另一種在SQL Server 企業(yè)管理器的當(dāng)前活動(dòng)窗口中查看鎖活動(dòng)的 Transact-SQL 方法。sp_spaceused 顯示對(duì)表（或整個(gè)數(shù)據(jù)庫(kù)）所使用的當(dāng)前硬盤(pán)空間量的估計(jì)。這是另一種在 SQL Server 企業(yè)管理器內(nèi)查看數(shù)據(jù)庫(kù)使用情況的 Transact-SQL 方法。sp_monitor 顯示統(tǒng)計(jì)，包括 CPU 使用、I/O 使用和自上次執(zhí)行 sp_monitor 后的空閑時(shí)間。DBCC 語(yǔ)句 能夠檢查性能

33、統(tǒng)計(jì)和數(shù)據(jù)庫(kù)的邏輯及物理一致性。 內(nèi)置函數(shù) 顯示自服務(wù)器啟動(dòng)后有關(guān) SQL Server 活動(dòng)的快照統(tǒng)計(jì)，這些統(tǒng)計(jì)存儲(chǔ)在預(yù)定義的 SQL Server 計(jì)數(shù)器內(nèi)。 SQL 事件探查器存儲(chǔ)過(guò)程和函數(shù) 使用 Transact-SQL 存儲(chǔ)過(guò)程收集 SQL 事件探查器統(tǒng)計(jì)信息。跟蹤標(biāo)記 顯示有關(guān)服務(wù)器內(nèi)的特定活動(dòng)的信息，用于診斷問(wèn)題或性能問(wèn)題（如死鎖鏈）。簡(jiǎn)單網(wǎng)絡(luò)管理協(xié)議（SNMP） SNMP是一個(gè)提供網(wǎng)絡(luò)管理服務(wù)的應(yīng)用程序協(xié)議。 事件或活動(dòng)SQL 事件探查器系統(tǒng)監(jiān)視器當(dāng)前活動(dòng)窗口Transact-SQL錯(cuò)誤日志趨勢(shì)分析是是重播捕獲的事件是特殊監(jiān)測(cè)是是是是生成警告是圖形界面是是是是在自定義應(yīng)用程序內(nèi)使用是是表表8-18-1要監(jiān)測(cè)