課程名稱：資料庫系統 授課老師：李春雄 博士

Presentation on theme: "課程名稱：資料庫系統 授課老師：李春雄 博士"— Presentation transcript:

1 課程名稱：資料庫系統 授課老師：李春雄 博士
第 十 章 交易管理 課程名稱：資料庫系統 授課老師：李春雄 博士

2 本章學習目標 1.讓讀者瞭解交易處理的流程及四大特性(ACID)。 2.讓讀者瞭解並行控制(Concurrency Control)的技
術及交易的隔離等級。

3 本章內容 10-1 何謂交易處理 10-2 交易的進行模式 10-3 巢狀交易(Nested Transaction)
10-1 何謂交易處理 10-2 交易的進行模式 10-3 巢狀交易(Nested Transaction) 10-4 設定交易儲存點 10-5 交易的隔離等級 10-6 並行控制的必要性

4 10-1 何謂交易處理　 【定義】 交易(Transaction)乃是一連串不可分割的資料庫操作指令的集合。當交易裡的每一個操作指令都成功時，該筆交易才算成功，否則交易就算失敗，必須恢復到交易前的資料狀態。 【交易處理的例子】我們想看看銀行金錢往來的情況。 假設有一位客戶從A銀行轉帳至B銀行，要做的動作為從A銀行的帳戶扣款、B銀行的帳戶加上轉帳的金額，兩個動作必須同時成功，只要有任何一個動作失敗，則此次轉帳失敗。如圖10-1所示。

5 【交易程序圖】　 在圖10-1中，要完成一個交易必須要經過四個步驟，若萬一在進行步驟4之前，銀行的資訊系統主機之電源中斷，或是發現到B銀行的帳戶不存在時，那要怎麼辦呢？這樣的話，在步驟2時提出來的2000元已經不在A銀行的帳戶了，也不在B銀行的帳戶，那2000走去那裡呢？該不會銀行多賺了2000元吧！

6 【交易程序圖】<續>　 為了不讓這樣的情況發生，我們可以使用交易處理來把一些對於資料庫的操作(A銀行扣掉2000元與B銀行存入2000元)視為同一個交易動作。因此，當交易裡的每一個操作指令都成功時，該筆交易才算成功，否則交易就算失敗，必須恢復到交易前的資料狀態。因此，在步驟2被扣掉的2000元，會因為交易失敗而自動被恢復到交易前的資料狀態。

7 我們可以撰寫下列的演算法來了解整個交易的過程：
Begin Transaction --開始交易 Dim A_account, B_account, Temp_account As Integer A_account =10,000 B_account =5,000 Temp_account=2,000 A_account=A_account - (Temp_account) B_account=B_account + (Temp_account) If A_account =8,000 And B_account=7,000 Then COMMIT --確認交易 Else ROLLBACK --用來將所有資料恢復到交易前的狀態 End If End Transaction --結束交易

8 由以上的演算法，我們大略可以得知，在資料庫中的交易要有三個基本的命令：BeginTransaction、Commit和Rollback。BeginTransaction標示交易的開始。發生在BeginTransaction和下一個命令(不是Rollback就是Commit)之間的任何事物會被視為交易的一部份。如圖10-2交易流程圖所示。

9 10-1.1 交易管理的四大特性 【交易目的】主要維持資料之間的以下四個目的 1. 一致性(Consistency)
交易管理的四大特性　 【交易目的】主要維持資料之間的以下四個目的 1. 一致性(Consistency) 2.完整性(Completeness) 3.正確性(Correctness) 4.並行控制(Concurrency) 而交易進行時，如何達到以上的目的，其最主要原因就是交易管理具有四個特性(ACID)。 【交易四大特性】 一、單元性(Atomicity) 二、一致性(Consistency) 三、隔離性(Isolation) 四、持久性(Durability)

10 一、不可分割性(Atomicity) 【定義】
將交易過程的所有對資料庫操作視為同一個單元工作，其中可能包括許多步驟，這些步驟要嘛全部執行成功，否則，整個交易宣告失敗。所以，整個交易是一個不可分割的邏輯單位。

11 【舉例】 假設現在有兩個交易，分別為T1與T2，時間由t1~t6，實際交易過程如下所示：
因此，如果在交易t4時間Read (B)的讀取操作發生錯誤，交易管理需要避免t3時間所Write(A)的資料庫寫入操作，並不會真正寫入資料庫，因為資料庫單元操作沒有全部執行，就都不能執行。 整 個 交 易 視 為 不 可 分 割 的 單 位 時間 交易T1 交易T2 t1 Read (A) | t2 A=A-2,000 t3 Write (A) t4 Read (B) t5 B=B+2,000 t6 Write (B) 2.不會真正寫入資料庫 1.發生錯誤

12 延伸學習： 資料庫的單元工作是由許多步驟所組成，而每一步驟就是每一句SQL命令的執行。其基本的架構如下：
延伸學習：　 資料庫的單元工作是由許多步驟所組成，而每一步驟就是每一句SQL命令的執行。其基本的架構如下： ================================ Begin Transaction 開始交易 SQL命令1 SQL命令2 …… SQL命令N if (產生錯誤) 進行ROLLBACK的動作 Rollback transaction else Commit transaction 交易成功 End Transaction 結束交易 說明：以上的交易操作(SQL命令1，SQL命令2，…，SQL命令N)，只要其中之一個SQL命令 產生錯誤時，將會導到整個交易失敗，並且執行Rollback Transaction。

13 二、一致性(Consistency) 【定義】
指交易過程所異動的資料在交易前與交易後必須一致，資料庫的資料必須仍然滿足完整性限制條件(利用資料表中的Check與Foreign Key)，即維持資料的一致性，如圖10-4所示： 因為，DBMS需要維持資料庫資料的一致性，同樣的，交易管理也必須要維持一致性。 資料庫 交易前 交易後 交易 滿足一致性

14 【舉例】 　 假設「張三」客戶欲從A銀行轉入2000元到B銀行，交易前「張三」在A銀行和B銀行的總和是15,000元，在交易完成後，A銀行和B銀行的總和必須還是15,000元，因此，在交易前後的帳戶總額是相同的。如下表所示：

15 三、隔離性(Isolation)　 【定義】隔離性是指多筆交易在同時交易時，雖然各交易是並行執行，不過各交易之間應該滿足獨立性，也就是說，一個交易不會影響到其它交易的執行結果，或被其它交易所干擾。

16 【舉例】 假設現在有兩個交易，分別為T1與T2，時間由t1~t5，實際交易過程如下所示：假設：A的預設值=10 說明：
【舉例】　 假設現在有兩個交易，分別為T1與T2，時間由t1~t5，實際交易過程如下所示：假設：A的預設值=10 說明： 當交易T1在時間t1時，會讀取A的預設值10，並且在t2時間將10 改為20，而交易T2在時間t4讀取A值，結果交易T1在時間t5時Abort(撤回)，形成交易T2所讀取的資料是不正確的，也必須要被Abort(撤回)。 時間 交易T1 交易T2 t1 Read (A) | t2 A=A+10 t3 Write (A) t4 t5 Abort(撤回) 不正確(Dirty Read)

17 【分析】交易T1的資料更新到一半尚未完成確認(Commit)時，卻被 交易T2來讀取，因此，交易T2只是取得交易T1的暫時性資
時間 交易T1 交易T2 t1 Read (A) | t2 A=A+10 t3 Write (A) t4 t5 Abort(撤回) 【分析】交易T1的資料更新到一半尚未完成確認(Commit)時，卻被 交易T2來讀取，因此，交易T2只是取得交易T1的暫時性資 料，此現象就稱為Dirty Read。 【解決方法】利用鎖定(Lock)資料的方式來隔離交易。

18 四、持久性(Durability) 【定義】
永久性是指當交易完成執行確認交易（Commit）後，資料庫會保存交易後的結果，即使系統掛了，交易的結果也不能遺失。如下圖所示：

19 【兩種機制與ACID分析】 資料庫系統的交易管理是指「並行控制」和「回復技術」兩個機制的合稱，因此，我們可以將兩種機制與ACID分析如下：
(1)「並行控制」機制是要維持「隔離性」和「一致性」保持 (2)「回復技術」機制是維持交易處理的「不可分割性」和「永久性」

20 10-1.2 交易的狀態與進行 一、交易的狀態 如圖10-6交易狀態轉換圖如下所示：
交易的狀態與進行　 一、交易的狀態 　一個交易狀態是由活動狀態(Active)、部分確認(Partially Committed)、確認(Committed)、失敗(Failed)及終止狀態(Terminated)等五個狀態組合而成。 如圖10-6交易狀態轉換圖如下所示：

21 1. 活動狀態(Active State) 【定義】
當「交易開始(Begin Transaction)」執行時即進入「活動狀態(Active State)」，在此狀態中可以對資料庫進行一系列的讀(Read)及寫(Write)動作。

22 【舉例】網路銀行轉帳的例子 假設某一位家長欲轉帳2000元給就讀遠方學校的兒子生活費用，因此，他必須要在ATM進行以下的操作動作：
步驟一：上網連到指定的網路銀行之網站 步驟二：輸入「身份證字號/統一編號/客戶編號」 輸入「使用者名稱」 輸入「簽入密碼」，後再按「登入」 系統會自動檢查是否正確。如果正確時，則再進行以下的步驟。 步驟三：查詢目前的帳戶餘額 步驟四：轉帳的操作動作…… 以上步驟三與步驟四就是所謂的「活動狀態(Active State)」。

23 2. 部分確認狀態 (Partially Committed State)
【定義】 指在對資料庫進行各種單元操作完成之後，也就是交易結束。此時即可進入「部分確認狀態(Partially Committed State)」，在此狀態中「同步控制」動作將會去檢查是否干擾其他正在執行中的交易。

24 【舉例】網路銀行轉帳的例子 步驟五：匯款的操作動作……完成之後，將會出現如下的畫面：
======================================= 請再確認轉帳資訊 轉出帳號：A 轉入帳號：B 轉帳金額 新台幣2,000元 請您再確認以上的轉帳資訊是否確正？ 「確認」「消取」 以上步驟就是所謂的「部分確認狀態(Partially Committed State) 」。

25 3. 確認狀態(Committed State)
【定義】 當「活動狀態」與「部分確認狀態」檢查動作都成功之後，即可進入「確認狀態(Committed State)」，亦即將交易過程真正的寫入資料庫中，表示此筆交易成功。

26 【舉例】網路銀行轉帳的例子 步驟六：在您按「確認」交易動作之後，將會出現如下的畫面：
======================================= 轉帳成功 交易時間： 2010/10/18 17:44:24 跨行序號： 轉出帳號：A 轉入帳號：B 轉帳金額：新台幣2,000元 轉帳手續：費 新台幣12元 交易備註：家長轉帳2000元給就讀遠方學校的兒子======================================= 以上步驟就是所謂的「確認狀態(Committed State) 」。

27 4. 失敗狀態(Failed State) 【定義】
當「活動狀態」或「部分確認狀態」檢查動作其中一項失敗時，此時會被要求進入「失敗狀態」，在此狀態中交易將會寫入「UNDO取消」動作，以回復到交易未執行前的狀態。

28 5. 終止狀態(Terminated State)
【定義】 是指在「交易失敗」或「交易成功」之後，最後都必須執行交易終止，亦即結束交易(End Transaction)。 由圖10-6中，若要結束交易功能的話，有兩種情況： 下達確認(Commit)或撤回(Rollback)指令這兩種情況才會使交易結束。因此，如果在交易處理當中，若執行的操作有成功的話，可以使用確認(Committed)指令。執行確認指令之後，交易功能的處理結果就會真正被反映出來。 如果在交易處理當中，若執行的操作失敗時，或想要重新再來一次的話，可以執行撤回(Rollback)指令。執行撤回指令之後，原來的交易操作會變成無效，資料會回到原本執行處理之前的狀態。

29 二、交易的進行 【定義】 一個完整且成功的交易，必須要經過一連串的交易動作，因此，我們必須要了解每一個交易動作的目的。如下所示：
二、交易的進行　 【定義】 一個完整且成功的交易，必須要經過一連串的交易動作，因此，我們必須要了解每一個交易動作的目的。如下所示： 1.BEGIN TRANSACTION(又可寫成BEGIN TRAN) 2.READ或WRITE 3.同步控制動作檢查 4.COMMIT TRANSACTION (又可寫成COMMIT TRAN、COMMIT或COMMIT WORK) 5.ROLLBACK TRANSACTION (又可寫成ROLLBACK TRAN, ROLLBACK 或ROLLBACK WORK) 6.UNDO 7.REDO

30 1. BEGIN TRANSACTION 它又可寫成BEGIN TRAN 【定義】
表示開始執行交易。如果交易成功，就使用確認交易COMMIT TRAN指令結束。 【格式】 但是，如果交易失敗，回復交易是使用ROLLBACK TRAN指令結束。【格式】 BEGIN TRAN ………… COMMIT TRAN BEGIN TRAN ………… ROLLBACK TRAN

31 2. READ或WRITE 【定義】 表示對資料庫進行讀寫動作。 【舉例】 新增(寫入動作)一筆記錄到「學生資料表」中 BEGIN TRAN
INSERT 學生資料表 VALUES('S001', '張三') ------ COMMIT TRAN

32 3. 同步控制動作檢查　 【定義】 對資料庫的各種操作完成之後，即可進入部分確認狀態，並且準備進入Commit，在此某些同步控制動作將檢查其是否干擾其他正在執行中的交易，同時也會有某些復原協定會去檢查。 註：在 Transaction 中的每一項操作結束後都必須檢查 BEGIN TRAN INSERT 學生資料表 VALUES('S001', '張三') IF ROLLBACK TRAN ELSE COMMIT TRAN

33 4. COMMIT TRANSACTION 它又可寫成COMMIT TRAN、COMMIT或COMMIT WORK 【定義】確認交易（Commit）：如果交易執行過程沒有錯誤，下達COMMIT指令，將交易更改的資料實際寫入資料庫，以便執行下一個交易，如下所示： 在確認交易成功之後，並且保證交易的資料更新一定會反應到資料庫中，因此，其對資料庫所作的改變會被確認，而不會被UNDO掉。 BEGIN TRAN INSERT 學生資料表 VALUES('S001', '張三') IF ROLLBACK TRAN ELSE COMMIT TRAN

34 5. ROLLBACK TRANSACTION 　 它又可寫成ROLLBACK TRAN, ROLLBACK 或ROLLBACK WORK 【定義】回復交易（Rollback）：如果交易執行過程有錯誤，就是下達ROLLBACK指令放棄交易，並將資料庫回復到交易前狀態，如下所示： BEGIN TRAN INSERT 學生資料表 VALUES('S001', '張三') IF ROLLBACK TRAN ELSE COMMIT TRAN

35 6. UNDO　 【定義】 與ROLL BACK動作相似，但是只會被用來回復到未進行單一動作前的狀態，而不是整個交易。

36 7. REDO　 【定義】 這是要重複執行某一交易中的動作，以確定所有已被確認的交易動作已經成功的作用在資料庫中。

37 【範例一】 　 確認對資料庫所做的交易

38 【範例二】 　 回復對資料庫所做的交易

39 【實作】 假設有A,B兩家銀行，其存款客戶資料及存款如下： (一)「匯款前」之後的A銀行與B銀行之客戶存款資料
序號 帳戶 姓名 存款 #1 A001 張三 10000 B001 一心 5000 #2 A002 李四 20000 B002 二聖 15000 #3 A003 王五 30000 B003 三多 25000

40 (二)「匯款後(成功)」A銀行與B銀行之客戶存款資料
現在A銀行的「張三」客戶欲匯款2000元給B銀行的「一心」客戶，如果，交易成功的話，最後A銀行的「張三」必須變成8000，B銀行的「一心」會變成7000，但是，如果交易失敗的話，A銀行的「張三」必須10000，B銀行的「一心」為5000，而不能發生A銀行的「張三」為10000（未扣款），B銀行的「一心」也為7000（已入款）的情況或A銀行的「張三」為8000（已扣款），B銀行的「一心」也為5000（未入款）等情況。

41 【解答】ch10-1-2_SQLQuery1.sql declare @Temp_account money
=2000 use ch10_DB Begin Transaction 開始交易 --指從A銀行的張三，扣除2000元 update dbo.A銀行客戶存款表 set where 帳戶='A001' if OR Rollback transaction 取消交易 --指在B銀行的一心，匯入2000元 update dbo.B銀行客戶存款表 set where 帳戶='B001' else Commit Transaction 確認交易 --顯示A銀行的「張三」帳戶餘額 select * from dbo.A銀行客戶存款表 --顯示B銀行的「一心」帳戶餘額 from dbo.B銀行客戶存款表

42 【顯示結果】 顯示A銀行的「張三」帳戶餘額 (2) 顯示B銀行的「一心」帳戶餘額

43 【隨堂練習1】承上一實作題，假設A銀行的「張三」帳戶餘額小於2000元時，在匯款之前尚未查詢，因此，欲匯出2000元給B銀行的「一心」時，請問如何實作呢？
【解答】ch10-1-2_SQLQuery2.sql use ch10_DB --交易成功 Begin Transaction 開始交易 money =2000 --指從A銀行的張三，扣除元 if(select 存款 from A銀行客戶存款表 where 帳戶='A001') >=2000 Begin update dbo.A銀行客戶存款表 set where 帳戶='A001' --指在B銀行的一心，匯入元 update dbo.B銀行客戶存款表 set where 帳戶='B001' if OR Rollback transaction 取消交易 else Commit Transaction 確認交易 end print’您目前的餘額不足！’ ---Rollback transaction 取消交易 --顯示A銀行的「張三」帳戶餘額 select * from dbo.A銀行客戶存款表 --顯示B銀行的「一心」帳戶餘額 from dbo.B銀行客戶存款表

44 【隨堂練習2】承上一實作題，假設A銀行的「張三」帳戶，欲匯出2000元給B銀行的「一心」時，但是，一心客戶已經改為’B111’。請問會產生什麼結果呢？
【解答】 匯款失敗，所以A銀行的「張三」帳戶餘額與B銀行的「張三」帳戶餘額不變。

45 10-2 交易的進行模式　 一個功能完整及安全的資訊系統，在某一交易執行時，如果發生系統中斷，系統就必須要確保交易進行中資料的一致性及正確性，在SQL Server中提供三種模式來進行交易。 一、自動認可交易(Auto commit Transaction) 二、外顯交易(Explicit Transaction) 三、隱含交易(Implicit Transaction)

46 10-2.1自動認可交易 (Auto commit Transaction)
【定義】 此種交易模式是SQL Server資料庫管理系統預設的模式，它是將個別的T-SQL指令視為一個交易。因此，當T-SQL指令對資料庫的操作「成功」時，就會自動執行Commit認確，否則就會被Rollback復原。 【概念圖】個別交易成功就會被執行

47 【實作】加選三門課程(2筆成功，１筆失敗) 假設在SQL Server中建立三個資料表及資料庫關聯圖，如下： 學生表 課程表 選課表

48 【解答】ch10-2-1_SQLQuery1.sql use ch10_DB --加選三門課程(2筆成功，１筆失敗)
Insert Into dbo.選課表(學號,課號,成績) values('S1001','C003',61) 加選成功第１筆 values('S1001','C004',71) 加選成功第２筆 values('S1001','C010',71) 加選失敗 --查詢加選後的結果 Select * From dbo.選課表 (1 個資料列受到影響) 訊息547，層級16，狀態0，行7 INSERT 陳述式與FOREIGN KEY 條件約束"FK_選課表_課程表" 衝突。衝突發生在資料庫"ch10_DBMS"，資料表"dbo.課程表", column '課號'。 陳述式已經結束。

49 【執行結果】＜新增了二門課程＞ 【說明】 在「自動認可」模式中，每一個交易僅僅由一個T-SQL陳述式組成。因此，不必擔心每一個交易的明確開始和結束。亦即每一個陳述式被SQL Server執行之後即可立即被認可。

50 10-2.2 外顯交易(Explicit Transaction)
【定義】 此種交易模式是透過Begin transaction來開始進行交易，並且以Rollback transaction或Commit transaction指令來結束交易。 當T-SQL指令對資料庫的一連串操作必須要全部「成功」時，才會執行Commit transaction 認確，否則就會全部被執行Rollback transaction 復原。 【概念圖】只有全部成功才會被執行

51 （一）第一種寫法： 使用自訂函數(AddClass) 【解答】ch10-2-2_SQLQuery1.sql /* 外顯交易(使用自訂函數)
*/ use ch10_DB --加選三門課程(2筆成功，１筆失敗) Begin transaction AddClass Insert Into dbo.選課表(學號,課號,成績) values('S1001','C003',61) 加選成功第１筆 values('S1001','C004',71) 加選成功第２筆 values('S1001','C010',71) 加選失敗 if --如果有產生錯誤時，則會全部Rollback Rollback transaction AddClass else Commit transaction AddClass --查詢加選後的結果 Select * From dbo.選課表 (1 個資料列受到影響) 訊息547，層級16，狀態0，行8 INSERT 陳述式與FOREIGN KEY 條件約束"FK_選課表_課程表" 衝突。衝突發生在資料庫"ch10_DBMS"，資料表"dbo.課程表", column '課號'。 陳述式已經結束。

52 【執行結果】＜沒有新增成功；因為如果有產生錯誤時，
則會全部Rollback＞

53 （二）第二種寫法 自動ROLLBACK 【解答】ch10-2-2_SQLQuery2.sql use ch10_DB
（二）第二種寫法　 自動ROLLBACK 【解答】ch10-2-2_SQLQuery2.sql use ch10_DB set xact_abort on --當有發生錯誤時會自動ROLLBACK --加選三門課程(2筆成功，1筆失敗) Begin transaction Insert Into dbo.選課表(學號,課號,成績) values('S1001','C003',61) 加選成功第筆 values('S1001','C004',71) 加選成功第筆 values('S1001','C010',71) 加選失敗 Commit transaction

54 （三）第三種寫法 使用TRY...CATCH 【解答】ch10-2-2_SQLQuery3.sql use ch10_DB
（三）第三種寫法　 使用TRY...CATCH 【解答】ch10-2-2_SQLQuery3.sql use ch10_DB Begin try --加選三門課程(2筆成功，1筆失敗) Begin transaction Insert Into dbo.選課表(學號,課號,成績) values('S1001','C003',61) 加選成功第筆 values('S1001','C004',71) 加選成功第筆 values('S1001','C010',71) 加選失敗 Commit transaction End Try Begin catch if (error_number()<>0) begin --列印'加選輸入錯誤' RAISERROR ('加選輸入錯誤.',16,1); rollback transaction end End Catch

55 10-2.3 隱含交易(Implicit Transaction)
【定義】此種交易模式是透過「set implicit_transactions on」的設定 ，來啟動隱含交易。因此，它不需要在開始交易時下達「Begin transaction」。當T-SQL指令對資料庫的一連串操作必須要全部「成功」時，才會執行Commit transaction 認確，否則就會全部被執行Rollback transaction 復原。 【概念圖】只有全部成功才會被執行

56 【解答】ch10-2-3_SQLQuery1.sql use ch10_DB --加選三門課程(2筆成功，１筆失敗)
set implicit_transactions on Insert Into dbo.選課表(學號,課號,成績) values('S1001','C003',61) 加選成功第筆 values('S1001','C004',71) 加選成功第筆 values('S1001','C010',71) 加選失敗 if --如果有產生錯誤時，則會全部Rollback Rollback transaction else Commit transaction

57 10-3巢狀交易(Nested Transaction)
除了以上三種交易模式之外，我們也可以使用「巢狀交易」。 【定義】 是指在交易處理中再包含另一個交易。 【使用時機】是在「預存程序」或「觸發程序」的交易。 當T-SQL指令對資料庫的一連串操作必須要全部「成功」時，才會執行Commit transaction 認確，否則就會全部被執行Rollback transaction 復原。

58 【概念圖】只有全部成功才會被執行

59 【實作】 【解答】ch10-3_SQLQuery1.sql use ch10_DB --加選三門課程(2筆成功，１筆失敗)
【實作】　 【解答】ch10-3_SQLQuery1.sql use ch10_DB --加選三門課程(2筆成功，１筆失敗) begin transaction OuterTransaction --外層交易 Insert Into dbo.選課表(學號,課號,成績) values('S1001','C003',61) 加選成功第１筆 --內層交易 begin transaction InnerTransaction values('S1001','C004',71) 加選成功第１筆 if --如果有產生錯誤時，則會全部Rollback Rollback transaction InnerTransaction else Commit transaction InnerTransaction values(‘S1001’,‘C010’,71) 加選失敗 Rollback transaction OuterTransaction Commit transaction OuterTransaction

60 10-4設定交易儲存點　 【定義】 是指在較龐大的交易過程中，執行時間較花費時間，因此，如果即將完成交易之前，發生無法預測的錯誤時，系統就必須要再執行Rollback，所以，又要再花費長時間重新執行一次。因此，適時的設定交易儲存點時，就不必回復整個交易。 【概念圖】 Begin transaction 開始交易 Tran_Savepoint 1 T-SQL指令集1 Tran_Savepoint 2 T-SQL指令集2 Tran_Savepoint 3 T-SQL指令集 撤回 If (交易失敗) Rollback Tran Tran_Savepoint 3 Tran_Savepoint 4 T-SQL指令集4 Commit transaction 說明：以上T-SQL指令集1,2,4三個交易會被執行， 而T-SQL指令集3會被撤回。

61 【實作】 【解答】ch10-4_SQLQuery1.sql use ch10_DB --加選四門課程(4筆成功)
【實作】　 【解答】ch10-4_SQLQuery1.sql use ch10_DB --加選四門課程(4筆成功) Begin transaction AddClass Save Tran P1 Insert Into dbo.選課表(學號,課號,成績) values('S1005','C001',60) 加選成功第筆 Save Tran P2 values('S1005','C002',70) 加選成功第筆 Save Tran P3 values('S1005','C003',80) 加選成功第筆 Save Tran P4 values('S1005','C010',90) 加選成功第筆 if --如果有產生錯誤時，則會全部Rollback Rollback Tran P4 --撤回 Save Tran P5 values('S1005','C004',90) 加選成功第筆 Commit transaction AddClass --查詢加選後的結果 Select * From dbo.選課表

62 【執行結果】

63 10-5 交易的隔離等級　 【定義】 隔離性是交易的保證之一，表示交易與交易之間不互相干擾，好像同時間就只有自己的交易存在一樣，隔離性保證的基本方式是在資料庫層面，也就是，對資料庫或相關欄位鎖定，因此，在同一時間內只允許一個交易進行更新或讀取。 隔離交易的基本方式是鎖定資料庫，但是，在實務上，如果鎖定整個資料庫時，將會導致嚴重的效能問題，因此，實務上會根據資料讀寫更新的頻繁性，來設定不同的交易隔離層級（transaction isolation level）。

64 常用交易隔離等級 基本上，在SQL Server中常用的交易隔離等級四有種：
常用交易隔離等級　 基本上，在SQL Server中常用的交易隔離等級四有種： 1.Read Uncommitted(讀取未認可) ：最低級別的隔離性。 2.Read Committed(讀取認可)：SQL Server預設的等級 3. Repeatable Read(可重覆讀取) 4. Seriazable(序列化)：最高級別的隔離性

65 1.Read Uncommitted(讀取未認可)
【定義】 指某個交易可以讀取另一個交易已更新但尚未commit的資料。此種 交易隔離等級是最差的方式，也就是完全沒有隔離效果，因此，可能 會讀取某一交易正在進行中，並且尚未被Committed的中間結果。 因此，此種讀取方式又稱為「Dirty Read」。 【解決方法】利用Read Committed 【語法】 【使用時機】查詢歷史性的資料。 【注意】這個隔離層級讀取錯誤資料的機率太高， 一般不會採用這種隔離層級。 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

66 2.Read Committed(讀取認可) 【定義】 在這個等級比read uncommited嚴格一些，它只允許讀取已認可的
資料（已經成為資料庫永久部分的資料）。所以允許unrepeatable read，但不允許dirty read，亦即不允許讀取尚未執行Commit的資料。 【存在問題】 當交易1讀取資料之後，不會在乎交易2更改資料，造成不一致現象。 【解決方法】利用Repeatable Read 【語法】 SET TRANSACTION ISOLATION LEVEL READ COMMITTED

67 3. Repeatable Read(可重覆讀取)
【定義】 此種交易隔離等級比read commited嚴格一些，它會鎖定查詢中的 資料，以防止其他交易更改資料，因此，可以確保每次交易所讀取 的資料是相同的。 【存在問題】當交易1讀取資料時，交易2卻可以「新增」與「刪除」資料。 【解決方法】利用SERIALIZABLE 【語法】 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

68 4. Seriazable(序列化) 【定義】 此種交易隔離等級是最嚴格的等級，也就是說某一交易所使用的所有
資料表，全部都會被鎖定。亦即同一個時間只能有一個交易，即所謂 的交易循序進行，因此，無法提供並行交易處理。以避免資料表被其 他交易進行新增、修改及刪除的操作。 【語法】 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

69 10-6 並行控制的必要性　 【定義】 並行控制就是使多個交易可以在同時間存取同一個資料項目，而這些交易之間不會互相干擾，即確保並行執行的交易間的隔離性(Isolation)。 為何並行控制是必要的，其原因如下四點： 1.遺失更新的問題(Lost Update) 2.未確認相依的問題(Uncommitted dependency problem； Dirty Read) 3.不一致分析的問題(Inconsistent Analysis problem) 4.無法重複的讀取（Nonrepeatable read）

70 10-6.1 遺失更新的問題(Lost Update)
【定義】 當多個交易以交錯的方式執行，而且針對相同的資料項目做存取的動作，會使得此資料目內容值不正確，亦即交易已經更新的資料被另一個交易覆寫，使用某一個交易動作無效。此時稱為遺失更新(Lost Update)。亦即某個交易對欄位進行更新的資訊，因另一個交易的介入而遺失。

71 【概念範例】 假設現在有兩個交易，分別為T1與T2，時間由t1~t5，實際交易過程如下所示：假設：x的預設值=10 說明：
交易T1在時間t3更改資料項x值為100，而在時間t4時卻被交易T2覆蓋(Overwrite)為60，因此，交易T1在時間t5再讀取x值時，卻是60而不是100。此種問題稱為交易T1在時間t3的更新動作遺失(Lost)現象。 所以，以上的排序「一定不是」可序列化的排程(因為有遺失更新的問題)。 時間 交易T1 交易T2 t1 <read(x),T1> | t2 <read(x),T2> t3 <write(x,10,100)> t4 <write(x,10,60)> t5

72 【實作】　 假設現在有兩個交易，分別為交易T1與T2。而交易T1和T2同時線上預訂高鐵火車座位，目前高鐵火車座位數尚餘100個，交易T1希望預訂10個座位，交易T2預訂20個座位。最後高鐵火車訂位資料庫的座位數卻還有80個， 因此，交易T1在時間t5再讀取x值時，卻是80而不是90因此，交易T1等於沒有執行，因為交易T1更新的座位數已經被交易T2覆寫。 時間 交易T1 交易T2 t1 <read(x),T1> | t2 <read(x),T2> t3 <write(x,100,90)> t4 <write(x,100,80)> t5

73 【解答】 ch10-6-01(交易1).sql 與 ch10-6-01(交易2).sql 交易T1 交易T2
use ch10_DB SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 交易 Begin Transaction select * from dbo.高鐵訂位表 交易 --請執行「ch (交易2).sql」==>step1 交易 UPdate dbo.高鐵訂位表 set 目前座位數=90 commit --請執行「ch (交易2).sql」==>step2 交易 ==>step1 交易 ==>step2 set 目前座位數=80

74 10-6.2 未確認相依的問題 (Uncommitted dependency problem；Dirty Read)
【定義】 又稱為暫時更新問題，即對尚未認可的資料進行讀取。亦即第一個交易修改資料，而第二個交易在第一個交易「確認前」讀取修改的資料。如果第一個交易中途發生故障，必須撤回(回復)的情況。第二個交易將取得不正確的資料。

75 【概念範例】 現在有兩個交易，分別為T1與T2，時間由t1~t5，實際交易過程如下所示：假設：x的預設值=10 說明：
【概念範例】 　 現在有兩個交易，分別為T1與T2，時間由t1~t5，實際交易過程如下所示：假設：x的預設值=10 說明： 交易T1在時間t2更改資料項x值為100，並且被交易T2讀取(read)，但交易T1在時間t4時 Abort(撤回)，因此，導致交易T2在t2時間所讀取交易T1的中間結果，產生錯誤現象。 時間 交易T1 交易T2 t1 <read(x),T1> | t2 <write(x,10,100)> <read(x),T2> t3 <write(x,100,60)> t4 Abort(撤回)

76 實務範例：　 交易T1和T2存取同一位學生的成績記錄，交易T1在時間t2因為成績登記錯誤，將那位學生的成績從80分改為90分，交易T2在時間t3讀取的是尚未確認交易的中間結果資料(90分)，它是一個錯誤的結果。 時間 交易T1 交易T2 t1 <read(x),T1> | t2 <write(x,80,90)> t3 <read(x),T2> t4 t5 Abort(撤回)

77 【實作１】 檔案名稱：ch10-6-02(交易1).sql 與 ch10-6-02(交易2).sql 交易T1 交易T2
【實作１】　 檔案名稱：ch (交易1).sql 與 ch (交易2).sql 交易T1 交易T2 use ch10_DB SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; 交易 Begin Transaction --讀取 select * from dbo.學生成績表 --寫入 UPdate dbo.學生成績表 set 成績=成績+10 交易 --請執行「ch (交易2).sql」 Rollback Transaction 交易 ==>step1 /*造成剛才讀取的"成績=90"是DIRTY READ 因為交易1在尚未執行確認之前，交易2就去讀取。*/

78 【實作２】解決方法：READ COMMITTED
檔案名稱：ch A(交易1).sql 與 ch A(交易2).sql 交易T1 交易T2 use ch10_DB SET TRANSACTION ISOLATION LEVEL READ COMMITTED;--Default 交易 Begin Transaction --讀取 select * from dbo.學生成績表 --寫入 UPdate dbo.學生成績表 set 成績=成績+10 交易 --請執行「ch A(交易2).sql」 交易 Rollback Transaction 交易 ==>step1 /* 無法完成.等待中......(可解決DIRTY READ)) */

79 10-6.3 不一致分析的問題 (Inconsistent Analysis problem)
【定義】 又稱為不正確總計問題(Incorrect summary problem)，指一個交易正在計算一群資料項目的聚合函數(Aggregate function)時，其中某些資料項目被另一個交易更新，而造成聚合函數無法得到正確的結果。 【註】聚合函數(Aggregate function)：指用來計算及統計的函數。 【例如】 SUM(),AVERAGE(),COUNT(),MAX(),MIN()。

80 概念範例：　 現在有兩個交易，分別為T1與T2，時間由t1~t7，實際交易過程如下所示：假設：x的預設值=10, y的預設值=20，而交易T1欲求出SUM=2x+y=40。 說明： 交易T1在時間t2讀取資料項x值為10，並且在時間t3時SUM的值指定為20(=2*10)，在時間t4交易T2讀取y，並且在時間t5 更改y為60，因此，導致交易T1在時間t7時的SUM值為80，而不是40，產生不正確的錯誤現象。 時間 交易T1 交易T2 t1 SUM=0 | t2 <read(x),T1> t3 SUM=SUM+2x t4 t5 t6 t7 <read(y),T1> SUM=SUM+y <read(y),T2> <write(y),T2,20,60)>

81 實務範例： 交易T1和T2存取同一位客戶在銀行的X和Y兩個帳戶，交易前兩個帳戶餘額分別為700和300元，交易T1是計算兩個帳戶的存款總額，交易T2分別在x帳戶提出200元和y帳戶存入200。 說明： 交易T1在時間t2讀取資料項x值為700，而交易T2在時間t3時讀取x值，並在時間t4時讀取y值，在時間t5 更改x為500及在時間t6 更改y為500，因此，導致交易T1在時間t8時的SUM值為1200，而不是1000，產生不正確的錯誤現象。 時間 交易T1 交易T2 t1 SUM=0 | t2 <read(x),T1> t3 <read(x),T2> t4 t5 t6 t7 t8 <read(y),T1> Sum=x + y <read(y),T2> <write(x),T2,700,500> <write(y),T2,300,500>

82 【實作１】 執行結果： 檔案名稱：ch10-6-03(交易1).sql 與 ch10-6-03(交易2).sql 交易T1 交易T2
【實作１】　 檔案名稱：ch (交易1).sql 與 ch (交易2).sql 交易T1 交易T2 use ch10_DB SET TRANSACTION ISOLATION LEVEL READ COMMITTED;--Default; 交易 Begin Transaction INT INT from dbo.銀行帳戶表 print 'X=' + 交易 --請執行「ch (交易2).sql」 INT INT print 'Y=' + print 'SUM=' + Commit Transaction /*造成剛才讀取的"X帳戶=700"與目前讀取的"X帳戶=500"，不一致現象。 因為交易1在尚未執行確認之前，交易2就去「update」。 */ 交易 ==>step1 UPdate dbo.銀行帳戶表 set X帳戶=500 set Y帳戶=500 執行結果：

83 【實作2】 交易一(解決方法：Repeatable READ) 執行結果：
【實作2】　 交易一(解決方法：Repeatable READ) 檔案名稱：ch A(交易1).sql 與 ch A(交易2).sql 交易T1 交易T2 use ch10_DB SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 交易 Begin Transaction INT INT from dbo.銀行帳戶表 print 'X=' + 交易 --請執行「ch (交易2).sql」 INT INT print 'Y=' + print 'SUM=' + Commit Transaction 交易 ==>step1 UPdate dbo.銀行帳戶表 set X帳戶=500 set Y帳戶=500 /* 無法完成.等待中......(可解決DIRTY READ)) */ 執行結果：

84 10-6.4 無法重複的讀取 （Nonrepeatable read）
【定義】 重複的讀取會獲得不一致的資料。當某一交易T1需要讀取同一項目資料兩次或兩次以上時，但因為另一個交易T2在其讀取間隔期間內修了該項目。如此，將會導致交易T1對同一個項目資料會有不同的結果。雖然Repeatable READ 可以解決不一致的問題，但是，它卻存在另一個問題就是，當交易1在查詢時，而交易2卻還可以進行新增與刪除的問題。因此，我們可以利用交易隔離中最嚴格的方式(SERIALIZABLE)，就可以解決此一問題。

85 【實作1】 問題：當交易讀取資料時，交易卻可以新增與刪除資料
檔案名稱：ch (交易1).sql 與 ch (交易2).sql 交易T1 交易T2 use ch10_DB SET TRANSACTION ISOLATION LEVEL REPEATABLE READ; 交易 Begin Transaction select * from dbo.學生表 交易 --請執行「ch (交易2).sql」 Commit Transaction /*當交易讀取資料時，交易2卻可以新增資料， 導致交易1無法繼續讀取資料。 */ 交易 ==>step1 Insert Into dbo.學生表(學號,姓名) values('S1006','王霏')

86 【實作2】 解決方法：SERIALIZABLE 交易T1 交易T2
【實作2】　 解決方法：SERIALIZABLE 檔案名稱：ch A(交易1).sql 與 ch A(交易2).sql 交易T1 交易T2 use ch10_DB SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; 交易 Begin Transaction select * from dbo.學生表 交易 --請執行「ch A(交易2).sql」 Commit Transaction Insert Into dbo.學生表(學號,姓名) values('S1007','七賢') delete from dbo.學生表 where 學號='S1006' /* 無法完成.等待中......(可解決交易查詢1，而交易2卻新增與刪除的問題) */

87 SQL Server提供四種不同交易隔離等級
注意：read uncommited出錯的機率太大，大部份的應用程式會選用read commited或repeatable read的隔離層級，而serializable執行完全的鎖定，交易只能循序進行，嚴重傷害系統效能。 隔離層級 並行控制三問題 Read Uncommitted Read Committed Repeatable Read Seriazable 遺失更新問題 (Lost Update)  未確認問題(Uncommitted；Dirty Read)  不一致問題(Inconsistent) 無法重複的讀取（Nonrepeatable read）