事務(wù)的
隔離性是由鎖來保證的
一撒桨、概述
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制沐悦。當(dāng)多個(gè)線程并發(fā)訪問某個(gè)數(shù)據(jù)的時(shí)候敦第，我們就需要保證這個(gè)數(shù)據(jù)在任何時(shí)刻最多只有一個(gè)線程在訪問锨推，保證數(shù)據(jù)的完整性和一致性仓洼。在開發(fā)過程中加鎖是為了保證數(shù)據(jù)的一致性座韵，這個(gè)思想在數(shù)據(jù)庫領(lǐng)域中同樣很重要险绘。在數(shù)據(jù)庫中，除傳統(tǒng)的計(jì)算資源（CPU誉碴、RAM宦棺、I/O等）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源翔烁。為了保證數(shù)據(jù)的一致性渺氧，需要對(duì)并發(fā)操作進(jìn)行控制，因此產(chǎn)生了鎖蹬屹。同時(shí)鎖機(jī)制也為實(shí)現(xiàn)MySQL的各個(gè)隔離級(jí)別提供了保證侣背。鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素白华。
二、 MySQL并發(fā)事務(wù)訪問相同記錄
并發(fā)事務(wù)訪問相同記錄的情況大致可以劃分為3種
2.1贩耐、讀-讀情況
讀-讀情況弧腥，即并發(fā)事務(wù)相繼讀取相同的記錄。讀取操作本身不會(huì)對(duì)記錄有任何影響潮太，并不會(huì)引起什么問題管搪，所以允許這種情況的發(fā)生
2.2 寫-寫情況
寫-寫情況，即并發(fā)事務(wù)相繼對(duì)相同的記錄做出改動(dòng)铡买。
-
1更鲁、在這種情況下會(huì)發(fā)生
臟寫的問題，任何一種隔離級(jí)別都不允許這種問題的發(fā)生奇钞。所以在多個(gè)未提交事務(wù)相繼對(duì)一條記錄做改動(dòng)時(shí)澡为，需要讓它們排隊(duì)執(zhí)行，這個(gè)排隊(duì)的過程其實(shí)是通過鎖來實(shí)現(xiàn)的景埃。這個(gè)所謂的鎖其實(shí)是一個(gè)內(nèi)存中的結(jié)構(gòu)媒至，在事務(wù)執(zhí)行前本來是沒有鎖的，也就是說一開始是沒有鎖結(jié)構(gòu)和記錄進(jìn)行關(guān)聯(lián)的
未加鎖結(jié)構(gòu)時(shí)數(shù)據(jù).png -
2谷徙、當(dāng)一個(gè)事務(wù)想對(duì)這條記錄做改動(dòng)時(shí)拒啰，首先會(huì)看看內(nèi)存中有沒有與這條記錄關(guān)聯(lián)的
鎖結(jié)構(gòu)，當(dāng)沒有的時(shí)候就會(huì)在內(nèi)存中生成一個(gè)鎖結(jié)構(gòu)與之關(guān)聯(lián)完慧。比如谋旦，事務(wù)T1要對(duì)這條記錄做改動(dòng)，就需要生成一個(gè)鎖結(jié)構(gòu)與之關(guān)聯(lián)記錄關(guān)聯(lián)鎖結(jié)構(gòu).png -
3骗随、在
鎖結(jié)構(gòu)里有很多信息蛤织，為了簡(jiǎn)化理解，只把兩個(gè)比較重要的屬性拿來說明-
trx信息：代表這個(gè)鎖結(jié)構(gòu)是哪個(gè)事務(wù)生成的 -
is_waiting：代表當(dāng)前事務(wù)是否在等待
-
4鸿染、當(dāng)事務(wù)
T1改動(dòng)了這條記錄后指蚜，就生成了一個(gè)鎖結(jié)構(gòu)與該記錄關(guān)聯(lián)，因?yàn)橹皼]有別的事務(wù)為這條記錄加鎖涨椒，所以is_waiting屬性就是false摊鸡，把這個(gè)場(chǎng)景就稱之為獲取鎖成功，或者加鎖成功蚕冬，然后就可以繼續(xù)執(zhí)行操作了-
5免猾、在事務(wù)
T1提交之前，另一個(gè)事務(wù)T2也想對(duì)該記錄做改動(dòng)囤热，那么先看看有沒有鎖結(jié)構(gòu)與這條記錄關(guān)聯(lián)猎提，發(fā)現(xiàn)有一個(gè)鎖結(jié)構(gòu)與之關(guān)聯(lián)后，然后也生成了一個(gè)鎖結(jié)構(gòu)與這條記錄關(guān)聯(lián)旁蔼，不過該鎖結(jié)構(gòu)的is_waiting屬性值為true锨苏，表示當(dāng)前事務(wù)需要等待疙教，我們把這個(gè)場(chǎng)景就稱之為獲取失敗，或者加鎖失敗
事務(wù)獲取鎖伞租，鎖結(jié)構(gòu).png -
6贞谓、事務(wù)
T1提交之后，就會(huì)把該事務(wù)生成的鎖結(jié)構(gòu)釋放掉葵诈，然后看看還有沒有別的事務(wù)在等待獲取鎖裸弦，發(fā)現(xiàn)了事務(wù)T2還在等待獲取鎖，所以把事務(wù)T2對(duì)應(yīng)的鎖結(jié)構(gòu)的is_waiting屬性值設(shè)置為false作喘，然后把該事務(wù)對(duì)應(yīng)的線程喚醒理疙，讓它繼續(xù)執(zhí)行，此時(shí)事務(wù)T2就算獲取到了鎖image.png
小結(jié)
不加鎖：不需要在內(nèi)存中生成對(duì)應(yīng)的
鎖結(jié)構(gòu)泞坦，可以直接執(zhí)行操作獲取鎖成功沪斟，或者加鎖成功：在內(nèi)存中生成了對(duì)應(yīng)的
鎖結(jié)構(gòu)，而且鎖結(jié)構(gòu)的is_waiting屬性為false暇矫，也就是事務(wù)可以繼續(xù)執(zhí)行操作獲取鎖失敗，或者加鎖失敗择吊，或者沒有獲取到鎖：在內(nèi)存中生成了對(duì)應(yīng)的
鎖結(jié)構(gòu)李根，不過鎖結(jié)構(gòu)的is_waiting屬性為true，也就是事務(wù)需要等待几睛，不可以繼續(xù)執(zhí)行操作
2.3 讀-寫或?qū)?讀情況
讀-寫或寫-讀房轿，即一個(gè)事務(wù)進(jìn)行讀取操作，另一個(gè)進(jìn)行改動(dòng)操作所森。這種情況下可能發(fā)生臟讀 囱持、 不可重復(fù)讀 、 幻讀的問題焕济。各個(gè)數(shù)據(jù)庫廠商對(duì)SQL標(biāo)準(zhǔn)的支持都可能不一樣纷妆。比如MySQL在REPEATABLE READ隔離級(jí)別上就已經(jīng)解決了幻讀問題
2.4 并發(fā)問題的解決方案
通過兩種可選的解決方案來解決
臟讀 、 不可重復(fù)讀 晴弃、 幻讀這些問題
2.4.1掩幢、方案一：讀操作利用多版本并發(fā)控制（MVCC），寫操作進(jìn)行 加鎖 上鞠。
所謂
MVCC际邻，就是生成一個(gè)ReadView，通過ReadView找到符合條件的記錄版本（歷史版本由undo日志構(gòu)建）芍阎。查詢語句只能讀到在生成ReadView之前已提交事務(wù)所做的更改世曾，在生成ReadView之前未提交的事務(wù)或者之后才開啟的事務(wù)所做的更改是看不到的。而寫操作肯定針對(duì)的是最新版本的記錄谴咸，讀記錄的歷史版本和改動(dòng)記錄的最新版本本身并不沖突轮听，也就是采用MVCC時(shí)骗露，讀-寫操作并不沖突。
- 普通的SELECT語句在
READ COMMITTED和REPEATABLE READ隔離級(jí)別下會(huì)使用到MVCC讀取記錄- 在
READ COMMITTED隔離級(jí)別下蕊程，一個(gè)事務(wù)在執(zhí)行過程中每次執(zhí)行SELECT操作時(shí)都會(huì)生成一個(gè)ReadView椒袍，ReadView的存在本身就保證了事務(wù)不可以讀取到未提交的事務(wù)所做的更改，也就是避免了臟讀現(xiàn)象 - 在
REPEATABLE READ隔離級(jí)別下藻茂，一個(gè)事務(wù)在執(zhí)行過程中只有 第一次執(zhí)行SELECT操作才會(huì)生成一個(gè)ReadView驹暑，之后的SELECT操作都復(fù)用這個(gè)ReadView，這樣也就避免了不可重復(fù)讀和幻讀的問題
- 在
2.4.2辨赐、方案二：讀优俘、寫操作都采用 加鎖 的方式
如果一些業(yè)務(wù)場(chǎng)景不允許讀取記錄的舊版本，而是每次都必須去
讀取記錄的最新版本掀序。如在銀行存款的業(yè)務(wù)中帆焕，你需要先把賬戶的余額讀出來，然后將其加上本次存款的數(shù)額不恭，最后再寫到數(shù)據(jù)庫中叶雹。在將賬戶余額讀取出來后，就不想讓別的事務(wù)再訪問該余額换吧，直到本次存款事務(wù)執(zhí)行完成折晦，其他事務(wù)才可以訪問賬戶的余額。這樣在讀取記錄的時(shí)候就需要對(duì)其進(jìn)行加鎖操作沾瓦，這樣也就意味著讀操作和寫操作也像寫-寫操作那樣排隊(duì)執(zhí)行
臟讀的產(chǎn)生是因?yàn)楫?dāng)前事務(wù)讀取了另一個(gè)事務(wù)未提交的一條記錄满着，如果另一個(gè)事務(wù)再寫記錄的時(shí)候就給這條記錄加鎖，那么當(dāng)前事務(wù)就無法繼續(xù)讀取該記錄了贯莺，所以也就不會(huì)有臟讀問題的產(chǎn)生了不可重復(fù)讀的產(chǎn)生是因?yàn)?code>當(dāng)前事務(wù)先讀取一條記錄风喇，另外一個(gè)事務(wù)對(duì)該記錄做了改動(dòng)之后并提交之后，當(dāng)前事務(wù)再次讀取時(shí)會(huì)獲得不同的值缕探，如果在當(dāng)前事務(wù)讀取記錄時(shí)就給改記錄加鎖魂莫，那么另一個(gè)事務(wù)就無法修改改記錄，自然也不會(huì)發(fā)生不可重復(fù)讀了爹耗。幻讀問題的產(chǎn)生是因?yàn)楫?dāng)前事務(wù)讀取了一個(gè)范圍的記錄豁鲤，然后另外的事務(wù)向該范圍內(nèi)插入了新記錄，當(dāng)前事務(wù)再次讀取該范圍的記錄時(shí)發(fā)現(xiàn)了新插入的新記錄鲸沮。采用加鎖的方式解決幻讀問題就有一些麻煩琳骡，因?yàn)楫?dāng)前事務(wù)在第一次讀取記錄時(shí)幻讀記錄并不存在，所以讀取的時(shí)候加鎖就有點(diǎn)尷尬（因?yàn)椴⒉恢澜o誰加鎖）
小結(jié)
- 采用
MVCC方式的話讼溺，讀-寫操作彼此并不沖突楣号，性能更高 - 采用
加鎖方式的話，讀-寫操作彼此需要排隊(duì)執(zhí)行，影響性能 - 一般情況下我們當(dāng)然愿意采用
MVCC來解決讀-寫 操作并發(fā)執(zhí)行的問題炫狱，但是業(yè)務(wù)在某些特殊情況下藻懒，要求必須采用加鎖的方式執(zhí)行
三、鎖的不同角度分類
1视译、從數(shù)據(jù)操作的類型劃分：讀鎖嬉荆、寫鎖
對(duì)于數(shù)據(jù)庫中并發(fā)事務(wù)的
讀-讀情況并不會(huì)引起什么問題。對(duì)于寫-寫酷含、讀-寫或?qū)?讀這些情況可能會(huì)引起一些問題鄙早，需要使用MVCC或者加鎖的方式來解決它們。在使用加鎖的方式解決問題時(shí)椅亚，由于既要允許讀-讀情況不受影響限番，又要使寫-寫、讀-寫或?qū)?讀這些情況中的操作相互阻塞呀舔，所以 MySQL 實(shí)現(xiàn)一個(gè)由兩種類型的鎖組成的鎖系統(tǒng)來解決弥虐。這兩種類型的鎖通常被稱為共享鎖（Shared Lock，S Lock）和排它鎖（Exclusive Lock媚赖，X Lock）也叫讀鎖（ReadLock）和寫鎖（Write Lock）
讀鎖：也稱為共享鎖霜瘪、英文用S表示。針對(duì)同一份數(shù)據(jù)惧磺，多個(gè)事務(wù)的讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響粥庄，相互不阻塞的寫鎖：也稱為排他鎖、英文用X表示豺妓。當(dāng)前寫操作沒有完成前，它會(huì)阻斷其他寫鎖和讀鎖布讹。這樣就能確保在給定的時(shí)間里琳拭，只有一個(gè)事務(wù)能執(zhí)行寫入，并防止其他用戶讀取正在寫入的同一資源需要注意的是對(duì)于 InnoDB 引擎來說描验，
讀鎖和寫鎖可以加在表上白嘁，也可以加在行上。
對(duì)于
行級(jí)讀寫鎖來說：如果一個(gè)事務(wù)T1已經(jīng)獲得了某個(gè)行 r 的讀鎖膘流，那么此時(shí)另外的一個(gè)事務(wù)T2是可以去獲得這個(gè)行 r 的讀鎖絮缅，因?yàn)樽x取操作并沒有改變行 r 的數(shù)據(jù)；但是呼股，如果某個(gè)事務(wù)T3想獲取行 r 的寫鎖耕魄，則它必須等待事務(wù)T1、T2釋放掉行 r 上的讀鎖才行
1.1彭谁、鎖定讀
在采用
加鎖方式解決臟讀吸奴、不可重復(fù)讀、幻讀這些問題時(shí)，讀取一條記錄時(shí)需要獲取記錄的S鎖则奥，其實(shí)是不嚴(yán)謹(jǐn)?shù)目既螅袝r(shí)候需要在讀取記錄時(shí)就獲取記錄的X鎖來禁止別的事務(wù)讀寫該記錄，為此MySQL提出了兩種比較特殊的SELECT語句格式
1.1.1读处、對(duì)讀取的記錄加S鎖
在普通的 SELECT 語句后邊加
LOCK IN SHARE MODE糊治，如果當(dāng)前事務(wù)執(zhí)行了該語句，那么它會(huì)為讀取到的記錄加S鎖罚舱，這樣允許別的事務(wù)繼續(xù)獲取這些記錄的S 鎖（比方說別的事務(wù)也使用SELECT . . . LOCK IN SHARE MODE語句來讀取這些記錄）井辜，但是不能獲取這些記錄的X 鎖（比如使用SELECT . . . FOR UPDATE語句來讀取這些記錄，或者直接修改這些記錄）馆匿。如果別的事務(wù)想要獲取這些記錄的X 鎖抑胎，那么它們會(huì)阻塞，直到當(dāng)前事務(wù)提交之后將這些記錄上的S 鎖釋放掉
SELECT . . . LOCK IN SHARE MODE;
# 或
SELECT . . . FOR SHARE; #(8.0新增語法)
1.1.2渐北、對(duì)讀取的記錄加X 鎖
在普通的 SELECT 語句后邊加
FOR UPDATE阿逃，如果當(dāng)前事務(wù)執(zhí)行了該語句，那么它會(huì)為讀取到的記錄加X 鎖赃蛛，這樣既不允許別的事務(wù)獲取這些記錄的S 鎖（比方說別的事務(wù)使用SELECT . . . LOCK IN SHARE MODE語句來讀取這些記錄）恃锉，也不允許獲取這些記錄的X 鎖（比如使用SELECT . . . FOR UPDATE語句來讀取這些記錄，或者直接修改這些記錄）呕臂。如果別的事務(wù)想要獲取這些記錄的S 鎖或者X 鎖破托，那么它們會(huì)阻塞，直到當(dāng)前事務(wù)提交之后將這些記錄上的X 鎖釋放掉
SELECT . . . FOR UPDATE;
1.1.3歧蒋、MySQL8.0新特性
在 5.7及之前的版本土砂，
SELECT . . . FOR UPDATE，如果獲取不到鎖谜洽，會(huì)一直等待萝映，直到innodb_lock_wait_timeout超時(shí)。在8.0版本中阐虚，SELECT . . . FOR UPDATE序臂，SELECT . . . FOR SHARE添加NOWAIT、SKIP LOCKED語法实束，跳過鎖等待奥秆，或者跳過鎖定
- 通過添加
NOWAIT、SKIP LOCKED語法咸灿，能夠立即返回构订。如果查詢的行已經(jīng)加鎖- 那么
NOWAIT會(huì)立即報(bào)錯(cuò)返回 - 那么
SKIP LOCKED也會(huì)立即返回，只是返回的結(jié)果中不包含被鎖定的行
- 那么
1.1.4避矢、實(shí)戰(zhàn)
- session1 獲取
X 鎖
# 開啟事務(wù)
begin;
# 獲取 X 鎖
select * from account where id = 2 for update;
- session2 一直等待鲫咽，直到報(bào)錯(cuò)
select * from account where id = 2 for update;
- session3 獲取鎖時(shí)使用
nowait
select * from account where id = 2 for update nowait;
- session4 獲取鎖時(shí)使用
skip locked
select * from account where id = 2 for update skip locked;
1.2、寫操作
寫操作無非是DELETE分尸、UPDATE锦聊、INSERT三種
DELETE：對(duì)一條記錄做DELETE操作的過程其實(shí)是先在B+樹中定位到這條記錄的位置，然后獲取這條記錄的X鎖箩绍，再執(zhí)行delete mark操作孔庭。也可以把這個(gè)定位待刪除記錄在B+樹中位置的過程看成是一個(gè)獲取X鎖的鎖定讀-
UPDATE：在對(duì)一條記錄做UPDATE操作時(shí)分為三種情況情況1：未修改該記錄的鍵值（
聚簇索引，一般就是主鍵id）材蛛，并且被更新的列占用的存儲(chǔ)空間在修改前后未發(fā)生變化圆到。則先在B+樹中定位到這條記錄的位置，然后再獲取一下記錄的X鎖卑吭，最后在原記錄的位置進(jìn)行修改操作芽淡。也可以把這個(gè)定位待修改記錄在B+樹中位置的過程看成是一個(gè)獲取X鎖的鎖定讀。情況2：未修改該記錄的鍵值（
聚簇索引豆赏，一般就是主鍵id）挣菲，并且至少有一個(gè)被更新的列占用的存儲(chǔ)空間在修改前后發(fā)生變化。則先在B+樹中定位到這條記錄的位置掷邦，然后獲取一下記錄的X 鎖白胀，將該記錄徹底刪除掉（就是把記錄徹底移入垃圾鏈表），最后再插入一條新紀(jì)錄抚岗。這個(gè)定位待修改記錄在B+樹中位置的過程看成是一個(gè)獲取X鎖的鎖定讀或杠，新插入的記錄由INSERT操作提供的隱式鎖進(jìn)行保護(hù)情況3：修改了該記錄的鍵值，則相當(dāng)于在原記錄上做
DELETE操作之后再來一次INSERT操作宣蔚，加鎖操作就需要按照DELETE和INSERT的規(guī)則進(jìn)行了
INSERT：一般情況下向抢，新插入一條記錄的操作并不加鎖，通過一種稱之為隱式鎖的結(jié)構(gòu)來保護(hù)這條新插入的記錄在本事務(wù)提交前不被別的事務(wù)訪問
2胚委、從數(shù)據(jù)操作的粒度劃分：表級(jí)鎖挟鸠、頁級(jí)鎖、行鎖
為了盡可能提高數(shù)據(jù)庫的并發(fā)能力篷扩，每次鎖定的數(shù)據(jù)范圍越小越好，理論上每次只鎖定當(dāng)前操作的數(shù)據(jù)的方案會(huì)得到最大的并發(fā)度茉盏，但是管理鎖是很
消耗資源的事情（涉及獲取鉴未、檢查、釋放鎖等動(dòng)作）鸠姨。因此數(shù)據(jù)庫系統(tǒng)需要在高并發(fā)響應(yīng)和系統(tǒng)性能兩方面進(jìn)行平衡铜秆，這樣就產(chǎn)生了鎖粒度（Lock Granularity）的概念。對(duì)一條記錄加鎖影響的也只是這條記錄而已讶迁，就說這個(gè)鎖的粒度比較細(xì)连茧；其實(shí)一個(gè)事務(wù)也可以在表級(jí)別進(jìn)行加鎖，自然就被稱之為表級(jí)鎖或者表鎖，對(duì)一個(gè)表加鎖影響整個(gè)表中的記錄啸驯，我們就說這個(gè)鎖的粒度比較粗客扎。鎖的粒度主要分為表級(jí)鎖、頁級(jí)鎖和行鎖
2.1罚斗、表鎖（Table Lock）
表鎖會(huì)鎖定整張表徙鱼，它是 MySQL 中最基本的
鎖策略，并不依賴于存儲(chǔ)引擎（不管你是 MySQL 的什么存儲(chǔ)引擎针姿，對(duì)于表鎖的策略都是一樣的）袱吆，并且表鎖是開銷最小的策略（因?yàn)榱６缺容^大）。由于表級(jí)鎖一次會(huì)將整個(gè)表鎖定距淫，所以可以很好的避免死鎖問題绞绒。當(dāng)然，鎖的粒度大所帶來最大的負(fù)面影響就是出現(xiàn)鎖資源競(jìng)爭(zhēng)的概率升高榕暇，導(dǎo)致并發(fā)率大打折扣
2.1.1蓬衡、表級(jí)別的S鎖（共享鎖）、X鎖（排他鎖）
在對(duì)某個(gè)表執(zhí)行
SELECT拐揭、INSERT撤蟆、DELETE、UPDATE語句時(shí)堂污，InnoDB存儲(chǔ)引擎是不會(huì)為這個(gè)表添加表級(jí)別的S鎖 或者 X鎖的家肯。在對(duì)某個(gè)表執(zhí)行一些諸如ALTER TABLE 、 DROP TABLE這類的DDL語句時(shí)盟猖，其他事務(wù)對(duì)這個(gè)表并發(fā)執(zhí)行諸如SELECT讨衣、INSERT、DELETE式镐、UPDATE的語句會(huì)發(fā)生阻塞反镇。同理，某個(gè)事務(wù)中對(duì)某個(gè)表執(zhí)行SELECT娘汞、INSERT歹茶、DELETE、UPDATE語句時(shí)你弦，在其他會(huì)話中對(duì)這個(gè)表執(zhí)行DDL語句也會(huì)發(fā)生阻塞惊豺。這個(gè)過程其實(shí)是通過在server層使用一種稱之為元數(shù)據(jù)鎖（英文名：Metadata Locks，簡(jiǎn)稱 MDL ）結(jié)構(gòu)來實(shí)現(xiàn)的禽作。一般情況下尸昧，不會(huì)使用InnoDB存儲(chǔ)引擎提供的表級(jí)別的S鎖和X鎖。只會(huì)在一些特殊情況下旷偿，比方說崩潰恢復(fù)過程中用到烹俗。比如爆侣，在系統(tǒng)變量autocommit=0，innodb_table_locks = 1時(shí)幢妄，手動(dòng)獲取InnoDB存儲(chǔ)引擎提供的表t 的S鎖或者X鎖可以這么寫
LOCK TABLES t READ：InnoDB存儲(chǔ)引擎會(huì)對(duì)表 t 加表級(jí)別的S鎖兔仰。LOCK TABLES t WRITE：InnoDB存儲(chǔ)引擎會(huì)對(duì)表 t 加表級(jí)別的X鎖-
MySQL的表級(jí)鎖有兩種模式：（以MyISAM表進(jìn)行操作的演示）
- 表共享讀鎖（Table Read Lock）
- 表獨(dú)占寫鎖（Table Write Lock）
實(shí)戰(zhàn)
盡量避免在使用InnoDB存儲(chǔ)引擎的表上使
LOCK TABLES這樣的手動(dòng)鎖表語句，它們并不會(huì)提供什么額外的保護(hù)磁浇，只是會(huì)降低并發(fā)能力而已斋陪。InnoDB的厲害之處還是實(shí)現(xiàn)了更細(xì)粒度的行鎖，關(guān)于InnoDB表級(jí)別的 S鎖 和 X鎖了解一下就可以了置吓。
- 1无虚、創(chuàng)建存儲(chǔ)引擎為 myisam 的表
CREATE TABLE mylock
(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(20)
) ENGINE myisam;
- 2、插入數(shù)據(jù)
INSERT INTO mylock(name) VALUES ('a');
- 3衍锚、查看表上是否加鎖
SHOW OPEN TABLES ; # 主要關(guān)注 In_use 字段的值
或者
SHOW OPEN TABLES WHERE in_use > 0;
- 4友题、手動(dòng)增加表鎖
LOCK TABLES mylock READ ; # 存儲(chǔ)引擎會(huì)對(duì)表 mylock 加 表級(jí)別的共享鎖
LOCK TABLES mylock WRITE ; # 存儲(chǔ)引擎會(huì)對(duì)表 mylock 加 表級(jí)別的排他鎖
- 5、加鎖后查看
LOCK TABLES mylock WRITE ;
SHOW OPEN TABLES WHERE in_use > 0;
- 6戴质、釋放表鎖
UNLOCK TABLES ;
2.1.1.1度宦、實(shí)戰(zhàn)-加表讀鎖
為 mylock 表加 Read 鎖，觀察阻塞情況
- 1告匠、session1 獲得表 mylock 的Read 鎖
LOCK TABLES mylock READ ;
- 2戈抄、當(dāng)前 session1 可以查詢?cè)摫碛涗?/li>
SELECT *
FROM mylock;
- 3、當(dāng)前 session1 不能查詢其他沒有鎖定的表
SELECT *
FROM account;
- 4后专、當(dāng)前 session1 中插入或者更新鎖定的表都會(huì)提示錯(cuò)誤
INSERT INTO mylock(name) VALUES ('b');
- 5划鸽、session2 也可以查詢?cè)摫碛涗?/li>
SELECT *
FROM mylock;
- 6、session2 也可以查詢或者更新未鎖定表
INSERT INTO account(id, balance) VALUES (5,500);
- 7戚哎、session2 插入或者更新鎖定表會(huì)一直等待獲得鎖
2.1.1.2裸诽、實(shí)戰(zhàn)-加表寫鎖
為 mylock 表加 write 鎖，觀察阻塞的情況
- 1型凳、session1 獲取 mylock 的 WRITE 鎖定
LOCK TABLES mylock WRITE ;
- 2丈冬、當(dāng)前 session1 對(duì)鎖定表的查詢 + 更新 + 插入操作都可以
SELECT *
FROM mylock;
UPDATE mylock
SET name = 'c'
WHERE id = 3;
INSERT INTO mylock(name) VALUES ('d');
- 3、session2 對(duì)鎖定表的查詢被阻塞甘畅，需要等待鎖被釋放
2.1.1.3埂蕊、小結(jié)
MyISAM在執(zhí)行查詢語句（SELECT）前，會(huì)給涉及的所有表加讀鎖疏唾，在執(zhí)行增刪改操作前蓄氧，會(huì)給涉及的表加寫鎖。
InnoDB存儲(chǔ)引擎是不會(huì)為這個(gè)表添加表級(jí)別的讀鎖或者寫鎖
2.1.2荸实、意向鎖 （intention lock）
InnoDB 支持
多粒度鎖（multiple granularity locking）匀们，它允許行級(jí)鎖與表級(jí)鎖共存缴淋，而意向鎖就是其中的一種表鎖
1准给、意向鎖的存在是為了協(xié)調(diào)行鎖和表鎖的關(guān)系泄朴，支持多粒度（表鎖 與 行鎖）的鎖并存
2、意向鎖是一種不與行級(jí)鎖沖突的表級(jí)鎖
3露氮、表明某個(gè)事務(wù)正在某些行持有了鎖或該事務(wù)準(zhǔn)備去持有鎖
2.1.2.1祖灰、意向鎖種類
-
意向共享鎖（intention shared lock, IS）：事務(wù)有意向?qū)Ρ碇械哪承┬屑?code>共享鎖（S鎖）
-- 事務(wù)要獲取某些行的 S 鎖，必須先獲得表的 IS 鎖畔规。
SELECT column FROM table ... LOCK IN SHARE MODE;
-
意向排他鎖（intention exclusive lock, IX）：事務(wù)有意向?qū)Ρ碇械哪承┬屑?code>排他鎖（X鎖）
-- 事務(wù)要獲取某些行的 X 鎖局扶，必須先獲得表的 IX 鎖。
SELECT column FROM table ... FOR UPDATE;
- 意向鎖是由存儲(chǔ)引擎
自己維護(hù)的叁扫，用戶無法手動(dòng)操作意向鎖三妈，在為數(shù)據(jù)行加共享/排他鎖之前，InnoDB會(huì)先獲取該數(shù)據(jù)行所在數(shù)據(jù)表的對(duì)應(yīng)意向鎖
2.1.2.2莫绣、意向鎖要解決的問題
現(xiàn)在有兩個(gè)事務(wù)畴蒲，分別是
T1和T2，其中T2試圖在該表級(jí)別上應(yīng)用共享或排他鎖对室，如果沒有意向鎖存在模燥，那么T2就需要去檢查各個(gè)頁或行是否存在鎖；如果存在意向鎖掩宜，那么此時(shí)就會(huì)受到由T1控制的表級(jí)別意向鎖的阻塞蔫骂。T2在鎖定該表前不必檢查各個(gè)頁或行鎖，而只需檢查表上的意向鎖牺汤。簡(jiǎn)單來說就是給更大一級(jí)別的空間示意里面是否已經(jīng)上過鎖辽旋。在數(shù)據(jù)表的場(chǎng)景中，如果我們給某一行數(shù)據(jù)加上了排他鎖慧瘤，數(shù)據(jù)庫會(huì)自動(dòng)給更大一級(jí)的空間戴已，比如數(shù)據(jù)頁或數(shù)據(jù)表加上意向鎖，告訴其他人這個(gè)數(shù)據(jù)頁或數(shù)據(jù)表已經(jīng)有人上過排他鎖了锅减，這樣當(dāng)其他人想要獲取數(shù)據(jù)表排他鎖的時(shí)候糖儡，只需要了解是否有人已經(jīng)獲取了這個(gè)數(shù)據(jù)表的意向排他鎖即可
- 如果事務(wù)想要獲得數(shù)據(jù)表中某些記錄的
共享鎖，就需要在數(shù)據(jù)表上添加意向共享鎖 - 如果事務(wù)想要獲得數(shù)據(jù)表中某些記錄的
排他鎖怔匣，就需要在數(shù)據(jù)表上添加意向排他鎖
2.1.2.3握联、意向鎖實(shí)戰(zhàn)
- 創(chuàng)建表 teacher
CREATE TABLE teacher (
id INT NOT NULL ,
name VARCHAR(200) NOT NULL ,
PRIMARY KEY (id)
);
- 插入數(shù)據(jù)
INSERT INTO teacher (id, name)
VALUES (1, 'zs'),
(2, 'ls'),
(3, 'ww'),
(4, 'zl');
- 事務(wù) A 獲取某一行的
排他鎖，并未提交
BEGIN ;
SELECT *
FROM teacher
WHERE id = 3 FOR UPDATE ;
- 事務(wù) B 試圖獲取 teacher 表的
表讀鎖
BEGIN ;
LOCK TABLES teacher READ ;
- 結(jié)果：因?yàn)?code>共享鎖與
排他鎖互斥每瞒，所以事務(wù) B 在試圖對(duì) teacher 表加共享鎖的時(shí)候金闽，必須保證兩個(gè)條件- 當(dāng)前沒有其他事務(wù)持有 teacher 表的
排他鎖 - 當(dāng)前沒有其他事務(wù)持有 teacher 表中任意一行的
排他鎖
- 當(dāng)前沒有其他事務(wù)持有 teacher 表的
- 實(shí)戰(zhàn)小結(jié)
事務(wù)B檢測(cè) 事務(wù)A持有 teacher 表的
意向排他鎖，就可以得知事務(wù)A 必然持有該表中某些數(shù)據(jù)行的排他鎖剿骨，那么事務(wù)B 對(duì) teacher 表的加鎖請(qǐng)求就會(huì)被排斥（阻塞）代芜，而無需去檢測(cè)表中的每一行數(shù)據(jù)是否存在排他鎖
2.1.2.4、意向鎖的并發(fā)性
意向鎖不會(huì)與行級(jí)的共享 / 排他鎖互斥浓利！正因?yàn)槿绱耍?code>意向鎖并不會(huì)影響到多個(gè)事務(wù)對(duì)不同數(shù)據(jù)行加排他鎖時(shí)的并發(fā)性挤庇。（不然我們直接用普通的表鎖就行了）
上述實(shí)戰(zhàn)阻塞原因：事務(wù)A對(duì)表teacher中的id=3的記錄加了
排他鎖钞速，也就是說表 teacher 中已經(jīng)有了意向排他鎖，事務(wù)B想對(duì)表teacher加表級(jí)的讀鎖造成阻塞事務(wù) C 對(duì)表 teacher 其他行添加
排他鎖是完全OK的
SELECT *
FROM teacher
WHERE id = 4 FOR UPDATE ;
- 小結(jié)：事務(wù)C 申請(qǐng) teacher 表的
意向排他鎖嫡秕。事務(wù)C檢測(cè)到事務(wù)A持有 teacher表的意向排他鎖渴语。因?yàn)?code>意向鎖之間并不互斥，所以事務(wù)C獲取到了 teacher 表的意向排他鎖昆咽。因?yàn)?id = 4 的數(shù)據(jù)行上不存在任何排他鎖驾凶，最終事務(wù)C成功獲取到了該數(shù)據(jù)行上的排他鎖
2.1.2.5、結(jié)論
- InnoDB 支持
多粒度鎖掷酗，特定場(chǎng)景下调违，行級(jí)鎖可以與表級(jí)鎖共存。
- InnoDB 支持
- 2.
意向鎖之間互不排斥泻轰，但除了IS與S兼容外翰萨，意向鎖會(huì)與共享鎖 / 排他鎖互斥 。 -
IX糕殉，IS是表級(jí)鎖勤揩，不會(huì)和行級(jí)的X山害，S鎖發(fā)生沖突恳邀。只會(huì)和表級(jí)的X柬甥，S發(fā)生沖突。
-
-
意向鎖在保證并發(fā)性的前提下羡洁，實(shí)現(xiàn)了行鎖和表鎖共存 且滿足事務(wù)隔離性的要求玷过。
-
2.1.3、自增鎖（AUTO-INC鎖）
2.1.3.1筑煮、在使用MySQL過程中辛蚊，我們可以為表的某個(gè)列添加 AUTO_INCREMENT 屬性
CREATE TABLE `teacher`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;
2.1.3.2、 Simple inserts （簡(jiǎn)單插入）
可以
預(yù)先確定要插入的行數(shù)（當(dāng)語句被初始處理時(shí)）的語句真仲。包括沒有嵌套子查詢的單行和多行INSERT...VALUES() 和 REPLACE語句袋马。
- 例如
INSERT INTO `teacher` (name) VALUES ('zhangsan'), ('lisi');
2.1.3.3、Bulk inserts （批量插入）
事先不知道要插入的行數(shù) （和所需自動(dòng)遞增值的數(shù)量）的語句秸应。比如INSERT ... SELECT虑凛， REPLACE ... SELECT 和 LOAD DATA語句，但不包括純INSERT软啼。 InnoDB在每處理一行桑谍，為AUTO_INCREMENT列分配一個(gè)新值。
2.1.3.4祸挪、 Mixed-mode inserts （混合模式插入）
這些是
Simple inserts語句但是指定部分新行的自動(dòng)遞增值锣披。例如INSERT INTO teacher (id,name) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');只是指定了部分id的值。另一種類型的混合模式插入是INSERT ... ON DUPLICATE KEY UPDATE。對(duì)于上面數(shù)據(jù)插入的案例雹仿，MySQL中采用了自增鎖的方式來實(shí)現(xiàn)榜跌，AUTO-INC鎖是向使用含有 AUTO_INCREMENT 列的表中插入數(shù)據(jù)時(shí)需要獲取的一種特殊的表級(jí)鎖，在執(zhí)行插入語句時(shí)就在表級(jí)別加一個(gè)AUTO-INC鎖盅粪，然后為每條待插入記錄的 AUTO_INCREMENT 修飾的列分配遞增的值，在該語句執(zhí)行結(jié)束后悄蕾，再把AUTO-INC鎖釋放掉票顾。一個(gè)事務(wù)在持有 AUTO-INC 鎖的過程中，其他事務(wù)的插入語句都要被阻塞帆调，可以保證一個(gè)語句中分配的遞增值是連續(xù)的奠骄。也正因?yàn)榇耍洳l(fā)性不高番刊，當(dāng)我們向一個(gè)有 AUTO_INCREMENT 關(guān)鍵字的主鍵插入值的時(shí)候含鳞，每條語句都要對(duì)這個(gè)表鎖進(jìn)行競(jìng)爭(zhēng)，這樣的并發(fā)潛力其實(shí)是很低的芹务，所以InnoDB通過innodb_autoinc_lock_mode的不同取值來提供不同的鎖定機(jī)制蝉绷，來顯著提高 SQL 語句的可伸縮性和性能。innodb_autoinc_lock_mode有三種取值枣抱，分別對(duì)應(yīng)與不同鎖定模式
（1）innodb_autoinc_lock_mode = 0(“傳統(tǒng)”鎖定模式)：在此鎖定模式下熔吗，所有類型的insert語句都會(huì)獲得一個(gè)特殊的表級(jí)AUTO-INC鎖，用于插入具有AUTO_INCREMENT列的表佳晶。這種模式其實(shí)就如我們上面的例子桅狠，即每當(dāng)執(zhí)行insert的時(shí)候，都會(huì)得到一個(gè)表級(jí)鎖(AUTO-INC鎖)轿秧，使得語句中生成的auto_increment為順序中跌，且在binlog中重放的時(shí)候，可以保證master與slave中數(shù)據(jù)的auto_increment是相同的菇篡。因?yàn)槭?code>表級(jí)鎖漩符，當(dāng)在同一時(shí)間多個(gè)事務(wù)中執(zhí)行insert的時(shí)候，對(duì)于AUTO-INC鎖的爭(zhēng)奪會(huì)限制并發(fā)能力（2）innodb_autoinc_lock_mode = 1(“連續(xù)”鎖定模式)：在MySQL 8.0 之前驱还，連續(xù)鎖定模式是默認(rèn)的陨仅。在這個(gè)模式下，bulk inserts仍然使用AUTO-INC表級(jí)鎖铝侵，并保持到語句結(jié)束灼伤。這適用于所有INSERT ... SELECT，REPLACE ... SELECT和LOAD DATA語句咪鲜。同一時(shí)刻只有一個(gè)語句可以持有AUTO-INC鎖狐赡。對(duì)于Simple inserts（要插入的行數(shù)事先已知），則通過在mutex（輕量鎖） 的控制下獲得所需數(shù)量的自動(dòng)遞增值來避免表級(jí)AUTO-INC鎖疟丙， 它只在分配過程的持續(xù)時(shí)間內(nèi)保持颖侄，而不是直到語句完成鸟雏。不使用表級(jí)AUTO-INC鎖，除非AUTO-INC鎖由另一個(gè)事務(wù)保持览祖。如果另一個(gè)事務(wù)保持AUTO-INC鎖孝鹊，則Simple inserts等待AUTO-INC鎖，如同它是一個(gè)bulk inserts展蒂。（3）innodb_autoinc_lock_mode = 2(“交錯(cuò)”鎖定模式)：從MySQL 8.0開始又活，交錯(cuò)鎖模式是默認(rèn)設(shè)置。在此鎖定模式下锰悼，自動(dòng)遞增值保證在所有并發(fā)執(zhí)行的所有類型的insert語句中是唯一且單調(diào)遞增的柳骄。但是，由于多個(gè)語句可以同時(shí)生成數(shù)字（即箕般，跨語句交叉編號(hào)）耐薯，為任何給定語句插入的行生成的值可能不是連續(xù)的
2.1.4、元數(shù)據(jù)鎖（MDL鎖）
MySQL5.5引入了meta data lock丝里，簡(jiǎn)稱MDL鎖曲初，屬于表鎖范疇。MDL 的作用是杯聚，保證讀寫的正確性复斥。比如，如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)械媒，而執(zhí)行期間另一個(gè)線程對(duì)這個(gè) 表結(jié)構(gòu)做變更目锭，增加了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上纷捞，肯定是不行的痢虹。因此，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候主儡，加 MDL讀鎖奖唯；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL 寫鎖糜值。讀鎖之間不互斥丰捷，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。讀寫鎖之間寂汇、寫鎖之間是互斥的病往，用來保證變更結(jié)構(gòu)操作的安全性，解決了DML 和 DDL操作之間的一致性問題骄瓣。無需顯示使用停巷，在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。
2.1.4.1、會(huì)話A-從表中查詢數(shù)據(jù)
BEGIN ;
SELECT *
FROM teacher;
2.1.4.2畔勤、會(huì)話B-修改表結(jié)構(gòu)蕾各，增加新列
BEGIN ;
ALTER TABLE teacher ADD age INT NOT NULL ;
2.1.4.3、查看當(dāng)前MySQL的進(jìn)程
SHOW PROCESSLIST ;
- 通過會(huì)話C可以看出會(huì)話B被阻塞庆揪，這是由于會(huì)話A拿到了
teacher表的元數(shù)據(jù)讀鎖式曲，會(huì)話B想申請(qǐng)teacher表的元數(shù)據(jù)寫鎖，由于讀寫鎖互斥缸榛，會(huì)話B需要等待會(huì)話A釋放元數(shù)據(jù)鎖才能執(zhí)行吝羞。
2.1.4.4、小結(jié)
會(huì)話A會(huì)對(duì)表teacher加一個(gè)MDL 讀鎖仔掸，之后會(huì)話B要加MDL 寫鎖會(huì)被Blocked，因?yàn)?會(huì)話A的MDL 讀鎖還沒有釋放医清，而會(huì)話C要在表teacher上申請(qǐng)MDL 讀鎖的請(qǐng)求也會(huì)被會(huì)話 B阻塞起暮。因?yàn)?code>所有對(duì)表的增刪改查操作都需要先申請(qǐng) MDL 讀鎖，就都被 會(huì)話B 阻塞会烙，等于這個(gè)時(shí)候完全不可讀寫了
