mysql數(shù)據(jù)庫(kù)怎么鎖 mysql鎖表操作

輕松掌握MySQL數(shù)據(jù)庫(kù)鎖機(jī)制的相關(guān)原理[1]

在一個(gè)update和insert操作頻繁的表中 少量數(shù)據(jù)測(cè)試的時(shí)候運(yùn)行良好 在實(shí)際運(yùn)營(yíng)中 因數(shù)據(jù)量比較大( 萬(wàn)條記錄) 會(huì)出現(xiàn)死鎖現(xiàn)象 用show processlist查看 可以看到一個(gè)update語(yǔ)句狀態(tài)是Locked 一個(gè)delete語(yǔ)句狀態(tài)是Sending data 查看了一下參考手冊(cè) 把鎖定相關(guān)的資料整理下來(lái) 以便自己記錄和追蹤該問(wèn)題的解決情況

創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供阿勒泰網(wǎng)站建設(shè)、阿勒泰做網(wǎng)站、阿勒泰網(wǎng)站設(shè)計(jì)、阿勒泰網(wǎng)站制作等企業(yè)網(wǎng)站建設(shè)、網(wǎng)頁(yè)設(shè)計(jì)與制作、阿勒泰企業(yè)網(wǎng)站模板建站服務(wù)，10年阿勒泰做網(wǎng)站經(jīng)驗(yàn)，不只是建網(wǎng)站，更提供有價(jià)值的思路和整體網(wǎng)絡(luò)服務(wù)。

MySQL 支持對(duì)MyISAM和MEMORY表進(jìn)行表級(jí)鎖定 對(duì)BDB表進(jìn)行頁(yè)級(jí)鎖定 對(duì)InnoDB 表進(jìn)行行級(jí)鎖定 在許多情況下 可以根據(jù)培訓(xùn)猜測(cè)應(yīng)用程序使用哪類鎖定類型最好 但一般很難說(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è)查詢開(kāi)始時(shí)立即請(qǐng)求所有必要的鎖定并且總是以同樣的順序鎖定表來(lái)管理

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

◆ 如果在表上沒(méi)有鎖 在它上面放一個(gè)寫(xiě)鎖

◆否則 把鎖定請(qǐng)求放在寫(xiě)鎖定隊(duì)列中

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

◆如果在表上沒(méi)有寫(xiě)鎖定 把一個(gè)讀鎖定放在它上面

◆否則 把鎖請(qǐng)求放在讀鎖定隊(duì)列中

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

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

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

InnoDB 使用行鎖定 BDB 使用頁(yè)鎖定 對(duì)于這兩種存儲(chǔ)引擎 都可能存在死鎖 這是因?yàn)?在SQL語(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í)鎖定

在以下情況下 表鎖定優(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 操作 沒(méi)有任何寫(xiě)操作

lishixinzhi/Article/program/MySQL/201311/29594

mysql 的鎖以及間隙鎖

mysql 為并發(fā)事務(wù)同時(shí)對(duì)一條記錄進(jìn)行讀寫(xiě)時(shí)，提出了兩種解決方案：

1）使用 mvcc 的方法，實(shí)現(xiàn)多事務(wù)的并發(fā)讀寫(xiě)，但是這種讀只是“快照讀”，一般讀的是歷史版本數(shù)據(jù)，還有一種是“當(dāng)前讀”，一般加鎖實(shí)現(xiàn)“當(dāng)前讀”，或者 insert、update、delete 也是當(dāng)前讀。

2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫(xiě)鎖）

快照讀：就是select

當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，處理的都是當(dāng)前的數(shù)據(jù)，需要加鎖。

mysql 在 RR 級(jí)別怎么處理幻讀的呢？一般來(lái)說(shuō)，RR 級(jí)別通過(guò) mvcc 機(jī)制，保證讀到低于后面事務(wù)的數(shù)據(jù)。但是 select for update 不會(huì)觸發(fā) mvcc，它是當(dāng)前讀。如果后面事務(wù)插入數(shù)據(jù)并提交，那么在 RR 級(jí)別就會(huì)讀到插入的數(shù)據(jù)。所以，mysql 使用 行鎖 + gap 鎖（簡(jiǎn)稱 next-key 鎖）來(lái)防止當(dāng)前讀的時(shí)候插入。

Gap Lock在InnoDB的唯一作用就是防止其他事務(wù)的插入操作，以此防止幻讀的發(fā)生。

Innodb自動(dòng)使用間隙鎖的條件：

如何對(duì)MySQL數(shù)據(jù)庫(kù)表進(jìn)行鎖定

服務(wù)器由兩種表的鎖定方法：

1.內(nèi)部鎖定

內(nèi)部鎖定可以避免客戶機(jī)的請(qǐng)求相互干擾——例如，避免客戶機(jī)的SELECT查詢被另一個(gè)客戶機(jī)的UPDATE查詢所干擾。也可以利用內(nèi)部鎖定機(jī)制防止服務(wù)器在利用myisamchk或isamchk檢查或修復(fù)表時(shí)對(duì)表的訪問(wèn)。

語(yǔ)法：

鎖定表：LOCK TABLES tbl_name {READ | WRITE},[ tbl_name {READ | WRITE},…]

解鎖表：UNLOCK TABLES

LOCK TABLES為當(dāng)前線程鎖定表。UNLOCK TABLES釋放被當(dāng)前線程持有的任何鎖。當(dāng)線程發(fā)出另外一個(gè)LOCK TABLES時(shí)，或當(dāng)服務(wù)器的連接被關(guān)閉時(shí)，當(dāng)前線程鎖定的所有表自動(dòng)被解鎖。

如果一個(gè)線程獲得在一個(gè)表上的一個(gè)READ鎖，該線程(和所有其他線程)只能從表中讀。如果一個(gè)線程獲得一個(gè)表上的一個(gè)WRITE鎖，那么只有持鎖的線程READ或WRITE表，其他線程被阻止。

每個(gè)線程等待(沒(méi)有超時(shí))直到它獲得它請(qǐng)求的所有鎖。

WRITE鎖通常比READ鎖有更高的優(yōu)先級(jí)，以確保更改盡快被處理。這意味著，如果一個(gè)線程獲得READ鎖，并且然后另外一個(gè)線程請(qǐng)求一個(gè)WRITE鎖, 隨后的READ鎖請(qǐng)求將等待直到WRITE線程得到了鎖并且釋放了它。

顯然對(duì)于檢查，你只需要獲得讀鎖。再者鐘情跨下，只能讀取表，但不能修改它，因此他也允許其它客戶機(jī)讀取表。對(duì)于修復(fù)，你必須獲得些所以防止任何客戶機(jī)在你對(duì)表進(jìn)行操作時(shí)修改它。

2.外部鎖定

服務(wù)器還可以使用外部鎖定（文件級(jí)鎖）來(lái)防止其它程序在服務(wù)器使用表時(shí)修改文件。通常，在表的檢查操作中服務(wù)器將外部鎖定與myisamchk或isamchk作合使用。但是，外部鎖定在某些系統(tǒng)中是禁用的，因?yàn)樗荒芸煽康倪M(jìn)行工作。對(duì)運(yùn)行myisamchk或isamchk所選擇的過(guò)程取決于服務(wù)器是否能使用外部鎖定。如果不使用，則必修使用內(nèi)部鎖定協(xié)議。

如果服務(wù)器用--skip-locking選項(xiàng)運(yùn)行，則外部鎖定禁用。該選項(xiàng)在某些系統(tǒng)中是缺省的，如Linux?？梢酝ㄟ^(guò)運(yùn)行mysqladmin variables命令確定服務(wù)器是否能夠使用外部鎖定。檢查skip_locking變量的值并按以下方法進(jìn)行：

◆

如果skip_locking為off，則外部鎖定有效您可以繼續(xù)并運(yùn)行人和一個(gè)實(shí)用程序來(lái)檢查表。服務(wù)器和實(shí)用程序?qū)⒑献鲗?duì)表進(jìn)行訪問(wèn)。但是，運(yùn)行任何一個(gè)實(shí)用程序之前，應(yīng)該使用mysqladmin

flush-tables。為了修復(fù)表，應(yīng)該使用表的修復(fù)鎖定協(xié)議。

◆

如果skip_locaking為on，則禁用外部鎖定，所以在myisamchk或isamchk檢查修復(fù)表示服務(wù)器并不知道，最好關(guān)閉服務(wù)器。如果堅(jiān)持是服務(wù)器保持開(kāi)啟狀態(tài)，月確保在您使用此表示沒(méi)有客戶機(jī)來(lái)訪問(wèn)它。必須使用卡黨的鎖定協(xié)議告訴服務(wù)器是該表不被其他客戶機(jī)訪問(wèn)。

檢查表的鎖定協(xié)議

本節(jié)只介紹如果使用表的內(nèi)部鎖定。對(duì)于檢查表的鎖定協(xié)議，此過(guò)程只針對(duì)表的檢查，不針對(duì)表的修復(fù)。

1.調(diào)用mysql發(fā)布下列語(yǔ)句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name READ;mysqlFLUSH TABLES;

該鎖防止其它客戶機(jī)在檢查時(shí)寫(xiě)入該表和修改該表。FLUSH語(yǔ)句導(dǎo)致服務(wù)器關(guān)閉表的文件，它將刷新仍在告訴緩存中的任何為寫(xiě)入的改變。

2.執(zhí)行檢查過(guò)程

$myisamchk tbl_name$ isamchk tbl_name

3.釋放表鎖

mysqlUNLOCK TABLES;

如果myisamchk或isamchk指出發(fā)現(xiàn)該表的問(wèn)題，將需要執(zhí)行表的修復(fù)。

修復(fù)表的鎖定協(xié)議

這里只介紹如果使用表的內(nèi)部鎖定。修復(fù)表的鎖定過(guò)程類似于檢查表的鎖定過(guò)程，但有兩個(gè)區(qū)別。第一，你必須得到寫(xiě)鎖而非讀鎖。由于你需要修改表，因此根本不允許客戶機(jī)對(duì)其進(jìn)行訪問(wèn)。第二，必須在執(zhí)行修復(fù)之后發(fā)布FLUSH

TABLE語(yǔ)句，因?yàn)閙yisamchk和isamchk建立的新的索引文件，除非再次刷新改表的高速緩存，否則服務(wù)器不會(huì)注意到這個(gè)改變。本例同樣適合優(yōu)化表的過(guò)程。

1.調(diào)用mysql發(fā)布下列語(yǔ)句：

$mysql –u root –p db_namemysqlLOCK TABLE tbl_name WRITE;mysqlFLUSH TABLES;

2.做數(shù)據(jù)表的拷貝，然后運(yùn)行myisamchk和isamchk：

$cp tbl_name.* /some/other/dir$myisamchk --recover tbl_name$ isamchk --recover tbl_name

--recover選項(xiàng)只是針對(duì)安裝而設(shè)置的。這些特殊選項(xiàng)的選擇將取決與你執(zhí)行修復(fù)的類型。

3.再次刷新高速緩存，并釋放表鎖：

mysqlFLUSH TABLES;mysqlUNLOCK TABLES;

文章標(biāo)題：mysql數(shù)據(jù)庫(kù)怎么鎖 mysql鎖表操作
文章位置：http://www.jiaotiyi.com/article/ddohdsi.html