Oracle、MySQL、DB2并發控制機制的異同是什么

這篇文章主要介紹“Oracle、MySQL、DB2并發控制機制的異同是什么”，在日常操作中，相信很多人在Oracle、MySQL、DB2并發控制機制的異同是什么問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Oracle、MySQL、DB2并發控制機制的異同是什么”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

數據庫的數據一致性支持機制：事務、鎖、日志。

首先我們看看什么是事務。

一、事務

事務：又稱為交易，訪問數據庫系統的可恢復的最小單元。

1、事務的ACID

• 原子性(Atomicity)：事務為一個整體的工作單元，事務對數據庫的操作要么全部執行，要么全部取消;

• 一致性(Consistency)：事務完成時，所有數據都保持一致狀態;

• 隔離性(Isolation)：事務所做的修改必須與其他事務所做的修改隔離。事務查看數據時數據的狀態要么為其他事務修改之前要么為其他事務修改之后，不會為中間狀態。即多個事務不能同時修改同一份數據;

• 持久性(Durability)：事務提交后，對數據庫所做的修改會永久保存。

2、事務的初始化和終止

事務在可執行的SQL第一次執行時會自動初始化，事務一旦初始化，就必須終止(COMMIT或ROLLBACK)。

1)關于事務的COMMIT和ROLLBACK

多數情況下，事務通過執行COMMIT或ROLLBACK終止事務。執行COMMIT語句后，事務初始化后對數據庫做出的所有改變都會變成永久的;執行ROLLBACK語句后，事務初始化后對數據庫做出的所有改變都會被撤銷，數據庫返回事務開始之前的狀態。

2)關于不成功的事務的結果

上面說了當事務被COMMIT或ROLLBACK終止語句后會發生什么，如果事務完成之前系統發生故障，會發生什么?這種情況下，數據庫管理器將撤銷所有未COMMIT的修改，從而恢復數據的一致性。

DB2中通過ACTIVE  LOG日志文件實現撤銷修改。日志文件包含關于事務執行的每個語句的信息，以及事務是否被成功COMMIT或ROLLBACK的信息。

MySQL和Oracle利用undo log撤銷修改。undo  log記錄了行的修改操作，執行事務中由于某種原因失敗，或使用ROLLBACK時，就可以利用undo log將數據恢復到修改之前的樣子。

3、事務的隔離級別

1)潛在問題

事務為什么需要多種可以設置的隔離級別呢?通常，鎖可以實現并發操作中事務的隔離，保證數據的一致性。鎖提高了并發性能，但會帶來潛在的問題：

• 臟讀：當前事務可以讀到另外一個事務中未提交的數據。

• 不可重復讀：在一個事務內讀到的同一條數據是不一樣的。

• 幻讀：事務A在相同條件下第二次讀取時讀到新插入的數據。

• 丟失更新：一個事務的更新操作會被另一個事務的更新操作所覆蓋，從而導致數據的不一致。 例如：

• 事務T1將行記錄修改為V1，事務T1未提交。

• 事務T2將行記錄修改為V2，事務T2未提交。

• 事務T1提交。

• 事務T2提交。

在當前數據庫的鎖機制下不會導致理論意義上的丟失更新問題，但是實際上在所有多用戶計算機系統環境下都有可能產生這個問題。例如：

• 事務T1查詢一行數據，放入本地內存，顯示給User1。

• 事務T2查詢一行數據，放入本地內存，顯示給User2。

• User1修改這行記錄，更新數據庫并提交。

• User2修改這行記錄，更新數據庫并提交。

這些問題往往和系統數據庫的使用方式和形態有關。而設置事務的隔離級別，就是根據不同的場景來解決以上問題。比如上面所說的丟失更新問題，隔離級別中SELECT…FOR  UPDATE即帶有更新意圖讀的時候，步驟1、2都是要上寫鎖的，避免丟失更新的問題。下面詳解數據庫的隔離級別及其加鎖方式。

2)數據庫的隔離級別及其加鎖方式

① SQL標準定義的四個隔離級別

• READ UNCOMMITTED：未提交讀。事務可以看到其他事務所有未提交的數據。讀取數據不加鎖;

• READ COMMITTED：提交讀。事務只可以看到其他事務已經提交的數據;

• REPEATABLE READ：重復度。鎖定事務引用的符合檢索條件的部分行，其他事務不可修改這些行，但可執行INSERT操作。即可能出現幻讀;

• SERIALIZABLE：可串行化。強制的進行排序，在每個讀數據行上添加鎖，所有事務依次逐個執行，事務之間不會產生干擾。事務提交后釋放鎖。會導致大量超時現象和鎖競爭。

② 四種隔離級別會導致的問題

③ 數據庫中的隔離級別

DB2中的隔離級別：

• CS(Cursor  Stability)：游標穩定性。逐行鎖定數據，該行數據未修改時，鎖定解除，繼續加鎖讀取下一行，該行數據有修改時，則該行鎖定持續到事務終止。CS的程序不能查看其他程序未COMMIT的更改。

CS提供了最大的并發性。但同一事務同一游標被處理兩次，可能返回不同的結果，即不可重復度;CS程序讀取的行上有任何可更新游標時，其他任何應用程序都不能更新或刪除該行。

CS是DB2默認的隔離級別。在需要最大并行性但只能看到其他程序已COMMIT的數據時使用。

• RR(Repeatable  Read)：可重復讀。RR會鎖定事務引用的所有行，直到COMMIT。其他程序不能修改該數據，如果一條數據被訪問兩次，返回相同的結果。

RR是最高隔離級別，可以最好的保證數據一致性，但是大量鎖定數據，會導致并發度大大降低，同時有可能超過系統定義的持有鎖數量的限制。

相當于標準定義隔離級別中的SERIALIZABLE相比，上鎖范圍一致。

• RS(Read  Stability)：讀穩定性。RS會鎖定事務引用的所有行中符合檢索條件的部分行。其他程序不可修改，但可執行INSERT操作，所以同一事務中，如果數據被訪問兩次可能返回新插入的數據，即幻讀，但是舊數據不會有改變。

相比RR，RS鎖定數據的數量大大減少，并發度得到提升。比較適合在并發環境下運行，但只適合在同一事物中不會多次發出相同查詢，或不要求相同查詢獲得相同結果的程序，避免發生幻讀。

DB2的RS和標準定義隔離級別中的REPEATABLE READ(重復讀)類似，避免了臟讀，但是會出現幻讀問題。

• UR(Uncommitted  Read)：未提交讀，也就是“臟”讀。UR不會加任何鎖，可以讀數據庫中的任何數據，包含已修改但未COMMIT的數據。讀的數據可能與真實的數據有一定差距。

UR級別最常用于只讀表上的查詢，或者只執行查詢且不關心能否讀到其他程序未COMMIT的數據時常用。

UR相當于標準定義隔離級別中的READ UNCOMMITTED(未提交讀)。

MySQL支持標準定義的四種隔離級別，默認的隔離級別為REPEATABLE  READ(重復度)，但是與標準SQL不同的是，MySQL的InnoDB存儲引擎在REPEATABLE READ的隔離級別下，使用Next-Key  Lock(鎖定一個范圍，并鎖定記錄本身)，因此避免幻讀的產生。所以說InnoDB存儲引擎在REPEATABLE  READ的隔離級別下已經能保證事務的隔離性要求，即達到SQL標準的SERIALIZABLE隔離級別。

Oracle數據庫支持READ COMMITTED(提交讀)和SERIALIZABLE這兩種事務隔離級別。默認的隔離級別是READ  COMMITTED(提交讀)。

二、鎖

事務隔離級別是并發控制的整體解決方案，其實際上是綜合利用各種類型的鎖和行版本控制來解決并發問題。

這里我們主要看數據庫中的基本鎖。

1、鎖的類型

• S-LOCK：共享鎖。又叫讀鎖，當用戶要進行數據的讀取時，對數據加上共享鎖。共享鎖可以同時加多個;

• X-LOCK：排他鎖。又叫寫鎖。SQL  INSERT/UPDATE/DELETE語句執行時會上X-LOCK。排他鎖只可以加一個，和其他的排他鎖共享鎖都相斥;

• U-LOCK：修改鎖。CURSOR SELECT 有UPDATE OF 子句時，FETCH時對讀出的記錄，會上U-LOCK。

DB2、MySQL、Oracle都支持S-LOCK和X-LOCK，DB2還支持U-LOCK。

2、事務隔離級別中讀數據時的鎖類型

如上，數據庫在各種隔離級別下，SQL執行INSERT/UPDATE/DELETE語句時都會上X-LOCK，那么在讀數據時如何上鎖呢?

DB2和MySQL在Uncommitted Read隔離級別下，不加任何鎖。

1)DB2

DB2在另外三種CS、RR、RS隔離級別時，SELECT語句，或CURSOR SELECT無UPDATE  OF子句，FETCH時對讀出的記錄會上S-LOCK，不同的是，CS在讀取下一行數據時就釋放上一行的鎖，RR、RS在事務提交時才釋放鎖;SELET…FOR  UPDATE對讀取的數據都是加U鎖，CS在讀取下一行數據時就釋放上一行的鎖，RR、RS在事務提交時才釋放鎖;INSERT/UPDATE/DELETE語句執行時會上X-LOCK，CS、RR、RS都是在事務提交時才釋放X鎖，其他事務不能對已鎖定的行加任何鎖。

2)MySQL

MySQL的InnoDB在隔離級別READ COMMITED 和 REPEATABLE  READ(MySQL的默認隔離級別)下SELECT時不上鎖，即MySQL中的一致性非鎖定讀;只有指定SELECT…LOCK IN SHARE  MOAD才對記錄上S-LOCK，SERIALIZABLE隔離級別下SELECT對記錄上S-LOCK;三種隔離級別下，SELET…FOR  UPDATE對讀取的數據都是加X鎖，在MySQL中叫做一致性鎖定讀。

3)Oracle

Oracle中只支持READ COMMITED和SERIALIZABLE隔離級別。這兩種隔離級別下的鎖機制和InnoDB一致。Oracle中不需要READ  UNCOMMITTED隔離級別，是因為READ UNCOMMITTED主要功能是提高只讀時的并發性，而Oracle在READ  COMMITED隔離級別下使用一致性非鎖定讀也有同樣的功能。

3、一致性非鎖定讀

隔離級別READ COMMITED 和 REPEATABLE READ(MySQL的默認隔離級別)都使用一致性非鎖定讀，  SELECT時不上鎖，那么如何保證事務的隔離性呢?這兩種隔離級別采用快照數據的方式保證隔離性。讀取時對于上了X鎖的數據，都會去讀取行的一個快照數據。快照數據是指該行的之前版本的數據，通過undo段實現。而undo段用來在事務中回滾數據，因此快照數據本身沒有額外的開銷。

READ COMMITED 和 REPEATABLE  READ兩種隔離級別在讀快照數據時的區別是，RC總是讀取最新的快照數據，所以可能會發生不可重復讀，即第二次讀取的數據和第一次不一致;而RR總是讀取事務開始時的快照，所以不會發生不可重復度。

非鎖定讀機制不會等待行上X鎖的釋放，極大的提高了數據庫的并發性。是InnoDB的默認讀取方式。

三、小結

并發控制在保證數據一致性的前提下提供最大的并發性，而保證數據一致性的前提就是保證事務的隔離性，事務的隔離性和并發性是成反比的，隔離級別越高，并發性越低。所以程序要視并發性和隔離性的輕重選擇隔離級別。

到此，關于“Oracle、MySQL、DB2并發控制機制的異同是什么”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！