mysql怎么設(shè)置悲觀鎖

在mysql中可以使用select…for update實(shí)現(xiàn)悲觀鎖。這樣那條數(shù)據(jù)就被我們鎖定了，其它的事務(wù)必須等本次事務(wù)提交之后才能執(zhí)行。從而保證數(shù)據(jù)不會(huì)被其他事務(wù)更改從而導(dǎo)致數(shù)據(jù)的異常。但是select…for update不會(huì)阻塞select的查詢。

我們提供的服務(wù)有：成都網(wǎng)站制作、成都做網(wǎng)站、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)、微信公眾號(hào)開發(fā)、網(wǎng)站優(yōu)化、網(wǎng)站認(rèn)證、珠暉ssl等。為上千余家企事業(yè)單位解決了網(wǎng)站和推廣的問題。提供周到的售前咨詢和貼心的售后服務(wù)，是有科學(xué)管理、有技術(shù)的珠暉網(wǎng)站制作公司

需要注意的是mysql在采用InnoDB時(shí)，默認(rèn)為行鎖，且只有明確額指定主鍵，MySQL 才會(huì)執(zhí)行行鎖，鎖住對(duì)應(yīng)的那條數(shù)據(jù)，否則MySQL 將會(huì)執(zhí)行表鎖(將整個(gè)數(shù)據(jù)表單給鎖住)。

mysql中的鎖都有哪些

MySQL 中有哪些鎖？

數(shù)據(jù)庫中鎖的設(shè)計(jì)初衷處理并發(fā)問題，作為多用戶共享資源，當(dāng)出現(xiàn)并發(fā)訪問的時(shí)候，數(shù)據(jù)庫需要合理控制資源訪問規(guī)則。鎖就是實(shí)現(xiàn)這些訪問規(guī)則中的重要數(shù)據(jù)。

鎖的分類

根據(jù)加鎖范圍，MySQL 里面的鎖可以分成 全局鎖 、 表級(jí)鎖 、 行鎖 三類。

全局鎖

全局鎖，就是對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖，MySQL 提供了一個(gè)加全局讀鎖的方法，命令是:

Flush tables with read lock (FTWRL)

當(dāng)需要整個(gè)庫只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線程的：數(shù)據(jù)更新語句（增刪改），數(shù)據(jù)定義語句（建表，修改表結(jié)構(gòu)）和更新事務(wù)的提交語句將會(huì)被阻塞。

全局鎖的使用場(chǎng)景

全局鎖的定型使用場(chǎng)景，做 全庫邏輯備份 。也就是把整個(gè)庫每個(gè)表都 Select 出來，然后存成文本。

如何整個(gè)庫都只讀，會(huì)有什么問題？ 如果你在主庫上備份，那么在備份期間都不能執(zhí)行更想，業(yè)務(wù)就基本上停擺。 如果在從庫上備份，那么備份期間從庫不能執(zhí)行主庫同步過來的 binlog ,會(huì)導(dǎo)致從延遲。 既然要全庫只讀， 為什么不使用set global readonly=true的方式呢?

readonly 方式也可以讓全庫進(jìn)入只讀狀態(tài),但我還是會(huì)建議你用FTWRL方式， 主要有兩個(gè)原因：

一是， 在有些系統(tǒng)中， readonly的值會(huì)被用來做其他邏輯，比如用來判斷一個(gè)庫是主庫還是備庫。因此，修改global變量的方式影響面更大， 我不建議你使用。 二是， 在異常處理機(jī)制上有差異。如果執(zhí)行FTWRL命令之后由于客戶端發(fā)生異常斷開， 那么MySQL會(huì)自動(dòng)釋放這個(gè)全局鎖， 整個(gè)庫回到可以正常更新的狀態(tài)。而將整個(gè)庫設(shè)置為readonly之后， 如果客戶端發(fā)生異常， 則數(shù)據(jù)庫就會(huì)一直保持readonly狀態(tài)， 這樣會(huì)導(dǎo)致整個(gè)庫長(zhǎng)時(shí)間處于不可寫狀態(tài)， 風(fēng)險(xiǎn)較高 表級(jí)別鎖

MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lok, MDL）。表鎖的語法是 ：

lock tables ... read/write

與 FTWRL 類似,可以使用 unlock tables 主動(dòng)釋放鎖，也可以在客戶端斷開的時(shí)候自動(dòng)釋放。需要注意的是，lock tables語法除了會(huì)限制別的線程的讀寫外，也限定了本線程接下來的操作對(duì)象。

MDL 表級(jí)鎖

MDL 不需要顯示使用，在訪問一個(gè)表的時(shí)候自動(dòng)加上， MDL 保證讀寫的正確性，也就是說在查詢數(shù)據(jù)時(shí)，不允許有其他線程對(duì)這個(gè)表結(jié)構(gòu)做變更。

什么操作會(huì)加 MDL 鎖？

在MySQL 5.5版本中引入了MDL， 當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL讀鎖 ；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL寫鎖 。

讀鎖之間不互斥，因此可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。 讀寫之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另外一個(gè)執(zhí)行完才能執(zhí)行。 更改表結(jié)構(gòu)要注意哪些？

給一個(gè)表加字段， 或者修改字段， 或者加索引， 需要掃描全表的數(shù)據(jù)。在對(duì)大表操作的時(shí)候， 你肯定會(huì)特別小心， 以免對(duì)線上服務(wù)造成影響。而實(shí)際上， 即使是小表， 操作不慎也會(huì)出問題，導(dǎo)致整個(gè)庫的線程爆滿。

舉個(gè)例子

我們來看一下下面的操作序列， 假設(shè)表t是一個(gè)小表。

image

session A先啟動(dòng)， 這時(shí)候會(huì)對(duì)表t加一個(gè) MDL讀鎖 。由于session B需要的也是 MDL讀鎖 ， 因此可以正常執(zhí)行。 session C會(huì)被blocked， 是因?yàn)閟ession A的MDL讀鎖還沒有釋放， 而session C需要MDL寫鎖， 因此只能被阻塞，讀寫鎖互斥。 如果只有session C自己被阻塞還沒什么關(guān)系， 但是之后所有要在表t上新申請(qǐng)MDL讀鎖的請(qǐng)求也會(huì)被session C阻塞。前面我們說了，所有對(duì)表的增刪改查操作都需要先申請(qǐng)MDL讀鎖， 就都被鎖住， 等于這個(gè)表現(xiàn)在完全不可讀寫了。

如果某個(gè)表上的查詢語句頻繁， 而且客戶端有重試機(jī)制，也就是說超時(shí)后會(huì)再起一個(gè)新session 再請(qǐng)求的話， 這個(gè) 庫的線程很快就會(huì)爆滿 。事務(wù)中的MDL鎖， 在語句執(zhí)行開始時(shí)申請(qǐng)， 但是語句結(jié)束后并不會(huì)馬上釋放， 而會(huì)等到整個(gè)事務(wù)提交后再釋放。

怎么解決這個(gè) 更改表結(jié)構(gòu)問題

比較理想的機(jī)制是， 在alter table語句里面設(shè)定等待時(shí)間， 如果在這個(gè)指定的等待時(shí)間里面能夠拿到MDL寫鎖最好， 拿不到也不要阻塞后面的業(yè)務(wù)語句， 先放棄。

ALTER TABLE tbl_name NOWAIT add column ... ALTER TABLE tbl_name WAIT N add column ...

PHP MYSQL中 表鎖和行鎖 一般什么情況下使用 另外具體怎么寫

手動(dòng)加鎖：

表級(jí)鎖

lock tables tablename read;//共享鎖

lock tables tablename write;//排它鎖

unlock tables;//解鎖

php直接query就行了。

除了MyIsam，誰還會(huì)無聊到顯式加鎖？

InnoDB直接跑事務(wù)默認(rèn)會(huì)觸發(fā)隱式鎖,不需要自己lock和unlock。

使用SETNX鎖防止緩存血崩

在介紹如何使用SETNX實(shí)現(xiàn)鎖之前，先讓我們考慮這么一個(gè)問題：

假設(shè)我們現(xiàn)在有一個(gè)熱點(diǎn)數(shù)據(jù)，數(shù)據(jù)存儲(chǔ)在mysql中，我們使用redis做了一層緩存，在某個(gè)時(shí)間點(diǎn)緩存失效，這時(shí)候有大量的請(qǐng)求到來，他們同時(shí)到達(dá)，因?yàn)榫彺鎚iss，有可能同時(shí)把請(qǐng)求打到mysql，可能會(huì)出現(xiàn)血崩現(xiàn)象，那么如何避免這個(gè)問題的發(fā)生？

答案就是使用鎖，我們可以在緩存miss去mysql拿數(shù)據(jù)的時(shí)候用一把鎖，保證緩存失效只會(huì)去mysql請(qǐng)求一次。

參照官方文檔 SETNX - Redis 說明，我們可以使用

如果返回1，表示我們成功得到了鎖，并且鎖在lock timeout秒后就會(huì)invalid，這時(shí)候我們就可以去mysql拿數(shù)據(jù)并將其緩存。

如果返回0，表示鎖已被占，可以重試或是去做別的事情。

下面是原文對(duì)死鎖現(xiàn)象的描述：

如果客戶端在SETNX返回0之后都等待若干時(shí)間，然后DEL lock.foo，然后重新SETNX，會(huì)有問題嗎？

確實(shí)會(huì)有上述的問題，C1和C2同時(shí)到達(dá)，可能會(huì)同時(shí)獲得鎖，那有什么解決方案么？

關(guān)鍵地方在于GETSET指令，能夠在設(shè)置時(shí)間戳的時(shí)候判斷該時(shí)間戳是否被修改過，如果被修改過，就返回0，保證不會(huì)有兩個(gè)客戶端同時(shí)設(shè)置了新的時(shí)間戳。

關(guān)于MySQL中的表鎖和行鎖

mysql行鎖和表鎖

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或純線程并發(fā)訪問某一資源的機(jī)制。在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所在有數(shù)據(jù)庫必須解決的一個(gè)問題，鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。從這個(gè)角度來說，鎖對(duì)數(shù)據(jù)庫而言顯得尤其重要，也更加復(fù)雜。

概述

相對(duì)其他數(shù)據(jù)庫而言，MySQL的鎖機(jī)制比較簡(jiǎn)單，其最顯著的特點(diǎn)是不同的存儲(chǔ)引擎支持不同的鎖機(jī)制。

MySQL大致可歸納為以下3種鎖：

表級(jí)鎖：開銷小，加鎖快；不會(huì)出現(xiàn)死鎖；鎖定粒度大，發(fā)生鎖沖突的概率最高，并發(fā)度最低。

行級(jí)鎖：開銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。

頁面鎖：開銷和加鎖時(shí)間界于表鎖和行鎖之間；會(huì)出現(xiàn)死鎖；鎖定粒度界于表鎖和行鎖之間，并發(fā)度一般

MySQL表級(jí)鎖的鎖模式（MyISAM)

MySQL表級(jí)鎖有兩種模式：表共享鎖（Table Read Lock）和表獨(dú)占寫鎖（Table Write Lock）。

對(duì)MyISAM的讀操作，不會(huì)阻塞其他用戶對(duì)同一表請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求；

對(duì)MyISAM的寫操作，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫操作；

MyISAM表的讀操作和寫操作之間，以及寫操作之間是串行的。

當(dāng)一個(gè)線程獲得對(duì)一個(gè)表的寫鎖后，只有持有鎖線程可以對(duì)表進(jìn)行更新操作。其他線程的讀、寫操作都會(huì)等待，直到鎖被釋放為止。

MySQL表級(jí)鎖的鎖模式

MySQL的表鎖有兩種模式：表共享讀鎖（Table Read Lock）和表獨(dú)占寫鎖（Table Write Lock）。鎖模式的兼容如下表

MySQL中的表鎖兼容性

當(dāng)前鎖模式/是否兼容/請(qǐng)求鎖模式

讀鎖 ? ?是 ? ?是 ? ?否 ?

寫鎖 ? ?是 ? ?否 ? ?否 ?

可見，對(duì)MyISAM表的讀操作，不會(huì)阻塞其他用戶對(duì)同一表的讀請(qǐng)求，但會(huì)阻塞對(duì)同一表的寫請(qǐng)求；對(duì)MyISAM表的寫操作，則會(huì)阻塞其他用戶對(duì)同一表的讀和寫請(qǐng)求；MyISAM表的讀和寫操作之間，以及寫和寫操作之間是串行的！（當(dāng)一線程獲得對(duì)一個(gè)表的寫鎖后，只有持有鎖的線程可以對(duì)表進(jìn)行更新操作。其他線程的讀、寫操作都會(huì)等待，直到鎖被釋放為止。）

如何加表鎖

MyISAM在執(zhí)行查詢語句（SELECT）前，會(huì)自動(dòng)給涉及的所有表加讀鎖，在執(zhí)行更新操作（UPDATE、DELETE、INSERT等）前，會(huì)自動(dòng)給涉及的表加寫鎖，這個(gè)過程并不需要用戶干預(yù)，因此用戶一般不需要直接用LOCK TABLE命令給MyISAM表顯式加鎖。在本書的示例中，顯式加鎖基本上都是為了方便而已，并非必須如此。

給MyISAM表顯示加鎖，一般是為了一定程度模擬事務(wù)操作，實(shí)現(xiàn)對(duì)某一時(shí)間點(diǎn)多個(gè)表的一致性讀取。

要特別說明以下兩點(diǎn)內(nèi)容。

上面的例子在LOCK TABLES時(shí)加了‘local’選項(xiàng)，其作用就是在滿足MyISAM表并發(fā)插入條件的情況下，允許其他用戶在表尾插入記錄

在用LOCKTABLES給表顯式加表鎖是時(shí)，必須同時(shí)取得所有涉及表的鎖，并且MySQL支持鎖升級(jí)。也就是說，在執(zhí)行LOCK TABLES后，只能訪問顯式加鎖的這些表，不能訪問未加鎖的表；同時(shí)，如果加的是讀鎖，那么只能執(zhí)行查詢操作，而不能執(zhí)行更新操作。其實(shí)，在自動(dòng)加鎖的情況下也基本如此，MySQL問題一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會(huì)出現(xiàn)死鎖（Deadlock Free）的原因

一個(gè)session使用LOCK TABLE 命令給表film_text加了讀鎖，這個(gè)session可以查詢鎖定表中的記錄，但更新或訪問其他表都會(huì)提示錯(cuò)誤；同時(shí)，另外一個(gè)session可以查詢表中的記錄，但更新就會(huì)出現(xiàn)鎖等待。

當(dāng)使用LOCK TABLE時(shí)，不僅需要一次鎖定用到的所有表，而且，同一個(gè)表在SQL語句中出現(xiàn)多少次，就要通過與SQL語句中相同的別名鎖多少次，否則也會(huì)出錯(cuò)！

并發(fā)鎖

在一定條件下，MyISAM也支持查詢和操作的并發(fā)進(jìn)行。

MyISAM存儲(chǔ)引擎有一個(gè)系統(tǒng)變量concurrent_insert，專門用以控制其并發(fā)插入的行為，其值分別可以為0、1或2。

當(dāng)concurrent_insert設(shè)置為0時(shí)，不允許并發(fā)插入。

當(dāng)concurrent_insert設(shè)置為1時(shí)，如果MyISAM允許在一個(gè)讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄。這也是MySQL的默認(rèn)設(shè)置。

當(dāng)concurrent_insert設(shè)置為2時(shí)，無論MyISAM表中有沒有空洞，都允許在表尾插入記錄，都允許在表尾并發(fā)插入記錄。

可以利用MyISAM存儲(chǔ)引擎的并發(fā)插入特性，來解決應(yīng)用中對(duì)同一表查詢和插入鎖爭(zhēng)用。例如，將concurrent_insert系統(tǒng)變量為2，總是允許并發(fā)插入；同時(shí)，通過定期在系統(tǒng)空閑時(shí)段執(zhí)行OPTIONMIZE TABLE語句來整理空間碎片，收到因刪除記錄而產(chǎn)生的中間空洞。

MyISAM的鎖調(diào)度

前面講過，MyISAM存儲(chǔ)引擎的讀和寫鎖是互斥，讀操作是串行的。那么，一個(gè)進(jìn)程請(qǐng)求某個(gè)MyISAM表的讀鎖，同時(shí)另一個(gè)進(jìn)程也請(qǐng)求同一表的寫鎖，MySQL如何處理呢？答案是寫進(jìn)程先獲得鎖。不僅如此，即使讀進(jìn)程先請(qǐng)求先到鎖等待隊(duì)列，寫請(qǐng)求后到，寫鎖也會(huì)插到讀請(qǐng)求之前！這是因?yàn)镸ySQL認(rèn)為寫請(qǐng)求一般比讀請(qǐng)求重要。這也正是MyISAM表不太適合于有大量更新操作和查詢操作應(yīng)用的原因，因?yàn)椋罅康母虏僮鲿?huì)造成查詢操作很難獲得讀鎖，從而可能永遠(yuǎn)阻塞。這種情況有時(shí)可能會(huì)變得非常糟糕！幸好我們可以通過一些設(shè)置來調(diào)節(jié)MyISAM的調(diào)度行為。

通過指定啟動(dòng)參數(shù)low-priority-updates，使MyISAM引擎默認(rèn)給予讀請(qǐng)求以優(yōu)先的權(quán)利。

通過執(zhí)行命令SET LOW_PRIORITY_UPDATES=1，使該連接發(fā)出的更新請(qǐng)求優(yōu)先級(jí)降低。

通過指定INSERT、UPDATE、DELETE語句的LOW_PRIORITY屬性，降低該語句的優(yōu)先級(jí)。

雖然上面3種方法都是要么更新優(yōu)先，要么查詢優(yōu)先的方法，但還是可以用其來解決查詢相對(duì)重要的應(yīng)用（如用戶登錄系統(tǒng)）中，讀鎖等待嚴(yán)重的問題。

另外，MySQL也提供了一種折中的辦法來調(diào)節(jié)讀寫沖突，即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個(gè)合適的值，當(dāng)一個(gè)表的讀鎖達(dá)到這個(gè)值后，MySQL變暫時(shí)將寫請(qǐng)求的優(yōu)先級(jí)降低，給讀進(jìn)程一定獲得鎖的機(jī)會(huì)。

上面已經(jīng)討論了寫優(yōu)先調(diào)度機(jī)制和解決辦法。這里還要強(qiáng)調(diào)一點(diǎn)：一些需要長(zhǎng)時(shí)間運(yùn)行的查詢操作，也會(huì)使寫進(jìn)程“餓死”！因此，應(yīng)用中應(yīng)盡量避免出現(xiàn)長(zhǎng)時(shí)間運(yùn)行的查詢操作，不要總想用一條SELECT語句來解決問題。因?yàn)檫@種看似巧妙的SQL語句，往往比較復(fù)雜，執(zhí)行時(shí)間較長(zhǎng)，在可能的情況下可以通過使用中間表等措施對(duì)SQL語句做一定的“分解”，使每一步查詢都能在較短時(shí)間完成，從而減少鎖沖突。如果復(fù)雜查詢不可避免，應(yīng)盡量安排在數(shù)據(jù)庫空閑時(shí)段執(zhí)行，比如一些定期統(tǒng)計(jì)可以安排在夜間執(zhí)行。

InnoDB鎖問題

InnoDB與MyISAM的最大不同有兩點(diǎn)：一是支持事務(wù)（TRANSACTION）；二是采用了行級(jí)鎖。

行級(jí)鎖和表級(jí)鎖本來就有許多不同之處，另外，事務(wù)的引入也帶來了一些新問題。

1.事務(wù)（Transaction）及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元，事務(wù)具有4屬性，通常稱為事務(wù)的ACID屬性。

原性性（Actomicity）：事務(wù)是一個(gè)原子操作單元，其對(duì)數(shù)據(jù)的修改，要么全都執(zhí)行，要么全都不執(zhí)行。

一致性（Consistent）：在事務(wù)開始和完成時(shí)，數(shù)據(jù)都必須保持一致狀態(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改，以操持完整性；事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹索引或雙向鏈表）也都必須是正確的。

隔離性（Isolation）：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行。這意味著事務(wù)處理過程中的中間狀態(tài)對(duì)外部是不可見的，反之亦然。

持久性（Durable）：事務(wù)完成之后，它對(duì)于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

2.并發(fā)事務(wù)帶來的問題

相對(duì)于串行處理來說，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫資源的利用率，提高數(shù)據(jù)庫系統(tǒng)的事務(wù)吞吐量，從而可以支持可以支持更多的用戶。但并發(fā)事務(wù)處理也會(huì)帶來一些問題，主要包括以下幾種情況。

更新丟失（Lost Update）：當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生丟失更新問題——最后的更新覆蓋了其他事務(wù)所做的更新。例如，兩個(gè)編輯人員制作了同一文檔的電子副本。每個(gè)編輯人員獨(dú)立地更改其副本，然后保存更改后的副本，這樣就覆蓋了原始文檔。最后保存其更改保存其更改副本的編輯人員覆蓋另一個(gè)編輯人員所做的修改。如果在一個(gè)編輯人員完成并提交事務(wù)之前，另一個(gè)編輯人員不能訪問同一文件，則可避免此問題

臟讀（Dirty Reads）：一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)并提交前，這條記錄的數(shù)據(jù)就處于不一致狀態(tài)；這時(shí)，另一個(gè)事務(wù)也來讀取同一條記錄，如果不加控制，第二個(gè)事務(wù)讀取了這些“臟”的數(shù)據(jù)，并據(jù)此做進(jìn)一步的處理，就會(huì)產(chǎn)生未提交的數(shù)據(jù)依賴關(guān)系。這種現(xiàn)象被形象地叫做“臟讀”。

不可重復(fù)讀（Non-Repeatable Reads）：一個(gè)事務(wù)在讀取某些數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象叫做“不可重復(fù)讀”。

幻讀（Phantom Reads）：一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”。

3.事務(wù)隔離級(jí)別

在并發(fā)事務(wù)處理帶來的問題中，“更新丟失”通常應(yīng)該是完全避免的。但防止更新丟失，并不能單靠數(shù)據(jù)庫事務(wù)控制器來解決，需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來解決，因此，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任。

“臟讀”、“不可重復(fù)讀”和“幻讀”，其實(shí)都是數(shù)據(jù)庫讀一致性問題，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決。數(shù)據(jù)庫實(shí)現(xiàn)事務(wù)隔離的方式，基本可以分為以下兩種。

一種是在讀取數(shù)據(jù)前，對(duì)其加鎖，阻止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改。

另一種是不用加任何鎖，通過一定機(jī)制生成一個(gè)數(shù)據(jù)請(qǐng)求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照（Snapshot），并用這個(gè)快照來提供一定級(jí)別（語句級(jí)或事務(wù)級(jí)）的一致性讀取。從用戶的角度，好像是數(shù)據(jù)庫可以提供同一數(shù)據(jù)的多個(gè)版本，因此，這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（MultiVersion Concurrency Control，簡(jiǎn)稱MVCC或MCC），也經(jīng)常稱為多版本數(shù)據(jù)庫。

數(shù)據(jù)庫的事務(wù)隔離級(jí)別越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行，這顯然與“并發(fā)”是矛盾的，同時(shí)，不同的應(yīng)用對(duì)讀一致性和事務(wù)隔離程度的要求也是不同的，比如許多應(yīng)用對(duì)“不可重復(fù)讀”和“幻讀”并不敏感，可能更關(guān)心數(shù)據(jù)并發(fā)訪問的能力。

為了解決“隔離”與“并發(fā)”的矛盾，ISO/ANSI SQL92定義了4個(gè)事務(wù)隔離級(jí)別，每個(gè)級(jí)別的隔離程度不同，允許出現(xiàn)的副作用也不同，應(yīng)用可以根據(jù)自己業(yè)務(wù)邏輯要求，通過選擇不同的隔離級(jí)別來平衡＂隔離＂與＂并發(fā)＂的矛盾

事務(wù)4種隔離級(jí)別比較

隔離級(jí)別/讀數(shù)據(jù)一致性及允許的并發(fā)副作用 ? ?讀數(shù)據(jù)一致性 ? ?臟讀 ? ?不可重復(fù)讀 ? ?幻讀 ?

未提交讀（Read uncommitted）

最低級(jí)別，只能保證不讀取物理上損壞的數(shù)據(jù) ? ?是 ? ?是 ? ?是 ?

已提交度（Read committed） ? ?語句級(jí) ? ?否 ? ?是 ? ?是 ?

可重復(fù)讀（Repeatable read） ? ?事務(wù)級(jí) ? ?否 ? ?否 ? ?是 ?

可序列化（Serializable） ? ?最高級(jí)別，事務(wù)級(jí) ? ?否 ? ?否 ? ?否 ?

最后要說明的是：各具體數(shù)據(jù)庫并不一定完全實(shí)現(xiàn)了上述4個(gè)隔離級(jí)別，例如，Oracle只提供Read committed和Serializable兩個(gè)標(biāo)準(zhǔn)級(jí)別，另外還自己定義的Read only隔離級(jí)別：SQL Server除支持上述ISO/ANSI SQL92定義的4個(gè)級(jí)別外，還支持一個(gè)叫做＂快照＂的隔離級(jí)別，但嚴(yán)格來說它是一個(gè)用MVCC實(shí)現(xiàn)的Serializable隔離級(jí)別。MySQL支持全部4個(gè)隔離級(jí)別，但在具體實(shí)現(xiàn)時(shí)，有一些特點(diǎn)，比如在一些隔離級(jí)下是采用MVCC一致性讀，但某些情況又不是。

獲取InonoD行鎖爭(zhēng)用情況

可以通過檢查InnoDB_row_lock狀態(tài)變量來分析系統(tǒng)上的行鎖的爭(zhēng)奪情況：

如果發(fā)現(xiàn)爭(zhēng)用比較嚴(yán)重，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高，還可以通過設(shè)置InnoDB Monitors來進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等，并分析鎖爭(zhēng)用的原因。?

InnoDB的行鎖模式及加鎖方法

InnoDB實(shí)現(xiàn)了以下兩種類型的行鎖。

共享鎖（s）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。

排他鎖（X）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實(shí)現(xiàn)多粒度鎖機(jī)制，InnoDB還有兩種內(nèi)部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

意向共享鎖（IS）：事務(wù)打算給數(shù)據(jù)行共享鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加共享鎖前必須先取得該表的IS鎖。

意向排他鎖（IX）：事務(wù)打算給數(shù)據(jù)行加排他鎖，事務(wù)在給一個(gè)數(shù)據(jù)行加排他鎖前必須先取得該表的IX鎖。

InnoDB行鎖模式兼容性列表

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前的鎖兼容，InnoDB就請(qǐng)求的鎖授予該事務(wù)；反之，如果兩者兩者不兼容，該事務(wù)就要等待鎖釋放。

意向鎖是InnoDB自動(dòng)加的，不需用戶干預(yù)。對(duì)于UPDATE、DELETE和INSERT語句，InnoDB會(huì)自動(dòng)給涉及及數(shù)據(jù)集加排他鎖（X）；對(duì)于普通SELECT語句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X）；對(duì)于普通SELECT語句，InnoDB不會(huì)任何鎖；事務(wù)可以通過以下語句顯示給記錄集加共享鎖或排鎖。

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他鎖（X）：SELECT * FROM table_name WHERE ...?FOR UPDATE

用SELECT .. IN SHARE MODE獲得共享鎖，主要用在需要數(shù)據(jù)依存關(guān)系時(shí)確認(rèn)某行記錄是否存在，并確保沒有人對(duì)這個(gè)記錄進(jìn)行UPDATE或者DELETE操作。但是如果當(dāng)前事務(wù)也需要對(duì)該記錄進(jìn)行更新操作，則很有可能造成死鎖，對(duì)于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用，應(yīng)該使用SELECT ... FOR UPDATE方式獲取排他鎖。

InnoDB行鎖實(shí)現(xiàn)方式

InnoDB行鎖是通過索引上的索引項(xiàng)來實(shí)現(xiàn)的，這一點(diǎn)MySQL與Oracle不同，后者是通過在數(shù)據(jù)中對(duì)相應(yīng)數(shù)據(jù)行加鎖來實(shí)現(xiàn)的。InnoDB這種行鎖實(shí)現(xiàn)特點(diǎn)意味者：只有通過索引條件檢索數(shù)據(jù)，InnoDB才會(huì)使用行級(jí)鎖，否則，InnoDB將使用表鎖！

在實(shí)際應(yīng)用中，要特別注意InnoDB行鎖的這一特性，不然的話，可能導(dǎo)致大量的鎖沖突，從而影響并發(fā)性能。

什么時(shí)候使用表鎖

對(duì)于InnoDB表，在絕大部分情況下都應(yīng)該使用行級(jí)鎖，因?yàn)槭聞?wù)和行鎖往往是我們之所以選擇InnoDB表的理由。但在個(gè)另特殊事務(wù)中，也可以考慮使用表級(jí)鎖。

第一種情況是：事務(wù)需要更新大部分或全部數(shù)據(jù)，表又比較大，如果使用默認(rèn)的行鎖，不僅這個(gè)事務(wù)執(zhí)行效率低，而且可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務(wù)的執(zhí)行速度。

第二種情況是：事務(wù)涉及多個(gè)表，比較復(fù)雜，很可能引起死鎖，造成大量事務(wù)回滾。這種情況也可以考慮一次性鎖定事務(wù)涉及的表，從而避免死鎖、減少數(shù)據(jù)庫因事務(wù)回滾帶來的開銷。

當(dāng)然，應(yīng)用中這兩種事務(wù)不能太多，否則，就應(yīng)該考慮使用MyISAM表。

在InnoDB下 ，使用表鎖要注意以下兩點(diǎn)。

（1）使用LOCK TALBES雖然可以給InnoDB加表級(jí)鎖，但必須說明的是，表鎖不是由InnoDB存儲(chǔ)引擎層管理的，而是由其上一層MySQL Server負(fù)責(zé)的，僅當(dāng)autocommit=0、innodb_table_lock=1（默認(rèn)設(shè)置）時(shí)，InnoDB層才能知道MySQL加的表鎖，MySQL Server才能感知InnoDB加的行鎖，這種情況下，InnoDB才能自動(dòng)識(shí)別涉及表級(jí)鎖的死鎖；否則，InnoDB將無法自動(dòng)檢測(cè)并處理這種死鎖。

（2）在用LOCAK TABLES對(duì)InnoDB鎖時(shí)要注意，要將AUTOCOMMIT設(shè)為0，否則MySQL不會(huì)給表加鎖；事務(wù)結(jié)束前，不要用UNLOCAK TABLES釋放表鎖，因?yàn)閁NLOCK TABLES會(huì)隱含地提交事務(wù)；COMMIT或ROLLBACK產(chǎn)不能釋放用LOCAK TABLES加的表級(jí)鎖，必須用UNLOCK TABLES釋放表鎖，正確的方式見如下語句。

關(guān)于死鎖

MyISAM表鎖是deadlock free的，這是因?yàn)镸yISAM總是一次性獲得所需的全部鎖，要么全部滿足，要么等待，因此不會(huì)出現(xiàn)死鎖。但是在InnoDB中，除單個(gè)SQL組成的事務(wù)外，鎖是逐步獲得的，這就決定了InnoDB發(fā)生死鎖是可能的。

發(fā)生死鎖后，InnoDB一般都能自動(dòng)檢測(cè)到，并使一個(gè)事務(wù)釋放鎖并退回，另一個(gè)事務(wù)獲得鎖，繼續(xù)完成事務(wù)。但在涉及外部鎖，或涉及鎖的情況下，InnoDB并不能完全自動(dòng)檢測(cè)到死鎖，這需要通過設(shè)置鎖等待超時(shí)參數(shù)innodb_lock_wait_timeout來解決。需要說明的是，這個(gè)參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲取所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源，造成嚴(yán)重性能問題，甚至拖垮數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時(shí)閾值，可以避免這種情況發(fā)生。

通常來說，死鎖都是應(yīng)用設(shè)計(jì)的問題，通過調(diào)整業(yè)務(wù)流程、數(shù)據(jù)庫對(duì)象設(shè)計(jì)、事務(wù)大小、以及訪問數(shù)據(jù)庫的SQL語句，絕大部分都可以避免。下面就通過實(shí)例來介紹幾種死鎖的常用方法。

（1）在應(yīng)用中，如果不同的程序會(huì)并發(fā)存取多個(gè)表，應(yīng)盡量約定以相同的順序?yàn)樵L問表，這樣可以大大降低產(chǎn)生死鎖的機(jī)會(huì)。如果兩個(gè)session訪問兩個(gè)表的順序不同，發(fā)生死鎖的機(jī)會(huì)就非常高！但如果以相同的順序來訪問，死鎖就可能避免。

（2）在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對(duì)數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來處理記錄，也可以大大降低死鎖的可能。

（3）在事務(wù)中，如果要更新記錄，應(yīng)該直接申請(qǐng)足夠級(jí)別的鎖，即排他鎖，而不應(yīng)該先申請(qǐng)共享鎖，更新時(shí)再申請(qǐng)排他鎖，甚至死鎖。

（4）在REPEATEABLE-READ隔離級(jí)別下，如果兩個(gè)線程同時(shí)對(duì)相同條件記錄用SELECT...ROR UPDATE加排他鎖，在沒有符合該記錄情況下，兩個(gè)線程都會(huì)加鎖成功。程序發(fā)現(xiàn)記錄尚不存在，就試圖插入一條新記錄，如果兩個(gè)線程都這么做，就會(huì)出現(xiàn)死鎖。這種情況下，將隔離級(jí)別改成READ COMMITTED，就可以避免問題。

（5）當(dāng)隔離級(jí)別為READ COMMITED時(shí)，如果兩個(gè)線程都先執(zhí)行SELECT...FOR UPDATE，判斷是否存在符合條件的記錄，如果沒有，就插入記錄。此時(shí)，只有一個(gè)線程能插入成功，另一個(gè)線程會(huì)出現(xiàn)鎖等待，當(dāng)?shù)?個(gè)線程提交后，第2個(gè)線程會(huì)因主鍵重出錯(cuò)，但雖然這個(gè)線程出錯(cuò)了，卻會(huì)獲得一個(gè)排他鎖！這時(shí)如果有第3個(gè)線程又來申請(qǐng)排他鎖，也會(huì)出現(xiàn)死鎖。對(duì)于這種情況，可以直接做插入操作，然后再捕獲主鍵重異常，或者在遇到主鍵重錯(cuò)誤時(shí)，總是執(zhí)行ROLLBACK釋放獲得的排他鎖。

盡管通過上面的設(shè)計(jì)和優(yōu)化等措施，可以大減少死鎖，但死鎖很難完全避免。因此，在程序設(shè)計(jì)中總是捕獲并處理死鎖異常是一個(gè)很好的編程習(xí)慣。

如果出現(xiàn)死鎖，可以用SHOW INNODB STATUS命令來確定最后一個(gè)死鎖產(chǎn)生的原因和改進(jìn)措施。

總結(jié)

對(duì)于MyISAM的表鎖，主要有以下幾點(diǎn)

（1）共享讀鎖（S）之間是兼容的，但共享讀鎖（S）和排他寫鎖（X）之間，以及排他寫鎖之間（X）是互斥的，也就是說讀和寫是串行的。

（2）在一定條件下，MyISAM允許查詢和插入并發(fā)執(zhí)行，我們可以利用這一點(diǎn)來解決應(yīng)用中對(duì)同一表和插入的鎖爭(zhēng)用問題。

（3）MyISAM默認(rèn)的鎖調(diào)度機(jī)制是寫優(yōu)先，這并不一定適合所有應(yīng)用，用戶可以通過設(shè)置LOW_PRIPORITY_UPDATES參數(shù)，或在INSERT、UPDATE、DELETE語句中指定LOW_PRIORITY選項(xiàng)來調(diào)節(jié)讀寫鎖的爭(zhēng)用。

（4）由于表鎖的鎖定粒度大，讀寫之間又是串行的，因此，如果更新操作較多，MyISAM表可能會(huì)出現(xiàn)嚴(yán)重的鎖等待，可以考慮采用InnoDB表來減少鎖沖突。

對(duì)于InnoDB表，主要有以下幾點(diǎn)

（1）InnoDB的行銷是基于索引實(shí)現(xiàn)的，如果不通過索引訪問數(shù)據(jù)，InnoDB會(huì)使用表鎖。

（2）InnoDB間隙鎖機(jī)制，以及InnoDB使用間隙鎖的原因。

（3）在不同的隔離級(jí)別下，InnoDB的鎖機(jī)制和一致性讀策略不同。

（4）MySQL的恢復(fù)和復(fù)制對(duì)InnoDB鎖機(jī)制和一致性讀策略也有較大影響。

（5）鎖沖突甚至死鎖很難完全避免。

在了解InnoDB的鎖特性后，用戶可以通過設(shè)計(jì)和SQL調(diào)整等措施減少鎖沖突和死鎖，包括：

盡量使用較低的隔離級(jí)別

精心設(shè)計(jì)索引，并盡量使用索引訪問數(shù)據(jù)，使加鎖更精確，從而減少鎖沖突的機(jī)會(huì)。

選擇合理的事務(wù)大小，小事務(wù)發(fā)生鎖沖突的幾率也更小。

給記錄集顯示加鎖時(shí)，最好一次性請(qǐng)求足夠級(jí)別的鎖。比如要修改數(shù)據(jù)的話，最好直接申請(qǐng)排他鎖，而不是先申請(qǐng)共享鎖，修改時(shí)再請(qǐng)求排他鎖，這樣容易產(chǎn)生死鎖。

不同的程序訪問一組表時(shí)，應(yīng)盡量約定以相同的順序訪問各表，對(duì)一個(gè)表而言，盡可能以固定的順序存取表中的行。這樣可以大減少死鎖的機(jī)會(huì)。

盡量用相等條件訪問數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響。

不要申請(qǐng)超過實(shí)際需要的鎖級(jí)別；除非必須，查詢時(shí)不要顯示加鎖。

對(duì)于一些特定的事務(wù)，可以使用表鎖來提高處理速度或減少死鎖的可能

網(wǎng)頁題目：nk鎖怎么用mysql,時(shí)間鎖怎么設(shè)置時(shí)間
地址分享：http://m.2m8n56k.cn/article24/hojdje.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站排名、營(yíng)銷型網(wǎng)站建設(shè)、網(wǎng)站策劃、網(wǎng)頁設(shè)計(jì)公司、外貿(mào)建站、網(wǎng)站收錄

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：[email protected]。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)