MySQL——MyISAM 表鎖

MyISAM 表鎖

鎖的概念：

MySQL的表級鎖有兩種模式：

給表顯示加鎖：

MyISAM表不會出現死鎖（Deadlock Free）的原因

MyISAM的鎖調度

另一類表級鎖MDL鎖（metadata lock)

總結：

鎖的概念：

鎖是計算機協調多個進程或線程并發訪問某一資源的機制。在數據庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，數據也是一種供許多用戶共享的資源。如何保證數據并發訪問的一致性、有效性是所有數據庫必須解決的一個問題，鎖沖突也是影響數據庫并發訪問性能的一個重要因素。

MYISAM和MEMORY存儲引擎采用的是表級鎖（table-level locking）；

BDB存儲引擎采用的是頁面鎖（page-level locking），但也支持表級鎖；

InnoDB存儲引擎既支持行級鎖（row-level locking），也支持表級鎖，但默認情況下是采用行級鎖。

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

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

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

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

可以通過檢查table_locks_waited和table_locks_immediate狀態變量來分析系統上的表鎖定爭奪：

mysql> show status like ‘table%’;

如果Table_locks_waited的值比較高，則說明存在著較嚴重的表級鎖爭用情況。

MySQL的表級鎖有兩種模式：

表共享讀鎖（Table Read Lock）和表獨占寫鎖（Table Write Lock）。

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

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

MyISAM表的讀操作與寫操作之間，以及寫操作之間是串行的！

給表顯示加鎖：

MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖，這個過程并不需要用戶干預，因此，用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。

給 MyISAM 表顯式加鎖，一般是為了在一定程度模擬事務操作，實現對某一時間點多個表的一致性讀取。

例如，有一個訂單表orders，其中記錄有各訂單的總金額total，同時還有一個訂單明細表 order_detail，其中記錄有各訂單每一產品的金額小計 subtotal，假設我們需要檢查這兩個表的金額合計是否相符，可能就需要執行如下兩條SQL語句：

Select sum(total) from orders; Select sum(subtotal) from order_detail;

1

2

這時，如果不先給兩個表加鎖，就可能產生錯誤的結果，因為第一條語句執行過程中， order_detail表可能已經發生了改變。因此，正確的方法應該是：

Lock tables orders read local, order_detail read local; Select sum(total) from orders; Select sum(subtotal) from order_detail; Unlock tables;

1

2

3

4

上面的例子在LOCK TABLES時加了“local”選項，其作用就是在滿足MyISAM表并發插入條件的情況下，允許其他用戶在表尾并發插入記錄。

MyISAM表不會出現死鎖（Deadlock Free）的原因

在用LOCK TABLES給表顯式加表鎖時，必須同時取得所有涉及表的鎖，并且MySQL不支持鎖升級。

也就是說，在執行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時，如果加的是讀鎖，那么只能執行查詢操作，而不能執行更新操作。

在自動加鎖的情況下也是如此，MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因。

MyISAM的鎖調度

MyISAM存儲引擎的讀鎖和寫鎖是互斥的，讀寫操作是串行的。

那么，一個進程請求某個MyISAM表的讀鎖，同時另一個進程也請求同一表的寫鎖，MySQL如何處理呢？

答案是寫進程先獲得鎖。不僅如此，即使讀請求先到鎖等待隊列，寫請求后到，寫鎖也會插到讀鎖請求之前！

這是因為MySQL認為寫請求一般比讀請求要重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。

我們可以通過一些設置來調節MyISAM的調度行為。 通過指定啟動參數low-priority-updates，使MyISAM引擎默認給予讀請求以優先的權利。 通過執行命令

SET LOW_PRIORITY_UPDATES=1

1

使該連接發出的更新請求優先級降低。 通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先級。

雖然上面3種方法都是要么更新優先，要么查詢優先的方法，但還是可以用其來解決查詢相對重要的應用（如用戶登錄系統）中讀鎖等待嚴重的問題。

另外，MySQL也提供了一種折中的辦法來調節讀寫沖突，即給系統參數max_write_lock_count設置一個合適的值，當一個表的讀鎖達到這個值后，MySQL就暫時將寫請求的優先級降低，給讀進程一定獲得鎖的機會。

另一類表級鎖MDL鎖（metadata lock)

MDL（metadata lock)。MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個查詢正在遍歷一個表中的數據，而執行期間另一個線程對這個表結構做變更，刪了一列，那么查詢線程拿到的結果跟表結構對不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對表做結構變更操作的時候，加 MDL 寫鎖。

讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。

讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執行完才能開始執行。

總結：

對于MyISAM的表鎖，主要討論了以下幾點。

（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）與排他寫鎖（X）之間，以及排他寫鎖（X）之間是互斥的，也就是說讀和寫是串行的。

（2）在一定條件下，MyISAM允許查詢和插入并發執行，可以利用這一點來解決應用中對同一表查詢和插入的鎖爭用問題。

（3）MyISAM默認的鎖調度機制是寫優先，這并不一定適合所有應用，用戶可以通過設置LOW_PRIORITY_UPDATES 參數，或在 INSERT、UPDATE、DELETE 語句中指定LOW_PRIORITY選項來調節讀寫鎖的爭用。

（4）由于表鎖的鎖定粒度大，讀寫之間又是串行的，因此，如果更新操作較多，MyISAM表可能會出現嚴重的鎖等待，可以考慮采用InnoDB表來減少鎖沖突。

MySQL 任務調度

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。