mysql讀寫鎖與死鎖問題

在MySQL中對於使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定並且總是以同樣的順序鎖定表來管理。

對WRITE，MySQL使用的表鎖定方法原理如下：

◆ 如果在表上沒有鎖，在它上面放一個寫鎖。
◆否則，把鎖定請求放在寫鎖定隊列中。

對READ，MySQL使用的鎖定方法原理如下：

◆如果在表上沒有寫鎖定，把一個讀鎖定放在它上面。
◆否則，把鎖請求放在讀鎖定隊列中。

當一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然後是讀鎖定隊列中的線程。

這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到沒有更多的更新。

如果INSERT 語句不沖突，可以自由為MyISAM 表混合並行的INSERT 和SELECT 語句而不需要鎖定。

InnoDB 使用行鎖定，BDB 使用頁鎖定。對於這兩種存儲引擎，都可能存在死鎖。這是因為，在SQL語句處理期間，InnoDB 自動獲得行鎖定，BDB 獲得頁鎖定，而不是在事務啟動時獲得。

行級鎖定的優點：

· 當在許多線程中訪問不同的行時只存在少量鎖定沖突。
· 回滾時只有少量的更改。
· 可以長時間鎖定單一的行。

行級鎖定的缺點：

· 比頁級或表級鎖定占用更多的內存。
· 當在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖。
· 如果你在大部分數據上經常進行 GROUP BY 操作或者必須經常掃描整個表，比其它鎖定明顯慢很多。
· 用高級別鎖定，通過支持不同的類型鎖定，你也可以很容易地調節應用程序，因為其鎖成本小於行級鎖定。
下面說說MyISAM幾個容易忽視的配置選項：

concurrent_insert：

通常來說，在MyISAM裡讀寫操作是串行的，但當對同一個表進行查詢和插入操作時，為了降低鎖競爭的頻率，根據concurrent_insert的設置，MyISAM是可以並行處理查詢和插入的：

當concurrent_insert=0時，不允許並發插入功能。

當concurrent_insert=1時，允許對沒有洞洞的表使用並發插入，新數據位於數據文件結尾（缺省）。

當concurrent_insert=2時，不管表有沒有洞洞，都允許在數據文件結尾並發插入。

這樣看來，把concurrent_insert設置為2是很劃算的，至於由此產生的文件碎片，可以定期使用OPTIMIZE TABLE語法優化。

max_write_lock_count：

缺省情況下，寫操作的優先級要高於讀操作的優先級，即便是先發送的讀請求，後發送的寫請求，此時也會優先處理寫請求，然後再處理讀請求。這就造成一個問題：一旦我發出若干個寫請求，就會堵塞所有的讀請求，直到寫請求全都處理完，才有機會處理讀請求。此時可以考慮使用max_write_lock_count：

max_write_lock_count=1

有了這樣的設置，當系統處理一個寫操作後，就會暫停寫操作，給讀操作執行的機會。

low-priority-updates：

我們還可以更干脆點，直接降低寫操作的優先級，給讀操作更高的優先級。

low-priority-updates=1

綜合來看，concurrent_insert=2是絕對推薦的，至於max_write_lock_count=1和low-priority-updates=1，則視情況而定，如果可以降低寫操作的優先級，則使用low-priority-updates=1，否則使用max_write_lock_count=1。

表鎖定在下面的情況下也存在問題：

· 一個客戶發出長時間運行的查詢。
· 然後，另一個客戶對同一個表進行更新。該客戶必須等待直到SELECT完成。
· 另一個客戶對同一個表上發出了另一個 SELECT 語句。因為UPDATE比 SELECT 優先級高，該SELECT 語句等待UPDATE完成，並且等待第1個 SELECT 完成。

下面描述了一些方法來避免或減少表鎖定造成的競爭：

· 試圖使 SELECT 語句運行得更快。可能必須創建一些摘要(summary)表做到這點。
· 用--low-priority-updates啟動mysqld。這將給所有更新(修改)一個表的語句以比SELECT語句低的優先級。在這種情況下，在先前情形的第2個SELECT語句將在UPDATE語句前執行，而不需要等候第1個 SELECT 完成。
· 可以使用SET_UPDATES=1語句指定具體連接中的所有更新應使用低優先級。
· 可以用LOW_PRIORITY屬性給與一個特定的INSERT、UPDATE或DELETE語句較低優先級。
· 可以用HIGH_PRIORITY屬性給與一個特定的SELECT語句較高優先級。
· 為max_write_lock_count系統變量指定一個低值來啟動mysqld來強制MySQL在具體數量的插入完成後臨時提高所有等待一個表的SELECT 語句的優先級。這樣允許在一定數量的WRITE鎖定後給出READ鎖定。
· 如果你有關於INSERT結合SELECT的問題，切換到使用新的MyISAM表，因為它們支持並發的SELECT和INSERT。
· 如果你對同一個表混合插入和刪除，INSERT DELAYED將會有很大的幫助。
· 如果你對同一個表混合使用 SELECT 和DELETE 語句出現問題，DELETE 的LIMIT 選項可以有所幫助。
· 對 SELECT 語句使用SQL_BUFFER_RESULT可以幫助使表鎖定時間變短。
· 可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊列。在這種情況下，寫鎖定和讀鎖定將具有相同的優先級，對一些應用程序會有幫助。

這裡是一些MySQL中表鎖定相關的技巧：

· 如果不混合更新與需要在同一個表中檢查許多行的選擇，可以進行並行操作。
· 可以使用 LOCK TABLES 來提高速度，因為在一個鎖定中進行許多更新比沒有鎖定的更新要快得多。將表中的內容切分為幾個表也可以有所幫助。