Microsoft PowerPoint - sql2005_ch12.ppt

Size: px

Start display at page:

Download "Microsoft PowerPoint - sql2005_ch12.ppt"

谓阡强
5 years ago
Views:

1 第十二章資料庫交易 12-1 SQL Server 的交易機制交易的設計三種交易模式交易的其他設計 1/21

2 12-1 SQL Server 的交易機制出貨時的交易元件 : 出貨單庫存量更新交易 (Transaction) 交易中所有動作必須完成, 否則必須復原, 回到之前交易的狀態. 2/21

3 交易 (Transaction) 是以工作的單一邏輯單元 (Logic Unit) 來執行的一系列相關作業每一工作邏輯單元必須呈現出四種屬性, 才能成為一筆交易 : 1. 不可部份完成 (Atomicity) 2. 一致性 (Consistency) 3. 隔離性 (Isolation) 4. 耐久性 (Durability) 屬性交易特性重要! 3/21

4 交易四大特性 1. 不可部份完成性 : 交易必須是不可部份完成 (Atomic) 的工作 ; 資料的修改不是全部執行, 就是全部不執行 2. 一致性 : 交易完成時, 全部的資料必須維持一致性的狀態在關聯式資料庫 (Relational Database) 中, 必須將所有的規則 (Rule) 套用於交易的修改, 以維護所有的資料整合性 (Integrity) 所有的內部資料結構, 例如 B 型樹狀結構索引 (B-tree Index) 或是雙向連結串列 (Doubly-Linked List), 在交易終止時必須是正確的 3. 隔離性 : 並行的交易所做的修改, 必須與其他任何並行的交易所做的修改隔離交易所辨識的資料不是處於另一筆並行的交易修改資料之前的狀態, 就是處於第二筆交易完成後的狀態, 但是卻無法辨識中繼狀態這稱為序列化能力 (Serializability), 因為這樣可以產生重新載入起始資料並重新執行一系列的交易, 以便讓資料最終能夠與原始交易執行後的狀態相同的能力 4. 耐久性 : 交易完成之後, 其作用便永遠存在於系統之中即使系統發生失敗的事件但修改仍會保存 4/21

5 交易的設計應用程式主要是透過指定交易何時啟動及結束來控制交易可利用 T-SQL 陳述式或資料庫應用程式設計介面 (API) 函數來指定依照預設, 會在連線層級 (Connection Level) 管理交易在連接上啟動交易時, 連接上執行的所有 Transact-SQL 陳述式在交易結束之前都是該交易的一部份 1. 啟動交易可將交易啟動為外顯 (Explicit) 自動認可 (Autocommit) 或是隱含交易 (Implicit Transaction). 2. 結束交易可以使用 COMMIT 或 ROLLBACK 陳述式來結束交易 COMMIT 表示交易成功及認可 COMMIT 如果交易中發生錯誤, 或是使用者決定取消交易, 可將交易予以復原, 也就是 ROLLBACK 陳述式參考 /21

6 指定交易界限設計人員可以使用 T-SQL 陳述式或 API 函數與方法, 來識別 SQL Server 交易何時啟動與結束. Transact-SQL 指令碼可使用陳述式來定義交易 : BEGIN TRANSACTION COMMIT TRANSACTION COMMIT WORK ROLLBACK TRANSACTION ROLLBACK WORK SET IMPLICIT_TRANSACTION ON 外顯交易隱含交易 API 函數與方法 : ADO.NET, ADO, OLE DB, ODBC 在同一筆交易中同時使用兩種方法可能導致無法預期的結果例如, 不可使用 ODBC API 函數啟動一筆交易, 然後再用 T-SQL 的 COMMIT 陳述式認可這筆交易 6/21

7 [ 參考篇 ] API 函數與方法.NET Framework SQLClient 命名空間 : SqlConnection 物件上使用 BeginTransaction 方法, 可開始明確交易若要結束交易, 可呼叫 SqlTransaction 物件上的 Commit() 或 Rollback() 方法 ADO: 使用 Connection 物件的 BeginTrans 方法可啟動外顯交易若要結束交易, 可呼叫 Connection 物件的 CommitTrans 或 RollbackTrans 方法 OLE DB: 呼叫 ITransactionLocal::StartTransaction 方法可啟動交易呼叫 ITransaction::Commit 或 ITransaction::Abort 方法時, 將 fretaining 設定為 FALSE, 可在不自動啟動另一筆交易的情況下結束交易 ODBC API 不支援外顯交易, 僅支援自動認可及隱含交易 (Implicit Transaction) 7/21

8 交易處理期間的錯誤如果因為錯誤而讓交易無法順利完成,SQL Server 會自動回復交易, 並釋放交易所佔用的一切資源如果用戶端與 Database Engine 執行個體之間的網路連接已中斷, 該連接尚未處理完畢的交易會在網路通知該執行個體發生中斷時全部回復如果用戶端應用程式失敗或是用戶端電腦當機或重新啟動, 也會中斷連接,Database Engine 執行個體會在網路通知已發生中斷時, 回復所有尚未處理完畢的連接如果用戶端從應用程式登出, 便會復原任何尚未處理完畢的交易如果批次中發生執行階段陳述式錯誤 ( 例如條件約束違規 ),Database Engine 的預設行為是只回復產生錯誤的陳述式可以使用 SET XACT_ABORT 陳述式來變更這個行為在 SET XACT_ABORT ON 執行之後, 所有執行時期陳述式錯誤都會讓目前的交易自動回復但是編譯錯誤, 例如語法錯誤, 不會受到 SET XACT_ABORT 的影響 8/21

9 SET XACT_ABORT 當 SET XACT_ABORT 是 ON : 如果 Transact-SQL 陳述式產生執行階段錯誤, 就會終止和回復終止和回復整個交易當 SET XACT_ABORT 是 OFF : 在某些情況下, 只會回復產生錯誤的 Transact- SQL 陳述式, 交易會繼續進行即部份陳述式會成功執行隨著錯誤嚴重性而不同, 即使 SET XACT_ABORT 是 OFF, 也有可能回復整個交易 9/21

10 [ 參考篇 ] SET XACT_ABORT- 範例 USE AdventureWorks; IF OBJECT_ID(N't2', N'U') IS NOT NULL DROP TABLE t2; -- U 表示資料表 IF OBJECT_ID(N't1', N'U') IS NOT NULL DROP TABLE t1; CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY); CREATE TABLE t2 (a INT NOT NULL REFERENCES t1(a)); INSERT INTO t1 VALUES (1); INSERT INTO t1 VALUES (3); INSERT INTO t1 VALUES (4); INSERT INTO t1 VALUES (6); SET XACT_ABORT OFF; BEGIN TRANSACTION; INSERT INTO t2 VALUES (1); -- 新增成功 INSERT INTO t2 VALUES (2); -- Foreign key error. INSERT INTO t2 VALUES (3); -- 新增成功 COMMIT TRANSACTION; SELECT * FROM t2; SET XACT_ABORT ON; BEGIN TRANSACTION; INSERT INTO t2 VALUES (4); -- 新增失敗 INSERT INTO t2 VALUES (5); -- Foreign key error. INSERT INTO t2 VALUES (6); -- 新增失敗 COMMIT TRANSACTION; -- SELECT shows only keys 1 and 3 added. -- Key 2 insert failed and was rolled back, but -- XACT_ABORT was OFF and rest of transaction -- succeeded. -- Key 5 insert error with XACT_ABORT ON caused -- all of the second transaction to roll back. SELECT * FROM t2; 10/21

11 三種交易模式外顯交易 (Explicit Transaction) 由設計人員明確定義交易的啟動與結束的一種交易資料程式庫應用程式或 T-SQL 指令碼可使用 BEGIN TRANSACTION COMMIT TRANSACTION COMMIT WORK ROLLBACK TRANSACTION ROLLBACK WORK 等陳述式來定義外顯交易 11/21

12 1212Tran.sql 外顯交易 : 範例交易起點交易復原交易結束 12/21

13 12-1 SQL Server 的交易機制三種交易模式自動認可交易這是 SQL Server 的預設模式, 所有未特別宣告的交易, 都被視為自動認可的交易只以一個動作為交易範圍, 如一個 Update 或 Delete 等自動認可模式也是 ADO OLE DB ODBC 與資料程式庫的預設模式隱含交易透過 API 函數或 T-SQL 的 SET IMPLICIT_TRANSACTIONS ON 陳述式將隱含交易模式設定為開啟下一個陳述式會自動啟始一筆新的交易 13/21

14 交易的其他設計 BEGIN TRANSACTION 外顯交易的起始點 VARCHAR(10) -- 宣告交易名稱變數 = 'ntransaction' BEGIN -- 包括 " 交易名稱 " UPDATE Products SET ProductCost = ProductCost * 1.05 WHERE SupplierID =3 COMMIT TRANSACTION ntransaction 14/21

15 交易的其他設計 SAVE TRANSACTION 設定儲存點, 但不是儲存為檔案, 它類似 Basic 中的標記, 可以在程式任意處返回儲存點. USE ebook BEGIN TRAN UPDATE Products SET MemberPrice = MemberPrice + 5 SAVE TRANSACTION UpdateMemberPriceOK -- 設定儲存點 UPDATE Products SET ProductCost=ProductCost + 5 WHERE SupplierID = 2 > 0 BEGIN ROLLBACK TRANSACTION UpdateMemberPriceOK END ELSE BEGIN COMMIT TRANSACTION END 15/21

16 交易的其他設計巢狀交易交易之中還有交易, 最常見的例子是在預存程序內使用交易, 又在呼叫預存程序時使用交易, 形成巢狀交易在內層交易中, 只有 ROLLBACK 會有作用, 內層的 COMMIT 則會被忽略 ( 不能省略 ) 在外層認可交易時, 會一併認可所有內層交易, 若復原外層, 也會復原其下所有內層交易 COMMIT 有沒有作用, 就視其在巢狀交易中的位置而定 16/21

17 巢狀交易 : 範例 USE ebook BEGIN TRAN -- 執行 BEGIN TRAN,@@TRANCOUNT 值 + 1 EXEC eb_sp_deliveryok '111' UPDATE ORDERS SET DeliveryOK=1 WHERE OrderID=' ' BEGIN ROLLBACK TRAN END ELSE BEGIN COMMIT TRAN -- 執行 COMMIT TRAN,@@TRANCOUNT 值 - 1 END 17/21

18 交易的隔離等級 1. READ UNCOMMITTED: 等於沒有隔離. 2. READ COMMITTED: 這是 SQL Server 2005 的預設交易隔離等級, 不允許讀取尚未 COMMIT 的資料, 也就是未認可前的資料不可讀取 3. REPEATABLE READ: 交易中的所有使用的資料均會鎖定, 但可新增. 4. SERIALIZABLE: 與上項類似, 但不可以在鎖定中的資料範圍新增資料, 這是限制最嚴格的一種 5. SNAPSHOT: 類似 READ COMMITTED, 但若資料在另一交易中被認可, 則再讀取一次, 可讀取被認可的資料 ( 不支援分散式交易 ). 18/21

19 SET TRANSACTION ISOLATION LEVEL DBCC: Database Console Commands 資料庫主控台命令 19/21

20 分散式交易指的是在兩台 SQL Server 伺服器的資料庫內進行處理, 使用的交易, 跨越同一伺服器的兩個資料庫的資料處理, 也可以視為分散式交易使用分散式交易前, 必須先開啟 MS DTC (Microsoft Distributed Transaction Coordinator (MS DTC), 也就是 Microsoft 分散式交易協調器 ( 管理員 ). 若要啟始分散式交易, 可使用 BEGIN DISTRIBUTED TRANSACTION 語法 20/21

21 分散式交易 : 範例如何在 Windows Server 2003 叢集上設定 Microsoft Distributed Transaction Coordinator (MS DTC) 英文 : 中文 : 21/21

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘 ITE 資訊專業人員鑑定資料庫系統開發與設計實務試卷編號 :IDS101 注意事項一本測驗為單面印刷試題, 共計十三頁第二至十三頁為四十道學科試題, 測驗時間 90 分鐘 : 每題 2.5 分, 總測驗時間為 90 分鐘二執行 CSF 測驗系統 -Client