SQL 和Oracle對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的差異性.doc

SQL 和Oracle對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的差異性作者: Jake, 出處:IT專(zhuān)家網(wǎng)論壇,責(zé)任編輯: 陳子琪, 2009-10-11 13:00在吉日嘎拉的軟件編程走火入魔之：數(shù)據(jù)庫(kù)事務(wù)處理入門(mén)(適合初學(xué)者閱讀)文章中關(guān)于MS SQL Server和Oracle對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的差異性引起一些爭(zhēng)論，因此記錄我對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的想法。背景在吉日嘎拉的軟件編程走火入魔之：數(shù)據(jù)庫(kù)事務(wù)處理入門(mén)(適合初學(xué)者閱讀)文章中關(guān)于MS SQL Server和Oracle對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的差異性引起一些爭(zhēng)論，因此記錄我對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的想法。簡(jiǎn)介本文講述MS SQL Server和Oracle對(duì)數(shù)據(jù)庫(kù)事務(wù)處理的差異性，以及Oracle如何對(duì)事務(wù)處理的實(shí)現(xiàn)。什么是事務(wù)數(shù)據(jù)庫(kù)事務(wù)(Database Transaction)是一組數(shù)據(jù)庫(kù)操作的處理單元。事務(wù)符合ACID的特性：Atomic:原子性，要么全部要么一無(wú)所有。All or None.Consistent:一致性，所有依賴(lài)關(guān)系以及約束一致。Isolated:分離性，不同事務(wù)不互相影響。Durable:持久性，提交事務(wù)的數(shù)據(jù)需要持久化。為什么要使用事務(wù)實(shí)現(xiàn)事務(wù)主要有兩大功能：1.保證數(shù)據(jù)庫(kù)的consistent(一致性狀態(tài))，保持所有依賴(lài)關(guān)系以及約束一致)。哪怕數(shù)據(jù)庫(kù)管理系統(tǒng)出現(xiàn)故障時(shí)(例如斷電)，也能恢復(fù)到一致性狀態(tài)。例如一個(gè)銀行轉(zhuǎn)帳系統(tǒng)，張三給李四轉(zhuǎn)3000圓RMB，張三帳號(hào)上減3000和李四帳號(hào)上加3000需要同時(shí)完成，否則系統(tǒng)的帳就不平了。也例如有些銷(xiāo)售系統(tǒng)的匯總表和明細(xì)表，是一個(gè)主表和一個(gè)從表，需要同步更新。2.并發(fā)時(shí)分離不同事務(wù)操作。例如編輯過(guò)程中的數(shù)據(jù)不給其他事務(wù)查詢(xún)到。這也是相對(duì)的，在特效需求下可能要支持dirty read(臟讀)，但不是這里討論的范圍了。SQL Server 2008 的事務(wù)類(lèi)型1.自動(dòng)提交事務(wù) Autocommit Transactions這是SQL Server默認(rèn)的事務(wù)類(lèi)型，每一條單獨(dú)的SQL語(yǔ)句(SQL statement)都是單獨(dú)的一個(gè)事務(wù)，語(yǔ)句執(zhí)行完畢后自動(dòng)提交。調(diào)用方不需要手工控制事務(wù)流程。2.顯示事務(wù) Explicit Transactions調(diào)用方需要調(diào)用API或者使用T-SQL的BEGIN TRANSACTION 語(yǔ)句來(lái)打開(kāi)事務(wù)。需要調(diào)用COMMIT 或者 ROLLBACK TRANSACTION 來(lái)提交或者回滾。3.隱式事務(wù) Implicit Transactions使用SET IMPLICIT_TRANSACTIONS ON把事務(wù)模式變成隱式模式。調(diào)用方不需要執(zhí)行BEGIN TRANSACTION 語(yǔ)句來(lái)打開(kāi)事務(wù)。數(shù)據(jù)庫(kù)引擎執(zhí)行到SQL語(yǔ)句的時(shí)候自動(dòng)打開(kāi)事務(wù)。調(diào)用方需要調(diào)用COMMIT 或者 ROLLBACK TRANSACTION 來(lái)提交或者回滾。當(dāng)數(shù)據(jù)庫(kù)引擎執(zhí)行下一個(gè)SQL語(yǔ)句時(shí)又自動(dòng)打開(kāi)一個(gè)新事務(wù)。參考:Controlling Transactions (Database Engine)Oracle的事務(wù)類(lèi)型Oracle的事務(wù)處理類(lèi)型有點(diǎn)像SQL Server的隱式事務(wù)。當(dāng)執(zhí)行到第一個(gè)可執(zhí)行的SQL語(yǔ)句時(shí)自動(dòng)打開(kāi)事務(wù)，然后需要調(diào)用方執(zhí)行commit或者rollback來(lái)提交或者回滾事務(wù)，如果有DDL語(yǔ)句，Oracle也會(huì)自動(dòng)提交事務(wù)的。參考:Transaction ManagementOracle的事務(wù)的實(shí)現(xiàn)Oracle的結(jié)構(gòu)分邏輯上和物理上的區(qū)別。邏輯上的結(jié)構(gòu)是表空間，而物理上的結(jié)構(gòu)是數(shù)據(jù)文件。邏輯實(shí)現(xiàn)Oracle下實(shí)現(xiàn)事務(wù)在邏輯上是由Undo Tablespace來(lái)實(shí)現(xiàn)的。Undo Tablespace包含Undo Segements(段)，而Undo Segements包含Undo Data。Undo Data是支持事務(wù)的邏輯單元。圖源自于Oracle Database 10g: Administration Workshop IUndo Data用于保存修改前后的數(shù)據(jù)，以支持回滾，長(zhǎng)時(shí)間查詢(xún)，和flashback查詢(xún)，以及失敗事務(wù)恢復(fù)的功能。由于有了Undo Data，回滾事務(wù)變得容易了，因?yàn)閁ndo Data保存了修改前后的數(shù)據(jù)，保證了事務(wù)的原子性。失敗事務(wù)恢復(fù)和事務(wù)回滾類(lèi)似，當(dāng)網(wǎng)絡(luò)中斷或者其他原因?qū)е率聞?wù)異常停止，數(shù)據(jù)庫(kù)引擎可以恢復(fù)到一致性狀態(tài)。同時(shí)Undo Data也支持長(zhǎng)時(shí)間查詢(xún)(Read-consistent)，例如有表格T，主鍵為key，有字段為f1，數(shù)據(jù)如下，盡管只有4條數(shù)據(jù)，假設(shè)需要很長(zhǎng)時(shí)間進(jìn)行查詢(xún)。事務(wù)一開(kāi)始查詢(xún)，一直沒(méi)結(jié)束，而事務(wù)二開(kāi)始修改key為1的數(shù)據(jù)為Z，事務(wù)二進(jìn)行提交，數(shù)據(jù)變成下面的表格。但是事務(wù)一查詢(xún)結(jié)束的時(shí)候還是讀出A,B,C,D，因?yàn)椴樵?xún)是從Undo中讀出快照。Flashback查詢(xún)是Oracle 10g引進(jìn)的功能，可以查詢(xún)出提交之后修改之前的數(shù)據(jù)，例如上面例子事務(wù)三在事務(wù)二提交后想查詢(xún)出A,B,C,D可以通過(guò)Flashback查詢(xún)來(lái)完成。這也是有Undo Data來(lái)支持的。物理實(shí)現(xiàn)從上面的邏輯實(shí)現(xiàn)看，只是知道了事務(wù)以及Undo Data的作用，還不清楚Oracle對(duì)事務(wù)的支持到底怎么實(shí)現(xiàn)的。下面從物理結(jié)構(gòu)上講述Oracle怎么對(duì)事務(wù)進(jìn)行支持。請(qǐng)先看一個(gè)物理結(jié)構(gòu)圖。圖源自于Oracle Database 10g: Administration Workshop I為了簡(jiǎn)化，我只是用個(gè)人的語(yǔ)言講述和事務(wù)有關(guān)的部件。SGA可以理解為全局內(nèi)存。其中Database Buffer Cache存放的是從數(shù)據(jù)文件中讀取的數(shù)據(jù)緩存。紫色的圓柱體為數(shù)據(jù)文件。Redo Log Buffer為重做日志緩存，也就是保存日志的內(nèi)存塊，一切的數(shù)據(jù)的修改都會(huì)記錄在Redo Log Buffer里面。例如用回事務(wù)二更新key為1數(shù)據(jù)的例子。當(dāng)事務(wù)二更新key為1數(shù)據(jù)從A到Z。Oracle數(shù)據(jù)庫(kù)引擎會(huì)把key為1的數(shù)據(jù)的 rowid，修改前數(shù)據(jù)A以及修改后數(shù)據(jù)Z都記錄在Redo Log Buffer里面。如果事務(wù)二繼續(xù)更新key為2的數(shù)據(jù)為Y，那么key為2的數(shù)據(jù)的rowid，修改前數(shù)據(jù)B以及修改后數(shù)據(jù)Y也記錄到Redo Log Buffer里面。每一筆數(shù)據(jù)都記錄，而且是流水線性記錄。一旦事務(wù)二進(jìn)行提交，LGWR進(jìn)程(Log Writer，日志寫(xiě)進(jìn)程)就會(huì)把Redo Log Buffer的數(shù)據(jù)按順序?qū)懙絃og Datafile里面，也就是日志數(shù)據(jù)文件里面，當(dāng)寫(xiě)日志文件完成，Oracle數(shù)據(jù)庫(kù)引擎會(huì)生成一個(gè)SCN(system change number，系統(tǒng)更新號(hào))，到這時(shí)候Oracle數(shù)據(jù)庫(kù)引擎會(huì)通知調(diào)用方提交完成了。這里可以看到Oracle在提交的時(shí)候不必要把更新數(shù)據(jù)寫(xiě)回?cái)?shù)據(jù)文件，而是寫(xiě)到日志文件里面。因?yàn)轫樞驅(qū)懢€性的日志文件速度快很多，而寫(xiě)數(shù)據(jù)文件是需要隨機(jī)讀寫(xiě)。由于線性記錄和SCN號(hào)控制，數(shù)據(jù)庫(kù)引擎可以通過(guò)redo log(重做日志文件)的操作得到最新的數(shù)據(jù)。當(dāng)然在Checkpoint的時(shí)候數(shù)據(jù)文件是最終還是會(huì)更新的，只是說(shuō)事務(wù)提交的時(shí)候更新數(shù)據(jù)文件不是必須步驟，這樣能很大的提高性能。由于這個(gè)機(jī)制，回滾變得很簡(jiǎn)單，要讀沒(méi)提交前之前的SCN是很容易的事情。對(duì)于初學(xué)者關(guān)于數(shù)據(jù)庫(kù)事務(wù)處理的建議我自己也是從新手一步步走過(guò)來(lái)，現(xiàn)在也不是老鳥(niǎo)，算是有點(diǎn)經(jīng)驗(yàn)，如果是剛?cè)腴T(mén)的同學(xué)，你覺(jué)得有用就看一下，沒(méi)用就過(guò)了。對(duì)于數(shù)據(jù)庫(kù)事務(wù)的處理，開(kāi)始的時(shí)候不需要很深入了解數(shù)據(jù)庫(kù)的原理，當(dāng)然以后還是需要了解的，優(yōu)先級(jí)排后而已。高優(yōu)先級(jí)如下：首先，事務(wù)不是什么高深神秘的東西，我從入行開(kāi)始所做的所有系統(tǒng)，包括現(xiàn)在的嵌入式系統(tǒng)，都用到事務(wù)。我并不覺(jué)得大部分系統(tǒng)事務(wù)有什么問(wèn)題，只是一些約束和同步機(jī)制，真的有問(wèn)題從自身系統(tǒng)設(shè)計(jì)角度看，不一定說(shuō)從數(shù)據(jù)庫(kù)技術(shù)角度去找解決方法。例如Oracle的長(zhǎng)時(shí)間查詢(xún)?nèi)绻鸘ndo Data(歷史數(shù)據(jù))給覆蓋了，Oracle會(huì)拋出異常ORA-01555: snapshot too old”，如果出現(xiàn)這種問(wèn)題，我會(huì)從自身系統(tǒng)設(shè)計(jì)角度入手，為什么有那么大的查詢(xún)，為什么在這個(gè)查詢(xún)中其他事務(wù)會(huì)更新數(shù)據(jù)，這些查詢(xún)是否只是查一次就夠了，查的過(guò)程是否需要鎖住表等等。然而這個(gè)問(wèn)題可以通過(guò)數(shù)據(jù)庫(kù)調(diào)優(yōu)解決，但是我想問(wèn)題的角度首先是從自身系統(tǒng)設(shè)計(jì)出發(fā)。第二，要知道的是不同數(shù)據(jù)庫(kù)的事務(wù)類(lèi)型的區(qū)別，例如MS SQL Sever是默認(rèn)是自動(dòng)提交事務(wù)，用的時(shí)候需要知道每個(gè)語(yǔ)句都有單獨(dú)的事務(wù)在操作。而Oracle是類(lèi)似于隱式事務(wù)，必須手工comm