任何數據庫的主要要求之一就是實現可伸縮性。只有將爭用（鎖定）最小化（如果不能一起刪除），才可以實現。由于讀/寫/更新/刪除是數據庫中發生的一些主要的頻繁操作，因此對于這些操作并發進行而不被阻塞非常重要。為了實現這一目標，大多數主要數據庫都采用了一種稱為多版本并發控制的并發模型，該模型將爭用降低到最低限度。

什么是MVCC

多版本并發控制（以下簡稱MVCC）是一種算法，可通過維護同一對象的多個版本來提供精細的并發控制，以使READ和WRITE操作不會沖突。這里的WRITE表示UPDATE和DELETE，因為無論如何新插入的記錄都將按照隔離級別受到保護。每個WRITE操作都會生成對象的新版本，并且每個并發讀取操作都會根據隔離級別讀取對象的不同版本。由于讀取和寫入操作均在同一對象的不同版本上進行，因此這些操作都不需要完全鎖定，因此兩者都可以并發操作。爭用仍然存在的唯一情況是當兩個并發事務嘗試寫入同一記錄時。

當前大多數主要數據庫都支持MVCC。該算法的目的是維護同一對象的多個版本，因此MVCC的實現因數據庫而異，僅在創建和維護多個版本方面有所不同。因此，相應的數據庫操作和數據存儲發生了變化。

公認的實現MVCC的方法是PostgreSQL和Firebird / Interbase使用的一種，而InnoDB和Oracle使用的另一種。在隨后的章節中，我們將詳細討論如何在PostgreSQL和InnoDB中實現它。

PostgreSQL中的MVCC

為了支持多個版本，PostgreSQL維護每個對象的其他字段（PostgreSQL術語為Tuple），如下所述：

xmin –插入或更新元組的交易的交易ID。如果是UPDATE，則將使用此事務ID分配更新版本的元組。

xmax –刪除或更新元組的交易的交易ID。如果是UPDATE，則為元組的當前現有版本分配此事務ID。在新創建的元組上，此字段的默認值為null。

PostgreSQL將所有數據存儲在稱為HEAP的主存儲中（頁面的默認大小為8KB）。所有新元組都將xmin作為創建它的事務進行處理，而舊版本元組（已更新或刪除）將分配給xmax。從舊版本元組到新版本始終存在鏈接。在隔離的情況下，較舊版本的元組可用于在回滾的情況下重新創建元組，并可通過READ語句讀取較舊版本的元組。

考慮到表有兩個元組，T1（值1）和T2（值2），可以在下面的3個步驟中演示新行的創建：

MVCC：在PostgreSQL中存儲多個版本

如圖所示，數據庫中最初有兩個元組，其值分別為1和2。

然后，在第二步中，將值2的行T2更新為值3。這時，將使用新值創建一個新版本，并將其存儲為與現有元組相鄰并將其存儲在同一存儲區域中。在此之前，較舊的版本將分配給xmax并指向最新版本的元組。

類似地，在第三步中，當刪除具有值1的行T1時，將在同一位置虛擬刪除現有行（即，它為當前事務分配了xmax）。沒有為此創建新版本。

接下來，讓我們看一下每個操作如何創建多個版本，以及如何保持事務隔離級別而不用一些實際示例進行鎖定。在下面的所有示例中，默認情況下使用“ READ COMMITTED”隔離。

插入

每次插入記錄時，都會創建一個新的元組，并將其添加到屬于相應表的頁面之一中。

PostgreSQL并發INSERT操作

正如我們在這里看到的逐步：

會話A啟動事務并獲取事務ID 495。

會話B啟動事務并獲取事務ID 496。

會話A插入一個新的元組（獲取存儲在HEAP中）

現在，添加了將xmin設置為當前事務ID 495的新元組。

但是從會話B中看不到相同的內容，因為xmin（即495）仍未提交。

一旦提交。

數據對兩個會話均可見。

更新

PostgreSQL UPDATE不是“ IN-PLACE”更新，即它不會使用所需的新值來修改現有對象。相反，它將創建該對象的新版本。因此，UPDATE大致涉及以下步驟：

它將當前對象標記為已刪除。

然后，它添加該對象的新版本。

將對象的舊版本重定向到新版本。

因此，即使許多記錄保持不變，HEAP也會占用空間，就好像插入了多條記錄一樣。

PostgreSQL并發INSERT操作

正如我們在這里看到的逐步：

會話A啟動事務并獲取事務ID 497。

會話B啟動事務并獲取事務ID 498。

會話A更新現有記錄。

在這里，會話A看到一個版本的元組（更新的元組），而會話B看到另一個版本（舊元組，但xmax設置為497）。兩個元組版本都存儲在HEAP存儲中（甚至同一頁面也取決于空間可用性）

一旦會話A提交了事務，則由于提交了舊元組的xmax，舊元組將過期。

現在，兩個會話都看到相同版本的記錄。

刪除

刪除幾乎與UPDATE操作類似，只是它不必添加新版本。只是將當前對象標記為DELETED，如UPDATE情況中所述。

PostgreSQL并發DELETE操作

會話A啟動事務并獲取事務ID 499。

會話B啟動事務并獲取事務ID 500。

會話A刪除現有記錄。

在這里，會話A沒有看到任何從當前事務中刪除的元組。而Session-B看到該元組的較舊版本（xmax為499；刪除該記錄的事務）。

一旦會話A提交了事務，則由于提交了舊元組的xmax，舊元組將過期。

現在，兩個會話都看不到已刪除的元組。

如我們所見，沒有任何操作可以直接刪除對象的現有版本，并且在需要的地方都可以添加對象的其他版本。

現在，讓我們看一下如何在具有多個版本的元組上執行SELECT查詢：SELECT需要讀取所有版本的元組，直到根據隔離級別找到合適的元組為止。假設有元組T1，它已更新并創建了新版本T1'，并在更新時又創建了T1''：

SELECT操作將通過此表的堆存儲并首先檢查T1。如果提交了T1 xmax事務，則它將移至該元組的下一個版本。

假設現在也提交了T1'元組xmax，然后再次移動到該元組的下一個版本。

最后，它找到T1''并看到xmax未提交（或為null），并且根據隔離級別，T1''xmin對于當前事務可見。最后，它將讀取T1''元組。

如我們所見，它需要遍歷元組的所有3個版本，以便找到合適的可見元組，直到過期的元組被垃圾收集器（VACUUM）刪除為止。

InnoDB中的MVCC

為了支持多個版本，InnoDB為每行維護其他字段，如下所述：

DB_TRX_ID：插入或更新該行的事務的事務ID。

DB_ROLL_PTR：它也稱為回滾指針，它指向寫入回滾段的撤消日志記錄（在下一個更多內容中）。

與PostgreSQL一樣，InnoDB在所有操作中也會創建該行的多個版本，但是舊版本的存儲有所不同。

對于InnoDB，更改后的行的舊版本保存在單獨的表空間/存儲中（稱為undo段）。因此，與PostgreSQL不同，InnoDB在主存儲區中僅保留行的最新版本，而在undo段中保留較舊的行。還原段中的行版本用于回滾時的撤消操作，并根據隔離級別通過READ語句讀取舊版本的行。

考慮到表有兩行，T1（值1）和T2（值2），可以通過以下3個步驟來演示新行的創建：

MVCC：在InnoDB中存儲多個版本

從圖中可以看出，數據庫中最初有兩行，其值分別為1和2。

然后在第二階段中，將值2的行T2更新為值3。這時，將使用新值創建新版本，并替換舊版本。在此之前，較舊的版本將存儲在撤消段中（請注意，UNDO段版本僅具有增量值）。另外，請注意，回滾段中有一個從新版本到舊版本的指針。因此，與PostgreSQL不同，InnoDB更新是“ IN-PLACE”。

類似地，在第三步中，當刪除具有值1的行T1時，則在主存儲區域中虛擬刪除了現有行（即，它只是在行中標記了一個特殊位），并在其中添加了與之對應的新版本。撤消段。同樣，從主存儲器到撤消段只有一個滾動指針。

從外部看，所有操作的行為均與PostgreSQL相同。只是多個版本的內部存儲有所不同。

MVCC：PostgreSQL與InnoDB

現在，讓我們分析PostgreSQL和InnoDB在MVCC實現方面的主要區別是什么：

舊版本的大小
PostgreSQL只是在元組的較舊版本上更新xmax，因此較舊版本的大小與相應的插入記錄相同。這意味著，如果您有3個版本的舊元組，則所有版本都將具有相同的大小（除非每次更新時實際數據大小有所不同）。
而對于InnoDB，存儲在Undo段中的對象版本通常小于相應的插入記錄。這是因為僅將更改的值（即差分）寫入UNDO日志。

INSERT操作
即使對于INSERT，InnoDB也需要在UNDO段中寫入一條額外的記錄，而PostgreSQL僅在UPDATE的情況下才創建新版本。

在回滾的情況下還原舊版本
PostgreSQL不需要任何特定的東西就可以在回滾的情況下恢復舊版本。請記住，舊版本的xmax等于更新此元組的事務。因此，在提交該事務ID之前，對于并發快照，它被視為活動元組。事務回滾后，所有事務都會自動將相應的事務視為活動事務，因為這將是中止的事務。
而對于InnoDB，則明確要求在回滾發生后重建對象的舊版本。

回收舊版本占用的空間
對于PostgreSQL，僅當沒有并行快照讀取該版本時，才可以將較早版本占用的空間視為已耗盡。舊版本失效后，VACUUM操作可以回收它們所占用的空間。VACUUM可以手動觸發，也可以作為后臺任務觸發，具體取決于配置。
InnoDB UNDO日志主要分為INSERT UNDO和UPDATE UNDO。相應的事務提交后，第一個將被丟棄。第二個需要保留，直到與任何其他快照平行為止。InnoDB沒有顯式的VACUUM操作，但是在類似的行上，它具有異步的PURGE來丟棄作為后臺任務運行的UNDO日志。

延遲真空的影響
如前所述，在PostgreSQL的情況下延遲真空會產生巨大的影響。即使不斷刪除記錄，它也會導致表開始膨脹并導致存儲空間增加。它也可能達到需要完全抽真空的地步，這是非常昂貴的操作。

如果表格過大，則順序掃描
PostgreSQL順序掃描必須遍歷對象的所有舊版本，即使所有舊版本都已失效（直到使用真空將其刪除為止）。這是PostgreSQL中最典型且討論最多的問題。請記住，PostgreSQL將所有版本的元組存儲在同一存儲中。
而對于InnoDB，除非需要，否則不需要讀取撤消記錄。如果所有撤消記錄都已失效，則僅足以讀取對象的所有最新版本。

指數
PostgreSQL將索引存儲在單獨的存儲中，該存儲與HEAP中的實際數據保持一個鏈接。因此，即使INDEX不變，PostgreSQL也必須更新INDEX部分。盡管稍后通過實現HOT（僅堆元組）更新解決了此問題，但仍然存在一個局限，即如果無法在同一頁面中容納新的堆元組，則它將回退到常規UPDATE。
InnoDB沒有問題，因為它們使用聚集索引。

結論

PostgreSQL MVCC沒有什么缺點，特別是在工作負載頻繁更新/刪除的情況下，存儲空間過大。因此，如果您決定使用PostgreSQL，則應該非常小心地配置VACUUM。

PostgreSQL社區也承認這是一個主要問題，他們已經開始研究基于UNDO的MVCC方法（臨時名稱為ZHEAP），我們可能會在以后的版本中看到同樣的情況。

總結

以上是生活随笔為你收集整理的postgresql select for update 多行加锁顺序_PostgreSQL和Mysql的MVCC实现机制的差异对比的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。