MySQL的幾種鎖機制的使用介紹

MySQL的幾種鎖機制的使用介紹

鎖

在日常的開發過程中，為了控制線程的并發肯定會用到鎖機制。對于數據庫而言，鎖機制就是數據庫為了保證數據的一致性，而使各種共享資源在被并發訪問變得有序所設計的一種規則。

當然MySQL也不例外，根據不同的存儲引擎，MySQL中鎖的特性大致歸納為如下：

（注：由于BDB已經被InnoDB所取代，我們只討論MyISAM表鎖和InnoDB行鎖的問題）

不同的鎖在開銷、加鎖速度、死鎖、粒度、并發性能等方面也有很大差異，

表鎖：開銷小，加鎖快；不會出現死鎖；鎖定力度大，發生鎖沖突概率高，并發度最低 行鎖：

行鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度小，發生鎖沖突的概率低，并發度高 頁鎖：

頁面鎖：開銷和加鎖速度介于表鎖和行鎖之間；會出現死鎖；鎖定粒度介于表鎖和行鎖之間，并發度一般

MyISAM

MyISAM的表級鎖有兩種模式：表共享讀鎖和表獨占寫鎖，在兼容性方面，除了讀鎖與讀鎖之間互相兼容之外，其余互不兼容。此外，MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的。

通常，MyISAM在執行查詢語句之前會自動給涉及的所有表加讀鎖，在執行更新操作之前會自動給涉及的表加寫鎖，這個過程并不需要用戶干預。MyISAM在自動加鎖時，總是一次性獲得SQL語句所需的全部鎖，這也是MyISAM表不會出現死鎖的原因。

考慮到某些情況下為了在一定程度上模擬事務操作，實現對某一時間點多個表的一致性讀取，例如訂單、訂單明細，用戶也可以用LOCK TABLE命令給MyISAM表顯式加鎖，例如lock table xxx read/write。

需要注意的是，在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及到表的鎖，并且MySQL不支持鎖升級，也就是說，在執行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執行查詢操作，而不能執行更新操作。

之前說過MyISAM表的讀和寫是串行的，但這是就總體而言的。在一定條件下，MyISAM表也支持查詢和插入操作的并發進行。

MyISAM存儲引擎有一個系統變量concurrent_insert，專門用以控制其并發插入的行為，其值分別可以為0、1或2。

           1） 當concurrent_insert設置為0時，不允許并發插入。 

           2）當concurrent_insert設置為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的默認設置。 

            3）當concurrent_insert設置為2時，無論MyISAM表中有沒有空洞，都允許在表尾并發插入記錄。

如果讀進程和寫進程同時對表加鎖，MyISAM會怎么處理呢？MyISAM寫進程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！這是因為MySQL認為寫請求一般比讀請求要重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。當然MySQL也提供了一種折中的辦法來調節讀寫沖突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。

InnoDB

InnoDB實現了以下類型的行鎖，

1）共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。

2） 排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。 

    1）意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。

     2）意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。

上述鎖模式的兼容情況具體如下表所示，x表示沖突，√表示兼容，

需要注意的是，

1）意向鎖是InnoDB自動加的，不需用戶干預。

2)對于UPDATE、DELETE和INSERT語句，InnoDB會自動給涉及數據集加排他鎖（X)；

3)對于普通SELECT語句，InnoDB不會加任何鎖；

4)事務可以通過以下語句顯示給  記錄集 加共享鎖或排他鎖，

    共享鎖（S）：SELECT * FROM table_name WHERE … LOCK IN SHARE MODE。 

    排他鎖（X）：SELECT * FROM table_name WHERE … FOR UPDATE。

共享鎖主要用在需要數據依存關系時來確認某行記錄是否存在，并確保沒有人對這個記錄進行UPDATE或者DELETE操作。但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進行更新操作的應用，應該使用排他鎖。

重點來了，InnoDB行鎖是通過給索引上的索引項加鎖實現的，這意味著只有通過索引條件檢索數據，InnoDB才使用行級鎖，否則InnoDB使用表鎖。

間隙鎖（Next-Key鎖）

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

InnoDB使用間隙鎖的目的，

1)一方面是為了防止幻讀，以滿足相關隔離級別的要求，對于上面的例子，要是不使用間隙鎖，如果其他事務插入了empid大于100的任何記錄，那么本事務如果再次執行上述語句，就會發生幻讀；

2)另外一方面，是為了滿足其恢復和復制的需要。

表鎖

對于InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們之所以選擇InnoDB表的理由。

但在個別特殊事務中，也可以考慮使用表級鎖。

第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。 

第二種情況是：事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖、減少數據庫因事務回滾帶來的開銷。

當然，應用中這兩種事務不能太多，否則，就應該考慮使用MyISAM表了。

死鎖

發生死鎖后，InnoDB一般都能自動檢測到，并使一個事務釋放鎖并回退，另一個事務獲得鎖，繼續完成事務。但在涉及外部鎖，或涉及表鎖的情況下，InnoDB并不能完全自動檢測到死鎖，這需要通過設置鎖等待超時參數 innodb_lock_wait_timeout來解決。需要說明的是，這個參數并不是只用來解決死鎖問題，在并發訪問比較高的情況下，如果大量事務因無法立即獲得所需的鎖而掛起，會占用大量計算機資源，造成嚴重性能問題，甚至拖跨數據庫。我們通過設置合適的鎖等待超時閾值，可以避免這種情況發生。

此外，我們在操作數據庫時也可以遵循一些原則，避免死鎖發生：

1)在應用中，如果不同的程序會并發存取多個表，應盡量約定以相同的順序來訪問表，這樣可以大大降低產生死鎖的機會。

2)在程序以批量方式處理數據的時候，如果事先對數據排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現死鎖的可能。 

3)在事務中，如果要更新記錄，應該直接申請足夠級別的鎖，即排他鎖，而不應先申請共享鎖，更新時再申請排他鎖，因為當用戶申請排他鎖時，其他事務可能又已經獲得了相同記錄的共享鎖，從而造成鎖沖突，甚至死鎖。 

4)在REPEATABLE-READ隔離級別下，如果兩個線程同時對相同條件記錄用SELECT…FOR UPDATE加排他鎖，在沒有符合該條件記錄情況下，兩個線程都會加鎖成功。程序發現記錄尚不存在，就試圖插入一條新記錄，如果兩個線程都這么做，就會出現死鎖。這種情況下，將隔離級別改成READ COMMITTED，就可避免問題。 

5)當隔離級別為READ COMMITTED時，如果兩個線程都先執行SELECT…FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時，只有一個線程能插入成功，另一個線程會出現鎖等待，當第1個線程提交后，第2個線程會因主鍵重出錯，但雖然這個線程出錯了，卻會獲得一個排他鎖！這時如果有第3個線程又來申請排他鎖，也會出現死鎖。

當然，如果發生死鎖了，我們也可以使用SHOW INNODB STATUS命令來確定最后一個死鎖產生的原因。