MySQL中 LBCC 和 MVCC 的理解及常見問題範例

1. 事務

介紹MVCC之前，先介紹下事務：事務是為了保證資料庫中資料的完整性和一致性。

事務的4個基本要素：

• 原子性（Atomicity）：要麼同時成功，要麼同時失敗。（通過undo log回滾紀錄檔實現）
• 一致性（Consistency）：一方扣款 xxx 元，另一方收款 xxx 元，符合事物發展的正常邏輯（通過lock鎖實現）
• 隔離性（Isolation）：此時有多個類似 扣款/收款 事件同時發生，每個事件之間是相互獨立的（通過 lock鎖 + MVCC實現）
• 永續性（Durability）：不管資料庫宕機或重啟，資料最終都落到了磁碟上，下次載入依然可見 （通過 redo log實現）

2. MVCC初探

目的：主要是為了 提高資料庫並行效能。用更好的方式去處理 讀/寫 衝突，做到即使有 讀/寫 衝突時，也能做到不加鎖，非阻塞並行讀。

不同隔離級別下，可能引發的問題： 髒讀：並行情況下，一方事務讀到了另一方事務 “已 update 但未 commit” 的資料，破壞了事務隔離性。不可重複讀：並行情況下，一方事務讀到了另一方事務 “已 update 或 delete ，並 commit ” 的資料，破壞了事務隔離性。幻讀：並行情況下，一方事務讀到了另一方事務" insert 並 commit "的資料，導致前後讀取結果不一致。

MVCC中的四種事務隔離級別：

提問：V1、V2、V3在不同事務隔離級別下讀取到的值分別是：

• RU-讀未提交 級別：20、20、20（可能發生：髒讀、不可重複讀）
• RC-讀已提交 級別：18、20、20（不可能發生：髒讀、可能發生：不可重複度）
• RR-可重複讀 級別：18、18、20 （不可能發生：髒讀、不可重複讀；但是因為事務A已提交，所以V3再次查詢時跟事務A是沒有隔離性的要求的，因此V3讀取到的是20）

3. LBCC & MVCC

•  LBCC（Lock-Base Concurrency Control）基於鎖的並行控制；
• MVCC（Multiversion Concurrency Control）多版本並行控制；

LBCC 鎖相關：

MySQL 5.5 版本之前，預設的儲存引擎是MyISAM，5.5之後預設引擎是Innodb。Innodb支援事務，包括：行鎖/表鎖，MyISAM不支援。 意向鎖 意向共用鎖/讀鎖（表鎖型別，無法手動建立），mysql 中語法： lock in share mode意向排它鎖/寫鎖（表鎖型別，無法手動建立），mysql 中語法： for update

常見問題：為什麼要加入意向鎖？

意向鎖並不是真正用來鎖定資料的，而是用來告訴你當前表中是否已經有了被 共用鎖/排它鎖
鎖定的資料行。如果有就沒必要再去加無用的表鎖了，起到一個標識作用，提高加表鎖的效率（相當於高鐵洗手間門上方是否有人正在使用的 “指示燈”）。

記錄鎖（Record Lock）、間隙鎖（Gap Lock）、臨鍵鎖（Next-Key Lock）：

• 介紹：臨鍵鎖 = 記錄鎖 + 間隙鎖，是 RR 可重複讀-隔離級別下獨有的，
• 目的：間隙鎖的出現就是為了解決可重複讀隔離級別下的幻讀問題

問題：如圖示：執行此sql語句（先開啟事務）：BEGIN; SELECT * FROM tbl WHERE id > 15 FOR UPDATE; ，以下兩個sql語句可以執行成功嗎？

MVCC底層實現詳解：

快照讀（實際上為讀相關的操作）：讀取的是記錄的可見版本 (有可能是歷史版本)，不用加鎖。

簡單的 SELECT 操作，屬於快照讀，不加鎖。

SELECT * FROM user WHERE ? 

當前讀（實際上為寫相關的操作）：在事務中，update 資料前，還要去MySQL中重新讀取一遍該資料對應最新版本的記錄，並且 當前讀 返回的記錄都會加上鎖，保證其他事務不會再並行修改這條記錄。以下兩種方式都屬於當前讀，需要加鎖：

• 特殊讀 (加鎖讀): SELECT * FROM user WHERE id = xxx LOCK IN SHARE MODE;
• INSERT / UPDATE / DELETE 等寫操作。

問題：在 RR-可重複讀 的預設隔離級別下，假設起始的age為18，那麼Q1和Q2對應的age分別是多少呢？

• 針對 “事務B” 分析：因為存在 UPDATE 寫操作，觸發了 當前讀，所以要先去讀最新提交的版本號記錄（即：事務C UPDATE 後提交的記錄），然後事務B再去執行自己的 UPDATE 操作。也就是要先去讀事務C提交的最新資料為19，然後事務B自身再 UPDATE 加1最終變為20。
• 針對 “事務A” 分析：因為事務A本身是沒有任何的寫操作，僅僅是 SELECT 查詢操作，觸發 快照讀。所以事務A只認準事務 BEGIN 開始之前記錄的 最新最後提交的版本號，其記錄值也就是初始的18。

• BEGIN 事務開始的時候會建立一個快照，併為對應事務分配一個事務id，即 TRX_ID
• 開啟事務之前最後的版本號為：up_limit_id=999，對應 age=18
• 事務B和事務C都有 UPDATE 操作（當前讀），所以 row_trx_id 為自身的 TRX_ID 的值，分別是1001和1002。而事務A沒有 UPDATE 操作（快照讀），所以只認準事務A在 事務開始前 最後的版本號 up_limit_id=999，其 age=18。

總結

• 事務：是為了保證資料庫中資料的完整性和一致性。事務的4個特性：ACID。
• MVCC的好處：提高資料庫並行效能。用更好的方式去處理 讀/寫 衝突，做到即使有 讀/寫 衝突時，也能做到不加鎖，非阻塞並行讀。
• MVCC四種隔離級別 ：讀未提交、讀已提交、可重複讀（MySQL預設級別）、序列化。
• MVCC事務隔離級別中，常見的三種問題：髒讀、幻讀、不可重複讀。在RR的預設隔離級別下，單純的 SELECT 只觸發 “快照讀” 。而當你包含 INSERT / UPDATE / DELETE 等 寫操作 時，這時就會觸發 當前讀，也就是在事務中，在相關寫操作之前會再去讀取一次其他事務的最後提交記錄。這裡的關鍵在於你事務中的sql是單純的 SELECT 語句（快照讀），還是你事務在的sql是包含了INSERT / UPDATE / DELETE 等 寫操作（當前讀）。
• 沒有建立索引或索引失效，行鎖會升級為表鎖，因為找不到對應行記錄。所以為了避免兩個事務同時修改一張表的不同記錄會導致表鎖的問題，建議加上索引，這樣就只是行鎖，而不會升級為表鎖！
• 幻讀的解決關鍵在於 間隙鎖 和 臨鍵鎖（臨鍵鎖 = 記錄鎖 + 間隙鎖） 。

最後，補充一個問題點：

如果不宣告的建立主鍵，會有哪些危害？ 比如你的id（假設int型別）沒有宣告為主鍵，並且也沒有宣告唯一索引（當未宣告主鍵時，唯一索引會被取代為主鍵）

• 行鎖升級為表鎖
• 當資料量達到頂峰的時候，可能會造成“主鍵衝突”，int的取值範圍為2^32 -1，當未宣告主鍵時，達到最大值範圍時，id會再次重新從0開使自增，這時候可能會出現覆蓋之前row_id記錄的情況，造成資料丟失。相反的，如果宣告主鍵的話，那麼當id達到上限時，再次insert時會報“主鍵衝突”錯誤，這時候可以將之前的int 型別的id改為big int。
• MySQL會自動宣告一個“隱藏主鍵 row_id”，佔6位元組。而你自己宣告int型別的主鍵時，只會消耗4位元組。因此這是一種資源的浪費！

到此這篇關於MySQL中 LBCC 和 MVCC 的理解及常見問題範例的文章就介紹到這了,更多相關MySQL中LBCC和 MVCC內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！