在一個update和insert操作頻繁的表中，少量數(shù)據(jù)測試的時候運行良好，在實際運營中，因數(shù)據(jù)量比較大(21萬條記錄)，會出現(xiàn)死鎖現(xiàn)象，用show processlist查看，可以看到一個update語句狀態(tài)是Locked，一個delete語句狀態(tài)是Sending data。查看了一下參考手冊，把鎖定相關(guān)的資料整理下來，以便自己記錄和追蹤該問題的解決情況：
MySQL 5.1支持對MyISAM和MEMORY表進行表級鎖定，對BDB表進行頁級鎖定，對InnoDB 表進行行級鎖定。在許多情況下，可以根據(jù)培訓(xùn)猜測應(yīng)用程序使用哪類鎖定類型好，但一般很難說出某個給出的鎖類型就比另一個好。一切取決于應(yīng)用程序，應(yīng)用程序的不同部分可能需要不同的鎖類型。為了確定是否想要使用行級鎖定的存儲引擎，應(yīng)看看應(yīng)用程序做什么并且混合使用什么樣的選擇和更新語句。例如，大多數(shù)Web應(yīng)用程序執(zhí)行許多選擇，而很少進行刪除，只對關(guān)鍵字的值進行更新，并且只插入少量具體的表?；綧ySQL MyISAM設(shè)置已經(jīng)調(diào)節(jié)得很好。
在MySQL中對于使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定并且總是以同樣的順序鎖定表來管理。
對WRITE，MySQL使用的表鎖定方法原理如下：
◆ 如果在表上沒有鎖，在它上面放一個寫鎖。
◆否則，把鎖定請求放在寫鎖定隊列中。
對READ，MySQL使用的鎖定方法原理如下：
◆如果在表上沒有寫鎖定，把一個讀鎖定放在它上面。
◆否則，把鎖請求放在讀鎖定隊列中。
當(dāng)一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然后是讀鎖定隊列中的線程。
這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到?jīng)]有更多的更新。
如果INSERT 語句不沖突，可以自由為MyISAM 表混合并行的INSERT 和SELECT 語句而不需要鎖定。
InnoDB 使用行鎖定，BDB 使用頁鎖定。對于這兩種存儲引擎，都可能存在死鎖。這是因為，在SQL語句處理期間，InnoDB 自動獲得行鎖定，BDB 獲得頁鎖定，而不是在事務(wù)啟動時獲得。
行級鎖定的優(yōu)點：
· 當(dāng)在許多線程中訪問不同的行時只存在少量鎖定沖突。
· 回滾時只有少量的更改。
· 可以長時間鎖定單一的行。
行級鎖定的缺點：
· 比頁級或表級鎖定占用更多的內(nèi)存。
· 當(dāng)在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖。
· 如果你在大部分?jǐn)?shù)據(jù)上經(jīng)常進行 GROUP BY 操作或者必須經(jīng)常掃描整個表，比其它鎖定明顯慢很多。
· 用高級別鎖定，通過支持不同的類型鎖定，你也可以很容易地調(diào)節(jié)應(yīng)用程序，因為其鎖成本小于行級鎖定。
在以下情況下，表鎖定優(yōu)先于頁級或行級鎖定：
· 表的大部分語句用于讀取。
· 對嚴(yán)格的關(guān)鍵字進行讀取和更新，你可以更新或刪除可以用單一的讀取的關(guān)鍵字來提取的一行：
• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;
• DELETE FROM tbl_name WHERE unique_key_col = key_value ;
· SELECT 結(jié)合并行的INSERT 語句，并且只有很少的UPDATE或 DELETE 語句。
· 在整個表上有許多掃描或 GROUP BY 操作，沒有任何寫操作。
MySQL 5.1支持對MyISAM和MEMORY表進行表級鎖定，對BDB表進行頁級鎖定，對InnoDB 表進行行級鎖定。在許多情況下，可以根據(jù)培訓(xùn)猜測應(yīng)用程序使用哪類鎖定類型好，但一般很難說出某個給出的鎖類型就比另一個好。一切取決于應(yīng)用程序，應(yīng)用程序的不同部分可能需要不同的鎖類型。為了確定是否想要使用行級鎖定的存儲引擎，應(yīng)看看應(yīng)用程序做什么并且混合使用什么樣的選擇和更新語句。例如，大多數(shù)Web應(yīng)用程序執(zhí)行許多選擇，而很少進行刪除，只對關(guān)鍵字的值進行更新，并且只插入少量具體的表?；綧ySQL MyISAM設(shè)置已經(jīng)調(diào)節(jié)得很好。
在MySQL中對于使用表級鎖定的存儲引擎，表鎖定時不會死鎖的。這通過總是在一個查詢開始時立即請求所有必要的鎖定并且總是以同樣的順序鎖定表來管理。
對WRITE，MySQL使用的表鎖定方法原理如下：
◆ 如果在表上沒有鎖，在它上面放一個寫鎖。
◆否則，把鎖定請求放在寫鎖定隊列中。
對READ，MySQL使用的鎖定方法原理如下：
◆如果在表上沒有寫鎖定，把一個讀鎖定放在它上面。
◆否則，把鎖請求放在讀鎖定隊列中。
當(dāng)一個鎖定被釋放時，鎖定可被寫鎖定隊列中的線程得到，然后是讀鎖定隊列中的線程。
這意味著，如果你在一個表上有許多更新，SELECT語句將等待直到?jīng)]有更多的更新。
如果INSERT 語句不沖突，可以自由為MyISAM 表混合并行的INSERT 和SELECT 語句而不需要鎖定。
InnoDB 使用行鎖定，BDB 使用頁鎖定。對于這兩種存儲引擎，都可能存在死鎖。這是因為，在SQL語句處理期間，InnoDB 自動獲得行鎖定，BDB 獲得頁鎖定，而不是在事務(wù)啟動時獲得。
行級鎖定的優(yōu)點：
· 當(dāng)在許多線程中訪問不同的行時只存在少量鎖定沖突。
· 回滾時只有少量的更改。
· 可以長時間鎖定單一的行。
行級鎖定的缺點：
· 比頁級或表級鎖定占用更多的內(nèi)存。
· 當(dāng)在表的大部分中使用時，比頁級或表級鎖定速度慢，因為你必須獲取更多的鎖。
· 如果你在大部分?jǐn)?shù)據(jù)上經(jīng)常進行 GROUP BY 操作或者必須經(jīng)常掃描整個表，比其它鎖定明顯慢很多。
· 用高級別鎖定，通過支持不同的類型鎖定，你也可以很容易地調(diào)節(jié)應(yīng)用程序，因為其鎖成本小于行級鎖定。
在以下情況下，表鎖定優(yōu)先于頁級或行級鎖定：
· 表的大部分語句用于讀取。
· 對嚴(yán)格的關(guān)鍵字進行讀取和更新，你可以更新或刪除可以用單一的讀取的關(guān)鍵字來提取的一行：
• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;
• DELETE FROM tbl_name WHERE unique_key_col = key_value ;
· SELECT 結(jié)合并行的INSERT 語句，并且只有很少的UPDATE或 DELETE 語句。
· 在整個表上有許多掃描或 GROUP BY 操作，沒有任何寫操作。