1 MySQL的三種鎖

1.1 表鎖

• 開(kāi)銷(xiāo)小，加鎖快
• 不會(huì)出現(xiàn)死鎖
• 鎖定粒度大，發(fā)生鎖沖突的概率最高古拴，并發(fā)度最低

1.2 行鎖

• 開(kāi)銷(xiāo)大，加鎖慢
• 會(huì)出現(xiàn)死鎖
• 鎖定粒度小真友，發(fā)生鎖沖突的概率最低黄痪，并發(fā)度最高

1.3 頁(yè)鎖

• 開(kāi)銷(xiāo)和加鎖時(shí)間介于表鎖和行鎖之間
• 會(huì)出現(xiàn)死鎖
• 鎖定粒度介于表鎖和行鎖之間，并發(fā)度一般

1.4 引擎與鎖

• MyISAM和MEMORY支持表鎖
• BDB支持頁(yè)鎖,也支持表鎖
• Innodb既支持行鎖,也支持表鎖,默認(rèn)行鎖

1.5 查詢(xún)表鎖爭(zhēng)用情況

檢查table_locks_waited和table_locks_immediate狀態(tài)變量分析

• table_locks_immediate : 可以立即獲取鎖的次數(shù)
• table_locks_waited : 不能立即獲取鎖盔然，需要等待鎖的次數(shù)

image

image

table_locks_waited 的值越高桅打，則說(shuō)明存在嚴(yán)重的表級(jí)鎖的爭(zhēng)用情況

2 表鎖模式（MyISAM)

ＭySQL的表鎖有兩種模式

• 表共享讀鎖（Table Read Lock）
• 表獨(dú)占寫(xiě)鎖（Table Write Lock）

2.1 表鎖兼容性

鎖模式的兼容如下表

可見(jiàn)，對(duì)ＭyISAM表的讀操作,不會(huì)阻塞其他用戶(hù)對(duì)同一表的讀請(qǐng)求,但會(huì)阻塞對(duì)同一表的寫(xiě)請(qǐng)求;

對(duì)ＭyISAM表的寫(xiě)操作,則會(huì)阻塞其他用戶(hù)對(duì)同一表的讀和寫(xiě)請(qǐng)求;

ＭyISAM表的讀和寫(xiě)操作之間,以及寫(xiě)和寫(xiě)操作之間是串行的!(當(dāng)某一線程獲得對(duì)一個(gè)表的寫(xiě)鎖后,只有持有鎖的線程可以對(duì)表進(jìn)行更新操作.其他線程的讀愈案、寫(xiě)操作都會(huì)等待,直到鎖被釋放為止)

2.2 如何加表鎖

對(duì)于 MyISAM 引擎

• 執(zhí)行select前,會(huì)自動(dòng)給涉及的所有表加 讀
• 執(zhí)行更新（update,delete,insert)會(huì)自動(dòng)給涉及到的表加 寫(xiě)

不需要用戶(hù)直接顯式用lock table命令

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

2.2.1 實(shí)例

• 訂單表 - orders
  記錄各訂單的總金額total
• 訂單明細(xì)表 - order_detail
  記錄各訂單每一產(chǎn)品的金額小計(jì)subtotal

假設(shè)我們需要檢查這兩個(gè)表的金額合計(jì)是否相符,可能就需要執(zhí)行如下兩條SQL

圖片上傳失敗...(image-3017e3-1547370332969)

這時(shí),如果不先給這兩個(gè)表加鎖,就可能產(chǎn)生錯(cuò)誤的結(jié)果;

因?yàn)榈谝粭l語(yǔ)句執(zhí)行過(guò)程中,order_detail表可能已經(jīng)發(fā)生了改變.

因此,正確寫(xiě)法應(yīng)該如下

圖片上傳失敗...(image-8081d7-1547370332969)

2.2.2 注意點(diǎn)

• 上面的例子在LOCK TABLES時(shí)加了‘local’選項(xiàng)站绪，其作用就是在滿足MyISAM表并發(fā)插入條件的情況下遭铺，允許其他用戶(hù)在表尾插入記錄
• 在用LOCK TABLES給表顯式加表鎖時(shí)，必須同時(shí)取得所有涉及表的鎖恢准，并且MySQL支持鎖升級(jí);
  也就是說(shuō)魂挂，在執(zhí)行LOCK TABLES后，只能訪問(wèn)顯式加鎖的這些表馁筐，不能訪問(wèn)未加鎖的表涂召；
  同時(shí)，如果加的是讀鎖敏沉，那么只能執(zhí)行查詢(xún)操作果正，而不能執(zhí)行更新操作
  其實(shí)炎码，在自動(dòng)加鎖的情況下也基本如此，MySQL會(huì)一次獲得SQL語(yǔ)句所需要的全部鎖.這也正是MyISAM表不會(huì)出現(xiàn)死鎖（Deadlock Free）的原因

2.3 tips

當(dāng)使用lock tables時(shí),不僅需要一次鎖定用到的所有表

且同一表在SQL語(yǔ)句中出現(xiàn)多少次,就要通過(guò)與SQL語(yǔ)句中別名鎖多少次

lock table actor read

會(huì)提示錯(cuò)誤

select a.first_name.....

需要對(duì)別名分別鎖定

lock table actor as a read,actor as b read;

3 MyISAM的并發(fā)鎖

在一定條件下辅肾，MyISAM也支持并發(fā)插入和讀取

3.1 系統(tǒng)變量 : concurrent_insert

控制其并發(fā)插入的行為,其值分別可以為

• 0 不允許并發(fā)插入,所有插入對(duì)表加互斥鎖
• 1 只要表中無(wú)空洞,就允許并發(fā)插入.
  MyISAM允許在一個(gè)讀表的同時(shí)，另一個(gè)進(jìn)程從表尾插入記錄(MySQL的默認(rèn)設(shè)置)
• 2 無(wú)論MyISAM表中有無(wú)空洞,都強(qiáng)制在表尾并發(fā)插入記錄
  若無(wú)讀線程,新行插入空洞中

可以利用MyISAM的并發(fā)插入特性轮锥，來(lái)解決應(yīng)用中對(duì)同表查詢(xún)和插入的鎖爭(zhēng)用

例如,將concurrent_insert系統(tǒng)變量設(shè)為2矫钓，總是允許并發(fā)插入;

同時(shí),通過(guò)定期在系統(tǒng)空閑時(shí)段執(zhí)行OPTIONMIZE TABLE語(yǔ)句來(lái)整理空間碎片，收到因刪除記錄而產(chǎn)生的中間空洞

刪除操作不會(huì)重整整個(gè)表,只是把 行 標(biāo)記為刪除,在表中留下空洞

MyISAM傾向于在可能時(shí)填滿這些空洞,插入時(shí)就會(huì)重用這些空間,無(wú)空洞則把新行插到表尾

3.2 MyISAM的鎖調(diào)度

MyISAM的讀和寫(xiě)鎖互斥,讀操作串行的

• 一個(gè)進(jìn)程請(qǐng)求某個(gè)MyISAM表的讀鎖,同時(shí)另一個(gè)進(jìn)程也請(qǐng)求同表的寫(xiě)鎖舍杜，MySQL如何處理呢新娜？
  寫(xiě)進(jìn)程先獲得鎖!!!
  不僅如此,即使讀進(jìn)程先請(qǐng)求先到鎖等待隊(duì)列,寫(xiě)請(qǐng)求后到,寫(xiě)鎖也會(huì)插到讀請(qǐng)求之前!!!

這是因?yàn)镸ySQL認(rèn)為寫(xiě)請(qǐng)求一般比讀請(qǐng)求重要

這也正是MyISAM表不適合有大量更新 / 查詢(xún)操作應(yīng)用的原因

大量的更新操作會(huì)造成查詢(xún)操作很難獲得讀鎖,從而可能永遠(yuǎn)阻塞

幸好,我們可以通過(guò)一些設(shè)置來(lái)調(diào)節(jié)MyISAM的調(diào)度行為

• 指定啟動(dòng)參數(shù)low-priority-updates
  使MyISAM引擎默認(rèn)給予讀請(qǐng)求以?xún)?yōu)先權(quán)利
• 執(zhí)行命令SET LOW_PRIORITY_UPDATES=1
  使該連接發(fā)出的更新請(qǐng)求優(yōu)先級(jí)降低
• 指定INSERT、UPDATE既绩、DELETE語(yǔ)句的LOW_PRIORITY屬性
  降低該語(yǔ)句的優(yōu)先級(jí)

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

另外私杜，MySQL也提供了一種折中的辦法來(lái)調(diào)節(jié)讀寫(xiě)沖突;

即給系統(tǒng)參數(shù)max_write_lock_count設(shè)置一個(gè)合適的值;

當(dāng)一個(gè)表的讀鎖達(dá)到這個(gè)值后，MySQL便暫時(shí)將寫(xiě)請(qǐng)求的優(yōu)先級(jí)降低救欧，給讀進(jìn)程一定獲得鎖的機(jī)會(huì)

* * *

4 InnoDB鎖

InnoDB與MyISAM的最大不同有兩點(diǎn)

• 支持事務(wù)
• 采用行鎖

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

4.1 事務(wù)

一組SQL語(yǔ)句組成的邏輯處理單元

• 原子性(Actomicity)
  事務(wù)是一個(gè)原子操作單元笆怠，其對(duì)數(shù)據(jù)的修改铝耻，要么全都執(zhí)行，要么全都不執(zhí)行
• 一致性（Consistent）
  在事務(wù)開(kāi)始和完成時(shí)蹬刷，數(shù)據(jù)都必須保持一致?tīng)顟B(tài)
  這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改瓢捉，以保持完整性
  事務(wù)結(jié)束時(shí)，所有的內(nèi)部數(shù)據(jù)結(jié)構(gòu)（如B樹(shù)索引或雙向鏈表）也都必須是正確的
• 隔離性（Isolation）
  一個(gè)事務(wù)所做的修改在最終提交前對(duì)其他事務(wù)不可見(jiàn)
• 持久性（Durability）
  一旦事務(wù)提交办成，它對(duì)于數(shù)據(jù)的修改會(huì)持久化到DB

4.2 事務(wù)的問(wèn)題

相對(duì)于串行處理來(lái)說(shuō)泡态，并發(fā)事務(wù)處理能大大增加數(shù)據(jù)庫(kù)資源的利用率，提高數(shù)據(jù)庫(kù)系統(tǒng)的事務(wù)吞吐量迂卢，從而可以支持可以支持更多的用戶(hù)

但并發(fā)事務(wù)處理也會(huì)帶來(lái)一些問(wèn)題某弦，主要包括以下幾種情況

• 更新丟失（Lost Update）
  當(dāng)多個(gè)事務(wù)選擇同一行,然后基于最初選定值更新該行時(shí)，由于事務(wù)隔離性冷守，最后的更新覆蓋了其他事務(wù)所做的更新.
  例如，兩個(gè)編輯人員制作了同一文檔的電子副本惊科。每個(gè)編輯人員獨(dú)立地更改其副本拍摇，然后保存更改后的副本，這樣就覆蓋了原始文檔馆截。最后保存其更改保存其更改副本的編輯人員覆蓋另一個(gè)編輯人員所做的修改;
  如果在一個(gè)編輯人員完成并提交事務(wù)之前充活，另一個(gè)編輯人員不能訪問(wèn)同一文件蜂莉，則可避免此問(wèn)題
• 臟讀（Dirty Reads）
  一個(gè)事務(wù)正在對(duì)一條記錄做修改，在該事務(wù)提交前混卵，這條記錄的數(shù)據(jù)就處于不一致?tīng)顟B(tài)
  這時(shí)映穗，另一個(gè)事務(wù)也來(lái)讀取同一條記錄，讀取了這些未提交的數(shù)據(jù)
• 不可重復(fù)讀（Non-Repeatable Reads）
  一個(gè)事務(wù)在讀取某些數(shù)據(jù)已經(jīng)發(fā)生了改變幕随、或某些記錄已經(jīng)被刪除
• 幻讀（Phantom Reads)
  一個(gè)事務(wù)按相同的查詢(xún)條件重新讀取以前檢索過(guò)的數(shù)據(jù)蚁滋，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢(xún)條件的新數(shù)據(jù)

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

在并發(fā)事務(wù)的問(wèn)題中，“更新丟失”通常應(yīng)該是完全避免的;

但防止更新丟失,并不能單靠數(shù)據(jù)庫(kù)事務(wù)控制器來(lái)解決,需要應(yīng)用程序?qū)σ碌臄?shù)據(jù)加必要的鎖來(lái)解決,因此赘淮，防止更新丟失應(yīng)該是應(yīng)用的責(zé)任

“臟讀”辕录、“不可重復(fù)讀”和“幻讀”,其實(shí)都是數(shù)據(jù)庫(kù)讀一致性問(wèn)題,必須由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決

數(shù)據(jù)庫(kù)實(shí)現(xiàn)事務(wù)隔離的方式，基本可以分為以下兩種

• 在讀取數(shù)據(jù)前,對(duì)其加鎖,防止其他事務(wù)對(duì)數(shù)據(jù)進(jìn)行修改
• 不加任何鎖,通過(guò)一定機(jī)制生成一個(gè)數(shù)據(jù)請(qǐng)求時(shí)間點(diǎn)的一致性數(shù)據(jù)快照,并用這個(gè)快照來(lái)提供一定級(jí)別(語(yǔ)句級(jí)或事務(wù)級(jí))的一致性讀取.
  從用戶(hù)的角度,好像是數(shù)據(jù)庫(kù)可以提供同一數(shù)據(jù)的多個(gè)版本,因此,這種技術(shù)叫做數(shù)據(jù)多版本并發(fā)控制（ＭultiVersion Concurrency Control,MVCC）梢卸，也經(jīng)常稱(chēng)為多版本數(shù)據(jù)庫(kù)

數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別越嚴(yán)格,并發(fā)副作用越小,但付出的代價(jià)也越大

因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使事務(wù)在一定程度上“串行化”進(jìn)行,這顯然與“并發(fā)”矛盾

為了解決“隔離”與“并發(fā)”的矛盾走诞，ANSI SQL定義了４種隔離級(jí)別

查看Innodb行鎖爭(zhēng)用情況

如果發(fā)現(xiàn)爭(zhēng)用比較嚴(yán)重蚣旱，如Innodb_row_lock_waits和Innodb_row_lock_time_avg的值比較高

查詢(xún)information_schema相關(guān)表來(lái)查看鎖情況

設(shè)置Innodb monitors

進(jìn)一步觀察發(fā)生鎖沖突的表，數(shù)據(jù)行等戴陡，并分析鎖爭(zhēng)用的原因

停止監(jiān)視器

drop table innodb_monitor

默認(rèn)情況每15秒會(huì)向日志中記錄監(jiān)控的內(nèi)容;

如果長(zhǎng)時(shí)間打開(kāi)會(huì)導(dǎo)致.err文件變得非常巨大;

所以確認(rèn)原因后,要?jiǎng)h除監(jiān)控表關(guān)閉監(jiān)視器,或者通過(guò)使用--console選項(xiàng)來(lái)啟動(dòng)服務(wù)器以關(guān)閉寫(xiě)日志功能

4.4 InnoDB的行鎖

InnoDB支持以下兩種類(lèi)型的行鎖

• 共享鎖（讀鎖S）
  若事務(wù) T 對(duì)數(shù)據(jù)對(duì)象 A 加了 S 鎖;
  則事務(wù) T 可以讀 A 但不能修改 A;
  其它事務(wù)只能再對(duì)它加 S 鎖,而不能加 X 鎖,直到 T 釋放 A 上的 S 鎖;
  這保證了,其他事務(wù)可以讀 A塞绿，但在事務(wù) T 釋放 S 鎖之前，不能對(duì) A 做任何修改操作.
• 排他鎖（寫(xiě)鎖X）
  若事務(wù) T 對(duì)數(shù)據(jù)對(duì)象A加 X 鎖;
  事務(wù) T 可以讀 A 也可以修改 A;
  其他事務(wù)不能對(duì) A 加任何鎖,直到 T 釋放 A 上的鎖;
  這保證了,其他事務(wù)在 T 釋放 A 上的鎖之前不能再讀取和修改 A .

MySQL InnoDB默認(rèn)行級(jí)鎖

行級(jí)鎖都是基于索引的,若一條SQL語(yǔ)句用不到索引是不會(huì)使用行級(jí)鎖的,會(huì)使用表級(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鎖

如果一個(gè)事務(wù)請(qǐng)求的鎖模式與當(dāng)前鎖兼容猜欺，InnoDB就請(qǐng)求的鎖授予該事務(wù);

反之位隶，如果兩者兩者不兼容，該事務(wù)就要等待鎖釋放

意向鎖是InnoDB自動(dòng)加的开皿，不需用戶(hù)干預(yù).

對(duì)于UPDATE涧黄、DELETE和INSERT語(yǔ)句赋荆，InnoDB會(huì)自動(dòng)給涉及及數(shù)據(jù)集加排他鎖（Ｘ）;

對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖.

對(duì)于SELECT語(yǔ)句,可以通過(guò)以下語(yǔ)句顯式地給記錄加讀/寫(xiě)鎖

• 共享鎖（Ｓ）

  
  

• 排他鎖（X）

  
  

共享鎖語(yǔ)句主要用在需要數(shù)據(jù)依存關(guān)系時(shí)確認(rèn)某行記錄是否存在;

并確保沒(méi)有人對(duì)這個(gè)記錄UPDATE或DELETE.

但如果當(dāng)前事務(wù)也需要對(duì)該記錄進(jìn)行更新,則很有可能造成死鎖;

對(duì)于鎖定行記錄后需要進(jìn)行更新操作的應(yīng)用,應(yīng)該使用排他鎖語(yǔ)句.

4.5 實(shí)例

4.5.1 Innodb共享鎖

| 獲得鎖寺擂，更新成功 |

4.5.2 Innodb排他鎖

4.6 行鎖的實(shí)現(xiàn)

行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)

如果沒(méi)有索引析苫，InnoDB將通過(guò)隱藏的聚簇索引來(lái)對(duì)記錄加鎖

• Record Locks:對(duì)索引項(xiàng)加鎖
• Gap lock:對(duì)索引項(xiàng)之的“間隙“衩侥，第一條記錄前的”間隙“，或最后一條記錄后的”間隙“矛物，加鎖
• Next-key lock：前兩種的組合，對(duì)記錄及其前面的間隙加鎖

行鎖實(shí)現(xiàn)特點(diǎn)意味著：

如果不通過(guò)索引條件檢索數(shù)據(jù),那么Innodb將對(duì)表的所有記錄加鎖峦萎，和表鎖一樣

間隙鎖（Next-Key鎖）

當(dāng)我們用范圍條件而不是相等條件檢索數(shù)據(jù),并請(qǐng)求共享或排他鎖時(shí),InnoDB會(huì)給符合條件的已有數(shù)據(jù)的索引項(xiàng)加鎖;

對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄,叫做“間隙(GAP)”,InnoDB也會(huì)對(duì)這個(gè)“間隙”加鎖,這種鎖機(jī)制就是所謂的間隙鎖（Next-Key鎖）.

舉例來(lái)說(shuō)爱榔，假如emp表中只有101條記錄糙及，其empid的值分別是1,2,...,100,101丁鹉，下面的SQL：

InnoDB 不僅會(huì)對(duì)符合條件的 empid 值為 101 的記錄加鎖;

也會(huì)對(duì) empid大于101（這些記錄并不存在）的“間隙”加鎖

間隙鎖的目的

• 防止幻讀,以滿足相關(guān)隔離級(jí)別的要求
  對(duì)于上例,若不使用間隙鎖,如果其他事務(wù)插入 empid 大于 100 的任何記錄,;
  那么本事務(wù)如果再次執(zhí)行上述語(yǔ)句,就會(huì)發(fā)生幻讀
• 滿足其恢復(fù)和復(fù)制的需要
  在使用范圍條件檢索并鎖定記錄時(shí);
  InnoDB 這種加鎖機(jī)制會(huì)阻塞符合條件范圍內(nèi)鍵值的并發(fā)插入,這往往會(huì)造成嚴(yán)重的鎖等待;
  因此,在實(shí)際開(kāi)發(fā)中,尤其是并發(fā)插入較多的應(yīng)用;
  我們要盡量?jī)?yōu)化業(yè)務(wù)邏輯,盡量使用相等條件來(lái)訪問(wèn)更新數(shù)據(jù),避免使用范圍條件.

4.7 when 使用表鎖

對(duì)于InnoDB,在絕大部分情況下都應(yīng)該使用行鎖

因?yàn)?code>事務(wù),行鎖往往是我們選擇InnoDB的理由

但在個(gè)別特殊事務(wù)中,也可以考慮使用表鎖

• 事務(wù)需要更新大部分?jǐn)?shù)據(jù)揣钦，表又較大
  若使用默認(rèn)的行鎖,不僅該事務(wù)執(zhí)行效率低(因?yàn)樾枰獙?duì)較多行加鎖,加鎖是需要耗時(shí)的);
  而且可能造成其他事務(wù)長(zhǎng)時(shí)間鎖等待和鎖沖突;
  這種情況下可以考慮使用表鎖來(lái)提高該事務(wù)的執(zhí)行速度
• 事務(wù)涉及多個(gè)表冯凹，較復(fù)雜，很可能引起死鎖匈庭，造成大量事務(wù)回滾
  這種情況也可以考慮一次性鎖定事務(wù)涉及的表阱持，從而避免死鎖魔熏、減少數(shù)據(jù)庫(kù)因事務(wù)回滾帶來(lái)的開(kāi)銷(xiāo)

當(dāng)然蒜绽，應(yīng)用中這兩種事務(wù)不能太多，否則鼎姊，就應(yīng)該考慮使用ＭyISAＭ

在InnoDB下 ,使用表鎖要注意

• 使用LOCK TALBES雖然可以給InnoDB加表鎖
  表鎖不是由InnoDB引擎層管理的,而是由其上一層ＭySQL Server負(fù)責(zé);
  僅當(dāng)autocommit=0相寇、innodb_table_lock=1（默認(rèn)設(shè)置）,InnoDB 引擎層才知道MySQL加的表鎖,ＭySQL Server才能感知InnoDB加的行鎖;
  這種情況下噪生，InnoDB才能自動(dòng)識(shí)別涉及表鎖的死鎖
  否則跺嗽，InnoDB將無(wú)法自動(dòng)檢測(cè)并處理這種死鎖
• 在用LOCK TALBES對(duì)InnoDB鎖時(shí)要注意,要將autocommit設(shè)為0桨嫁，否則ＭySQL不會(huì)給表加鎖
  事務(wù)結(jié)束前,不要用UNLOCK TALBES釋放表鎖,因?yàn)樗鼤?huì)隱式地提交事務(wù)
  COMMIT或ROLLBACK不能釋放用LOCK TALBES加的表鎖，必須用UNLOCK TABLES釋放表鎖楣导，正確的方式見(jiàn)如下語(yǔ)句

• 需要寫(xiě)表t1并從表t讀

  
  

5 死鎖

ＭyISAM表鎖是deadlock free的筒繁，這是因?yàn)椋蛓ISAM總是一次性獲得所需的全部鎖，要么全部滿足驮宴，要么等待堵泽，因此不會(huì)出現(xiàn)死鎖

但在InnoDB中恢总，除單個(gè)SQL組成的事務(wù)外片仿，鎖是逐步獲得的，這就決定了InnoDB發(fā)生死鎖是可能的

發(fā)生死鎖后钻蔑，InnoDB一般都能自動(dòng)檢測(cè)到咪笑，并使一個(gè)事務(wù)釋放鎖并退回娄涩，另一個(gè)事務(wù)獲得鎖蓄拣，繼續(xù)完成事務(wù)

• 但在涉及外部鎖球恤，或涉及鎖的情況下，InnoDB并不能完全自動(dòng)檢測(cè)到死鎖
  這需要通過(guò)設(shè)置鎖等待超時(shí)參數(shù)innodb_lock_wait_timeout來(lái)解決
  需要說(shuō)明的是堪置，這個(gè)參數(shù)并不是只用來(lái)解決死鎖問(wèn)題张惹，在并發(fā)訪問(wèn)比較高的情況下宛逗，如果大量事務(wù)因無(wú)法立即獲取所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源替蔬，造成嚴(yán)重性能問(wèn)題，甚至拖垮數(shù)據(jù)庫(kù)
  我們通過(guò)設(shè)置合適的鎖等待超時(shí)閾值德挣，可以避免這種情況發(fā)生快毛。

通常來(lái)說(shuō)唠帝，死鎖都是應(yīng)用設(shè)計(jì)的問(wèn)題襟衰，通過(guò)調(diào)整業(yè)務(wù)流程粪摘、數(shù)據(jù)庫(kù)對(duì)象設(shè)計(jì)徘意、事務(wù)大小、以及訪問(wèn)數(shù)據(jù)庫(kù)的SQL語(yǔ)句玖详，絕大部分都可以避免

下面就通過(guò)實(shí)例來(lái)介紹幾種死鎖的常用方法蟋座。

• 應(yīng)用中向臀，不同的程序會(huì)并發(fā)存取多個(gè)表
  盡量約定以相同的順序訪問(wèn)表
• 程序批處理數(shù)據(jù)時(shí)
  事先對(duì)數(shù)據(jù)排序,保證每個(gè)線程按固定的順序來(lái)處理記錄
• 在事務(wù)中,要更新記錄
  應(yīng)直接申請(qǐng)排他鎖,而不應(yīng)該先申請(qǐng)共享鎖
• 在可重復(fù)讀下,如果兩個(gè)線程同時(shí)對(duì)相同條件記錄用SELECT...ROR UPDATE加排他寫(xiě)鎖
  在沒(méi)有符合該記錄情況下诸狭，兩個(gè)線程都會(huì)加鎖成功
  程序發(fā)現(xiàn)記錄尚不存在驯遇，就試圖插入一條新記錄妹懒，如果兩個(gè)線程都這么做，就會(huì)出現(xiàn)死鎖
  這種情況下会前，將隔離級(jí)別改成READ COMMITTED瓦宜，就可以避免問(wèn)題
• 當(dāng)隔離級(jí)別為READ COMMITED時(shí)，如果兩個(gè)線程都先執(zhí)行SELECT...FOR UPDATE
  判斷是否存在符合條件的記錄反璃，沒(méi)有 -> 插入記錄;
  此時(shí)淮蜈，只有一個(gè)線程能插入成功已卷，另一個(gè)線程會(huì)出現(xiàn)鎖等待.
  當(dāng)?shù)冢眰€(gè)線程提交后侧蘸，第２個(gè)線程會(huì)因主鍵重出錯(cuò)讳癌，但雖然這個(gè)線程出錯(cuò)了晌坤，卻會(huì)獲得一個(gè)排他鎖！這時(shí)如果有第３個(gè)線程又來(lái)申請(qǐng)排他鎖埋虹，也會(huì)出現(xiàn)死鎖.
  對(duì)于這種情況搔课，可以直接做插入操作截亦，然后再捕獲主鍵重異常崩瓤，或者在遇到主鍵重錯(cuò)誤時(shí)却桶，總是執(zhí)行ROLLBACK釋放獲得的排他鎖

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

6 總結(jié)

6.1 MyISAM的表鎖

• 共享讀鎖之間是兼容的,但共享讀鎖和排他寫(xiě)鎖之間,以及排他寫(xiě)鎖之間互斥,即讀寫(xiě)串行
• 在一定條件下,ＭyISAM允許查詢(xún)/插入并發(fā),可利用這一點(diǎn)來(lái)解決應(yīng)用中對(duì)同一表查詢(xún)/插入的鎖爭(zhēng)用問(wèn)題
• ＭyISAM默認(rèn)的鎖調(diào)度機(jī)制是寫(xiě)優(yōu)先,這并不一定適合所有應(yīng)用,用戶(hù)可以通過(guò)設(shè)置LOW_PRIPORITY_UPDATES參數(shù)或在INSERT黔攒、UPDATE督惰、DELETE語(yǔ)句中指定LOW_PRIORITY選項(xiàng)來(lái)調(diào)節(jié)讀寫(xiě)鎖的爭(zhēng)用
• 由于表鎖的鎖定粒度大,讀寫(xiě)又是串行的,因此如果更新操作較多,ＭyISAM表可能會(huì)出現(xiàn)嚴(yán)重的鎖等待,可以考慮采用InnoDB表來(lái)減少鎖沖突

6.2 對(duì)于InnoDB表

• 行鎖基于索引實(shí)現(xiàn)
  如果不通過(guò)索引訪問(wèn)數(shù)據(jù),InnoDB會(huì)使用表鎖
• 間隙鎖機(jī)制及使用間隙鎖的原因
• 不同的隔離級(jí)別下赏胚，InnoDB的鎖機(jī)制和一致性讀策略不同
• ＭySQL的恢復(fù)和復(fù)制對(duì)InnoDB鎖機(jī)制和一致性讀策略也有較大影響
• 鎖沖突甚至死鎖很難完全避免

7 索引與鎖

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

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

• 精心設(shè)計(jì)索引朽们，并盡量使用索引訪問(wèn)數(shù)據(jù)鲫尊，使加鎖更精確沦偎，從而減少鎖沖突的機(jī)會(huì)豪嚎。

  
  
• 選擇合理的事務(wù)大小侈询，小事務(wù)發(fā)生鎖沖突的幾率也更小
• 給記錄集顯式加鎖時(shí)扔字，最好一次性請(qǐng)求足夠級(jí)別的鎖。比如要修改數(shù)據(jù)的話扭粱，最好直接申請(qǐng)排他鎖琢蛤，而不是先申請(qǐng)共享鎖虐块，修改時(shí)再請(qǐng)求排他鎖，這樣容易產(chǎn)生死鎖霜旧。
• 不同的程序訪問(wèn)一組表時(shí)挂据，應(yīng)盡量約定以相同的順序訪問(wèn)各表崎逃，對(duì)一個(gè)表而言眉孩，盡可能以固定的順序存取表中的行浪汪。這樣可以大減少死鎖的機(jī)會(huì)死遭。
• 盡量用相等條件訪問(wèn)數(shù)據(jù)，這樣可以避免間隙鎖對(duì)并發(fā)插入的影響钉迷。
• 不要申請(qǐng)超過(guò)實(shí)際需要的鎖級(jí)別糠聪；除非必須枷颊，查詢(xún)時(shí)不要顯示加鎖夭苗。

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

  
  
  
  
  image
  
  
  image
  
  
  image
  
  
  image

參考

MySQL中的鎖（表鎖丢习、行鎖）

本文由博客一文多發(fā)平臺(tái) OpenWrite 發(fā)布咐低！