文章目錄

• 鎖概述
• 鎖分類
• MyISAM 表鎖
• InnoDB 行鎖
• 事務及其ACID屬性
• InnoDB 的行鎖模式
• 注意
• MVCC
• InnoDB 中的 MVCC
• 參考
• 你的鼓勵也是我創作的動力

• Posted by 微博@Yangsc_o
• 原創文章，著作權宣告：自由轉載-非商用-非衍生-保持署名 | Creative Commons BY-NC-ND 3.0

鎖概述

鎖是計算機協調多個行程或執行緒并發訪問某一資源的機制（避免爭搶），
在資料庫中，除傳統的計算資源（如 CPU、RAM、I/O 等）的爭用以外，資料也是一種供許多用戶共享的資源，如
何保證資料并發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發訪問性能的
一個重要因素，從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加復雜

鎖分類

• 從對資料操作的粒度分

1） 表鎖：操作時，會鎖定整個表，
2） 行鎖：操作時，會鎖定當前操作行，

• 從對資料操作的型別分

1） 讀鎖（共享鎖）：針對同一份資料，多個讀操作可以同時進行而不會互相影響
2） 寫鎖（排它鎖）：當前操作沒有完成之前，它會阻斷其他寫鎖和讀鎖

• Mysql 鎖

相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的存盤引擎支持不同的鎖機制，下表中羅
列出了各存盤引擎對鎖的支持情況：

• MySQL這3種鎖的特性可大致歸納如下 ：

  • 表鎖

  偏向MyISAM 存盤引擎，開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖沖突的概率最高,并發度最低，

  • 行鎖

  偏向InnoDB 存盤引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高，

  • 頁面鎖

  開銷和加鎖時間界于表鎖和行鎖之間；會出現死鎖；鎖定粒度界于表鎖和行鎖之間，并發度一般

MyISAM 表鎖

• 對MyISAM 表的讀操作，不會阻塞其他用戶對同一表的讀請求，但會阻塞對同一表的寫請求；
• 對MyISAM 表的寫操作，則會阻塞其他用戶對同一表的讀和寫操作；

簡而言之，就是讀鎖會阻塞寫，但是不會阻塞讀，而寫鎖，則既會阻塞讀，又會阻塞寫，

• 此外，MyISAM 的讀寫鎖調度是寫優先，這也是MyISAM不適合做寫為主的表的存盤引擎的原因，因為寫鎖后，其他執行緒不能做任何操作，大量的更新會使查詢很難得到鎖，從而造成永遠阻塞，

InnoDB 行鎖

• 行鎖特點 ：偏向InnoDB 存盤引擎，開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖沖突的概率最低,并發度也最高，
• InnoDB 與 MyISAM 的最大不同有兩點：一是支持事務；二是 采用了行級鎖，

事務及其ACID屬性

• 事務是由一組SQL陳述句組成的邏輯處理單元，事務具有以下4個特性，簡稱為事務ACID屬性，
  
• 并發事務處理帶來的問題
  
• 事務隔離級別
  • 為了解決上述提到的事務并發問題，資料庫提供一定的事務隔離機制來解決這個問題，資料庫的事務隔離越嚴格，并發副作用越小，但付出的代價也就越大，因為事務隔離實質上就是使用事務在一定程度上“串行化” 進行，這顯然
    與“并發” 是矛盾的，
  • 資料庫的隔離級別有4個，由低到高依次為Read uncommitted、Read committed、Repeatable read、
    Serializable，這四個級別可以逐個解決臟寫、臟讀、不可重復讀、幻讀這幾類問題，
    
    Mysql 的資料庫的默認隔離級別為 Repeatable read；

InnoDB 的行鎖模式

InnoDB 實作了以下兩種型別的行鎖，

• 共享鎖（S）：又稱為讀鎖，簡稱S鎖，共享鎖就是多個事務對于同一資料可以共享一把鎖，都能訪問到資料，但是只能讀不能修改，
• 排他鎖（X）：又稱為寫鎖，簡稱X鎖，排他鎖就是不能與其他鎖并存，如一個事務獲取了一個資料行的排他鎖，其他事務就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務是可以對資料就行讀取和修改，

對于UPDATE、DELETE和INSERT陳述句，InnoDB會自動給涉及資料集加排他鎖（X)；對于普通SELECT陳述句，InnoDB不會加任何鎖；

• 無索引行鎖升級為表鎖

如果不通過索引條件檢索資料，那么InnoDB將對表中的所有記錄加鎖，實際效果跟表鎖一樣，

• 間隙鎖危害

當我們用范圍條件，而不是使用相等條件檢索資料，并請求共享或排他鎖時，InnoDB會給符合條件的已有資料進行加鎖； 對于鍵值在條件范圍內但并不存在的記錄，叫做 “間隙（GAP）” ，InnoDB也會對這個 “間隙” 加鎖，這種鎖機制就是所謂的間隙鎖（Next-Key鎖）

注意

• InnoDB存盤引擎由于實作了行級鎖定，雖然在鎖定機制的實作方面帶來了性能損耗可能比表鎖會更高一些，但是在整體并發處理能力方面要遠遠由于MyISAM的表鎖的，當系統并發量較高的時候，InnoDB的整體性能和MyISAM相比就會有比較明顯的優勢，

• 但是，InnoDB的行級鎖同樣也有其脆弱的一面，當我們使用不當的時候，可能會讓InnoDB的整體性能表現不僅不
  能比MyISAM高，甚至可能會更差，

1. 盡可能讓所有資料檢索都能通過索引來完成，避免無索引行鎖升級為表鎖，
2. 合理設計索引，盡量縮小鎖的范圍
3. 盡可能減少索引條件，及索引范圍，避免間隙鎖
4. 盡量控制事務大小，減少鎖定資源量和時間長度
5. 盡可使用低級別事務隔離（但是需要業務層面滿足需求）

MVCC

MVCC (Multiversion Concurrency Control) 中文全程叫多版本并發控制，是現代資料庫（包括 MySQL、Oracle、PostgreSQL 等）引擎實作中常用的處理讀寫沖突的手段，目的在于提高資料庫高并發場景下的吞吐性能，

• 如此一來不同的事務在并發程序中，SELECT 操作可以不加鎖而是通過 MVCC 機制讀取指定的版本歷史記錄，并通過一些手段保證保證讀取的記錄值符合事務所處的隔離級別，從而解決并發場景下的讀寫沖突，

InnoDB 中的 MVCC

• InnoDB MVCC 實作原理的關鍵

1、事務版本號
2、表的隱藏列，
3、undo log
4、 read view

InnoDB 中 MVCC 的實作方式為：每一行記錄都有兩個隱藏列：DATA_TRX_ID、DATA_ROLL_PTR（如果沒有主鍵，則還會多一個隱藏的主鍵列），

• DATA_TRX_ID

記錄最近更新這條行記錄的事務 ID，大小為 6 個位元組

• DATA_ROLL_PTR

表示指向該行回滾段（rollback segment）的指標，大小為 7 個位元組，InnoDB 便是通過這個指標找到之前版本的資料，該行記錄上所有舊版本，在 undo 中都通過鏈表的形式組織，

• DB_ROW_ID

行標識（隱藏單調自增 ID），大小為 6 位元組，如果表沒有主鍵，InnoDB 會自動生成一個隱藏主鍵，因此會出現這個列，另外，每條記錄的頭資訊（record header）里都有一個專門的 bit（deleted_flag）來表示當前記錄是否已經被洗掉，

• 組織 Undo Log 鏈
  
• 如何實作一致性讀 —— ReadView
  InnoDB 為了解決這個問題，設計了 ReadView（可讀視圖）的概念，
  ReadView ：每個 SELECT 陳述句開始時，會生成一致性視圖，ReadView 中是當前活躍的事務 ID 串列，稱之為 m_ids，其中最小值為 up_limit_id，最大值為low_limit_id，事務 ID 是事務開啟時 InnoDB 分配的，其大小決定了事務開啟的先后順序，因此我們可以通過 ID 的大小關系來決定版本記錄的可見性；

1. 如果被訪問版本的 trx_id 小于 m_ids 中的最小值 up_limit_id，說明生成該版本的事務在 ReadView 生成前就已經提交了，所以該版本可以被當前事務訪問，
2. 如果被訪問版本的 trx_id 大于 m_ids 串列中的最大值 low_limit_id，說明生成該版本的事務在生成 ReadView 后才生成，所以該版本不可以被當前事務訪問，需要根據 Undo Log 鏈找到前一個版本，然后根據該版本的 DB_TRX_ID 重新判斷可見性，
3. 如果被訪問版本的 trx_id 屬性值在 m_ids 串列中最大值和最小值之間（包含），那就需要判斷一下 trx_id 的值是不是在 m_ids 串列中，如果在，說明創建 ReadView 時生成該版本所屬事務還是活躍的，因此該版本不可以被訪問，需要查找 Undo Log 鏈得到上一個版本，然后根據該版本的 DB_TRX_ID 再從頭計算一次可見性；如果不在，說明創建 ReadView 時生成該版本的事務已經被提交，該版本可以被訪問，
4. 此時經過一系列判斷我們已經得到了這條記錄相對 ReadView 來說的可見結果，此時，如果這條記錄的 delete_flag 為 true，說明這條記錄已被洗掉，不回傳，否則說明此記錄可以安全回傳給客戶端，

參考

MySQL InnoDB MVCC 機制的原理及實作

你的鼓勵也是我創作的動力

打賞地址

標籤：MySQL

上一篇：mysql 存盤程序中雙重回圈嵌套的應用

下一篇：50個SQL陳述句(MySQL版) 問題九