MySql(三) MySql中的鎖機制

湖人總冠軍發表於2018-09-08
MySql
前面兩篇部落格中簡單的聊了下mysql中的索引，今天聊聊mysql(InnoDB引擎)中的鎖以及事務的實現

MySql(一) 淺析MySql索引

MySQL(二) MySql常用優化


講到鎖大家應該都不陌生。像是Java中常見的採用CAS演算法實現的樂觀鎖，典型的例子就是原子類，通過CAS自旋實現原子操作的更新，悲觀鎖通常都是Synchronized和Lock實現。

樂觀鎖與悲觀鎖

  • 樂觀鎖：每次讀資料的時候都認為其他人不會修改，所以不會上鎖，而是在更新的時候去判斷在此期間有沒有其他人更新了資料，可以使用版本號機制。在資料庫中可以通過為資料表增加一個版本號欄位實現。讀取資料時將版本號一同讀出，資料每次更新時對版本號加一。當我們更新的時候，判斷資料庫表對應記錄的當前版本號與第一次取出來的版本號值進行比對，如果值相等，則予以更新，否則認為是過期資料。樂觀鎖適用於多讀的應用型別，可以提高吞吐量。
  • 悲觀鎖：每次讀資料的時候都認為別人會修改，所以每次在讀資料的時候都會上鎖，這樣別人想讀這個資料時就會被阻塞。MySQL中就用到了很多這種鎖機制，比如行鎖，表鎖等，讀鎖，寫鎖等，都是在操作之前先上鎖。


共享鎖與排他鎖

  • 共享鎖：共享鎖又叫做讀鎖或S鎖，加上共享鎖後在事務結束之前其他事務只能再加共享鎖、只能對其進行讀操作不能寫操作，除此之外其他任何型別的鎖都不能再加了。

# 加上lock in share mode
SELECT description FROM book_book lock in share mode;複製程式碼

  • 排他鎖：排他鎖又叫寫鎖或X鎖，某個事務對資料加上排他鎖後，只能這個事務對其進行讀寫，在此事務結束之前，其他事務不能對其加任何鎖，可以讀取,不能進行寫操作，需等待其釋放。

# 加上for update
SELECT description FROM book_book for update; 
複製程式碼


行鎖與表鎖

行鎖與表鎖區別在於鎖的粒度，在Innodb引擎中既支援行鎖也支援表鎖(MyISAM引擎只支援表鎖)，只有通過索引條件檢索資料InnoDB才使用行級鎖，否則，InnoDB將使用表鎖。
  • 表鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突概率高，併發度最低
  • 行鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖衝突的概率低，併發度高

這裡有個比較疑惑的地方，為什麼表鎖不會出現死鎖？在MyISAM中由於沒有事務，一條SQL執行完鎖就釋放了，不會迴圈等待，所以只會出現阻塞而不會發生死鎖。但是在InnoDB中有事務就比較疑惑了，希望有了解的小夥伴指點指點@-@

下面舉兩個例子說明上面幾種鎖：

# 事務1
BEGIN;
SELECT description FROM book_book where name = 'JAVA程式設計思想' lock in share mode;

# 事務2
BEGIN;
UPDATE book_book SET name = 'new book' WHERE name = 'new';

# 檢視事務狀態
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

trx_id  trx_state       trx_started           trx_tables_locked    trx_rows_locked
39452	LOCK WAIT	2018-09-08 19:01:39	    1	                1	
282907511143936	RUNNING	2018-09-08 18:58:47	    1	                38	
複製程式碼

事務1給book表加上了共享鎖，事務2嘗試修改book表發生了阻塞，檢視事務狀態可以知道事務一由於沒有走索引使用了表鎖。

# 事務1
BEGIN;
SELECT description FROM book_book WHERE id = 2 lock in share mode;

# 事務2
BEGIN;
UPDATE book_book SET name = 'new book' WHERE id = 1; 

# 檢視事務狀態
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

trx_id          trx_state   trx_started     trx_tables_locked    trx_rows_locked
39454	        RUNNING	2018-09-08 19:10:44	1	                1	
282907511143936	RUNNING	2018-09-08 19:10:35	1	                1	
複製程式碼

事務1給book表加上了共享鎖，事務2嘗試修改book表並沒有發生阻塞。這是由於事務一和事務二都走了索引，所以使用的是行鎖，並不會發生阻塞。


意向鎖(InnoDB特有)

意向鎖的意義在於方便檢測表鎖和行鎖之間的衝突
  • 意向鎖：意向鎖是一種表級鎖，代表要對某行記錄進行操作。分為意向共享鎖(IS)和意向排他鎖(IX)。
  • 行鎖和表鎖之間的衝突：事務A給表中的某一行加了共享鎖，讓這一行只能讀不能寫。之後事務B申請整個表的排他鎖。如果事務B申請成功，那麼它就能修改表中的任意一行，這與A持有的行鎖是衝突的。InnoDB引入了意向鎖來判斷它們之間的衝突。
    • 沒有意向鎖的情況：1、判斷表是否已被其他事務用表鎖鎖表。2、判斷表中的每一行是否已被行鎖鎖住，這樣要遍歷整個表，效率很低。
    • 意向鎖存在的情況：1、判斷表是否已被其他事務用表鎖鎖表。2、判斷表上是否有意向鎖
  • 意向鎖存在時申請鎖：申請意向鎖的動作是資料庫完成的，上述例子中事務A申請一行的行鎖的時候，資料庫會自動先開始申請表的意向鎖，當事務B申請表的排他鎖時檢測到存在意向鎖則會阻塞。
  • 意向鎖會不會存在衝突： 意向鎖之間不會衝突, 因為意向鎖只是代表要對某行記錄進行操作。


各種鎖之間的共存情況

       IX     IS       X      S
IX    相容    相容    衝突    衝突
IS    相容    相容    衝突    相容
X     衝突    衝突    衝突    衝突
S     衝突    相容    衝突    相容
複製程式碼


死鎖

  • 概念：兩個或兩個以上的事務在執行過程中，因爭奪資源而造成的一種互相等待的現象。
  • 存在條件：1、 互斥條件：一個資源每次只能被一個事務使用。2、 請求與保持條件：一個事務因請求資源而阻塞時，對已獲得的資源保持不放。3、不剝奪條件：已獲得的資源，在末使用完之前不能強行剝奪。4、迴圈等待條件：形成一種頭尾相接的迴圈等待關係
  • 解除正在死鎖的狀態：撤銷其中一個事務


MVCC(多版本併發控制)

MVCC使得InnoDB更好的實現事務隔離級別中的REPEATABLE READ
  •  它使得InnoDB不再單純的使用行鎖來進行資料庫的併發控制，取而代之的是把資料庫的行鎖與行的多個版本結合起來，只需要很小的開銷,就可以實現非鎖定讀，從而大大提高資料庫系統的併發效能。
  • 實現：InnoDB實現MVCC的方法是它為每一行儲存三個額外的隱藏欄位
    • 1.DB_TRX_ID：一個6byte的標識，每處理一個事務，其值自動+1 ，可以通過語句“show engine innodb status”來查詢
    • 2.DB_ROLL_PTR: 大小是7byte,指向寫到rollback segment（回滾段）的一條undo log記錄 
    • 3.DB_ROW_ID: 大小是6byte,該值隨新行插入單調增加。
  • SELECT：返回的行資料需要滿足的條件：  1、資料行的建立版本號必須小於等於事務的版本2、行的刪除版本號(行中的特殊位被設定為將其標記為已刪除)一定是未定義的或者大於當前事務的版本號，確定了當前事務開始之前行沒有被刪除。
  • INSERT：InnoDB為每個新增行記錄當前系統版本號作為建立版本號。
  • DELETE：InnoDB為每個刪除行的記錄當前系統版本號作為行的刪除版本號。
  • UPDATE：InnoDB複製了一條資料。這條資料的版本號使用了系統版本號。它也把系統版本號作為老資料的刪除號。
  • 說明：這裡的讀是不加鎖的select等，MVCC實現可重複讀使用的是讀取undo中的已經提交的資料，是非阻塞的。insert操作時"建立時間"=DB_ROW_ID，這時"刪除時間"是未定義的；update時，複製新增行的"建立時間"=DB_ROW_ID，刪除時間未定義，舊資料行"建立時間"不變，刪除時間=該事務的DB_ROW_ID； delete操作，相應資料行的"建立時間"不變，刪除時間=該事務的DB_ROW_ID；


間隙鎖(Next-Key鎖)

間隙鎖使得InnoDB解決幻讀問題，加上MVCC使得InnoDB的RR隔離級別實現了序列化級別的效果，並且保留了比較好的併發效能。

定義：當我們用範圍條件檢索資料時請求共享或排他鎖時，InnoDB會給符合條件的已有資料的索引加鎖；對於鍵值在條件範圍內但並不存在的記錄，叫做間隙(GAP)，InnoDB也會對這個"間隙"加鎖，這種鎖機制就是間隙鎖。

例如：book表中存在bookId 1-80，90-99的記錄。SELECT * FROM book WHERE bookId < 100 FOR UPDATE。InnoDB不僅會對bookId值為1-80，90-99的記錄加鎖，也會對bookId在81-89之間(這些記錄並不存在)的間隙加鎖。這樣就能避免事務隔離級別可重複讀下的幻讀。






有問題的同學可以指出相互探討，如需轉載請註明出處。
參考文獻：
https://dev.mysql.com/doc/refman/5.7/en/innodb-multi-versioning.html
https://www.cnblogs.com/chenpingzhao/p/5065316.html


相關文章