第八章游標(biāo)、事務(wù)和鎖.ppt

1、-SQL Server 2000,第八章 游標(biāo)、事務(wù)和鎖,游標(biāo),游標(biāo)（Cursor）是一個(gè)用來保存“數(shù)據(jù)集” 的容器?？梢詫?duì)其中的數(shù)據(jù)以記錄為單位（利用循環(huán)技術(shù)）一行、一行地處理。 游標(biāo)主要用在服務(wù)器上，由從客戶端發(fā)送給服務(wù)器的Transact_SQL 語(yǔ)句或是批處理存儲(chǔ)過程觸發(fā)器中的Transact_SQL 進(jìn)行管理。 Transact_SQL 游標(biāo)不支持提取數(shù)據(jù)塊或多行數(shù)據(jù)。,對(duì)游標(biāo)的處理過程,DECLARE 游標(biāo) OPEN 游標(biāo) 從一個(gè)游標(biāo)中FETCH 信息 CLOSE 或DEALLOCATE 游標(biāo),聲明游標(biāo),定義游標(biāo)的滾動(dòng)行為和用于生成游標(biāo)對(duì)其進(jìn)行操作的結(jié)果集的查詢。 聲明一個(gè)游標(biāo)主要

2、包括以下主要內(nèi)容： 游標(biāo)名字 數(shù)據(jù)來源（表和列） 選取條件 屬性（僅讀或可修改）,聲明游標(biāo),SQL-92語(yǔ)法格式： DECLARE cursor_name INSENSITIVE SCROLL CURSOR FOR select_statement FOR READ ONLY | UPDATE OF column_name ,.n 其中： cursor_name 指游標(biāo)的名字 INSENSITIVE：表明會(huì)將游標(biāo)定義所選取出來的數(shù)據(jù)記錄存放在一臨時(shí)表內(nèi)(建立在tempdb 數(shù)據(jù)庫(kù)下) SCROLL：表明所有的提取操作，如FIRST、 LAST、 PRIOR、 NEXT、 RELATIVE、 A

3、BSOLUTE都可用。如果不使用該保留字，那么只能進(jìn)行NEXT 提取操作。 select_statement：是定義結(jié)果集的SELECT 語(yǔ)句 READ ONLY：表明不允許游標(biāo)內(nèi)的數(shù)據(jù)被更新，盡管在缺省狀態(tài)下游標(biāo)是允許更新的。 UPDATE OF column_name,n：定義在游標(biāo)中可被修改的列，如果不指出要更新的列，那么所有的列都將被更新。,聲明游標(biāo),當(dāng)游標(biāo)被成功創(chuàng)建后，游標(biāo)名成為該游標(biāo)的惟一標(biāo)識(shí)。如果在以后的存儲(chǔ)過程觸發(fā)器或Transact_SQL 腳本中使用游標(biāo)必須指定該游標(biāo)的名字,聲明游標(biāo),例： declare cur_roysched cursor for select lor

4、ange , hirange , royalty from roysched where title_id=BU1032,擴(kuò)展的游標(biāo)聲明語(yǔ)法,Transact-SQL 擴(kuò)展語(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 ,.n 其

5、中： LOCAL：定義游標(biāo)的作用域僅限在其所在的存儲(chǔ)過程、觸發(fā)器或批處理中。當(dāng)建立游標(biāo)的存儲(chǔ)過程執(zhí)行結(jié)束后，游標(biāo)會(huì)被自動(dòng)釋放。因此我們常在存儲(chǔ)過程中使用OUTPUT 保留字，將游標(biāo)傳遞給該存儲(chǔ)過程的調(diào)用者。這樣在存儲(chǔ)過程執(zhí)行結(jié)束后可以引用該游標(biāo)變量。 GLOBAL：定義游標(biāo)的作用域是整個(gè)會(huì)話層會(huì)話層，指用戶的連接時(shí)間，它包括從用戶登錄到SQLSERVER 到脫離數(shù)據(jù)庫(kù)的整段時(shí)間。選擇GLOBAL 表明，在整個(gè)會(huì)話層的任何存儲(chǔ)過程、觸發(fā)器或批處理中都可以使用該游標(biāo)。只有當(dāng)用戶脫離數(shù)據(jù)庫(kù)時(shí)該游標(biāo)才會(huì)被自動(dòng)釋放。,標(biāo)準(zhǔn)游標(biāo),例： declare cur_authors cursor for sele

6、ct au_id, au_lname, au_fname, phone, address, city, state, contract from authors,讀游標(biāo),例： declare cur_authors cursor for select au_lname, au_fname, phone, address, city, state from authors for read only,更新游標(biāo),例： declare cur_authors cursor for select au_lname, au_fname from authors for update,打開游標(biāo),游標(biāo)在聲明

7、以后，如果要從游標(biāo)中讀取數(shù)據(jù)必須打開游標(biāo)。 打開一個(gè)Transact-SQL服務(wù)器游標(biāo)使用OPEN 命令其語(yǔ)法規(guī)則為： OPEN GLOBAL cursor_name | cursor_variable_name 其中： GLOBAL：定義游標(biāo)為一全局游標(biāo) cursor_name：為聲明的游標(biāo)名字，如果一個(gè)全局游標(biāo)和一個(gè)局部游標(biāo)都使用同一個(gè)游標(biāo)名。則如果使用GLOBAL 便表明其為全局游標(biāo)，否則表明其為局部游標(biāo)。 cursor_variable_name:為游標(biāo)變量 在游標(biāo)被成功打開之后CURSOR_ROWS 全局變量將用來記錄游標(biāo)內(nèi)數(shù)據(jù)行數(shù)。,讀取游標(biāo)中的數(shù)據(jù),當(dāng)游標(biāo)被成功打開以后，就可以從

8、游標(biāo)中逐行地讀取數(shù)據(jù)以進(jìn)行相關(guān)處理。從游標(biāo)中讀取數(shù)據(jù)主要使用FETCH 命令其語(yǔ)法規(guī)則為： FETCH NEXT | PRIOR | FIRST | LAST | ABSOLUTE n | nvar | RELATIVE n | nvar FROM GLOBAL cursor_name | cursor_variable_name INTO variable_name,.n ,讀取游標(biāo)中的數(shù)據(jù),例：為 authors 表中姓以字母 B 開頭的行聲明了一個(gè)簡(jiǎn)單的游標(biāo)，并使用 FETCH NEXT 逐個(gè)提取行信息。 USE pubs GO DECLARE authors_cursor CURSOR

9、 FOR SELECT au_lname FROM authors WHERE au_lname LIKE B% ORDER BY au_lname OPEN authors_cursor FETCH NEXT FROM authors_cursor,關(guān)閉游標(biāo),使用CLOSE 命令關(guān)閉游標(biāo) 在處理完游標(biāo)中數(shù)據(jù)之后必須關(guān)閉游標(biāo)來釋放數(shù)據(jù)結(jié)果集和定位于數(shù)據(jù)記錄上的鎖，CLOSE 語(yǔ)句關(guān)閉游標(biāo)但不釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu) 語(yǔ)法格式： CLOSE GLOBAL cursor_name | cursor_variable_name 其中： GLOBAL：指定 cursor_name 指的是全局游標(biāo)。 cu

10、rsor_name：開放游標(biāo)的名稱。 cursor_variable_name：與開放游標(biāo)關(guān)聯(lián)的游標(biāo)變量的名稱。,關(guān)閉游標(biāo),例：顯示在一個(gè)基于游標(biāo)的進(jìn)程中CLOSE 語(yǔ)句的正確位置 。 USE pubs GO DECLARE authorcursor CURSOR FOR SELECT au_fname, au_lname FROM authors ORDER BY au_fname, au_lname OPEN authorcursor FETCH NEXT FROM authorcursor WHILE FETCH_STATUS = 0 BEGIN FETCH NEXT FROM auth

11、orcursor END CLOSE authorcursor DEALLOCATE authorcursor GO,釋放游標(biāo),在使用游標(biāo)時(shí)，各種針對(duì)游標(biāo)的操作或者引用游標(biāo)名或者引用指向游標(biāo)的游標(biāo)變量，當(dāng)CLOSE 命令關(guān)閉游標(biāo)時(shí)并沒有釋放游標(biāo)占用的數(shù)據(jù)結(jié)構(gòu)。因此常使用DEALLOCATE 命令。通過該命令可以刪除掉游標(biāo)與游標(biāo)名或游標(biāo)變量之間的聯(lián)系并且釋放游標(biāo)占用的所有系統(tǒng)資源。 其語(yǔ)法格式為： DEALLOCATE GLOBAL cursor_name | cursor_variable_name,游標(biāo)變量,首先我們先聲明一個(gè)游標(biāo) declare titleauthor_cur scroll

12、 cursor for select * from titleauthor 使用SET 語(yǔ)句將一游標(biāo)賦值給游標(biāo)變量 declare cur_ta cursor set cur_ta = titleauthor_cur 將聲明游標(biāo)語(yǔ)句放在游標(biāo)賦值語(yǔ)句中 declare cur_ta cursor declare titleauthor_cur scroll cursor for select * from titleauthor set cur_ta = titleauthor_cur,實(shí)例：,Use joindb Declare mycursor1 Scroll cursor For sele

13、ct * from buyers OPEN mycursor1 FETCH FIRST FROM mycursor1 -返回游標(biāo)中的第一行并將其作為當(dāng)前行 FETCH NEXT FROM mycursor1 -返回緊跟當(dāng)前行之后的結(jié)果行，并且當(dāng)前行遞增為結(jié)果行 FETCH RELATIVE 1 FROM mycursor1-返回當(dāng)前行之后的第 1 行并將返回的行變成新的當(dāng)前行 FETCH ABSOLUTE 2 FROM mycursor1 -返回從游標(biāo)頭開始的第 2行并將返回的行變成新的當(dāng)前行 FETCH LAST FROM mycursor1 -返回游標(biāo)中的最后一行并將其作為當(dāng)前行 CLOS

14、E mycursor1 DEALLOCATE mycursor1,使用系統(tǒng)存儲(chǔ)過程確定游標(biāo)的特性,管理事務(wù)和鎖,關(guān)系數(shù)據(jù)庫(kù)的4個(gè)顯著特征： 安全性：通過帳戶策略與許可來保證 完整性：保證數(shù)據(jù)正確性、有效性、相容性。 有實(shí)體完整性，參照完整性，域完整性 監(jiān)測(cè)性：有很多工具 并發(fā)性：因?yàn)槭蔷W(wǎng)絡(luò)數(shù)據(jù)庫(kù)，靠事務(wù)與鎖來實(shí)現(xiàn),事務(wù),事務(wù)：是一個(gè)以完整單位提交的邏輯單元 性質(zhì)：ACID 原子性：（Atomictity）事務(wù)必須全部成功或全都不成功 一致性：數(shù)據(jù)庫(kù)中的數(shù)據(jù)處于一致性的狀態(tài) 隔離性：用lock來隔離事務(wù) 永久性：事務(wù)一旦提交，不能再次回滾,事務(wù),寫法： 開頭：begin tran;begin t

15、ransaction;begin 結(jié)尾： 1）提交：commit tran/transaction; 2）回滾：rollback trans; rollback transaction; rollback,事務(wù),事務(wù)的類型： 外顯事務(wù)（Explicit Trans）： 表明首尾，有些語(yǔ)句不能出現(xiàn)在事務(wù)中 （Alter database;Backup log;restore database） 自動(dòng)提交事務(wù)(Atocommit trans) 未指明外顯事務(wù)時(shí)，默認(rèn)每一條T_SQL語(yǔ)句都是一個(gè)事務(wù) 隱式事務(wù)（Implicit Trans） set implicit_transactions on（

16、啟動(dòng)隱性事務(wù)模式）后，事務(wù)自動(dòng)開始，直到遇到commit/rollback,事務(wù),并發(fā)訪問產(chǎn)生的問題： 丟失更新 讀未提交數(shù)據(jù) 不一致檢索（幻影） 例如：庫(kù)房中機(jī)器400件，甲：60 乙：40 丟失更新：,事務(wù),讀未提交數(shù)據(jù)（臟讀）：,事務(wù),事務(wù)的隔離等級(jí)： （由低到高共4級(jí)）（隔離性與并發(fā)性是消長(zhǎng)的關(guān)系） Read uncommitted：不隔離，讀取到的數(shù)據(jù)隨時(shí)會(huì)被改變 Read committed：我未提交，你不能讀；我讀了以后，你可以改 Repeatable read：事務(wù)中讀到的數(shù)據(jù)不允許別人刪除、修改，但可以插入 Serializable:將用到的數(shù)據(jù)表完全鎖定，別人不能增、刪、改

17、。并發(fā)性最低。 用語(yǔ)句改變?cè)O(shè)置,事務(wù),語(yǔ)法格式： SET TRANSACTION ISOLATION LEVEL READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE ,事務(wù),注意：事務(wù)應(yīng)該盡可能短，同時(shí)避免嵌套 （事務(wù)嵌套除了使語(yǔ)句更復(fù)雜外別無好處）,鎖,總體機(jī)制： 樂觀的并發(fā)性控制：（Optimistic Concurrency）：假設(shè)發(fā)生數(shù)據(jù)沖突的機(jī)會(huì)很小，故不會(huì)持續(xù)鎖定 悲觀性并發(fā)控制：與前相反，事務(wù)中持續(xù)鎖定要使用的數(shù)據(jù) 封鎖的對(duì)象： RID：并發(fā)性最高，行級(jí)鎖是大型數(shù)據(jù)庫(kù)的標(biāo)志 KEY：已經(jīng)設(shè)置為索引的

18、列 Page：主要用它，盡管有一點(diǎn)浪費(fèi) Extend（擴(kuò)展）：一般不用 Table：用于處理大量數(shù)據(jù)時(shí) DB：實(shí)質(zhì)上是DB恢復(fù)時(shí)的一種單用戶模式,鎖,鎖的類型 ： 共享鎖：Shared lock=讀鎖 一般在讀數(shù)據(jù)時(shí)系統(tǒng)自動(dòng)加上該鎖， 別人能讀但不能改，又叫讀鎖 排它鎖：Exlusive lock寫鎖 一般在對(duì)數(shù)據(jù)進(jìn)行insert、update、delete時(shí)系統(tǒng)自動(dòng)加上排它鎖 其他事務(wù)不能存取或鎖定該事務(wù) 修改鎖：Update lock 當(dāng)加上該鎖后又進(jìn)行更改數(shù)據(jù)的操作時(shí)，肯定能自動(dòng)升級(jí)為排它鎖 一旦成功鎖定，等于拿到了升級(jí)的保證書，不會(huì)發(fā)生死鎖，因?yàn)槊看沃挥幸粋€(gè)修改鎖 模式鎖：一個(gè)會(huì)話正在調(diào)用，另一個(gè)會(huì)話不能將其刪除 意圖鎖：表示只想鎖定對(duì)象中某部分的底層資源 意圖讀：（