MS SQL Server和Oracle對資料庫交易處理的差異性

背景

在吉日嘎拉的軟體編程走火入魔之：資料庫交易處理入門(適合初學者閱讀)文章中關於MS SQL Server和Oracle對資料庫交易處理的差異性引起一些爭論，因此記錄我對資料庫交易處理的想法。

 

簡介

本文講述MS SQL Server和Oracle對資料庫交易處理的差異性，以及Oracle如何對交易處理的實現。

 

什麼是事務

資料庫事務(Database Transaction)是一組資料庫操作的處理單元。事務符合ACID的特性：

Atomic:原子性，要麼全部要麼一無所有。All or None.

Consistent:一致性，所有依賴關係以及約束一致。

Isolated:分離性，不同事務不互相影響。

Durable:持久性，提交事務的資料需要持久化。

 

為什麼要使用事務

實現事務主要有兩大功能：

1.保證資料庫的consistent(一致性狀態)，保持所有依賴關係以及約束一致)。哪怕資料庫管理系統出現故障時(例如斷電)，也能恢複到一致性狀態。例如一個銀行轉帳系統，張三給李四轉3000圓RMB，張三帳號上減3000和李四帳號上加3000需要同時完成，否則系統的帳就不平了。也例如有些銷售系統的匯總表和明細表，是一個主表和一個從表，需要同步更新。

2.並發時分離不同事務操作。例如編輯過程中的資料不給其他事務查詢到。這也是相對的，在特效需求下可能要支援dirty read(髒讀)，但不是這裡討論的範圍了。

 

SQL Server 2008 的事務類型

1.自動認可事務 Autocommit Transactions

這是SQL Server預設的事務類型，每一條單獨的SQL語句(SQL statement)都是單獨的一個事務，語句執行完畢後自動認可。調用方不需要手工控制事務流程。

2.顯示事務 Explicit Transactions

調用方需要調用API或者使用T-SQL的BEGIN TRANSACTION 語句來開啟事務。需要調用COMMIT 或者 ROLLBACK TRANSACTION 來提交或者復原。

3.隱含交易 Implicit Transactions

使用SET IMPLICIT_TRANSACTIONS ON把事務模式變成隱式模式。調用方不需要執行BEGIN TRANSACTION 語句來開啟事務。資料庫引擎執行到SQL語句的時候自動開啟事務。調用方需要調用COMMIT 或者 ROLLBACK TRANSACTION 來提交或者復原。當資料庫引擎執行下一個SQL語句時又自動開啟一個新事務。

參考:Controlling Transactions (Database Engine)

 Oracle的事務類型

Oracle的交易處理類型有點像SQL Server的隱含交易。當執行到第一個可執行檔SQL語句時自動開啟事務，然後需要調用方執行commit或者rollback來提交或者復原事務，如果有DDL語句，Oracle也會自動認可事務的。

參考:Transaction Management

 

Oracle的事務的實現

Oracle的結構分邏輯上和物理上的區別。邏輯上的結構是資料表空間，而物理上的結構是資料檔案。

邏輯實現

Oracle下實現事務在邏輯上是由Undo Tablespace來實現的。Undo Tablespace包含Undo Segements(段)，而Undo Segements包含Undo Data。Undo Data是支援事務的邏輯單元。

圖源自於《Oracle Database 10g: Administration Workshop I》

Undo Data用於儲存修改前後的資料，以支援復原，長時間查詢，和flashback查詢，以及失敗事務恢複的功能。

由於有了Undo Data，復原事務變得容易了，因為Undo Data儲存了修改前後的資料，保證了事務的原子性。失敗事務恢複和交易回復類似，當網路中斷或者其他原因導致事務異常停止，資料庫引擎可以恢複到一致性狀態。

同時Undo Data也支援長時間查詢(Read-consistent)，例如有表格T，主鍵為key，有欄位為f1，資料如下，儘管只有4條資料，假設需要很長時間進行查詢。

key	f1
1	A
2	B
3	C
4	D

事務一開始查詢，一直沒結束，而事務二開始修改key為1的資料為Z，事務二進行提交，資料變成下面的表格。但是事務一查詢結束的時候還是讀出A,B,C,D，因為查詢是從Undo中讀出快照。

key	f1
1	Z
2	B
3	C
4	D

Flashback查詢是Oracle 10g引進的功能，可以查詢出提交之後修改之前的資料，例如上面例子事務三在事務二提交後想查詢出A,B,C,D可以通過Flashback查詢來完成。這也是有Undo Data來支援的。

 

物理實現

從上面的邏輯實現看，只是知道了事務以及Undo Data的作用，還不清楚Oracle對事務的支援到底怎麼實現的。下面從物理結構上講述Oracle怎麼對事務進行支援。請先看一個物理結構圖。

 

圖源自於《Oracle Database 10g: Administration Workshop I》

為了簡化，我只是用個人的語言講述和事務有關的組件。SGA可以理解為全域記憶體。其中Database Buffer Cache存放的是從資料檔案中讀取的資料緩衝。紫色的圓柱體為資料檔案。Redo Log Buffer為重做日誌緩衝，也就是儲存日誌的記憶體塊，一切的資料的修改都會記錄在Redo Log Buffer裡面。例如用回事務二更新key為1資料的例子。當事務二更新key為1資料從A到Z。Oracle資料庫引擎會把key為1的資料的rowid，修改前資料A以及修改後資料Z都記錄在Redo Log Buffer裡面。如果事務二繼續更新key為2的資料為Y，那麼key為2的資料的rowid，修改前資料B以及修改後資料Y也記錄到Redo Log Buffer裡面。每一筆資料都記錄，而且是流水線性記錄。一旦事務二進行提交，LGWR進程(Log Writer，日誌寫進程)就會把Redo Log Buffer的資料按順序寫到Log Datafile裡面，也就是日誌資料檔案裡面，當寫記錄檔完成，Oracle資料庫引擎會產生一個SCN(system change number，系統更新號)，到這時候Oracle資料庫引擎會通知調用方提交完成了。這裡可以看到Oracle在提交的時候不必要把更新資料寫回資料檔案，而是寫到記錄檔裡面。因為順序寫線性記錄檔速度快很多，而寫資料檔案是需要隨機讀寫。由於線性記錄和SCN號控制，資料庫引擎可以通過redo log(重做記錄檔)的操作得到最新的資料。當然在Checkpoint的時候資料檔案是最終還是會更新的，只是說事務提交的時候更新資料檔案不是必須步驟，這樣能很大的提高效能。

由於這個機制，復原變得很簡單，要讀沒提交前之前的SCN是很容易的事情。

 

對於初學者關於資料庫交易處理的建議

我自己也是從新手一步步走過來，現在也不是老鳥，算是有點經驗，如果是剛入門的同學，你覺得有用就看一下，沒用就過了。

對於資料庫事務的處理，開始的時候不需要很深入瞭解資料庫的原理，當然以後還是需要瞭解的，優先順序排後而已。高優先順序如下：

首先，事務不是什麼高深神秘的東西，我從入行開始所做的所有系統，包括現在的嵌入式系統，都用到事務。我並不覺得大部分系統事務有什麼問題，只是一些約束和同步機制，真的有問題從自身系統設計角度看，不一定說從資料庫技術角度去找解決方案。例如Oracle的長時間查詢如果Undo Data(曆史資料)給覆蓋了，Oracle會拋出異常"ORA-01555: snapshot too old”，如果出現這種問題，我會從自身系統設計角度入手，為什麼有那麼大的查詢，為什麼在這個查詢中其他事務會更新資料，這些查詢是否只是查一次就夠了，查的過程是否需要鎖住表等等。然而這個問題可以通過資料庫調優解決，但是我想問題的角度首先是從自身系統設計出發。

第二，要知道的是不同資料庫的事務類型的區別，例如MS SQL Sever是預設是自動認可事務，用的時候需要知道每個語句都有單獨的事務在操作。而Oracle是類似於隱含交易，必須手工commit或者rollback。

第三，使用事務要知道一一對應，特別是嵌套事務的時候，有始有終。很多問題時候發生終的時候，注意異常處理需要結束已經開啟的事務。

 

第一點是心理問題，第二三點是技術問題，做好我覺得就可以入門開發系統了。以後碰到問題在一步步深入。

 

MS SQL Server對事務的實現下一篇再講了。上述是我對Oracle實現的理解，有不當之處也希望指出，我可以完善文章。