第11章_游標(biāo)與事務(wù)

1、第第1010章章 游標(biāo)與事務(wù)游標(biāo)與事務(wù)主要內(nèi)容主要內(nèi)容 游標(biāo)的概念游標(biāo)的概念游標(biāo)的基本操作游標(biāo)的基本操作事務(wù)和鎖事務(wù)和鎖2022-4-2729.1游標(biāo)的管理 9.1.1 9.1.1 游標(biāo)的簡(jiǎn)介及用途游標(biāo)的簡(jiǎn)介及用途1.1.游標(biāo)的概念游標(biāo)的概念 游標(biāo)（游標(biāo)（CursorCursor）是一種處理數(shù)據(jù)的方法，為了查看或者）是一種處理數(shù)據(jù)的方法，為了查看或者處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中向前或者向后處理結(jié)果集中的數(shù)據(jù)，游標(biāo)提供了在結(jié)果集中向前或者向后瀏覽數(shù)據(jù)的能力。我們可以把游標(biāo)看成一個(gè)在記錄集中的指瀏覽數(shù)據(jù)的能力。我們可以把游標(biāo)看成一個(gè)在記錄集中的指針，通過這個(gè)指針既可以指向當(dāng)前位置，又可

2、以指向結(jié)果集針，通過這個(gè)指針既可以指向當(dāng)前位置，又可以指向結(jié)果集中的任意位置，并且允許用戶對(duì)指定位置的數(shù)據(jù)進(jìn)行處理，中的任意位置，并且允許用戶對(duì)指定位置的數(shù)據(jù)進(jìn)行處理，可以把結(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序或其他位置?？梢园呀Y(jié)果集中的數(shù)據(jù)放在數(shù)組、應(yīng)用程序或其他位置。 2.2.使用游標(biāo)的優(yōu)點(diǎn)使用游標(biāo)的優(yōu)點(diǎn) 允許程序?qū)τ刹樵冋Z句允許程序?qū)τ刹樵冋Z句SELECTSELECT返回的記錄集中的每一行數(shù)據(jù)返回的記錄集中的每一行數(shù)據(jù)執(zhí)行相同或不同的操作，而不是對(duì)整個(gè)行集合執(zhí)行同一個(gè)操執(zhí)行相同或不同的操作，而不是對(duì)整個(gè)行集合執(zhí)行同一個(gè)操作；作； 提供對(duì)基于游標(biāo)位置的行進(jìn)行刪除和更新的能力；提供對(duì)基于游標(biāo)位置

3、的行進(jìn)行刪除和更新的能力； 游標(biāo)實(shí)際上作為面向集合的數(shù)據(jù)庫管理系統(tǒng)和面向行的游標(biāo)實(shí)際上作為面向集合的數(shù)據(jù)庫管理系統(tǒng)和面向行的程序設(shè)計(jì)之間的橋梁，使這兩種處理方式通過游標(biāo)聯(lián)系起來。程序設(shè)計(jì)之間的橋梁，使這兩種處理方式通過游標(biāo)聯(lián)系起來。提供腳本、存儲(chǔ)過程和觸發(fā)器中使用的用于訪問結(jié)果集中數(shù)提供腳本、存儲(chǔ)過程和觸發(fā)器中使用的用于訪問結(jié)果集中數(shù)據(jù)的據(jù)的T-SQLT-SQL語句。語句。 1 1游標(biāo)的聲明：游標(biāo)的聲明：格式：DECLARE cursor_nameinsensitiveSCROLLCURSOR local global static|keyset|dynamic FOR select_stat

4、ement forREAD ONLY|UPDATEOF column_name_list 9.1.2游標(biāo)的基本操作游標(biāo)的基本操作 2 2游標(biāo)的創(chuàng)建過程游標(biāo)的創(chuàng)建過程1)打開Microsoft SQL Server Manager 管理器。單擊常用工具欄中的按鈕，新建一個(gè)數(shù)據(jù)庫查詢文檔，如圖9.1所示。圖 9. 1 新建查詢2)2)在數(shù)據(jù)庫引擎查詢文檔中輸入如下代碼：在數(shù)據(jù)庫引擎查詢文檔中輸入如下代碼：Use xsUse xsDeclare hy1 cursor for Declare hy1 cursor for Select Select * * from from 學(xué)生學(xué)生 where w

5、here 性別性別= =男男只讀游標(biāo)的創(chuàng)建：只讀游標(biāo)的創(chuàng)建：Use xsUse xsDeclare hy2 cursor forDeclare hy2 cursor forSelect Select * * from from 學(xué)生學(xué)生where where 入學(xué)成績(jī)?nèi)雽W(xué)成績(jī)500500For read onlyFor read only更新游標(biāo)的創(chuàng)建：更新游標(biāo)的創(chuàng)建：Use xsUse xsDeclare hy3 cursor for Declare hy3 cursor for Select Select * * from from 學(xué)生學(xué)生 where where 班級(jí)班級(jí)= =計(jì)算機(jī)計(jì)

6、算機(jī)For updateFor update正確輸入后，按鍵盤上的正確輸入后，按鍵盤上的“F5F5”鍵執(zhí)行鍵執(zhí)行SQLSQL語句，顯示如下提語句，顯示如下提示信息：示信息：Command(s) completed successfullyCommand(s) completed successfully。創(chuàng)建游標(biāo)完畢。創(chuàng)建游標(biāo)完畢。3游標(biāo)的打開游標(biāo)的打開格式：OPEN GLOBAL cursor_name|cursor_variable_name說明：Cursor_name:要打開的游標(biāo)名。cursor_variable_name:游標(biāo)變量名，該變量名是引用了的一個(gè)游標(biāo)。GLOBAL: 用來說

7、明打開的是全局游標(biāo)，省略時(shí)打開局部游標(biāo)。4游標(biāo)數(shù)據(jù)的讀取游標(biāo)數(shù)據(jù)的讀取 創(chuàng)建游標(biāo)并打開游標(biāo)以后，利用T-SQL可以讀取游標(biāo)中的數(shù)據(jù)，語法格式如下： Fetch next | prior | first | last absolute n | relative n From 游標(biāo)名 into 變量名5關(guān)閉游標(biāo)：關(guān)閉游標(biāo)：利用游標(biāo)處理完數(shù)據(jù)之后，必須關(guān)閉游標(biāo)，格式如下：Close 游標(biāo)名稱6釋放游標(biāo)釋放游標(biāo)游標(biāo)關(guān)閉以后并沒有釋放游標(biāo)所占用的系統(tǒng)資源，因此關(guān)閉游標(biāo)以后，必須釋放游標(biāo)才能釋放游標(biāo)所占的系統(tǒng)資源，格式如下： Deallocate 游標(biāo)名稱9.2 9.2 事務(wù)事務(wù)9.2.1 9.2.1 事

8、務(wù)及工作原理事務(wù)及工作原理1 1事務(wù)事務(wù) 事務(wù)是用戶定義的一個(gè)數(shù)據(jù)庫操作序列，這些操作要么全做要么全不做，是一個(gè)不可分割的工作單位。2 2事務(wù)特性事務(wù)特性 原子性(Atomicity)、一致性(Consistency)、隔離性(Isolation)和持續(xù)性(Durability)。這四個(gè)特性簡(jiǎn)稱為ACID特性。3 3SQL ServerSQL Server通過記錄事務(wù)日志保持事務(wù)的持久性。通過記錄事務(wù)日志保持事務(wù)的持久性。數(shù)據(jù)庫系統(tǒng)一般采用記錄日志和恢復(fù)機(jī)制來保證事務(wù)的一致性；采用并發(fā)控制機(jī)制來保證多個(gè)事務(wù)的交叉運(yùn)行不影響這些事務(wù)的原子性。4 4事務(wù)的工作原理（事務(wù)的工作原理（COMMIT,R

9、OLLBACK,UNDO,REDOCOMMIT,ROLLBACK,UNDO,REDO）102022-4-279.2.29.2.2執(zhí)行事務(wù)及執(zhí)行模式執(zhí)行事務(wù)及執(zhí)行模式1 1事務(wù)的執(zhí)行事務(wù)的執(zhí)行 應(yīng)用程序主要通過指定事務(wù)啟動(dòng)和結(jié)束的時(shí)間來控制事務(wù)。2 2事務(wù)的類型事務(wù)的類型 根據(jù)運(yùn)行模式，SQL Server 2005將事務(wù)分為4種類型：自動(dòng)提交事務(wù)、顯式事務(wù)、隱式事務(wù)和批處理事務(wù)。1） 顯式事務(wù) 顯式事務(wù)指每個(gè)事務(wù)通過API函數(shù)或通過發(fā)出Transact-SQL BEGIN TRANSACTION 語句來顯式啟動(dòng)事務(wù)，以COMMIT或ROLLBACK語句顯式結(jié)束。2） 自動(dòng)提交事務(wù) 自動(dòng)提交事務(wù)

10、是指每條語句都是一個(gè)事務(wù)。3） 隱式事務(wù) 隱式事務(wù)是指在前一個(gè)事務(wù)完成時(shí)新事務(wù)隱式啟動(dòng)，但每個(gè)事務(wù)仍以COMMIT或ROLLBACK語句顯式完成。4） 批范圍的事務(wù) 只適用于多個(gè)活動(dòng)的結(jié)果集 (MARS)，在 MARS 會(huì)話中啟動(dòng)的 Transact-SQL 顯式或隱式事務(wù)將變成批范圍的事務(wù)。112022-4-279.2.3 9.2.3 編寫事務(wù)編寫事務(wù)控制事務(wù)用到的T-SQL語句:1 1BEGIN TRANSACTION BEGIN TRANSACTION 語句語句2 2COMMI T TRANSACTIONCOMMI T TRANSACTION語句語句3 3ROLLBACK TRANSAC

11、TION ROLLBACK TRANSACTION 或或 ROLLBACK WORK ROLLBACK WORK 語句語句例8-12 定義一個(gè)事務(wù)，將所有外聘教師的考評(píng)成績(jī)定為良好，并提交該事務(wù)。BEGIN TRANSACTION EX_teacher_gradeUPDATE 考評(píng)SET 得分情況=良好WHERE 檔案類型=外聘GO COMMIT TRANSACTION GO122022-4-279.2.4 9.2.4 事務(wù)保存點(diǎn)事務(wù)保存點(diǎn)保存點(diǎn)提供了一種機(jī)制，用于回滾部分事務(wù)。在創(chuàng)建保存點(diǎn)后，可以執(zhí)行 ROLLBACK TRANSACTION savepoint_name 語句以回滾到保存點(diǎn)

12、，而不是回滾到事務(wù)的起點(diǎn)。 語法格式為：SAVE TRAN | TRANSACTION savepoint_name | savepoint_variable ; 132022-4-279.2.59.2.5事務(wù)隔離級(jí)別事務(wù)隔離級(jí)別 隔離級(jí)別定義一個(gè)事務(wù)必須與其他事務(wù)所進(jìn)行的資源或數(shù)據(jù)更改相隔離的程度。 Microsoft SQL Server 2005的隔離級(jí)別由低到高包括： 未提交讀（隔離事務(wù)的最低級(jí)別，只能保證不讀取物理上損壞的數(shù)據(jù)） 已提交讀（數(shù)據(jù)庫引擎的默認(rèn)級(jí)別） 可重復(fù)讀 可序列化（隔離事務(wù)的最高級(jí)別，事務(wù)之間完全隔離） Transact-SQL使用 SET TRANSACTION

13、ISOLATION LEVEL 語句設(shè)置隔離級(jí)別142022-4-279.3 9.3 鎖鎖9.3.1 9.3.1 鎖及其作用鎖及其作用當(dāng)多個(gè)用戶并發(fā)地存取數(shù)據(jù)庫時(shí)會(huì)產(chǎn)生多個(gè)事務(wù)同時(shí)存取同一數(shù)據(jù)的情況，如果對(duì)并發(fā)操作不加控制就可能會(huì)存取和存儲(chǔ)不正確的數(shù)據(jù)，破壞事務(wù)的一致性和數(shù)據(jù)庫的致性。由多用戶并發(fā)事務(wù)執(zhí)行所導(dǎo)致的數(shù)據(jù)不一致可能會(huì)導(dǎo)致下面幾種情況：1丟失修改2不可重復(fù)讀3臟讀 (Dirty Read)鎖是防止其他事務(wù)訪問指定的資源控制、實(shí)現(xiàn)并發(fā)控制的一種主要手段。封鎖是指一個(gè)事務(wù)在對(duì)某個(gè)數(shù)據(jù)對(duì)象操作之前，先向系統(tǒng)提出請(qǐng)求，對(duì)其加鎖，在事務(wù)結(jié)束之后釋放鎖。在事務(wù)釋放它的鎖之前，其他事務(wù)不能更新此數(shù)

14、據(jù)對(duì)象。152022-4-279.3.2 9.3.2 鎖的對(duì)象與模式鎖的對(duì)象與模式1 1鎖的對(duì)象鎖的對(duì)象 SQL Server具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的資源。鎖的對(duì)象包括下表所列的資源：162022-4-27資源資源 說明說明 RID用于鎖定堆中的單個(gè)行的行標(biāo)識(shí)符。KEY索引中用于保護(hù)可序列化事務(wù)中的鍵范圍的行鎖。PAGE數(shù)據(jù)庫中的 8 KB 頁，例如數(shù)據(jù)頁或索引頁。EXTENT一組連續(xù)的八頁，例如數(shù)據(jù)頁或索引頁。HOBT堆或B樹。保護(hù)索引或沒有聚集索引的表中數(shù)據(jù)頁堆的鎖。TABLE包括所有數(shù)據(jù)和索引的整個(gè)表。FILE數(shù)據(jù)庫文件。APPLICATION應(yīng)用程序?qū)Ｓ玫馁Y源。MET

15、ADATA元數(shù)據(jù)鎖。ALLOCATION_UNIT分配單元。DATABASE整個(gè)數(shù)據(jù)庫。9.3.2 9.3.2 鎖的對(duì)象與模式鎖的對(duì)象與模式2 2鎖模式鎖模式1） 共享鎖共享鎖（S 鎖）允許并行事務(wù)讀取同一種資源。2）排他鎖排他鎖（X 鎖）可以防止并發(fā)事務(wù)對(duì)資源進(jìn)行訪問。3） 更新鎖更新鎖（U 鎖）可以防止常見的死鎖。一次只有一個(gè)事務(wù)可以獲得資源的更新鎖（U 鎖）。如果事務(wù)修改資源，則更新鎖（U 鎖）轉(zhuǎn)換為排他鎖（X 鎖）。4） 意向鎖數(shù)據(jù)庫引擎使用意向鎖來保護(hù)共享鎖（S 鎖）或排他鎖（X 鎖）放置在鎖層次結(jié)構(gòu)的底層資源上。意向鎖有兩種用途： 防止其他事務(wù)以會(huì)使較低級(jí)別的鎖無效的方式修改較高級(jí)

16、別資源。提高數(shù)據(jù)庫引擎在較高的粒度級(jí)別檢測(cè)鎖沖突的效率。172022-4-279.3.3 9.3.3 死鎖問題死鎖問題死鎖是有兩個(gè)或以上的事務(wù)處于等待狀態(tài)，每個(gè)事務(wù)都在等待另一個(gè)事務(wù)解除封鎖，它才能繼續(xù)執(zhí)行下去，結(jié)果任何一個(gè)事務(wù)都無法執(zhí)行，這種現(xiàn)象就是死鎖。下面兩種情況下可能會(huì)出現(xiàn)死鎖：1兩個(gè)事務(wù)同時(shí)鎖定了兩個(gè)單獨(dú)的對(duì)象，并且第一個(gè)事務(wù)要求在另外一個(gè)事務(wù)鎖定的對(duì)象上獲得一鎖；2在一個(gè)數(shù)據(jù)庫中有若干個(gè)長(zhǎng)時(shí)間運(yùn)行的事務(wù)執(zhí)行并操作。降低死鎖的原則：1按同一順序訪問對(duì)象2避免事務(wù)中的用戶交互3保持事務(wù)簡(jiǎn)短并在一個(gè)批處理中4使用低隔離級(jí)別182022-4-279.3.4 9.3.4 查看鎖信息查看鎖信息Microsoft SQL Server 2005 提供的獲取有關(guān)數(shù)據(jù)庫引擎實(shí)例中的當(dāng)前鎖活動(dòng)的信息的方法： 使用 SQL Server Profiler，可以