Ch14. 交易處理交易 (Transaction) 概念介紹多人系統下的交易的並行控制錯誤與例外處理機制 Ch15. 資料庫的備份與還原資料庫的備份 / 還原、卸離 / 附加介紹

第五篇 系統管理與資料安全 • Ch14.交易處理 • 交易(Transaction)概念介紹 • 多人系統下的交易的並行控制 • 錯誤與例外處理機制 • Ch15.資料庫的備份與還原 • 資料庫的備份/還原、卸離/附加介紹 • 資料的匯出與匯入 • Ch16.SQL Server登入與系統安全 • SQL Server的登入驗證 • 伺服器角色與資料庫角色 • 存取權限的策略制定

第十四章 交易處理(Transaction Processing)

Chapter 14 Outline • 14-1 交易概念介紹 • 14-2 錯誤回復 • 14-3 交易的並行控制 • 14-4 SQL Server中交易與記錄檔 • 14-5 與交易有關的SQL敘述 • 14-6 建立一交易 • 14-7 SQL Server 的3種交易類型 • 14-8 巢狀式交易 • 14-9 分散式交易 • 14-10選擇交易隔離層級 • 14-11鎖定與死結 • 14-12本章總結

14-1 交易概念介紹 • 在資料庫系統中，交易(Transaction)概念提供了一個資料處理的邏輯單元(Logic Unit) 。 • 在該邏輯單元中如果全部執行成功，則會確定交易期間所修改的所有資料正式成為資料庫的內容；如果有發生錯誤，則必須取消或回復該交易期間內所有的資料修改。 • 資料庫系統是一個交易處理系統(Transaction Processing System)，它容許交易同步的被執行。

14-1 交易概念介紹 • 多使用者系統（Multiuser System） • 現今多數DBMS為多使用者系統，即同時容許一個以上使用者存取資料庫。這是因為DBMS安裝在多元程式（multiprogramming）的作業系統上，這些作業系統可以同時存取執行一個以上的處理程序以交錯（interleaved）方式分享CPU的使用權。

14-1 交易概念介紹 • 如下圖所示，有兩個處理程序Pi和Pj以交錯方式被CPU執行。在時間t1，Pi被CPU執行。當Pi需要資料庫資料，而這些資料不在主記載體時，DBMS必須呼叫作業系統，處理此I/O要求。

14-1 交易概念介紹 • 這時CPU會中斷Pi的執行，再呼叫檔案系統及磁碟管理系統的服務將資料讀取到主記載體。在此等待的時間中，為了提高CPU之使用率以及系統整體效能（throughput），CPU會執行其他處理程序，在本例中Pj會被CPU的排班程式（scheduler）選出而被執行，如上頁圖之時間t2所示。 • 假如當Pi之I/O完成後（時間t3），經過排班程式的選擇，Pi會重新拿到CPU的使用權。處理程序Pi和Pj以交錯方式執行其他部份，直到結束為止。當然，一個處理程序的CPU使用時間片斷（time slice）用完也會發生被置換（swap）的情況。

14-1 交易概念介紹 • 為何交易會與多使用者系統有關係，因為大多數的資料庫理論是在交錯同步(Interleaved Concurrency)的假設下發展出來的。由於系統在同一時間可能會執行多的process，因此如何保證資料讀取、寫入的正確性就是交易探討的重要課題。

14-1 交易概念介紹 • 交易的特性（Properties of Transaction） • 單元性（Atomicity） • 一個交易是一個單元（Atomic）的處理。如果是正常情況，則整個交易應完整的被執行。否則，發生任一錯誤情況時，則將交易所做的資料更新復原到交易開始前狀況（undo）。因此，單元性也稱之為不可部份完成性。 • 一致性（Consistency ） • 一個完整的交易能使資料庫從一個一致性狀態（consistent state）轉換到另一個一致性狀態。所謂一致性狀態是指資料庫的資料滿足資料庫綱要所定義的所有限制，例如整合限制條件。

14-1 交易概念介紹 • 隔離性（Isolation） • 一個執行中的交易不應被其他同步執行中的交易所影響，即交易間應具有隔離機制。 • 耐久性（Durability） • 一個交易成功的執行「認可」（commit）命令後，它對資料庫所作的任何資料更新處理，均反應在資料庫中。這些更新動作不會受「認可」後的任何錯誤所影響。

14-1 交易概念介紹 • 交易管理在DBMS是由錯誤回復(Failure Recovery)和並行控制(Concurrency Control)兩個主要機制來達成。而交易管理的主要目的是要維持資料庫中資料的一致性(Consistency)和正確性(Correctness)。 • 錯誤回復機制是維持交易處理的「單元性」和「耐久性」 • 並行控制機制是在維持「隔離性」和「一致性」

14-2 錯誤回復 • 當一個交易提交到DBMS要被執行時，DBMS的責任是要管理此交易順利的執行，包括下列的情況： • 當交易成功的執行「認可」(Commit)後，此交易對資料庫所作的資料更新需反應到資料庫中。 • 在交易執行當中不會被其他交易影響。 • 當錯誤發生時，交易的操作不會對資料庫產生影響。

14-2 錯誤回復 • 錯誤種類（Types of Failures）：一般可分為3大類，交易錯誤、系統錯誤以及儲存媒體錯誤 • 交易錯誤（Transaction Failures） • 邏輯錯誤（Logical Error） • 資料錯誤（Data Error） • 同步控制強制錯誤（Concurrency Control Enforcement Error） • 系統錯誤（System Failure） • 電腦錯誤（Computer Failures） • 天災（Catastrophes）

14-2 錯誤回復 • 儲存媒體錯誤（Storage Media Failures） • 主要是指磁碟錯誤。當交易正在讀寫資料庫資料時，發生磁碟錯誤，以致磁碟中資料可能遺失。針對這種錯誤可使用RAID（Redundant Array of Inexpensive Disk）硬體提高資料安全性。

14-2 錯誤回復 • 錯誤回復程序（Procedure of Failure Recovery） • 這個程序DBMS能自動完成而不需任何人工作業。在說明回復程序前，我們必須先介紹「檢查點」（checkpoint）事件。「檢查點」是一個系統設定的時間（例如：5分鐘、10分鐘），DBMS每隔「檢查點」所設定的時間會處理以下的動作： • 將主記憶體內所有日誌(Log)紀錄存回磁碟中。 • 將緩衝區內已認可(Commit)交易之資料回存到磁碟。 • 在日誌中存入檢查點紀錄。

14-2 錯誤回復 • 檢查點記錄的主要功能是幫助回復程序作Redo和Undo工作，所以包含了系統重啟之後必須處理的資訊。 • Redo：當系統發生錯誤前，交易已作「認可」(Commit)動作，但資料尚未寫到磁碟上，根據「一致性原則」，從最後一個「檢查點」到「認可」的操作，需要Redo，或稱之為向前恢復(Roll Forward)。 • Undo：當錯誤發生前，交易還未做「認可」動作，此時需要強制中斷該交易而作Undo的動作，也稱之為Rollback。

14-3 交易的並行控制 • 在一個多元程式(Multiprogramming)或多工(Multi-tasking)的資料庫系統中，必須提供「並行控制」(Concurrency Control)機制，否則會發生下列問題： • 遺失更新問題(Lost Update Program) • 暫時更新問題(Temporary Update Problem) • 不可重複讀取(Nonrepeatable Read) • 幽靈讀取(Phantom Read)

14-3 交易的並行控制 • 遺失更新問題(Lost Update Program)：兩個交易交錯地讀取了共用資料，並且作更新的操作，使得前一個交易的資料更新作業遺失了。 • 交易T1先讀取資料項目X，然後對X作更新處理（X ＝ X ＋ 100）。 • 接著交易T2也讀取X且作X ＝ X － 500的處理。 • 交易T1將X寫入資料庫中。 • 交易T2也將X更新至資料庫。 • 最後交易T1和T2分別執行「認可」（Commit）處理。在此情況下，交易T1對資料項目X的更新被交易T2所影響而遺失了。

14-3 交易的並行控制 • 暫時更新問題（Temporary Update Problem） • 當交易T1更新了某資料項目X，但在某原因之下這個交易被迫放棄（Abort）。如果資料項目X也被另一個交易T2讀取，這時候交易T2也必須連帶的將X回存成舊有的資料值（即T2也被Abort）。

14-3 交易的並行控制 • 暫時更新問題如圖所示 • 交易T1更新了資料X但在正常結束前發生了錯誤，使得X必須回復成舊的資料值。所以交易T2讀取了資料X的「暫時值」（Temporary value）。 • 這個被T2讀取的資料也被稱為「髒資料」（Dirty data），因此該問題也被稱為「髒讀取問題」（Dirty Read Problem）。

14-3 交易的並行控制 • 不可重複讀取(Nonrepeatable Read)：也稱之為不一致分析 (Inconsistent Analysis)，該情況發生在一交易讀取同一資料數次以上。例如：交易1在不同時間點讀取同一筆資料一次以上，而在其中任兩次讀取之間，有另一個交易2改變了該筆資料。此時則因為該資料錄的內容被改變了，所以會造成交易1讀取了不同內容的同一筆資料

14-3 交易的並行控制 • 幽靈讀取(Phantom Read )：發生在兩個交易中都對資料表作INSERT或DELETE動作時所發生。 • 例如：當交易1讀取一資料表，而交易2在交易1結束之前新增了一筆資料到該資料表中，則該筆新增資料對交易1而言有如幽靈(Phantom)，因為與第一次所讀到的資料不相同。反之，當交易1讀取一資料表時，而交易2卻刪除一些資料錄，結果就造成交易1中存在一些不屬於該資料表的幽靈資料錄。

14-3 交易的並行控制 • 並行控制的方法可分為二類：悲觀並行控制(Pessimistic)與樂觀並行控制(Optimistic) • 悲觀並行控制：使用者在執行某個動作而鎖定某些資源之後，其他使用者就不能執行會與該鎖定衝突的動作，直到擁有者解除鎖定為止。主要是用在高度爭用資源的環境中，以鎖定方式來保護資源的成本，會低於發生並行衝突時回復交易的成本。 • 樂觀並行控制：在該類型並行控制中，使用者在讀取資料時，並不會將資料鎖定。主要是用在低度爭用資源的環境中，偶爾回復交易的成本會優於讀取時鎖定資料的成本。

14-4 SQL Server中交易與記錄檔 • 交易是一組具有邏輯關聯性動作的集合，所有的動作必須全部完成，不然就是必須一件都未發生，不能夠處於部分完成的狀態，一交易是不可分割的。例如：將遊戲點數由A帳號轉移至B帳號即是一個交易應用，該交易包含了下面兩個動作： • 因轉出動作而修改A帳號資料 • 因轉入動作而修改B帳號資料

14-4 SQL Server中交易與記錄檔 • 如果系統已經完成了由A帳號轉出的動作，而此時發生了系統當機，當系統重新開機後，A帳號客戶就會白白的損失一筆遊戲點數，因為尚未轉到B帳號，這樣的系統就不太會有人敢使用了。因此，對於一個交易內所有動作的處理，必須是所有動作全部完成，否則就必須是所有動作全都沒有發生，這就是交易最基本的精神。

14-4 SQL Server中交易與記錄檔 • 交易的三個相關名詞-- ROLLBACK、SAVEPOINT以及COMMIT • ROLLBACK：當系統發生錯誤的時候，將部分完成交易解除作用，ROLLBACK可以將一交易復原至一SAVEPOINT或是交易開始時的狀態。 • SAVEPOINT：一個位置定義器，可以於一交易內定義SAVEPOINT，當系統發生錯誤時將交易復原至一SAVEPOINT。 • COMMIT：是存檔的動作，它會將一交易內所有對資料庫的修訂儲存至磁碟上。COMMIT可以保証所有的改變將成為資料庫的永久一部分，而且會釋放交易所使用的資源。

14-5 與交易有關的SQL敘述 • BEGIN TRANSACTION • COMMIT TRANSACTION • COMMIT WORK • SAVE TRANSACTION • ROLLBACK TRANSACTION • ROLLBACK WORK

14-5 與交易有關的SQL敘述 • BEGIN TRANSACTION敘述 • 語法結構： BEGIN TRAN[SACTION] [transaction_name | @tran_name_variable] • 功能：標示一使用者定義交易的起始點 • 引數： • transaction_name：為交易的指定名稱，transaction_name必須遵循識別字的命名規則，有效位數為32個文字。 • @tran_name_variable：為一地區性變數，包含一交易的名稱，必須為文字資料型態。

14-5 與交易有關的SQL敘述 • COMMIT TRANSACTION敘述 • 語法結構： COMMIT TRAN[SACTION] [transaction_name | @tran_name_variable] • 功能：標示一交易的終止點，表示一交易已成功的被執行了。 • 引數： • transaction_name：為前面BEGIN TRANSACTION敘述所指定的交易名稱，但SQL Server會忽略該transaction_name。 • tran_name_variable：為地區性一變數，包含一交易的名稱，必須為文字資料型態。

14-5 與交易有關的SQL敘述 • COMMIT WORK敘述 • 語法結構： COMMIT [WORK] • 功能：與COMMIT TRANSACTION相同，但是不能接受一使用者定義的交易名稱。COMMIT [WORK]

14-5 與交易有關的SQL敘述 • SAVE TRANSACTION敘述 • 語法結構： SAVE TRAN[SACTION] [savepoint_name | @savepoint_variable] • 功能：在一交易內設定一SAVEPOINT • 引數： • savepoint_name：為一SAVEPOINT的指定名稱 • @savepoint_variable：為一變數，包含一SAVEPOINT的名稱，必須為文字資料型態。

14-5 與交易有關的SQL敘述 • ROLLBACK TRANSACTION敘述 • 語法結構：ROLLBACK TRAN[SACTION] [transaction_name |@tran_name_variable | savepoint_name | @savepoint_variable] • 功能：將一交易ROLLBACK至交易的起始點或交易內的SAVEPOINT • 引數： • transaction_name：為一於BEGIN TRANSACTION敘述指定的交易名稱 • @tran_name_variable：為一地區性變數，包含一交易的名稱，必須為文字資料型態。 • savepoint_name：為一SAVEPOINT的指定名稱 • @savepoint_variable：為一地區性變數，包含一SAVEPOINT的名稱，必須為文字資料型態。

14-6 建立一交易 • 如何使用BEGIN TRANSACTION、COMMIT、SAVEPOINT以及ROLLBACK敘述，撰寫一個交易呢？一個交易的結構大致如下： BEGIN TRANSACTION statement1 statement2 . . . . . statementN IF （success） COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION ENDIF • BEGIN TRANSACTION標示著一個交易的開始，然後開始一系列的SQL操作敘述，最後檢查是否所有的資料修訂都已成功的被完成，是則執行COMMIT敘述將資料寫入磁碟，否則執行ROLLBACK敘述將資料回復至交易發生前的狀態。

14-6 建立一交易 • 範例14-6.1：請新增一筆訂單，訂單號碼為200，訂貨日期為2007-04-01，經銷商號碼為1019。該訂單包含兩個單項，第一單項產品號碼為1，供應商代碼為HSC，數量為2；第二單項產品號碼為2，供應商代碼為HFT，數量為6。 • 很明顯的必須將3個insert(一筆訂單資料，兩筆訂單細項資料)包裝在一個交易中，因為新增了訂單但是沒有新增細項資料就不正確。但是在新增資料的過程中要一一檢查是否正確才能COMMIT • 由於必須依依檢查，因此決定採用try …catch機制

14-6 建立一交易 SET IDENTITY_INSERT orders ON; BEGIN TRAN --開始交易 begin try --啟動例外處理機制 insert into orders(order_num,order_date,cust_num) values(200,'2007-04-01',1019) --訂單 insert into items(item_num,order_num,prod_num,supp_code,quantity) values(1, 200, 2, 'HSC', 2) --訂單細項一 insert into items(item_num,order_num,prod_num,supp_code,quantity) values(2, 200, 6, 'HSC', 2) --訂單細項二 COMMIT TRAN --如果沒有錯誤就認可該交易 end try begin catch --發生錯誤的處理機制 ROLLBACK TRAN --回復該次交易 end catch SET IDENTITY_INSERT orders ON;

14-7 SQL Server 的3種交易類型 • 交易是以每個連線主，所以每個連線都有自己的交易，但是不同的交易卻可能鎖定相同的物件而造成死結(Deadlock)的狀況 • 在SQL Server 2005中交易的模式有3種，分別為： • 外顯交易 (Explicit Transaction)、 • 自動認可交易(Autocommit Transaction) • 隱含交易 (Implicit Transaction)

14-7 SQL Server 的3種交易類型 • 外顯交易：是明確定義交易的啟動與結束的一種交易，所以亦稱之為明確交易，也就是使用SQL交易敘述所指定的交易。 • 自動認可交易：這是SQL Server 2005預設的交易管理模式。每一個T-SQL敘述句都會在完成時認可或回復。敘述句如果成功地完成則便會自動被認可；如果是遇到任何錯誤則被自動復原。 • 隱含交易：隱含交易的意思就是說不用下BEGIN TRANSACTION也會被認為在交易的狀態下。換句話說，從系統進入隱含交易之後所有執行的SQL敘述都會視為同一個交易，直到使用COMMIT或是ROLLBACK時才算結束一個交易。

14-8 巢狀式交易 • 巢狀式交易(Nested Transaction)是指數個交易彼此以階層的方式互相連結，而成為巢狀式的結構。 • @@TRANCOUNT為一整數之全域性變數，它指出目前交易的巢狀階層(Nested Level) • BEGIN TRANSACTION敘述會將@@TRANCOUNT加1 • ROLLBACK TRANSACTION敘述則會將@@TRANCOUNT設定為0 • COMMIT TRANSACTION或COMMIT WORK敘述會將@@TRANCOUNT減1

14-9 分散式交易 • 分散式交易(Distributed Transaction)是指一交易使用到多個伺服器的資源。SQL Server 2005亦支援分散式交易，允許使用者在一交易中修訂多個伺服器資料庫的資料，一分散式交易會使用到下列系統軟體： • 資源管理員(Resource Manager) • 交易管理員(Transaction Manager) • 二階段認可(Two-phase Commit/2PC)

14-9 分散式交易 • 資源管理員（Resource Manager） • 資源管理員為管控用於分散式交易資源的系統軟體，一分散式交易是由各別資源管理員的地區性交易（Local Transaction）所組成的。在分散式交易中，每一資源管理員都必須負責本身地區性交易的COMMIT或是ROLLBACK工作，而且要與其他的資源管理員相互協調。SQL Server本身即扮演著資源管理員的角色。

14-9 分散式交易 • 交易管理員（Transaction Manager） • 交易管理員為管控分散式交易COMMIT或ROLLBACK的系統軟體，它會協調各個資源管理員，以保證所有包含於一分散式交易的地區性交易能夠一起被COMMIT或是一起被ROLLBACK，Microsoft的分散式交易管理員（Distributed Transaction Coordinator/DTC）扮演著交易管理員的角色。

14-9 分散式交易 • 二階段認可（Two-phase Commit/2PC） • 準備階段 • 交易管理員送出一「準備COMMIT」要求給每一資源管理員，每一資源管理員會去執行地區性交易，完成後會只保有最低的必要系統資源（以保證資料的完整性），並傳回交易成功的訊息給交易管理員。 • 確認階段 • 如果每一資源管理員於準備階段都COMMIT成功，則交易管理員會送出「COMMIT命令」給一資源管理員。此時每一資源管理員會立刻將交易記錄為「已完成」，並且釋出佔用的系統資源。如果任一資源管理員於準備階段傳回錯誤訊息，則交易管理員會送出「ROLLBACK命令」給每一資源管理員。

14-10 選擇交易隔離層級 • 交易隔離層級(Isolation Level)是一種機制，讓我們可以動態的調整SQL Server於一連線內的鎖定狀況。 • 交易隔離層級將存取共同資料的多個交易加以隔離，以決定一交易接受「不一致資料」的程度。 • 低交易隔離層級可以增加系統並行處理的程度，但是會降低資料存取的正確性。 • 高交易隔離層級能夠讀取正確的資料，但是會降低系統並行處理的程度

14-10 選擇交易隔離層級 • 交易隔離層級決定了SQL Server使用鎖定機制的方式與程度以解決並行性控制上發生的問題。 • 不淨式讀取(Dirty Read)：或稱中途讀取，意思就是說在交易讀取到未經認可的中途資料。 • 不可重複式讀取(Nonrepeatable Read)：意思就是說在交易中讀取到的資料被其他交易改變了。 • 幽靈讀取(Phantom Read)：意思就是說交易中的資料表被其他交易改變了。

14-10 選擇交易隔離層級 • SQL Server 2005支援以下ANSI SQL所定義的4種交易隔離層級： • 讀取未認可 (Read Uncommitted，隔離交易的最低等級，僅能確保不會讀取實體上已損毀的資料) • 讀取認可(Read Committed，SQL Server的預設值) • 可重覆讀取(Repeatable Read) • 可序列化(Serializable，隔離交易的最高等級，使交易完全與其他交易隔離)

交易隔離層級 是否會發生不淨式讀取是否會發生不可重複式讀取是否會發生幽靈讀取 Read Uncommitted 是是是 Read Committed 否是是 Repeatable Read 否否是 Serializable 否否否 14-10 選擇交易隔離層級表14-10.1 交易隔離層級的現象 • Read Uncommitted與Read Committed屬於樂觀的並行性控制 • Repeatable Read與Serializable則屬於悲觀的並行性控制

14-10 選擇交易隔離層級 • 根據選擇的交易隔離層級，當存取資源時，系統會使用鎖機制(Lock)鎖住該資源，以便免其他交易來存取而發生一些錯誤的現象。 • 可使用SET TRANSACTION ISOLATION LEVEL敘述來設定交易隔離層級 • 語法結構：SET TRANSACTION ISOLATION LEVEL ｛READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE｝

14-10 選擇交易隔離層級 • SET LOCK_TIMEOUT敘述 • 語法結構：SET LOCK_TIMEOUT timeout_period • 功能：宣告一SQL敘述等待一鎖機制被釋放的時間(TIME-OUT)，以千分之一秒為單位，-1表示永久的等待，但永久等待很容易造成死結，此為系統預設值。 • 範例14-10.1：請設定TIME-OUT為1,800毫秒，然後查詢該設定值。 set lock_timeout 1800 select @@lock_timeout

14-11鎖定與死結 • SQL Server擁有多個資料粒度(Granularity)的鎖定(Lock)，允許交易鎖定不同類型的資源。 • 為了把鎖定的成本降至最低，SQL Server會自動依照交易的適當層級來鎖定資源。 • 鎖定於較小的資料粒度如資料列則可以提高並行性，但如果鎖定太多的資料列則會因為持有更多的鎖定而造成系統的負擔，可能反而不如直接鎖定資料表。 • 鎖定於較大的資料粒度如資料表，從並行性的角度來看會因為鎖定整個資料表而限制其他交易對於該資料表其他部份的存取。但由於必須維持的鎖定較少，因此系統的負擔較低。

14-11鎖定與死結 • 除了資料類型鎖定之外，還提供不同的資料鎖定模式： • 共用鎖定(S/Shared Lock)：用於不作更新資料的讀取作業，例如 SELECT敘述句。 • 獨佔鎖定(X/eXclusive Lock)：用於資料修改動作，例如INSERT、UPDATE以及DELETE。 • 更新鎖定(U/Update Lock)：更新(U)鎖定可防止常見的死結。 • 意圖鎖定(I/Intent Lock)：意圖鎖定主要是用來防止其他交易修改較高層級的資源，而導致較低層級的鎖定失效與改進SQL Server偵測資料粒度較高層級鎖定衝突的效率。

Ch14. 交易處理交易 (Transaction) 概念介紹多人系統下的交易的並行控制錯誤與例外處理機制 Ch15. 資料庫的備份與還原資料庫的備份 / 還原、卸離 / 附加介紹