## **MySQL的鎖機制** ## 本文腦圖  ## 鎖種類 Mysql中鎖的分類按照不同類型的劃分可以分成不同的鎖，按照**「鎖的粒度」**劃分可以分成：**「表鎖、頁鎖、行鎖」**；按照**「使用的方式」**劃分可以分為：**「共享鎖」**和**「排它鎖」**；按照思想的劃分：**「樂觀鎖」**和**「悲觀鎖」**。 下面我們對著這幾種劃分的鎖進行詳細的解說和介紹，在了解設計者設計鎖的概念的同時，也能深入的理解設計者的設計思想。 **「表鎖」**是粒度最大的鎖，開銷小，加鎖快，不會出現死鎖，但是由于粒度太大，因此造成鎖的沖突幾率大，并發性能低。 Mysql的**「MyISAM儲存引擎就支持表鎖」**，MyISAM的表鎖模式有兩種：**「表共享讀鎖」**和**「表獨占寫鎖」**。 當一個線程獲取到MyISAM表的讀鎖的時候，會阻塞其他用戶對該表的寫操作，但是不會阻塞其它用戶對該用戶的讀操作。 相反的，當一個線程獲取到MyISAM表的寫鎖的時候，就會阻塞其它用戶的讀寫操作對其它的線程具有排它性。 **「頁鎖」**的粒度是介于行鎖和表鎖之間的一種鎖，因為頁鎖是在BDB中支持的一種鎖機制，也很少沒人提及和使用，所以這里制作概述，不做詳解。 **「行鎖」**是粒度最小的鎖機制，行鎖的加鎖開銷性能大，加鎖慢，并且會出現死鎖，但是行鎖的鎖沖突的幾率低，并發性能高。 行鎖是InnoDB默認的支持的鎖機制，MyISAM不支持行鎖，這個也是InnoDB和MyISAM的區別之一。 行鎖在使用的方式上可以劃分為：**「共享讀鎖（S鎖）**「和」**排它寫鎖（X鎖）」**。 當一個事務對MySQL中的一條數據行加上了S鎖，當前事務不能修改該行數據只能執行讀操作，其他事務只能對該行數據加S鎖不能加X鎖。 若是一個事務對一行數據加了X鎖，該事務能夠對該行數據執行讀和寫操作，其它事務不能對該行數據加任何的鎖，既不能讀也不能寫。 **「悲觀鎖和樂觀鎖是在很多框架都存在的一種思想，不要狹義地認為它們是某一種框架的鎖機制」**。 數據庫管理系統中為了控制并發，保證在多個事務執行時的數據一致性以及事務的隔離性，使用悲觀鎖和樂觀鎖來解決并發場景下的問題。 Mysql的**「悲觀鎖的實現是基于MySQL自身的鎖機制實現，而樂觀鎖需要程序員自己去實現的鎖機制」**，最常見的樂觀鎖實現就鎖機制是**「使用版本號實現」**。 樂觀鎖設計思想的在`CAS`的運用也是比較經典，之前我寫過一篇關于CAS的文章，大家感興趣的可以參考這一篇\[深入剖析AQS和CAS，看了都說好\]。 從上面的介紹中說了每一種鎖的概念，但是很難說哪一種鎖就是最好的，鎖沒有最好的，只有哪種業務場景最適合哪種鎖，具體業務具體分析。 下面我們就具體基于MySQL的存儲引擎詳細的分析每一種鎖在存儲引擎中的運用和實現。 ## MyISAM MyISAM中默認支持的表級鎖有兩種：**「共享讀鎖」**和**「獨占寫鎖」**。表級鎖在MyISAM和InnoDB的存儲引擎中都支持，但是InnoDB默認支持的是行鎖。 MySQL中平時讀寫操作都是隱式的進行加鎖和解鎖操作，Mysql已經自動幫我們實現加鎖和解鎖操作了，若是想要測試鎖機制，我們就要顯示的自己控制鎖機制。 MySQL中可以通過以下sql來顯示的在事務中顯式的進行加鎖和解鎖操作： ~~~cpp // 顯式的添加表級讀鎖 LOCK TABLE 表名 READ // 顯示的添加表級寫鎖 LOCK TABLE 表名 WRITE // 顯式的解鎖（當一個事務commit的時候也會自動解鎖） unlock tables; ~~~ 下面我們就來測試一下MyISAM中的表級鎖機制，首先創建一個測試表`employee` ，這里要指定存儲引擎為MyISAM，并插入兩條測試數據： ~~~csharp CREATE TABLE IF NOT EXISTS employee ( id INT PRIMARY KEY auto_increment, name VARCHAR(40), money INT )ENGINE MyISAM INSERT INTO employee(name, money) VALUES('黎杜', 1000); INSERT INTO employee(name, money) VALUES('非科班的科班', 2000); ~~~ 查看一下，表結果如下圖所示：  image ### MyISAM表級寫鎖 （1）與此同時再開啟一個session窗口，然后在第一個窗口執行下面的sql，在session1中給表添加寫鎖： ~~~undefined LOCK TABLE employee WRITE ~~~ （2）可以在session2中進行查詢或者插入、更新該表數據，可以發現都會處于等待狀態，也就是session1鎖住了整個表，導致session2只能等待：  （3）在session1中進行查詢、插入、更新數據，都可以執行成功：  **「總結：」** 從上面的測試結果顯示**「當一個線程獲取到表級寫鎖后，只能由該線程對表進行讀寫操作，別的線程必須等待該線程釋放鎖以后才能操作」**。 ### MyISAM表級共享讀鎖 （1）接下來測試一下表級共享讀鎖，同樣還是利用上面的測試數據，第一步還是在session1給表加讀鎖。  （2）然后在session1中嘗試進行插入、更新數據，發現都會報錯，只能查詢數據。  （3）最后在session2中嘗試進行插入、更新數據，程序都會進入等待狀態，只能查詢數據，直到session1解鎖表session2才能插入、更新數據。  **「總結：」** 從上面的測試結果顯示**「當一個線程獲取到表級讀鎖后，該線程只能讀取數據不能修改數據，其它線程也只能加讀鎖，不能加寫鎖」**。 ### MyISAM表級鎖競爭情況 MyISAM存儲引擎中，可以通過查詢變量來查看并發場景鎖的爭奪情況，具體執行下面的sql語句： ~~~dart show status like 'table%'; ~~~  主要是查看`table_locks_waited`和`table_locks_immediate`的值的大小分析鎖的競爭情況。 `Table_locks_immediate`：表示能夠立即獲得表級鎖的鎖請求次數；`Table_locks_waited`表示不能立即獲取表級鎖而需要等待的鎖請求次數分析，**「值越大競爭就越嚴重」**。 ### 并發插入 通過上面的操作演示，詳細的說明了表級共享鎖和表級寫鎖的特點。但是在平時的執行sql的時候，這些**「解鎖和釋放鎖都是Mysql底層隱式的執行的」**。 上面的演示只是為了證明顯式的執行事務的過程共享鎖和表級寫鎖的加鎖和解鎖的特點，實際并不會這么做的。 在我們平時執行select語句的時候就會隱式的加讀鎖，執行增、刪、改的操作時就會隱式的執行加寫鎖。 MyISAM存儲引擎中，雖然讀寫操作是串行化的，但是它也支持并發插入，這個需要設置內部變量`concurrent_insert`的值。 它的值有三個值`0、1、2`。可以通過以下的sql查看`concurrent_insert`的默認值為**「AUTO(或者1)」**。  concurrent\_insert的值為`NEVER (or 0)`表示不支持比并發插入；值為`AUTO(或者1）`表示在MyISAM表中沒有被刪除的行，運行另一個線程從表尾插入數據；值為`ALWAYS (or 2)`表示不管是否有刪除的行，都允許在表尾插入數據。 ### 鎖調度 MyISAM存儲引擎中，**「假如同時一個讀請求，一個寫請求過來的話，它會優先處理寫請求」**，因為MyISAM存儲引擎中認為寫請求比讀請求重要。 這樣就會導致，**「假如大量的讀寫請求過來，就會導致讀請求長時間的等待，或者"線程餓死"，因此MyISAM不適合運用于大量讀寫操作的場景」**，這樣會導致長時間讀取不到用戶數據，用戶體驗感極差。 當然可以通過設置`low-priority-updates`參數，設置請求鏈接的優先級，使得Mysql優先處理讀請求。 ## InnoDB InnoDB和MyISAM不同的是，InnoDB支持**「行鎖」**和**「事務」**，行級鎖的概念前面以及說了，這里就不再贅述，事務的四大特性的概述以及實現的原理可以參考這一篇\[\]。 InnoDB中除了有**「表鎖」**和**「行級鎖」**的概念，還有Gap Lock（間隙鎖）、Next-key Lock鎖，**「間隙鎖主要用于范圍查詢的時候，鎖住查詢的范圍，并且間隙鎖也是解決幻讀的方案」**。 InnoDB中的行級鎖是**「對索引加的鎖，在不通過索引查詢數據的時候，InnoDB就會使用表鎖」**。 **「但是通過索引查詢的時候是否使用索引，還要看Mysql的執行計劃」**，Mysql的優化器會判斷是一條sql執行的最佳策略。 若是Mysql覺得執行索引查詢還不如全表掃描速度快，那么Mysql就會使用全表掃描來查詢，這是即使sql語句中使用了索引，最后還是執行為全表掃描，加的是表鎖。 若是對于Mysql的sql執行原理不熟悉的可以參考這一篇文章\[\]。最后是否執行了索引查詢可以通過`explain`來查看，我相信這個大家都是耳熟能詳的命令了。 ### InnoDB行鎖和表鎖 InnoDB的行鎖也是分為行級**「共享讀鎖（S鎖）**「和」**排它寫鎖（X鎖）」**，原理特點和MyISAM的表級鎖兩種模式是一樣的。 若想顯式的給表加行級讀鎖和寫鎖，可以執行下面的sql語句： ~~~csharp // 給查詢sql顯示添加讀鎖 select ... lock in share mode; // 給查詢sql顯示添加寫鎖 select ... for update； ~~~ （1）下面我們直接進入鎖機制的測試階段，還是創建一個測試表，并插入兩條數據： ~~~csharp // 先把原來的MyISAM表給刪除了 DROP TABLE IF EXISTS employee; CREATE TABLE IF NOT EXISTS employee ( id INT PRIMARY KEY auto_increment, name VARCHAR(40), money INT )ENGINE INNODB; // 插入測試數據 INSERT INTO employee(name, money) VALUES('黎杜', 1000); INSERT INTO employee(name, money) VALUES('非科班的科班', 2000); ~~~ （2）創建的表中可以看出對表中的字段只有id添加了主鍵索引，接著就是在session1窗口執行`begin`開啟事務，并執行下面的sql語句： ~~~csharp // 使用非索引字段查詢，并顯式的添加寫鎖 select * from employee where name='黎杜' for update; ~~~ （3）然后在session2中執行update語句，上面查詢的是id=1的數據行，下面update的是id=2的數據行，會發現程序也會進入等待狀態： ~~~bash update employee set name='ldc' where id =2; ~~~ 可見若是**「使用非索引查詢，直接就是使用的表級鎖」**，鎖住了整個表。  （4）若是session1使用的是id來查詢，如下圖所示：  （5）那么session2是可以成功update其它數據行的，但是這里我建議使用數據量大的表進行測試，因為前面我說過了**「是否執行索引還得看Mysql的執行計劃，對于一些小表的操作，可能就直接使用全表掃描」**。  （6）還有一種情況就是：假如我們給name字段也加上了普通索引，那么通過普通索引來查詢數據，并且查詢到多行數據，那它是鎖這多行數據還是鎖整個表呢？ 下面我們來測試一下，首先給**「name字段添加普通索引」**，如下圖所示：  （6）并插入一條新的數據name值與id=2的值相同，并顯式的加鎖，如下若是：  （7）當update其它數據行name值不是ldc的也會進入等待狀態，并且通過explain來查看是否name='ldc'有執行索引，可以看到sql語句是有執行索引條件的。   結論：從上面的測試鎖機制的演示可以得出以下幾個結論： 1. 執行非索引條件查詢執行的是表鎖。 2. 執行索引查詢是否是加行鎖，還得看Mysql的執行計劃，可以通過explain關鍵字來查看。 3. 用普通鍵索引的查詢，遇到索引值相同的，也會對其他的操作數據行的產生影響。 ### InnoDB間隙鎖 當我們使用范圍條件查詢而不是等值條件查詢的時候，InnoDB就會給符合條件的范圍索引加鎖，在條件范圍內并不存的記錄就叫做"間隙（GAP）" 大家大概都知道在事務的四大隔離級別中，不可重復讀會產生幻讀的現象，只能通過提高隔離級別到串行化來解決幻讀現象。 但是Mysql中的不可重復是已經解決了幻讀問題，它通過引入間隙鎖的實現來解決幻讀，通過給符合條件的間隙加鎖，防止再次查詢的時候出現新數據產生幻讀的問題。 例如我們執行下面的sql語句，就會對id大于100的記錄加鎖，在id>100的記錄中肯定是有不存在的間隙： ~~~csharp Select * from employee where id> 100 for update; ~~~ （1）接著來測試間隙鎖，新增一個字段num，并將num添加為普通索引、修改之前的數據使得num之間的值存在間隙，操作如下sql所示： ~~~csharp alter table employee add num int not null default 0; update employee set num = 1 where id = 1; update employee set num = 1 where id = 2; update employee set num = 3 where id = 3; insert into employee values(4,'kris',4000,5); ~~~  （2）接著在session1的窗口開啟事務，并執行下面操作：  （3）同時打開窗口session2，并執行新增語句： ~~~csharp insert into employee values(5,'ceshi',5000,2); // 程序出現等待 insert into employee values(5,'ceshi',5000,4); // 程序出現等待 insert into employee values(5,'ceshi',5000,6); // 新增成功 insert into employee values(6,'ceshi',5000,0); // 新增成功 ~~~ **「從上面的測試結果顯示在區間（1,3\]U\[3,5)之間加了鎖，是不能夠新增數據行，這就是新增num=2和num=4失敗的原因，但是在這個區間以外的數據行是沒有加鎖的，可以新增數據行」**。 根據索引的有序性，而普通索引是可以出現重復值，那么當我們第一個sesson查詢的時候只出現一條數據num=3，為了解決第二次查詢的時候出現幻讀，也就是出現兩條或者更多num=3這樣查詢條件的數據。 Mysql在滿足where條件的情況下，給`（1,3]U[3,5)`區間加上了鎖不允許插入num=3的數據行，這樣就解決了幻讀。 這里拋出幾種情況接著來測試間隙鎖。主鍵索引（唯一索引）是否會加上間隙鎖呢？范圍查詢是否會加上間隙鎖？使用不存在的檢索條件是否會加上間隙鎖？ 先來說說：**「主鍵索引（唯一索引）是否會加上間隙鎖呢？」** 因為主鍵索引具有唯一性，不允許出現重復，那么當進行等值查詢的時候id=3，只能有且只有一條數據，是不可能再出現id=3的第二條數據。 因此它只要鎖定這條數據（鎖定索引），在下次查詢當前讀的時候不會被刪除、或者更新id=3的數據行，也就保證了數據的一致性，所以主鍵索引由于他的唯一性的原因，是不需要加間隙鎖的。 再來說說第二個問題：**「范圍查詢是否會加上間隙鎖？」** 直接在session1中執行下面的sql語句，并在session2中在這個num>=3的查詢條件內和外新增數據： ~~~csharp select * from employee where num>=3 for update; insert into employee values(6,'ceshi',5000,2); // 程序出現等待 insert into employee values(7,'ceshi',5000,4); // 程序出現等待 insert into employee values(8,'ceshi',5000,1); // 新增數據成功 ~~~ 我們來分析以下原理：單查詢num>=3的時候，在現有的employee表中滿足條件的數據行，如下所示： | id | num | | --- | --- | | 3 | 3 | | 4 | 5 | | 5 | 6 | 那么在設計者的角度出發，我為了解決幻讀的現象：在num>=3的條件下是必須加上間隙鎖的。 而在小于num=3中，下一條數據行就是num=1了，為了防止在（1，3\]的范圍中加入了num=3的數據行，所以也給這個間隙加上了鎖，這就是添加num=2數據行出現等待的原因。 最后來說一說：**「使用不存在的檢索條件是否會加上間隙鎖？」** 假如是查詢num>=8的數據行呢？因為employee表并不存在中num=8的數據行，num最大num=6，所以為了解決幻讀（6，8\]與num>=8也會加上鎖。 說到這里我相信很多人已經對間隙鎖有了清晰和深入的認識，可以說是精通了，又可以和面試官互扯了。 假如你是第一次接觸Mysql的鎖機制，第一次肯定是懵的，建議多認真的看幾遍，跟著案例敲一下自己深刻的去體會，慢慢的就懂了。 ## 死鎖 死鎖在InnoDB中才會出現死鎖，MyISAM是不會出現死鎖，因為MyISAM支持的是表鎖，一次性獲取了所有的鎖，其它的線程只能排隊等候。 而InnoDB默認支持行鎖，獲取鎖是分步的，并不是一次性獲取所有的鎖，因此在鎖競爭的時候就會出現死鎖的情況。 雖然InnoDB會出現死鎖，但是并不影響InnoDB成為最受歡迎的存儲引擎，MyISAM可以理解為串行化操作，讀寫有序，因此支持的并發性能低下。 ### 死鎖案例一 舉一個例子，現在數據庫表employee中六條數據，如下所示：  其中name=ldc的有兩條數據，并且name字段為普通索引，分別是id=2和id=3的數據行，現在假設有兩個事務分別執行下面的兩條sql語句： ~~~csharp // session1執行 update employee set num = 2 where name ='ldc'; // session2執行 select * from employee where id = 2 or id =3; ~~~ 其中session1執行的sql獲取的數據行是兩條數據，假設先獲取到第一個id=2的數據行，然后cpu的時間分配給了另一個事務，另一個事務執行查詢操作獲取了第二行數據也就是id=3的數據行。 當事務2繼續執行的時候獲取到id=3的數據行，鎖定了id=3的數據行，此時cpu又將時間分配給了第一個事務，第一個事務執行準備獲取第二行數據的鎖，發現已經被其他事務獲取了，它就處于等待的狀態。 當cpu把時間有分配給了第二個事務，第二個事務準備獲取第一行數據的鎖發現已經被第一個事務獲取了鎖，這樣就行了死鎖，兩個事務彼此之間相互等待。 ### 死鎖案例二 第二種死鎖情況就是當一個事務開始并且update一條id=1的數據行時，成功獲取到寫鎖，此時另一個事務執行也update另一條id=2的數據行時，也成功獲取到寫鎖（id為主鍵）。 此時cpu將時間分配給了事務一，事務一接著也是update id=2的數據行，因為事務二已經獲取到id=2數據行的鎖，所以事務已處于等待狀態。 事務二有獲取到了時間，像執行update id=1的數據行，但是此時id=1的鎖被事務一獲取到了，事務二也處于等待的狀態，因此形成了死鎖。 | session1 | session2 | | --- | --- | | begin;update t set name='測試' where id=1; | begin | | update t set name='測試' where id=2; | | update t set name='測試' where id=2; | | 等待..... | update t set name='測試' where id=1; | | 等待..... | 等待...... | ### 死鎖的解決方案 首先要解決死鎖問題，在程序的設計上，當發現程序有高并發的訪問某一個表時，盡量對該表的執行操作串行化，或者鎖升級，一次性獲取所有的鎖資源。 然后也可以設置參數`innodb_lock_wait_timeout`，超時時間，并且將參數`innodb_deadlock_detect` 打開，當發現死鎖的時候，自動回滾其中的某一個事務。 ## 總結 上面詳細的介紹了MyISAM和InnoDB兩種存儲引擎的鎖機制的實現，并進行了測試。 MyISAM的表鎖分為兩種模式：**「共享讀鎖」**和**「排它寫鎖」**。獲取的讀鎖的線程對該數據行只能讀，不能修改，其它線程也只能對該數據行加讀鎖。 獲取到寫鎖的線程對該數據行既能讀也能寫，對其他線程對該數據行的讀寫具有排它性。 MyISAM中默認寫優先于去操作，因此MyISAM一般不適合運用于大量讀寫操作的程序中。 InnoDB的行鎖雖然會出現死鎖的可能，但是InnoDB的支持的并發性能比MyISAM好，行鎖的粒度最小，一定的方法和措施可以解決死鎖的發生，極大的發揮InnoDB的性能。 InnoDB中引入了間隙鎖的概念來決解出現幻讀的問題，也引入事務的特性，通過事務的四種隔離級別，來降低鎖沖突，提高并發性能。 作者：碼農小光 鏈接：https://www.jianshu.com/p/3d4044f87fdb 來源：簡書 著作權歸作者所有。商業轉載請聯系作者獲得授權，非商業轉載請注明出處。