一文學習MySQL 意向共用鎖、意向排他鎖、死鎖

2022-03-29 19:02:19

一、InnoDB表級鎖

我們知道，InnoDB是支援行鎖，但不是每次都獲取行鎖，如果不使用索引的，那還是獲取的表鎖。而且有的時候，我們希望直接去使用表鎖

在絕大部分情況下都應該使用行鎖，因為事務的並行效率比表鎖更高，但個別情況下也使用表級鎖：

事務需要更新大部分或全部資料，表又比較大，如果使用預設的行鎖，給大部分行都加鎖（此時不如直接加表鎖），不僅這個事務執行效率低，而且可能造成其他事務長時間等待和鎖衝突
事務涉及多個表，比較複雜，如果都用行鎖，很可能引起死鎖，造成大量事務回滾

當我們希望獲取表鎖時，可以使用以下命令：

LOCK TABLE user READ  -- 獲取這張表的讀鎖
LOCK TABLE user WRITE -- 獲取這張表的寫鎖
事務執行…
COMMIT/ROLLBACK;      -- 事務提交或者回滾
UNLOCK TABLES;        -- 本身自帶提交事務，釋放執行緒佔用的所有表鎖

在使用表鎖的時候，涉及到效率的問題：
如果我們要獲取一張表的排它鎖X，最起碼得確定，這張表沒有被其他事務獲取過S鎖或X鎖，以及這張表沒有任何行被其他事務獲取過行S或X鎖

假如這張表有1000萬個資料，那我怎麼知道這1000萬行哪些有行鎖哪些沒有行鎖呢？

除了挨個檢查，沒有更好的辦法，這就導致效率低下的問題

我們這裡學習的意向共用鎖和意向排他鎖就是用來解決，由於需要加表鎖而去挨個遍歷資料，確定是否有某些資料被加了行鎖，而導致的效率低下問題

二、意向共用鎖和意向排他鎖

為了可以更快速的獲取表鎖

意向共用鎖（IS鎖）：事務計劃給記錄加行共用鎖，事務在給一行記錄加共用鎖前，必須先取得該表的IS鎖
意向排他鎖（IX鎖）：事務計劃給記錄加行排他鎖，事務在給一行記錄加排他鎖前，必須先取得該表的IX鎖

在加行鎖之前，由InnoDB儲存引擎加上表的IS或IX鎖
意向鎖之間都相容，不會產生衝突
意向鎖存在的意義是為了更高效的獲取表鎖（表格中的X、S、IX、IS指的是表鎖，不是行鎖）
意向鎖是表級鎖，協調錶鎖和行鎖的共存關係，主要目的是顯示事務正在鎖定某行或者試圖鎖定某行。

分析事務1獲取行X鎖和事務2獲取表S鎖：

首先事務1需要給表的第10行資料加X鎖，於是InnoDB儲存引擎自動給整張表加上了IX鎖。當事務2再想獲取整張表的S鎖時，看到這張表已經有別的事務獲取了IX鎖了，就說明這張表肯定有某些資料被加上了X鎖，這就導致事務2不能給整張表加S鎖了。此時事務2只能等待，無法成功獲取表S鎖

三、死鎖

1. 資料庫中的死鎖

MyISAM 表鎖是 deadlock free 的，這是因為 MyISAM 不支援事務，只支援表鎖，而且總是一次獲得所需的全部鎖，要麼全部滿足，要麼等待，因此不會出現死鎖。如果是處理多張表，還是可能出現死鎖問題的

在 InnoDB 中，除單個 SQL 組成的事務外，鎖是逐步獲得的，即鎖的粒度比較小（行鎖），這就決定了在 InnoDB 中發生死鎖是可能的

死鎖問題一般都是我們自己的應用造成的，和多執行緒程式設計的死鎖情況相似，大部分都是由於我們多個執行緒在獲取多個鎖資源的時候，獲取的順序不同而導致的死鎖問題。因此我們應用在對資料庫的多個表做更新的時候，不同的程式碼段，應對這些表按相同的順序進行更新操作，以防止鎖衝突導致死鎖問題