MySQL鎖機制詳解及死結處理方式

MySQL鎖機制詳解及死結處理方式

為了給高並發情況下的MySQL進行更好的最佳化，有必要瞭解一下MySQL查詢更新時的鎖表機制。

一、概述
MySQL有三種鎖的層級：頁級、表級、行級。
MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；BDB儲存引擎採用的是頁面鎖（page-level
locking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下：
表級鎖：開銷小，加鎖快；不會出現死結；鎖定粒度大，發生鎖衝突的機率最高,並發度最低。
行級鎖：開銷大，加鎖慢；會出現死結；鎖定粒度最小，發生鎖衝突的機率最低,並發度也最高。
頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死結；鎖定粒度界於表鎖和行鎖之間，並發度一般。

二、MyISAM表鎖
MyISAM儲存引擎只支援表鎖，是現在用得最多的儲存引擎。
但表級鎖讓多線程可以同時從資料表中讀取資料，但是如果另一個線程想要寫資料的話，就必須要先取得排他訪問。正在更新資料時，必須要等到更新完成了，其他線程才能訪問這個表。（這種機製造成了並發讀寫容易出現表鎖爭奪而導致阻塞訪問）
1、查詢表級鎖爭用情況
可以通過檢查table_locks_waited和table_locks_immediate狀態變數來分析系統上的表鎖定爭奪：
  mysql> show status like 'table%';
  +———————–+———-+
  | Variable_name | Value |
  +———————–+———-+
  | Table_locks_immediate | 76939364 |  （表示可以立即擷取鎖的次數）
  | Table_locks_waited | 305089 |  （表示不能立即擷取鎖，需要等待鎖的次數;）       
  +———————–+———-+
  2 rows in set (0.00 sec)
  Table_locks_waited/(Table_locks_immediate+Table_locks_waited)
  這個比例值越大說明表級鎖爭用的情況越嚴重。
  例：比例值=0.01說明100次進程裡就有一次是需要等待鎖的進程；

2、MySQL表級鎖的鎖模式
MySQL的表級鎖有兩種模式：表共用讀鎖（Table Read Lock）和表獨佔寫鎖（Table Write
Lock）。MyISAM在執行查詢語句（SELECT）前，會自動給涉及的所有表加讀鎖，在執行更新操作（UPDATE、DELETE、INSERT等）前，會自動給涉及的表加寫鎖。
所以對MyISAM表進行操作，會有以下情況：
a、對MyISAM表的讀操作（加讀鎖），不會阻塞其他進程對同一表的讀請求，但會阻塞對同一表的寫請求。只有當讀鎖釋放後，才會執行其它進程的寫操作。
b、對MyISAM表的寫操作（加寫鎖），會阻塞其他進程對同一表的讀和寫操作，只有當寫鎖釋放後，才會執行其它進程的讀寫操作。

3、並發插入
原則上資料表有一個讀鎖時，其它進程無法對此表進行更新操作，但在一定條件下，MyISAM表也支援查詢和插入操作的並發進行。
MyISAM儲存引擎有一個系統變數concurrent_insert，專門用以控制其並發插入的行為，其值分別可以為0、1或2。
a、當concurrent_insert設定為0時，不允許並發插入。
b、當concurrent_insert設定為1時，如果MyISAM表中沒有空洞（即表的中間沒有被刪除的行），MyISAM允許在一個進程讀表的同時，另一個進程從表尾插入記錄。這也是MySQL的預設設定。
c、當concurrent_insert設定為2時，無論MyISAM表中有沒有空洞，都允許在表尾並發插入記錄。
4、MyISAM的鎖調度
由於MySQL認為寫請求一般比讀請求要重要，所以如果有讀寫請求同時進行的話，MYSQL將會優先執行寫操作。這樣MyISAM表在進行大量的更新操作時（特別是更新的欄位中存在索引的情況下），會造成查詢操作很難獲得讀鎖，從而導致查詢阻塞。
我們可以通過一些設定來調節MyISAM的調度行為：
a、通過指定啟動參數low-priority-updates，使MyISAM引擎預設給予讀請求以優先的權利。
b、通過執行命令SET LOW_PRIORITY_UPDATES=1，使該串連發出的更新要求優先順序降低。
c、通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優先順序。
    上面3種方法都是要麼更新優先，要麼查詢優先的方法。這裡要說明的就是，不要盲目的給mysql設定為讀優先，因為一些需要長時間啟動並執行查詢操作，也會使寫進程“餓死”。只有根據你的實際情況，來決定設定哪種操作優先。
    這些方法還是沒有從根本上同時解決查詢和更新的問題。在一個有大資料量高並發表的mysql裡，我們還可採用另一種策略來進行最佳化，那就是通過mysql主從（讀寫）分離來實現負載平衡，這樣可避免優先哪一種操作從而可能導致另一種操作的堵塞。下面將用一個篇幅來說明mysql的讀寫分離技術。

MyISAM使用的是 flock 類的函數，直接就是對整個檔案進行鎖定（叫做檔案鎖定），InnoDB使用的是 fcntl 類的函數，可以對檔案中局部資料進行鎖定（叫做行鎖定），所以區別就是在這裡。
另外MyISAM的資料表是按照單個檔案儲存體的，可以針對單個表檔案進行鎖定，但是InnoDB是一整個檔案，把索引、資料、結構全部儲存在 ibdata 檔案裡，所以必須用行鎖定。
死結
所謂死結<DeadLock>: 是指兩個或兩個以上的進程在執行過程中,
因爭奪資源而造成的一種互相等待的現象,若無外力作用,它們都將無法推進下去.
此時稱系統處於死結狀態或系統產生了死結,這些永遠在互相等竺的進程稱為死結進程.
表級鎖不會產生死結.所以解決死結主要還是真對於最常用的InnoDB.
遇到死結的處理方式

mysql -uxxx -pxxx -h伺服器ip --port=伺服器連接埠;（如果伺服器設定了ip和連接埠訪問的話，一定要帶ip和連接埠）

mysql> show processlist; #查看正在執行的sql （show full processlist;查看全部sql）
mysql> kill id #殺死sql進程；
如果進程太多找不到，就重啟mysql吧
/ect/init.d/mysql restart
或/ect/init.d/mysql stop（如果關不掉就直接kill -9 進程id）  再/ect/init.d/mysql start
去看看mysql記錄檔是否儲存死結日誌：
常用目錄：/var/log/mysqld.log；（該目錄還有其它相關記錄檔就都看看）
怎麼解決還是要看具體什麼問題.

本文永久更新連結地址：