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