MySQL鎖定機(jī)制的原理
以下的文章主要講述的是MySQL鎖定機(jī)制，我們大家都知道MySQL鎖定在實(shí)際的應(yīng)用中可以說(shuō)已是越來(lái)越廣泛了，所以關(guān)于它的實(shí)際操作已是越來(lái)越受到重視，以下就是具體方案的描述，望你能有所收獲。
支持對(duì)MyISAM和MEMORY表進(jìn)行表級(jí)鎖定，對(duì)BDB表進(jìn)行頁(yè)級(jí)鎖定，對(duì)InnoDB表進(jìn)行行級(jí)鎖定。
在許多情況下，可以根據(jù)培訓(xùn)猜測(cè)應(yīng)用程序使用哪類MySQL鎖定類型最好，但一般很難說(shuō)出某個(gè)給出的鎖類型就比另一個(gè)好。一切取決于應(yīng)用程序，應(yīng)用程序的不同部分可能需要不同的鎖類型。
為了確定是否想要使用行級(jí)鎖定的存儲(chǔ)引擎，應(yīng)看看應(yīng)用程序做什么并且混合使用什么樣的選擇和更新語(yǔ)句。例如，大多數(shù)Web應(yīng)用程序執(zhí)行許多選擇，而很少進(jìn)行刪除，只對(duì)關(guān)鍵字的值進(jìn)行更新，并且只插入少量具體的表。基本MySQL MyISAM設(shè)置已經(jīng)調(diào)節(jié)得很好。
在MySQL中對(duì)于使用表級(jí)鎖定的存儲(chǔ)引擎，表鎖定時(shí)不會(huì)死鎖的。這通過(guò)總是在一個(gè)查詢開始時(shí)立即請(qǐng)求所有必要的鎖定并且總是以同樣的順序鎖定表來(lái)管理。
對(duì)WRITE，MySQL使用的表鎖定方法原理如下：
如果在表上沒有鎖，在它上面放一個(gè)寫鎖。
否則，把鎖定請(qǐng)求放在寫鎖定隊(duì)列中。
對(duì)READ，MySQL使用的鎖定方法原理如下：
如果在表上沒有寫鎖定，把一個(gè)讀鎖定放在它上面。
否則，把鎖請(qǐng)求放在讀鎖定隊(duì)列中。
當(dāng)一個(gè)鎖定被釋放時(shí)，鎖定可被寫鎖定隊(duì)列中的線程得到，然后是讀鎖定隊(duì)列中的線程。
這意味著，如果你在一個(gè)表上有許多更新，SELECT語(yǔ)句將等待直到?jīng)]有更多的更新。
可以通過(guò)檢查table_locks_waited和table_locks_immediate狀態(tài)變量來(lái)分析系統(tǒng)上的表MySQL鎖定爭(zhēng)奪：
- MySQL> SHOW STATUS LIKE 'Table%';
- +-----------------------+---------+
- | Variable_name | Value |
- +-----------------------+---------+
- | Table_locks_immediate | 1151552 |
- | Table_locks_waited | 15324 |
- +-----------------------+---------+
如果INSERT語(yǔ)句不沖突，可以自由為MyISAM表混合并行的INSERT和SELECT語(yǔ)句而不需要鎖定。也就是說(shuō)，你可以在其它客戶正讀取MyISAM表的時(shí)候插入行。如果數(shù)據(jù)文件中間不包含空閑塊，不會(huì)發(fā)生沖突，因?yàn)樵谶@種情況下，記錄總是插入在數(shù)據(jù)文件的尾部。(從表的中部刪除或更新的行可能導(dǎo)致空洞）。如果有空洞，當(dāng)所有空洞填入新的數(shù)據(jù)時(shí)，并行的插入能夠重新自動(dòng)啟用。
如果不能同時(shí)插入，為了在一個(gè)表中進(jìn)行多次INSERT和SELECT操作，可以在臨時(shí)表中插入行并且立即用臨時(shí)表中的記錄更新真正的表。
這可用下列代碼做到：
- MySQL> LOCK TABLES real_table WRITE, insert_table WRITE;
- MySQL> INSERT INTO real_table SELECT * FROM insert_table;
- MySQL> TRUNCATE TABLE insert_table;
- MySQL> UNLOCK TABLES;
InnoDB使用行鎖定，BDB使用頁(yè)MySQL鎖定。對(duì)于這兩種存儲(chǔ)引擎，都可能存在死鎖。這是因?yàn)椋赟QL語(yǔ)句處理期間，InnoDB自動(dòng)獲得行鎖定和BDB獲得頁(yè)鎖定，而不是在事務(wù)啟動(dòng)時(shí)獲得。
行級(jí)鎖定的優(yōu)點(diǎn)：
當(dāng)在許多線程中訪問不同的行時(shí)只存在少量鎖定沖突。
回滾時(shí)只有少量的更改。
可以長(zhǎng)時(shí)間鎖定單一的行。
行級(jí)鎖定的缺點(diǎn)：
比頁(yè)級(jí)或表級(jí)鎖定占用更多的內(nèi)存。
當(dāng)在表的大部分中使用時(shí)，比頁(yè)級(jí)或表級(jí)鎖定速度慢，因?yàn)槟惚仨毇@取更多的鎖。
如果你在大部分?jǐn)?shù)據(jù)上經(jīng)常進(jìn)行GROUP BY操作或者必須經(jīng)常掃描整個(gè)表，比其它鎖定明顯慢很多。
用高級(jí)別鎖定，通過(guò)支持不同的類型鎖定，你也可以很容易地調(diào)節(jié)應(yīng)用程序，因?yàn)槠滏i成本小于行級(jí)鎖定。
在以下情況下，表鎖定優(yōu)先于頁(yè)級(jí)或行級(jí)鎖定：
表的大部分語(yǔ)句用于讀取。
對(duì)嚴(yán)格的關(guān)鍵字進(jìn)行讀取和更新，你可以更新或刪除可以用單一的讀取的關(guān)鍵字來(lái)提取的一行：
- 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語(yǔ)句，并且只有很少的UPDATE或DELETE語(yǔ)句。
在整個(gè)表上有許多掃描或GROUP BY操作，沒有任何寫操作。
不同于行級(jí)或頁(yè)級(jí)MySQL鎖定的選項(xiàng)：
版本(例如，為并行的插入在MySQL中使用的技術(shù))，其中可以一個(gè)寫操作，同時(shí)有許多讀取操作。這說(shuō)明數(shù)據(jù)庫(kù)或表支持?jǐn)?shù)據(jù)依賴的不同視圖，取決于訪問何時(shí)開始。其它共同的術(shù)語(yǔ)是“時(shí)間跟蹤”、“寫復(fù)制”或者“按需復(fù)制”。
按需復(fù)制在許多情況下優(yōu)先于頁(yè)級(jí)或行級(jí)鎖定。然而，在最壞的情況下，它可能比使用常規(guī)鎖定使用更多的內(nèi)存。
除了行級(jí)MySQL鎖定外，你可以使用應(yīng)用程序級(jí)鎖定，例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。這些是建議性鎖定，它們只能在運(yùn)行良好的應(yīng)用程序中工作。
【編輯推薦】
