SQL 和Oracle對數(shù)據(jù)庫事務處理的差異性

1、.：.；SQL 和Oracle對數(shù)據(jù)庫事務處置的差別性作者: Jake, 出處:IT專家網(wǎng)論壇,責任編輯: 陳子琪, 2021-10-11 13:00在吉日嘎拉的軟件編程走火入魔之：數(shù)據(jù)庫事務處置入門(適宜初學者閱讀)文章中關于MS SQL Server和Oracle對數(shù)據(jù)庫事務處置的差別性引起一些爭論，因此記錄我對數(shù)據(jù)庫事務處置的想法。背景在吉日嘎拉的軟件編程走火入魔之：數(shù)據(jù)庫事務處置入門(適宜初學者閱讀)文章中關于MS SQL HYPERLINK whatis.ctocio/searchwhatis/497/5948997.shtml t _bank Server和Oracle對數(shù)據(jù)庫事務

2、處置的差別性引起一些爭論，因此記錄我對數(shù)據(jù)庫事務處置的想法。簡介本文講述MS SQL Server和Oracle對數(shù)據(jù)庫事務處置的差別性，以及Oracle如何對事務處置的實現(xiàn)。什么是事務數(shù)據(jù)庫事務( HYPERLINK whatis.ctocio/searchwhatis/132/5947632.shtml t _bank Database Transaction)是一組數(shù)據(jù)庫操作的處置單元。事務符合ACID的特性： HYPERLINK whatis.ctocio/searchwhatis/470/6092470.shtml t _bank Atomic:原子性，要么全部要么一無一切。All

3、or None.Consistent:一致性，一切依賴關系以及約束一致。Isolated:分別性，不同事務不相互影響。Durable:耐久性，提交事務的數(shù)據(jù)需求耐久化。為什么要運用事務實現(xiàn)事務主要有兩大功能：1.保證數(shù)據(jù)庫的consistent(一致性形狀)，堅持一切依賴關系以及約束一致)。哪怕數(shù)據(jù)庫管理系統(tǒng)出現(xiàn)缺點時(例如斷電)，也能恢復到一致性形狀。例如一個銀行轉帳系統(tǒng)，張三給李四轉 HYPERLINK whatis.ctocio/searchwhatis/273/5949273.shtml t _bank 3000圓RMB，張三帳號上減3000和李四帳號上加3000需求同時完成，否那么系

4、統(tǒng)的帳就不平了。也例如有些銷售系統(tǒng)的匯總表和明細表，是一個主表和一個從表，需求同步更新。2.并發(fā)時分別不同事務操作。例如編輯過程中的數(shù)據(jù)不給其他事務查詢到。這也是相對的，在特效需求下能夠要支持dirty read(臟讀)，但不是這里討論的范圍了。SQL Server 2021 的事務類型1.自動提交事務 Autocommit Transactions這是SQL Server默許的事務類型，每一條單獨的SQL語句(SQL statement)都是單獨的一個事務，語句執(zhí)行終了后自動提交。調(diào)用方不需求手工控制事務流程。2.顯示事務 Explicit Transactions調(diào)用方需求調(diào)用API或者運

5、用T-SQL的BEGIN HYPERLINK whatis.ctocio/searchwhatis/297/6092797.shtml t _bank TRANSACTION 語句來翻開事務。需求調(diào)用 HYPERLINK whatis.ctocio/searchwhatis/318/5947318.shtml t _bank COMMIT 或者 HYPERLINK whatis.ctocio/searchwhatis/218/6093718.shtml t _bank ROLLBACK TRANSACTION 來提交或者回滾。3.隱式事務 Implicit Transactions運用 HYP

6、ERLINK whatis.ctocio/searchwhatis/23/5949023.shtml t _bank SET IMPLICIT_TRANSACTIONS ON把事務方式變成隱式方式。調(diào)用方不需求執(zhí)行BEGIN TRANSACTION 語句來翻開事務。數(shù)據(jù)庫引擎執(zhí)行到SQL語句的時候自動翻開事務。調(diào)用方需求調(diào)用COMMIT 或者 ROLLBACK TRANSACTION 來提交或者回滾。當數(shù)據(jù)庫引擎執(zhí)行下一個SQL語句時又自動翻開一個新事務。參考:Controlling Transactions (Database Engine)Oracle的事務類型Oracle的事務處置類型有

7、點像SQL HYPERLINK whatis.ctocio/searchwhatis/497/5948997.shtml t _bank Server的隱式事務。當執(zhí)行到第一個可執(zhí)行的SQL語句時自動翻開事務，然后需求調(diào)用方執(zhí)行 HYPERLINK whatis.ctocio/searchwhatis/318/5947318.shtml t _bank commit或者 HYPERLINK whatis.ctocio/searchwhatis/218/6093718.shtml t _bank rollback來提交或者回滾事務，假設有DDL語句，Oracle也會自動提交事務的。參考: HYP

8、ERLINK whatis.ctocio/searchwhatis/297/6092797.shtml t _bank Transaction ManagementOracle的事務的實現(xiàn)Oracle的構造分邏輯上和物理上的區(qū)別。邏輯上的構造是表空間，而物理上的構造是數(shù)據(jù)文件。邏輯實現(xiàn)Oracle下實現(xiàn)事務在邏輯上是由Undo HYPERLINK whatis.ctocio/searchwhatis/458/6028458.shtml t _bank Tablespace來實現(xiàn)的。Undo Tablespace包含Undo Segements(段)，而Undo Segements包含Undo

9、HYPERLINK whatis.ctocio/searchwhatis/99/5947599.shtml t _bank Data。Undo Data是支持事務的邏輯單元。 HYPERLINK database.ctocio/TLimages/picview/?/imagelist/2021/282/08xwoop4pi2e.png t _blank 圖源自于Undo Data用于保管修正前后的數(shù)據(jù)，以支持回滾，長時間查詢，和flashback查詢，以及失敗事務恢復的功能。由于有了Undo Data，回滾事務變得容易了，由于Undo Data保管了修正前后的數(shù)據(jù)，保證了事務的原子性。失敗事務

10、恢復和事務回滾類似，當網(wǎng)絡 HYPERLINK whatis.ctocio/searchwhatis/404/7420404.shtml t _bank 中斷或者其他緣由導致事務異常停頓，數(shù)據(jù)庫引擎可以恢復到一致性形狀。同時Undo Data也支持長時間查詢(Read-consistent)，例如有表格T，主鍵為 HYPERLINK whatis.ctocio/searchwhatis/25/5948525.shtml t _bank key，有字段為f1，數(shù)據(jù)如下，雖然只需4條數(shù)據(jù)，假設需求很長時間進展查詢。事務一開場查詢，不斷沒終了，而事務二開場修正 HYPERLINK whatis.ct

11、ocio/searchwhatis/25/5948525.shtml t _bank key為1的數(shù)據(jù)為Z，事務二進展提交，數(shù)據(jù)變成下面的表格。但是事務一查詢終了的時候還是讀出A,B,C,D，由于查詢是從Undo中讀出快照。Flashback查詢是Oracle 10g引進的功能，可以查詢出提交之后修正之前的數(shù)據(jù)，例如上面例子事務三在事務二提交后想查詢出A,B,C,D可以經(jīng)過Flashback查詢來完成。這也是有Undo HYPERLINK whatis.ctocio/searchwhatis/99/5947599.shtml t _bank Data來支持的。物理實現(xiàn)從上面的邏輯實現(xiàn)看，只是知

12、道了事務以及Undo Data的作用，還不清楚Oracle對事務的支持究竟怎樣實現(xiàn)的。下面從物理構造上講述Oracle怎樣對事務進展支持。請先看一個物理構造圖。圖源自于為了簡化，我只是用個人的言語講述和事務有關的部件。SGA可以了解為全局內(nèi)存。其中 HYPERLINK whatis.ctocio/searchwhatis/132/5947632.shtml t _bank Database Buffer HYPERLINK whatis.ctocio/searchwhatis/459/5946959.shtml t _bank Cache存放的是從數(shù)據(jù)文件中讀取的數(shù)據(jù)緩存。紫色的圓柱體為數(shù)據(jù)文

13、件。Redo Log Buffer為重做日志緩存，也就是保管日志的內(nèi)存塊，一切的數(shù)據(jù)的修正都會記錄在Redo Log Buffer里面。例如用回事務二更新 HYPERLINK whatis.ctocio/searchwhatis/25/5948525.shtml t _bank key為1數(shù)據(jù)的例子。當事務二更新key為1數(shù)據(jù)從A到Z。Oracle數(shù)據(jù)庫引擎會把key為1的數(shù)據(jù)的 rowid，修正前數(shù)據(jù)A以及修正后數(shù)據(jù)Z都記錄在Redo Log Buffer里面。假設事務二繼續(xù)更新key為2的數(shù)據(jù)為Y，那么key為2的數(shù)據(jù)的rowid，修正前數(shù)據(jù)B以及修正后數(shù)據(jù)Y也記錄到Redo Log Bu

14、ffer里面。每一筆數(shù)據(jù)都記錄，而且是流水線性記錄。一旦事務二進展提交，LGWR HYPERLINK whatis.ctocio/searchwhatis/292/7333792.shtml t _bank 進程(Log Writer，日志寫進程)就會把Redo Log Buffer的數(shù)據(jù)按順序?qū)懙絃og Datafile里面，也就是日志數(shù)據(jù)文件里面，當寫日志文件完成，Oracle數(shù)據(jù)庫引擎會生成一個SCN( HYPERLINK whatis.ctocio/searchwhatis/494/6026494.shtml t _bank system change number，系統(tǒng)更新號)，到這

15、時候Oracle數(shù)據(jù)庫引擎會通知調(diào)用方提交完成了。這里可以看到Oracle在提交的時候不用要把更新數(shù)據(jù)寫回數(shù)據(jù)文件，而是寫到日志文件里面。由于順序?qū)懢€性的日志文件速度快很多，而寫數(shù)據(jù)文件是需求隨機讀寫。由于線性記錄和SCN號控制，數(shù)據(jù)庫引擎可以經(jīng)過redo log(重做日志文件)的操作得到最新的數(shù)據(jù)。當然在Checkpoint的時候數(shù)據(jù)文件是最終還是會更新的，只是說事務提交的時候更新數(shù)據(jù)文件不是必需步驟，這樣能很大的提高性能。由于這個機制，回滾變得很簡單，要讀沒提交前之前的SCN是很容易的事情。對于初學者關于數(shù)據(jù)庫事務處置的建議我本人也是重新手一步步走過來，如今也不是老鳥，算是有點閱歷，假設是

16、剛入門的同窗，他覺得有用就看一下，沒用就過了。對于數(shù)據(jù)庫事務的處置，開場的時候不需求很深化了解數(shù)據(jù)庫的原理，當然以后還是需求了解的，優(yōu)先級排后而已。高優(yōu)先級如下：首先，事務不是什么高深奧秘的東西，我從入行開場所做的一切系統(tǒng)，包括如今的嵌入式系統(tǒng)，都用到事務。我并不覺得大部分系統(tǒng)事務有什么問題，只是一些約束和同步機制，真的有問題從本身系統(tǒng)設計角度看，不一定說從數(shù)據(jù)庫技術角度去找處理方法。例如Oracle的長時間查詢假設Undo HYPERLINK whatis.ctocio/searchwhatis/99/5947599.shtml t _bank Data(歷史數(shù)據(jù))給覆蓋了，Oracle會拋

17、出異常ORA-01555: snapshot too old，假設出現(xiàn)這種問題，我會從本身系統(tǒng)設計角度入手，為什么有那么大的查詢，為什么在這個查詢中其他事務會更新數(shù)據(jù)，這些查詢能否只是查一次就夠了，查的過程能否需求鎖住表等等。然而這個問題可以經(jīng)過數(shù)據(jù)庫調(diào)優(yōu)處理，但是我想問題的角度首先是從本身系統(tǒng)設計出發(fā)。第二，要知道的是不同數(shù)據(jù)庫的事務類型的區(qū)別，例如MS SQL Sever是默許是自動提交事務，用的時候需求知道每個語句都有單獨的事務在操作。而Oracle是類似于隱式事務，必需手工 HYPERLINK whatis.ctocio/searchwhatis/318/5947318.shtml t _bank commit或者 HYPERLINK whatis.ctocio/searchwhatis