mysql讀寫鎖與死鎖問(wèn)題

在MySQL中對(duì)于使用表級(jí)鎖定的存儲(chǔ)引擎，表鎖定時(shí)不會(huì)死鎖的。這通過(guò)總是在一個(gè)查詢開始時(shí)立即請(qǐng)求所有必要的鎖定并且總是以同樣的順序鎖定表來(lái)管理。

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

◆ 如果在表上沒(méi)有鎖，在它上面放一個(gè)寫鎖。
◆否則，把鎖定請(qǐng)求放在寫鎖定隊(duì)列中。

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

◆如果在表上沒(méi)有寫鎖定，把一個(gè)讀鎖定放在它上面。
◆否則，把鎖請(qǐng)求放在讀鎖定隊(duì)列中。

當(dāng)一個(gè)鎖定被釋放時(shí)，鎖定可被寫鎖定隊(duì)列中的線程得到，然后是讀鎖定隊(duì)列中的線程。

這意味著，如果你在一個(gè)表上有許多更新，SELECT語(yǔ)句將等待直到?jīng)]有更多的更新。

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

InnoDB 使用行鎖定，BDB 使用頁(yè)鎖定。對(duì)于這兩種存儲(chǔ)引擎，都可能存在死鎖。這是因?yàn)椋赟QL語(yǔ)句處理期間，InnoDB 自動(dòng)獲得行鎖定，BDB 獲得頁(yè)鎖定，而不是在事務(wù)啟動(dòng)時(shí)獲得。

行級(jí)鎖定的優(yōu)點(diǎn)：

· 當(dāng)在許多線程中訪問(wèn)不同的行時(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í)鎖定。
下面說(shuō)說(shuō)MyISAM幾個(gè)容易忽視的配置選項(xiàng)：

concurrent_insert：

通常來(lái)說(shuō)，在MyISAM里讀寫操作是串行的，但當(dāng)對(duì)同一個(gè)表進(jìn)行查詢和插入操作時(shí)，為了降低鎖競(jìng)爭(zhēng)的頻率，根據(jù)concurrent_insert的設(shè)置，MyISAM是可以并行處理查詢和插入的：

當(dāng)concurrent_insert=0時(shí)，不允許并發(fā)插入功能。

當(dāng)concurrent_insert=1時(shí)，允許對(duì)沒(méi)有洞洞的表使用并發(fā)插入，新數(shù)據(jù)位于數(shù)據(jù)文件結(jié)尾（缺省）。

當(dāng)concurrent_insert=2時(shí)，不管表有沒(méi)有洞洞，都允許在數(shù)據(jù)文件結(jié)尾并發(fā)插入。

這樣看來(lái)，把concurrent_insert設(shè)置為2是很劃算的，至于由此產(chǎn)生的文件碎片，可以定期使用OPTIMIZE TABLE語(yǔ)法優(yōu)化。

max_write_lock_count：

缺省情況下，寫操作的優(yōu)先級(jí)要高于讀操作的優(yōu)先級(jí)，即便是先發(fā)送的讀請(qǐng)求，后發(fā)送的寫請(qǐng)求，此時(shí)也會(huì)優(yōu)先處理寫請(qǐng)求，然后再處理讀請(qǐng)求。這就造成一個(gè)問(wèn)題：一旦我發(fā)出若干個(gè)寫請(qǐng)求，就會(huì)堵塞所有的讀請(qǐng)求，直到寫請(qǐng)求全都處理完，才有機(jī)會(huì)處理讀請(qǐng)求。此時(shí)可以考慮使用max_write_lock_count：

max_write_lock_count=1

有了這樣的設(shè)置，當(dāng)系統(tǒng)處理一個(gè)寫操作后，就會(huì)暫停寫操作，給讀操作執(zhí)行的機(jī)會(huì)。

low-priority-updates：

我們還可以更干脆點(diǎn)，直接降低寫操作的優(yōu)先級(jí)，給讀操作更高的優(yōu)先級(jí)。

low-priority-updates=1

綜合來(lái)看，concurrent_insert=2是絕對(duì)推薦的，至于max_write_lock_count=1和low-priority-updates=1，則視情況而定，如果可以降低寫操作的優(yōu)先級(jí)，則使用low-priority-updates=1，否則使用max_write_lock_count=1。

表鎖定在下面的情況下也存在問(wèn)題：

· 一個(gè)客戶發(fā)出長(zhǎng)時(shí)間運(yùn)行的查詢。
· 然后，另一個(gè)客戶對(duì)同一個(gè)表進(jìn)行更新。該客戶必須等待直到SELECT完成。
· 另一個(gè)客戶對(duì)同一個(gè)表上發(fā)出了另一個(gè) SELECT 語(yǔ)句。因?yàn)閁PDATE比 SELECT 優(yōu)先級(jí)高，該SELECT 語(yǔ)句等待UPDATE完成，并且等待第1個(gè) SELECT 完成。

下面描述了一些方法來(lái)避免或減少表鎖定造成的競(jìng)爭(zhēng)：

· 試圖使 SELECT 語(yǔ)句運(yùn)行得更快。可能必須創(chuàng)建一些摘要(summary)表做到這點(diǎn)。
· 用--low-priority-updates啟動(dòng)d。這將給所有更新(修改)一個(gè)表的語(yǔ)句以比SELECT語(yǔ)句低的優(yōu)先級(jí)。在這種情況下，在先前情形的第2個(gè)SELECT語(yǔ)句將在UPDATE語(yǔ)句前執(zhí)行，而不需要等候第1個(gè) SELECT 完成。
· 可以使用SET_UPDATES=1語(yǔ)句指定具體連接中的所有更新應(yīng)使用低優(yōu)先級(jí)。
· 可以用LOW_PRIORITY屬性給與一個(gè)特定的INSERT、UPDATE或DELETE語(yǔ)句較低優(yōu)先級(jí)。
· 可以用HIGH_PRIORITY屬性給與一個(gè)特定的SELECT語(yǔ)句較高優(yōu)先級(jí)。
· 為max_write_lock_count系統(tǒng)變量指定一個(gè)低值來(lái)啟動(dòng)mysqld來(lái)強(qiáng)制MySQL在具體數(shù)量的插入完成后臨時(shí)提高所有等待一個(gè)表的SELECT 語(yǔ)句的優(yōu)先級(jí)。這樣允許在一定數(shù)量的WRITE鎖定后給出READ鎖定。
· 如果你有關(guān)于INSERT結(jié)合SELECT的問(wèn)題，切換到使用新的MyISAM表，因?yàn)樗鼈冎С植l(fā)的SELECT和INSERT。
· 如果你對(duì)同一個(gè)表混合插入和刪除，INSERT DELAYED將會(huì)有很大的幫助。
· 如果你對(duì)同一個(gè)表混合使用 SELECT 和DELETE 語(yǔ)句出現(xiàn)問(wèn)題，DELETE 的LIMIT 選項(xiàng)可以有所幫助。
· 對(duì) SELECT 語(yǔ)句使用SQL_BUFFER_RESULT可以幫助使表鎖定時(shí)間變短。
· 可以更改mysys/thr_lock.c中的鎖代碼以使用單一的隊(duì)列。在這種情況下，寫鎖定和讀鎖定將具有相同的優(yōu)先級(jí)，對(duì)一些應(yīng)用程序會(huì)有幫助。

這里是一些MySQL中表鎖定相關(guān)的技巧：

· 如果不混合更新與需要在同一個(gè)表中檢查許多行的選擇，可以進(jìn)行并行操作。
· 可以使用 LOCK TABLES 來(lái)提高速度，因?yàn)樵谝粋€(gè)鎖定中進(jìn)行許多更新比沒(méi)有鎖定的更新要快得多。將表中的內(nèi)容切分為幾個(gè)表也可以有所幫助。

聲明：本網(wǎng)頁(yè)內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問(wèn)題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com