數(shù)據(jù)庫(kù)第13章

1、1 1第13章 游標(biāo)和其他概念13.1 游標(biāo)的概念游標(biāo)的概念13.2 定義游標(biāo)定義游標(biāo)13.3 游標(biāo)的使用游標(biāo)的使用13.4 事務(wù)事務(wù)13.5 并發(fā)控制并發(fā)控制2 2【技能目標(biāo)】【技能目標(biāo)】學(xué)會(huì)使用游標(biāo)；理解事務(wù)的概念并學(xué)會(huì)基本的事務(wù)操作；理解并發(fā)控制的基本方法。【知識(shí)目標(biāo)】【知識(shí)目標(biāo)】理解游標(biāo)的概念；掌握定義游標(biāo)的方法；掌握游標(biāo)的使用方法；理解事務(wù)的概念和屬性；掌握基本的事務(wù)操作；了解并發(fā)控制的基本方法。3 3執(zhí)行SELECT語(yǔ)句進(jìn)行查詢時(shí)返回一個(gè)結(jié)果集，結(jié)果集只能作為一個(gè)整體進(jìn)行處理，而不能對(duì)其中的部分行做單獨(dú)處理。但是在實(shí)際應(yīng)用開(kāi)發(fā)中，往往需要對(duì)結(jié)果集中的不同行進(jìn)行不同的處理，游標(biāo)正是S

2、QL Server提供的一種機(jī)制，它能夠?qū)Y(jié)果集進(jìn)行逐行處理，其工作方式類似于指針，可以指向結(jié)果集中的任意位置以此對(duì)指定位置的數(shù)據(jù)進(jìn)行處理。13.1 游游標(biāo)標(biāo)的的概概念念4 4使用游標(biāo)要遵循“定義游標(biāo)打開(kāi)游標(biāo)讀取游標(biāo)關(guān)閉游標(biāo)釋放(刪除)游標(biāo)”的順序。5 5定義游標(biāo)使用DECLARE CURSOR語(yǔ)句，有兩種語(yǔ)法格式：一種是SQL-92標(biāo)準(zhǔn)的游標(biāo)定義，另一種是Transact-SQL擴(kuò)展的游標(biāo)定義。13.2 定定 義義 游游 標(biāo)標(biāo)6 61. SQL-92標(biāo)準(zhǔn)的游標(biāo)定義標(biāo)準(zhǔn)的游標(biāo)定義SQL-92標(biāo)準(zhǔn)游標(biāo)定義的語(yǔ)法格式如下：DECLARE cursor_name INSENSITIVE SCROLL

3、 CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,n 參數(shù)說(shuō)明如下： cursor_name：游標(biāo)名。必須遵從標(biāo)識(shí)符命名規(guī)則。7 7 INSENSITIVE：定義一個(gè)游標(biāo)，以創(chuàng)建將由該游標(biāo)使用的數(shù)據(jù)的臨時(shí)副本。對(duì)游標(biāo)的所有請(qǐng)求都從tempdb中的臨時(shí)表中得到應(yīng)答，因此，在對(duì)該游標(biāo)進(jìn)行提取操作時(shí)返回的數(shù)據(jù)中不反映對(duì)基表所做的修改，并且該游標(biāo)不允許修改。 SCROLL：說(shuō)明所定義的游標(biāo)可以前滾、后滾，可使用所有的提取選項(xiàng)(FIRST，LAST，PRIOR，NEXT，RELATIVE，ABSOLUTE)。如果省略S

4、CROLL，則只能使用NEXT提取選項(xiàng)。8 8 select_statement：SELECT語(yǔ)句。由該查詢產(chǎn)生與所定義的游標(biāo)相關(guān)聯(lián)的結(jié)果集。在SELECT語(yǔ)句中不能出現(xiàn)COMPUTE、COMPUTE BY、FOR BROWSE和INTO這樣的關(guān)鍵字。 READ ONLY：說(shuō)明所定義的游標(biāo)是只讀的。UPDATE OF column_name ,n：定義游標(biāo)內(nèi)可更新的列。如果指定OF column_name ,n參數(shù)，則只允許修改所列出的列。如果在UPDATE中未指定列的列表，則可以更新所有列。9 9【例13-1】 在學(xué)生成績(jī)管理系統(tǒng)數(shù)據(jù)庫(kù)SCMS中，定義一個(gè)查詢學(xué)生表student的游標(biāo)。DE

5、CLARE cur_student1 CURSORFORSELECT sno,sname,ssex,borndate FROM student【例13-2】 將查詢系部表department的結(jié)果集定義一個(gè)只讀游標(biāo)。DECLARE cur_department CURSORFORSELECT * FROM departmentFOR READ ONLY10 10【例13-3】 將例13-1定義的游標(biāo)cur_student1定義成可更新的游標(biāo)。DECLARE cur_student1 CURSORFORSELECT sno,sname,ssex,borndate FROM student FOR

6、 UPDATE11 112. Transact-SQL擴(kuò)展的游標(biāo)定義擴(kuò)展的游標(biāo)定義Transact-SQL擴(kuò)展的游標(biāo)定義的語(yǔ)法格式如下：DECLARE cursor_name CURSOR LOCAL | GLOBAL FORWARD_ONLY | SCROLL STATIC | KEYSET | DYNAMIC | FAST_FORWARD READ_ONLY | SCROLL_LOCKS | OPTIMISTIC TYPE_WARNING FOR select_statement FOR UPDATE OF column_name ,n12 12參數(shù)說(shuō)明如下： LOCAL | GLOBAL

7、：說(shuō)明游標(biāo)的作用域。若兩者均未指定，則默認(rèn)值由default to local cursor數(shù)據(jù)庫(kù)選項(xiàng)的設(shè)置控制。 FORWARD_ONLY | SCROLL：說(shuō)明游標(biāo)的移動(dòng)方向。FORWARD_ONLY表示只能從第一行滾動(dòng)到最后一行。SCROLL的含義與SQL-92標(biāo)準(zhǔn)相同。 STATIC | KEYSET | DYNAMIC | FAST_FORWARD：定義游標(biāo)的類型。這個(gè)選項(xiàng)能顯示出原數(shù)據(jù)庫(kù)的更新操作(UPDATE，INSERT，DELETE)能否反映到打開(kāi)的游標(biāo)中。13 13STATIC：靜態(tài)游標(biāo)。其含義與SQL-92標(biāo)準(zhǔn)的INSENSITIVE關(guān)鍵字功能相同。游標(biāo)打開(kāi)后，數(shù)據(jù)庫(kù)用戶

8、對(duì)數(shù)據(jù)庫(kù)的更新操作不會(huì)反映到該類型游標(biāo)中。KEYSET：鍵集游標(biāo)。該類型游標(biāo)由稱為鍵的列或列的組合控制，其特點(diǎn)是行順序固定?？梢酝ㄟ^(guò)該類型游標(biāo)修改數(shù)據(jù)庫(kù)中表的非關(guān)鍵字列的值，但不可插入數(shù)據(jù)。DYNAMIC：動(dòng)態(tài)游標(biāo)。與靜態(tài)游標(biāo)正好相反。FAST_FORWARD：只進(jìn)游標(biāo)。它是只進(jìn)的游標(biāo)，但做過(guò)優(yōu)化。對(duì)數(shù)據(jù)庫(kù)的更新操作可以反映到該類型中。14 14 READ_ONLY | SCROLL_LOCKS | OPTIMISTIC：說(shuō)明游標(biāo)或基表的訪問(wèn)屬性。READ_ONLY：說(shuō)明所聲明的游標(biāo)為只讀的，不能通過(guò)該游標(biāo)更新數(shù)據(jù)。SCROLL_LOCKS：說(shuō)明通過(guò)游標(biāo)完成的定位更新或定位刪除可以成功。如果聲

9、明中已指定了關(guān)鍵字FAST_FORWARD，則不能指定SCROLL_LOCKS。OPTIMISTIC：說(shuō)明在數(shù)據(jù)被讀入游標(biāo)后，如果游標(biāo)中某行數(shù)據(jù)已發(fā)生變化，那么對(duì)游標(biāo)數(shù)據(jù)進(jìn)行更新或刪除可能會(huì)導(dǎo)致失敗。如果聲明中已指定了關(guān)鍵字FAST_FORWARD，則不能指定OPTIMISTIC。15 15 TYPE_WARNING：指定如果游標(biāo)從所請(qǐng)求的類型隱性轉(zhuǎn)換為另一種類型，則給客戶端發(fā)送警告消息。其他參數(shù)的含義與SQL-92標(biāo)準(zhǔn)中的一樣。【例13-4】 定義局部游標(biāo)，游標(biāo)的移動(dòng)方向可以前滾和后滾。DECLARE cur_student2 CURSORLOCAL SCROLL SCROLL_LOCKSF

10、ORSELECT sno,sname FROM student WHERE YEAR(borndate)=198916 1613.3.1 打開(kāi)游標(biāo)打開(kāi)游標(biāo)游標(biāo)定義好之后，要從游標(biāo)中讀取數(shù)據(jù)，必須先打開(kāi)游標(biāo)。在SQL Server中，使用OPEN語(yǔ)句打開(kāi)游標(biāo)，然后通過(guò)執(zhí)行在DECLARE CURSOR語(yǔ)句中指定的Transact-SQL語(yǔ)句來(lái)填充游標(biāo)。13.3 游游標(biāo)標(biāo)的的使使用用17 17打開(kāi)游標(biāo)的語(yǔ)法格式如下：OPEN GLOBAL cursor_name | cursor_variable_name參數(shù)說(shuō)明如下： GLOBAL：說(shuō)明打開(kāi)的是全局游標(biāo)，省略時(shí)打開(kāi)局部游標(biāo)。 cursor_na

11、me：打開(kāi)的游標(biāo)名。若一個(gè)全局游標(biāo)和一個(gè)局部游標(biāo)都使用同一個(gè)游標(biāo)名，則如果使用GLOBAL，便表明其為全局游標(biāo)，否則表明其為局部游標(biāo)。 cursor_variable_name：游標(biāo)變量名，該變量名引用了一個(gè)游標(biāo)。18 18打開(kāi)游標(biāo)后，可以使用全局變量ERROR來(lái)判斷游標(biāo)打開(kāi)是否成功。打開(kāi)成功返回0值，否則返回非0值。游標(biāo)被成功打開(kāi)后，可以使用全局變量CURSOR_ROWS來(lái)獲取游標(biāo)中的記錄行數(shù)。CURSOR_ROWS返回值及含義如表13-1所示。19 19表13-1 CURSOR_ROWS返回值及含義2020【例13-5】 打開(kāi)例13-4中定義的游標(biāo)cur_student2，查看該游標(biāo)的數(shù)據(jù)

12、行數(shù)。DECLARE cur_student2 CURSORLOCAL SCROLL SCROLL_LOCKSFORSELECT sno,sname FROM student WHERE YEAR(borndate)=1989OPEN cur_student221 21SELECT ERROR AS 游標(biāo)打開(kāi)是否成功SELECT CURSOR_ROWS AS 游標(biāo)cur_student2的數(shù)據(jù)行數(shù)執(zhí)行結(jié)果如圖13-1所示。2222圖13-1 打開(kāi)游標(biāo)232313.3.2 讀取游標(biāo)讀取游標(biāo)當(dāng)游標(biāo)被成功打開(kāi)后，就可以從游標(biāo)中逐行讀取數(shù)據(jù)，以進(jìn)行相關(guān)處理。從游標(biāo)中讀取數(shù)據(jù)主要使用FETCH語(yǔ)句。讀取

13、游標(biāo)的語(yǔ)法格式如下：FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|nvar|RELATIVE n|nvarFROMGLOBAL cursor_name | cursor_variable_nameINTO variable_name ,.n2424參數(shù)說(shuō)明如下： NEXT：返回結(jié)果集中當(dāng)前行的下一行，并且當(dāng)前行遞增為結(jié)果行。如果FETCH NEXT是第一次讀取游標(biāo)中的數(shù)據(jù)，則返回結(jié)果集中的第一行。NEXT是默認(rèn)的游標(biāo)提取選項(xiàng)。 PRIOR：返回結(jié)果集中當(dāng)前行的前一行，并且當(dāng)前行遞減為結(jié)果行。如果FETCH PRIOR是第一次讀取游標(biāo)中的數(shù)據(jù)，則無(wú)數(shù)據(jù)記錄返回，并

14、把游標(biāo)置于第一行之前。 FIRST：返回游標(biāo)中的第一行，并將其作為當(dāng)前行。 LAST：返回游標(biāo)中的最后一行，并將其作為當(dāng)前行。2525 ABSOLUTE n | nvar：如果n或nvar為正數(shù)，則表示從游標(biāo)中返回的數(shù)據(jù)行數(shù)；如果n或nvar為負(fù)數(shù)，則返回游標(biāo)內(nèi)從最后一行數(shù)據(jù)算起的第n或nvar行數(shù)據(jù)；如果n或nvar為0，則沒(méi)有行返回。n必須為整型常量，且nvar必須為smallint、tinyint或int。 RELATIVE n | nvar：與ABSOLUTE有一點(diǎn)區(qū)別，即其計(jì)算n是以當(dāng)前行為基點(diǎn)的。 INTO variable_name ，n：將使用FETCH語(yǔ)句讀取的數(shù)據(jù)存放在多個(gè)

15、變量中，在變量行中的每個(gè)變量必須與游標(biāo)結(jié)果集中相應(yīng)的列相對(duì)應(yīng)，每一變量的數(shù)據(jù)類型也要與游標(biāo)中數(shù)據(jù)列的數(shù)據(jù)類型相匹配。2626使用FETCH語(yǔ)句讀取游標(biāo)時(shí)，F(xiàn)ETCH_STATUS全局變量返回最后一次執(zhí)行FETCH語(yǔ)句的狀態(tài)。每次用FETCH從游標(biāo)中讀取數(shù)據(jù)時(shí)，都應(yīng)檢查該變量，以確定上次FETCH操作是否成功，來(lái)決定下一步處理。FETCH_STATUS返回值及含義如表13-2所示。2727表13-2 FETCH_STATUS返回值及含義2828在使用FETCH語(yǔ)句從游標(biāo)中讀取數(shù)據(jù)時(shí)應(yīng)注意：當(dāng)使用SQL-92語(yǔ)法來(lái)定義一個(gè)游標(biāo)時(shí)，如果沒(méi)有選擇SCROLL選項(xiàng)，則只能使用FETCH NEXT語(yǔ)句從游

16、標(biāo)中讀取數(shù)據(jù)，即只能從結(jié)果集第一行按順序每次讀取一行，由于不能使用FIRST、LAST和PRIOR，所以無(wú)法回滾讀取以前的數(shù)據(jù)；如果選擇了SCROLL選項(xiàng)，則可能使用所有的FETCH操作。2929當(dāng)使用Transact-SQL擴(kuò)展語(yǔ)法定義游標(biāo)時(shí)，有以下約定：(1) 如果定義了FORWARD-ONLY或FAST_FORWARD選項(xiàng)，則只能使用FETCH NEXT語(yǔ)句讀取游標(biāo)。(2) 如果沒(méi)有定義DYNAMIC、FORWARD_ONLY或FAST_FORWARD選項(xiàng)，而定義了KEYSET、STATIC或SCROLL中的任何一個(gè)，則可使用所有的FETCH操作。(3) DYNAMIC SCROLL游標(biāo)

17、支持所有的FETCH選項(xiàng)，但禁用ABSOLUTE選項(xiàng)。3030【例13-6】 在SCMS數(shù)據(jù)庫(kù)中，查詢學(xué)生表student，定義、讀取游標(biāo)。DECLARE cur_student1 CURSORLOCAL SCROLL SCROLL_LOCKSFORSELECT sno,sname,ssex,classno FROM studentOPEN cur_student1SELECT CURSOR_ROWS AS 游標(biāo)cur_student1的數(shù)據(jù)行數(shù)FETCH NEXT FROM cur_student1WHILE FETCH_STATUS=031 31BEGINFETCH NEXT FROM c

18、ur_student1END執(zhí)行結(jié)果如圖13-2所示。3232圖13-2 讀取游標(biāo)3333由此例可知，要想讀出全部記錄，需要用到WHILE循環(huán)，循環(huán)控制條件是FETCH_STATUS=0。343413.3.3 關(guān)閉與釋放游標(biāo)1. 關(guān)閉游標(biāo)游標(biāo)使用完后，要及時(shí)關(guān)閉，以釋放當(dāng)前結(jié)果集并解除定位于游標(biāo)記錄上的游標(biāo)鎖定。關(guān)閉游標(biāo)的語(yǔ)法格式如下：CLOSE GLOBAL cursor_name | cursor_variable_name 參數(shù)說(shuō)明如下： GLOBAL：用來(lái)說(shuō)明關(guān)閉全局游標(biāo)。 cursor_name：要關(guān)閉的游標(biāo)名。 cursor_variable_name：要關(guān)閉的游標(biāo)變量名。3535

19、2. 釋放游標(biāo)釋放游標(biāo)關(guān)閉游標(biāo)后，其數(shù)據(jù)結(jié)構(gòu)仍存儲(chǔ)在系統(tǒng)中，需要時(shí)仍然可以再次使用OPEN語(yǔ)句打開(kāi)和使用該游標(biāo)。如果確定以后不再使用該游標(biāo)，則可以刪除游標(biāo)，將游標(biāo)占用的系統(tǒng)空間釋放出來(lái)。釋放游標(biāo)的語(yǔ)法格式如下：DEALLOCATE GLOBAL cursor_name | cursor_variable_name363613.3.4 使用游標(biāo)使用游標(biāo)【例13-7】 定義一個(gè)游標(biāo)，用來(lái)查詢課程表course的信息，提取游標(biāo)第一行數(shù)據(jù)，提取完之后關(guān)閉和釋放游標(biāo)。DECLARE cursor_course CURSOR /*聲明游標(biāo)*/FOR SELECT * FROM course OPEN cu

20、rsor_course /*打開(kāi)游標(biāo)*/ FETCH NEXT FROM cursor_course /*提取第一行數(shù)據(jù)*/ CLOSE cursor_course /*關(guān)閉游標(biāo)*/ DEALLOCATE cursor_course /*釋放游標(biāo)*/執(zhí)行結(jié)果如圖13-3所示。3737圖13-3 提取游標(biāo)第一行數(shù)據(jù)3838【例13-8】 使用游標(biāo)打印一個(gè)簡(jiǎn)單的課程信息表格。SET NOCOUNT ON PRINT *課程信息表* PRINT PRINT -PRINT 課程號(hào) 課程名 開(kāi)課學(xué)期 學(xué)分 PRINT -DECLARE cno char(5), cname varchar(50),ter

21、m char(1),credit varchar(40) /*聲明變量*/ 3939DECLARE course_cursor CURSOR /*定義游標(biāo)*/ FOR SELECT cno,cname,term,credit FROM courseOPEN course_cursor /*打開(kāi)游標(biāo)*/ FETCH NEXT FROM course_cursor /*提取第一行數(shù)據(jù)*/INTO cno, cname, term,credit WHILE FETCH_STATUS = 0 BEGIN 4040 /*打印數(shù)據(jù)*/PRINT +cno+ +cname+ +term+ +creditPR

22、INT -FETCH NEXT FROM course_cursor /*提取下一行數(shù)據(jù)*/INTO cno, cname, term,credit END CLOSE course_cursor /*關(guān)閉和釋放游標(biāo)*/DEALLOCATE course_cursor執(zhí)行結(jié)果如圖13-4所示。41 41圖13-4 輸出課程信息表格424213.4.1 事務(wù)的概念事務(wù)的概念事務(wù)是SQL Server中的單個(gè)邏輯單元，一個(gè)事務(wù)內(nèi)的所有SQL語(yǔ)句作為一個(gè)整體執(zhí)行，要么全部執(zhí)行，要么都不執(zhí)行。通過(guò)事務(wù)，SQL Server能夠?qū)⑦壿嬒嚓P(guān)的一組操作綁定在一起，以便服務(wù)器保持?jǐn)?shù)據(jù)的完整性。事務(wù)是一個(gè)不可分

23、割的工作邏輯單元。在數(shù)據(jù)庫(kù)系統(tǒng)中執(zhí)行并發(fā)操作時(shí)，事務(wù)是作為最小的邏輯單元來(lái)使用的。13.4 事事 務(wù)務(wù)4343一個(gè)邏輯工作單元必須有四個(gè)屬性，稱為ACID(原子性、一致性、隔離性和持久性)屬性，只有這樣才能成為一個(gè)事務(wù)。(1) 原子性(Atomicity)：事務(wù)必須是原子工作單元。對(duì)于其數(shù)據(jù)修改，要么全都執(zhí)行，要么全都不執(zhí)行。(2) 一致性(Consistency)：事務(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)(如B樹(shù)索引或雙向鏈表)都必須是正確的。4444(3) 隔離性(Isolation

24、)：由并發(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ù)。這稱為可串行性，因?yàn)樗軌蛑匦卵b載起始數(shù)據(jù)，并且重播一系列事務(wù)，以使數(shù)據(jù)結(jié)束時(shí)的狀態(tài)與原始事務(wù)執(zhí)行的狀態(tài)相同。4545(4) 持久性(Durability)：事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的，該修改即使出現(xiàn)系統(tǒng)故障也將一直保持。通常在程序中用BEGIN TRANSACTION語(yǔ)句來(lái)標(biāo)識(shí)一個(gè)事務(wù)的開(kāi)始，用COMMIT TRANSACTION語(yǔ)句標(biāo)識(shí)事務(wù)的結(jié)束。只有執(zhí)行到COMMIT TRANSACTIO

25、N語(yǔ)句時(shí)，事務(wù)中對(duì)數(shù)據(jù)的更新操作才算確認(rèn)。464613.4.2 事務(wù)的分類事務(wù)的分類 按事務(wù)的啟動(dòng)和執(zhí)行模式，可以將事務(wù)分為以下四類。(1) 顯式事務(wù)：也稱為用戶定義或用戶指定的事務(wù)，即由用戶通過(guò)Transact-SQL事務(wù)語(yǔ)句定義，顯式地定義啟動(dòng)和結(jié)束的事務(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ù)中。4747 COMMIT WORK語(yǔ)句：標(biāo)記一個(gè)事務(wù)的結(jié)束。 ROLLBACK TRA

26、NSACTION語(yǔ)句：回滾顯式事務(wù)或隱式事務(wù)到事務(wù)的起始位置，或事務(wù)內(nèi)部的存儲(chǔ)點(diǎn)。 ROLLBACK WORK語(yǔ)句：回滾顯式事務(wù)到事務(wù)的起始位置。(2) 隱式事務(wù)：指在當(dāng)前事務(wù)提交或回滾后，SQL Server自動(dòng)開(kāi)始的事務(wù)。隱式事務(wù)不需要用BEGIN TRANSACTION語(yǔ)句來(lái)標(biāo)識(shí)一個(gè)事務(wù)的開(kāi)始，而只需用戶用COMMIT TRANSACTION語(yǔ)句、COMMIT WORK語(yǔ)句、ROLLBACK TRANSACTION語(yǔ)句、ROLLBACK WORK語(yǔ)句等提交或回滾事務(wù)，提交或回滾之后，SQL Server又自動(dòng)開(kāi)始一個(gè)新的事務(wù)。4848(3) 自動(dòng)事務(wù)：一種能夠自動(dòng)執(zhí)行并自動(dòng)回滾的事務(wù)。在

27、自動(dòng)事務(wù)模式下，當(dāng)一個(gè)語(yǔ)句成功執(zhí)行后，它被自動(dòng)提交，而當(dāng)它在執(zhí)行過(guò)程中產(chǎn)生錯(cuò)誤則被自動(dòng)回滾。自動(dòng)事務(wù)模式是SQL Server默認(rèn)的事務(wù)管理模式，當(dāng)與SQL Server建立連接后，直接進(jìn)入自動(dòng)事務(wù)模式，直到使用BEGIN TRANSACTION語(yǔ)句開(kāi)始一個(gè)顯式事務(wù)，或者執(zhí)行SET IMPLICIT_TRANSACTIONS ON語(yǔ)句進(jìn)入隱式事務(wù)模式。但當(dāng)顯式事務(wù)被提交或回滾，或者執(zhí)行SET IMPLICIT_TRANSACTIONS OFF語(yǔ)句后，SQL Server又進(jìn)入自動(dòng)事務(wù)管理模式。4949(4) 分布式事務(wù)：當(dāng)數(shù)據(jù)庫(kù)系統(tǒng)分布在不同的服務(wù)器上時(shí)，要保證所有服務(wù)器數(shù)據(jù)的一致性和完整性，

28、就要用到分布式事務(wù)。分布式事務(wù)跨越兩個(gè)或多個(gè)稱為資源管理器的服務(wù)器。必須在資源管理器之間協(xié)調(diào)事務(wù)管理。實(shí)際上跨越兩個(gè)或多個(gè)數(shù)據(jù)庫(kù)的單個(gè)SQL Server中的事務(wù)就是分布式事務(wù)。但是，SQL Server對(duì)分布式事務(wù)進(jìn)行內(nèi)部管理，對(duì)于用戶而言，其操作就像本地事務(wù)一樣。下面詳細(xì)介紹常用的顯式事務(wù)和隱式事務(wù)語(yǔ)句。50501. 顯式事務(wù)顯式事務(wù)顯式事務(wù)需要顯式定義事務(wù)的啟動(dòng)和結(jié)束。SQL Server通過(guò)BEGIN TRANSACTION、COMMIT TRANSACTION、COMMIT WORK、ROLLBACK TRANSACTION或ROLLBACK WORK等語(yǔ)句來(lái)完成。1) 啟動(dòng)事務(wù)使用

29、BEGIN TRANSACTION語(yǔ)句啟動(dòng)事務(wù)，將TRANCOUNT加1。其語(yǔ)法格式如下：BEGIN TRANSACTION transaction_name | tran_name_variable WITH MARK description51 51參數(shù)說(shuō)明如下： transaction_name：給事務(wù)分配的名稱。必須遵循標(biāo)識(shí)符命名規(guī)則，但是不允許標(biāo)識(shí)符多于32個(gè)字符。僅在嵌套的BEGINCOMMIT或BEGINROLLBACK語(yǔ)句的最外語(yǔ)句對(duì)上使用事務(wù)名。 tran_name_variable：用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用char、varchar、nchar或nva

30、rchar數(shù)據(jù)類型聲明該變量。 WITH MARK description：指定在日志中標(biāo)記事務(wù)。description是描述該標(biāo)記的字符串。52522) 結(jié)束事務(wù)如果沒(méi)有遇到錯(cuò)誤，可使用COMMIT TRANSACTION語(yǔ)句成功地結(jié)束事務(wù)。該事務(wù)中的所有數(shù)據(jù)修改在數(shù)據(jù)庫(kù)中都將永久有效。事務(wù)占用的資源將被釋放。其語(yǔ)法格式如下：COMMIT TRANSACTION transaction_name | tran_name_variable參數(shù)說(shuō)明如下：5353 transaction_name：SQL Server忽略該參數(shù)。transaction_name指定由前面的BEGIN TRANSA

31、CTION指派的事務(wù)名稱。 tran_name_variable：是用戶定義的、含有有效事務(wù)名稱的變量的名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。也可以使用COMMIT WORK來(lái)結(jié)束事務(wù)，該語(yǔ)句沒(méi)有參數(shù)。54543) 回滾事務(wù) 如果事務(wù)中出現(xiàn)錯(cuò)誤或者用戶決定取消事務(wù)，可回滾該事務(wù)?；貪L事務(wù)通過(guò)ROLLBACK語(yǔ)句來(lái)完成。其語(yǔ)法格式如下：ROLLBACK TRANSACTION transaction_name | tran_name_variable | savepoint_name | savepoint_variable參數(shù)說(shuō)明如下： tran

32、saction_name：在BEGIN TRANSACTION上的事務(wù)指派的名稱。嵌套事務(wù)時(shí)，transaction_name必須是來(lái)自最遠(yuǎn)的BEGIN TRANSACTION語(yǔ)句的名稱。5555 tran_name_variable：用戶定義的、含有有效事務(wù)名稱的變量的名稱。 savepoint_name：是來(lái)自SAVE TRANSACTION語(yǔ)句的savepoint_name。savepoint_name必須符合標(biāo)識(shí)符規(guī)則。 savepoint_variable：是用戶定義的、含有有效保存點(diǎn)名稱的變量的名稱。必須用char、varchar、nchar或nvarchar數(shù)據(jù)類型聲明該變量。R

33、OLLBACK TRANSACTION清除自事務(wù)起點(diǎn)或到某個(gè)保存點(diǎn)所做的所有數(shù)據(jù)修改，ROLLBACK還釋放由事務(wù)控制的資源?；貪L事務(wù)也可以使用ROLLBACK WORK語(yǔ)句。56562. 隱式事務(wù)隱式事務(wù) 在隱式事務(wù)模式下，當(dāng)SQL Server首次執(zhí)行某些Transact-SQL語(yǔ)句時(shí)，會(huì)自動(dòng)啟動(dòng)一個(gè)事務(wù)，而不需要使用BEGIN TRANSACTION語(yǔ)句。這些語(yǔ)句包括CREATE、DROP、INSERT、SELECT、UPDATE、DELETE、GRANT、REVOKE等。設(shè)置隱式事務(wù)模式的語(yǔ)法格式如下：SET IMPLICIT_TRANSACTIONS ON | OFF 當(dāng)設(shè)置為ON時(shí)

34、，設(shè)置為隱式事務(wù)模式；當(dāng)設(shè)置為OFF時(shí)，則返回到自動(dòng)事務(wù)模式。5757【例13-9】 簡(jiǎn)單事務(wù)的啟動(dòng)和關(guān)閉演示。USE SCMSGO SET NOCOUNT ON CREATE table t1 (a int)GO INSERT INTO t1 VALUES (1) GO PRINT 使用顯示事務(wù) BEGIN TRAN INSERT INTO t1 VALUES (2) 5858PRINT 事務(wù)內(nèi)的事務(wù)數(shù)目: + CAST(TRANCOUNT AS char(5) COMMIT TRAN PRINT 事務(wù)外的事務(wù)數(shù)目: + CAST(TRANCOUNT AS char(5) GO PRINT

35、設(shè)置IMPLICIT_TRANSACTIONS為ON GO SET IMPLICIT_TRANSACTIONS ON GO PRINT 使用隱式事務(wù) 5959GO INSERT INTO t1 VALUES (4) PRINT 事務(wù)內(nèi)的事務(wù)數(shù)目: + CAST(TRANCOUNT AS char(5) COMMIT TRAN PRINT 事務(wù)外的事務(wù)數(shù)目: + CAST(TRANCOUNT AS char(5) GO 執(zhí)行結(jié)果如圖13-5所示。6060圖13-5 簡(jiǎn)單事務(wù)的啟動(dòng)和關(guān)閉演示61 61以上語(yǔ)句中使用TRANCOUNT全局變量顯示打開(kāi)的事務(wù)數(shù)。TRANCOUNT全局變量返回當(dāng)前連接的

36、活動(dòng)事務(wù)數(shù)。6262SQL Server使用鎖定確保事務(wù)完整性和數(shù)據(jù)庫(kù)一致性。鎖定可以防止用戶讀取正在由其他用戶更改的數(shù)據(jù)，并可以防止多個(gè)用戶同時(shí)更改相同數(shù)據(jù)。如果不使用鎖定，則數(shù)據(jù)庫(kù)中的數(shù)據(jù)可能在邏輯上不正確，并且對(duì)數(shù)據(jù)的查詢會(huì)產(chǎn)生意想不到的結(jié)果。13.5 并并 發(fā)發(fā) 控控 制制636313.5.1 并發(fā)問(wèn)題并發(fā)問(wèn)題如果沒(méi)有鎖定且多個(gè)用戶同時(shí)訪問(wèn)一個(gè)數(shù)據(jù)庫(kù)，則當(dāng)它們的事務(wù)同時(shí)使用相同的數(shù)據(jù)時(shí)可能會(huì)發(fā)生數(shù)據(jù)不一致問(wèn)題，這就是并發(fā)問(wèn)題。它包括以下四種情況： 丟失更新(Lost Update)； 未確認(rèn)的相關(guān)性(臟讀，Dirty Read)； 不一致的分析(非重復(fù)讀，Non-Repeatable

37、Read)； 幻象讀。64641. 丟失更新丟失更新當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，會(huì)發(fā)生丟失更新問(wèn)題。每個(gè)事務(wù)都不知道其他事務(wù)的存在。最后的更新將重寫由其他事務(wù)所做的更新，這將導(dǎo)致數(shù)據(jù)丟失。例如，兩個(gè)用戶A和B，分別在T1、T2、T3和T4時(shí)間對(duì)表中的數(shù)據(jù)進(jìn)行操作，如表13-3所示。6565表13-3 丟 失 更 新用戶A和B都讀取x(x=50)，然后分別把x減去20和40，用戶A在T3時(shí)刻將修改后的x(x=30)寫回?cái)?shù)據(jù)庫(kù)。隨后，用戶B在T4時(shí)刻將修改后的x(x=10)寫回?cái)?shù)據(jù)庫(kù)。于是，對(duì)于用戶A而言，其修改就在T4時(shí)刻丟失了。66662. 未確認(rèn)的相關(guān)性未確認(rèn)

38、的相關(guān)性當(dāng)?shù)诙€(gè)事務(wù)選擇其他事務(wù)正在更新的行時(shí)，會(huì)發(fā)生未確認(rèn)的相關(guān)性問(wèn)題。第二個(gè)事務(wù)正在讀取的數(shù)據(jù)還沒(méi)有確認(rèn)并且可能由更新此行的事務(wù)所更改。例如，用戶A在T1時(shí)刻讀取x(x=50)，然后將x加上40，用戶B在T3時(shí)刻由數(shù)據(jù)緩存讀取x(x=90)，但是用戶A在T4時(shí)刻回滾事務(wù)，撤銷了對(duì)x的修改，數(shù)據(jù)庫(kù)中仍然維持x=50，而用戶B已經(jīng)把改變的數(shù)據(jù)(x=90)讀走。整個(gè)過(guò)程如表13-4所示。6767表13-4 臟 讀 數(shù) 據(jù)68683. 不一致的分析不一致的分析當(dāng)?shù)诙€(gè)事務(wù)多次訪問(wèn)同一行而且每次讀取不同的數(shù)據(jù)時(shí)，會(huì)發(fā)生不一致的分析問(wèn)題。不一致的分析與未確認(rèn)的相關(guān)性類似，因?yàn)槠渌聞?wù)也是正在更改第二個(gè)

39、事務(wù)正在讀取的數(shù)據(jù)。然而，在不一致的分析中，第二個(gè)事務(wù)讀取的數(shù)據(jù)是由已進(jìn)行了更改的事務(wù)提交的。不一致的分析涉及多次(兩次或更多)讀取同一行，而且每次信息都由其他事務(wù)更改，所以該行被非重復(fù)讀取。例如，用戶A和用戶B分別讀取x(x=50)，在T3時(shí)刻用戶讀取y并計(jì)算x+y=80。在T4時(shí)刻用戶B把x加上30，并在T5時(shí)刻將其寫入數(shù)據(jù)庫(kù)。6969在T6時(shí)刻用戶A讀取z，并仍然使用前面的x和y計(jì)算x+y+z=95。如果用戶A為進(jìn)行校核而把x、y和z重新讀取了一次，并進(jìn)行計(jì)算，則出現(xiàn)x+y+z=125。整個(gè)過(guò)程如表13-5所示。7070表13-5 非 重 復(fù) 讀71 714. 幻象讀幻象讀當(dāng)對(duì)某行執(zhí)行插

40、入或刪除操作，而該行屬于某個(gè)事務(wù)正在讀取的行的范圍時(shí)，會(huì)發(fā)生幻象讀問(wèn)題。事務(wù)第一次讀的行范圍顯示出其中一行已不存在于第二次讀或后續(xù)讀中，因?yàn)樵撔幸驯黄渌聞?wù)刪除。同樣，由于其他事務(wù)的插入操作，事務(wù)的第二次或后續(xù)讀顯示有一行已不存在于原始讀中。SQL Server 2005提供了樂(lè)觀并發(fā)控制和悲觀并發(fā)控制兩種并發(fā)控制方式。72721) 樂(lè)觀并發(fā)控制樂(lè)觀并發(fā)控制假定不太可能(但不是不可能)在多個(gè)用戶間發(fā)生資源沖突，允許不鎖定任何資源而執(zhí)行事務(wù)。只有試圖更改數(shù)據(jù)時(shí)才檢查資源以確定是否發(fā)生沖突。如果發(fā)生沖突，應(yīng)用程序必須讀取數(shù)據(jù)并再次嘗試進(jìn)行更改。73732) 悲觀并發(fā)控制悲觀并發(fā)控制根據(jù)需要在事務(wù)的

41、持續(xù)時(shí)間內(nèi)鎖定資源。除非出現(xiàn)死鎖，否則事務(wù)肯定會(huì)成功完成。樂(lè)觀并發(fā)控制使用游標(biāo)。SQL Server 2005默認(rèn)使用悲觀并發(fā)控制。747413.5.2 鎖定鎖定SQL Server 2005具有多粒度鎖定，允許一個(gè)事務(wù)鎖定不同類型的資源。為了使鎖定的成本減至最少，SQL Server自動(dòng)將資源鎖定在適合任務(wù)的級(jí)別。鎖定在較小的粒度(例如行)可以增加并發(fā)但需要較大的開(kāi)銷，因?yàn)殒i定了許多行，則需要控制更多的鎖。鎖定在較大的粒度(例如表)就并發(fā)而言是相當(dāng)昂貴的，因?yàn)殒i定整個(gè)表限制了其他事務(wù)對(duì)表中任意部分進(jìn)行訪問(wèn)，但要求的開(kāi)銷較低，因?yàn)樾枰S護(hù)的鎖較少。75751. SQL Server 2005鎖定模式分類鎖定模式分類SQL Server 2005提供的鎖定模式有以下幾類。1) 共享鎖共享(S)鎖允許并發(fā)事務(wù)讀取(SELECT)一個(gè)資源。資源上存在共享鎖時(shí)，任何其他事務(wù)都不能修改數(shù)據(jù)。一旦讀取數(shù)據(jù)，便立即釋放資源上的共享鎖，除非將事務(wù)隔離級(jí)別設(shè)置為可重復(fù)讀或更高級(jí)別，或者在事務(wù)生存周期內(nèi)用鎖定提示保留共享鎖。76762) 更新鎖更新(U)鎖可以防止通常形式的死鎖。一般更新模式由一個(gè)事務(wù)組成，此事務(wù)讀取記錄，獲