我們的數(shù)據(jù)庫一般都會(huì)并發(fā)執(zhí)行多個(gè)事務(wù)，多個(gè)事務(wù)可能會(huì)并發(fā)的對(duì)相同的一批數(shù)據(jù)進(jìn)行增刪改查操作，可能就會(huì)導(dǎo)致臟寫鹤树、臟讀铣焊、不可重復(fù)讀、幻讀這些問題罕伯。

這些問題的本質(zhì)都是數(shù)據(jù)庫的多事務(wù)并發(fā)問題曲伊，為了解決多事務(wù)并發(fā)問題，數(shù)據(jù)庫設(shè)計(jì)了事務(wù)隔離機(jī)制追他、鎖機(jī)制坟募、MVCC多版本并發(fā)控制隔離機(jī)制，用一整套機(jī)制來解決多事務(wù)并發(fā)問題邑狸。

事務(wù)及其ACID屬性

事務(wù)是由一組SQL語句組成的邏輯處理單元懈糯，事務(wù)具有以下4個(gè)屬性，通常簡(jiǎn)稱為事務(wù)的ACID屬性单雾。

• 原子性(Atomicity) ：事務(wù)是一個(gè)原子操作單元赚哗，其對(duì)數(shù)據(jù)的修改，要么全都執(zhí)行硅堆，要么全都不執(zhí)行屿储。
• 一致性(Consistent) ：在事務(wù)開始和完成時(shí)，數(shù)據(jù)都必須保持一致狀態(tài)渐逃。這意味著所有相關(guān)的數(shù)據(jù)規(guī) 則都必須應(yīng)用于事務(wù)的修改够掠，以保持?jǐn)?shù)據(jù)的完整性。
• 隔離性(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)故障也能夠保持畜普。

并發(fā)事務(wù)處理帶來的問題

更新丟失(Lost Update)或臟寫
當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行期丰，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在吃挑，就會(huì)發(fā)生丟失更新問題–最后的更新覆蓋了由其他事務(wù)所做的更新钝荡。

臟讀（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)象被形象的叫做“臟讀”荣病。

事務(wù)A讀取到了事務(wù)B已經(jīng)修改但尚未提交的數(shù)據(jù)，還在這個(gè)數(shù)據(jù)基礎(chǔ)上做了操作渗柿。此時(shí)个盆，如果B 事務(wù)回滾，A讀取的數(shù)據(jù)無效朵栖，不符合一致性要求颊亮。

不可重讀（Non-Repeatable Reads）
一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過的數(shù)據(jù)陨溅，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改 變终惑、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象就叫做“不可重復(fù)讀”声登。

事務(wù)A內(nèi)部的相同查詢語句在不同時(shí)刻讀出的結(jié)果不一致狠鸳，不符合隔離性

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

事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù)卸察，不符合隔離性

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

“臟讀”脯厨、“不可重復(fù)讀”和“幻讀”，其實(shí)都是數(shù)據(jù)庫讀一致性問題坑质，必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決合武。

image.png

數(shù)據(jù)庫的事務(wù)隔離越嚴(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ā)訪問的能力红淡。

show variables like 'tx_isolation';  -- 查看當(dāng)前數(shù)據(jù)庫的事務(wù)隔離級(jí)別
set tx_isolation='REPEATABLE-READ';  -- 設(shè)置事務(wù)隔離級(jí)別

Mysql默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀，用Spring開發(fā)程序時(shí)降铸，如果不設(shè)置隔離級(jí)別默認(rèn)用Mysql設(shè)置的隔離級(jí)別在旱，如果Spring設(shè)置了就用已經(jīng)設(shè)置的隔離級(jí)別

鎖

鎖是計(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è)重要因素绍豁。

鎖分類

從性能上分為樂觀鎖(用版本對(duì)比來實(shí)現(xiàn))和悲觀鎖芯咧；
從對(duì)數(shù)據(jù)庫操作的類型分，分為讀鎖和寫鎖(都屬于悲觀鎖) 竹揍；
從對(duì)數(shù)據(jù)操作的粒度分敬飒，分為表鎖和行鎖。

讀鎖（共享鎖芬位，S鎖(Shared)）：針對(duì)同一份數(shù)據(jù)无拗，多個(gè)讀操作可以同時(shí)進(jìn)行而不會(huì)互相影響
寫鎖（排它鎖，X鎖(eXclusive)）：當(dāng)前寫操作沒有完成前昧碉，它會(huì)阻斷其他寫鎖和讀鎖

表鎖

每次操作鎖住整張表英染。開銷小，加鎖快被饿；不會(huì)出現(xiàn)死鎖四康；鎖定粒度大，發(fā)生鎖沖突的概率最高狭握，并發(fā)度最低闪金；
一般用在整表數(shù)據(jù)遷移的場(chǎng)景。

-- 手動(dòng)增加表鎖 
lock table 表名稱 read(write)论颅，表名稱2 read(write); 
-- 查看表上加過的鎖 
show open tables; 
-- 刪除表鎖 
unlock tables;

行鎖

行鎖（Record Lock）：鎖直接加在索引記錄上面哎垦。
InnoDB與MYISAM的最大不同有兩點(diǎn)：

• InnoDB支持事務(wù)（TRANSACTION）
• InnoDB支持行級(jí)鎖

-- 共享鎖 S
SELECT table LOCK IN SHARE MODE 
-- 排它鎖 X
SELECT table FOR UPDATE

間隙鎖與插入意向鎖

當(dāng)鎖加在不存在的空閑空間（可以是兩個(gè)索引記錄之間，也可能是第一個(gè)索引記錄之前或最后一個(gè)索引之后的空間）恃疯，InnoDB會(huì)給符合條件的已有數(shù)據(jù)記錄的索引項(xiàng)加上間隙鎖（Gap Lock）漏设。

插入意向鎖 Insert Intention Locks，代表當(dāng)前事務(wù)準(zhǔn)備插入一行數(shù)據(jù)今妄。使用INSERT/UPDATE/DELETE語句都會(huì)默認(rèn)獲得插入意向鎖郑口。

小結(jié)
MyISAM在執(zhí)行查詢語句SELECT前，會(huì)自動(dòng)給涉及的所有表加讀鎖蛙奖，在執(zhí)行update潘酗、insert、delete操作會(huì)自動(dòng)給涉及的表加寫鎖雁仲。
InnoDB在執(zhí)行查詢語句SELECT時(shí)(非串行隔離級(jí)別)仔夺，不會(huì)加鎖。但是update攒砖、insert缸兔、delete操作會(huì)加行鎖日裙。
簡(jiǎn)而言之，就是讀鎖會(huì)阻塞寫惰蜜，但是不會(huì)阻塞讀昂拂。而寫鎖則會(huì)把讀和寫都阻塞。

無索引行鎖會(huì)升級(jí)為表鎖

鎖主要是加在索引上抛猖，如果對(duì)非索引字段更新格侯，行鎖可能會(huì)變表鎖。
session1 執(zhí)行：update account set balance = 800 where name = 'lilei';
session2 對(duì)該表任一行操作都會(huì)阻塞住 InnoDB的行鎖是針對(duì)索引加的鎖财著，不是針對(duì)記錄加的鎖联四。并且該索引不能失效，否則都會(huì)從行鎖升級(jí)為 表鎖撑教。
鎖定某一行還可以用lock in share mode(共享鎖) 和for update(排它鎖)朝墩，例如：select * from test_innodb_lock where a = 2 for update; 這樣其他session只能讀這行數(shù)據(jù)，修改則會(huì)被阻塞伟姐，直到鎖定行的session提交收苏。
結(jié)論
Innodb存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面所帶來的性能損耗可能比表級(jí)鎖定會(huì)要更高一些愤兵，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)優(yōu)于MYISAM的表級(jí)鎖定的鹿霸。當(dāng)系統(tǒng)并發(fā)量高的時(shí)候，Innodb 的整體性能和MYISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)了恐似。
但是杜跷，Innodb的行級(jí)鎖定同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候矫夷，可能會(huì)讓Innodb的整體性能表現(xiàn)不僅不能比MYISAM高，甚至可能會(huì)更差憋槐。

行鎖分析

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

show status like 'innodb_row_lock%';

對(duì)各個(gè)狀態(tài)量的說明如下：
Innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量双藕；
Innodb_row_lock_time: 從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度；
Innodb_row_lock_time_avg: 每次等待所花平均時(shí)間阳仔；
Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花時(shí)間忧陪；
Innodb_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)；
當(dāng)?shù)却螖?shù)很高近范，而且每次等待時(shí)長(zhǎng)也不小的時(shí)候嘶摊，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待， 然后根據(jù)分析結(jié)果著手制定優(yōu)化計(jì)劃评矩。

查看INFORMATION_SCHEMA系統(tǒng)庫鎖相關(guān)數(shù)據(jù)表

‐‐ 查看事務(wù)
select * from INFORMATION_SCHEMA.INNODB_TRX;
‐‐ 查看鎖
select * from INFORMATION_SCHEMA.INNODB_LOCKS;
‐‐ 查看鎖等待
select * from INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

‐‐ 釋放鎖叶堆，trx_mysql_thread_id可以從INNODB_TRX表里查看到
kill trx_mysql_thread_id

 ‐‐ 查看鎖等待詳細(xì)信息
show engine innodb status\G;

死鎖

set tx_isolation='repeatable-read'; 
Session_1執(zhí)行：select * from account where id=1 for update; 
Session_2執(zhí)行：select * from account where id=2 for update; 
Session_1執(zhí)行：select * from account where id=2 for update; 
Session_2執(zhí)行：select * from account where id=1 for update; 
-- 查看近期死鎖日志信息：
show engine innodb status\G; 

大多數(shù)情況mysql可以自動(dòng)檢測(cè)死鎖并回滾產(chǎn)生死鎖的那個(gè)事務(wù)，但是有些情況mysql沒法自動(dòng)檢測(cè)死鎖斥杜。

鎖優(yōu)化建議

1. 盡可能讓所有數(shù)據(jù)檢索都通過索引來完成虱颗，避免無索引行鎖升級(jí)為表鎖沥匈，合理設(shè)計(jì)索引，盡量縮小鎖的范圍忘渔；
2. 盡可能減少檢索條件范圍高帖，避免間隙鎖；
3. 盡量控制事務(wù)大小畦粮，減少鎖定資源量和時(shí)間長(zhǎng)度散址，涉及事務(wù)加鎖的sql盡量放在事務(wù)最后執(zhí)行；
4. 盡可能低級(jí)別事務(wù)隔離宣赔。

MVCC多版本并發(fā)控制機(jī)制

Mysql在可重復(fù)讀隔離級(jí)別下预麸，同樣的sql查詢語句在一個(gè)事務(wù)里多次執(zhí)行查詢結(jié)果相同，就算其它事務(wù)對(duì)數(shù)據(jù)有修改也不會(huì)影響當(dāng)前事務(wù)sql語句的查詢結(jié)果拉背。

這個(gè)隔離性就是靠MVCC(Multi-Version Concurrency Control)機(jī)制來保證的师崎，對(duì)一行數(shù)據(jù)的讀和寫兩個(gè)操作默認(rèn)是不會(huì)通過加鎖互斥來保證隔離性，避免了頻繁加鎖互斥椅棺，而在串行化隔離級(jí)別為了保證較高的隔離性是通過將所有操作加鎖互斥來實(shí)現(xiàn)的犁罩。
Mysql在讀已提交和可重復(fù)讀隔離級(jí)別下都實(shí)現(xiàn)了MVCC機(jī)制（正常的SELECT語句，后面不加FOR UPDATE和LOCK IN SHARE MODE的两疚，才會(huì)用MVCC去讀）床估。

undo日志版本鏈與read view機(jī)制

undo日志版本鏈是指一行數(shù)據(jù)被多個(gè)事務(wù)依次修改過后，在每個(gè)事務(wù)修改完后诱渤，Mysql會(huì)保留修改前的數(shù)據(jù)undo回滾日志丐巫，并且用兩個(gè)隱藏字段 trx_id 和 roll_pointer 把這些 undo日志串聯(lián)起來形成一個(gè)歷史記錄版本鏈(見下圖)。

image.png

版本鏈比對(duì)規(guī)則：

1. 如果 row 的 trx_id 落在綠色部分( trx_id<min_id )占卧，表示這個(gè)版本是已提交的事務(wù)生成的遗菠，這個(gè)數(shù)據(jù)是可見的；
2. 如果 row 的 trx_id 落在紅色部分( trx_id>max_id )华蜒，表示這個(gè)版本是由將來啟動(dòng)的事務(wù)生成的辙纬，是不可見的(若 row 的 trx_id 就是當(dāng)前自己的事務(wù)是可見的）；
3. 如果 row 的 trx_id 落在黃色部分(min_id <=trx_id<= max_id)叭喜，那就包括兩種情況：
   a. 若 row 的 trx_id 在視圖數(shù)組中贺拣，表示這個(gè)版本是由還沒提交的事務(wù)生成的，不可見(若 row 的 trx_id 就是當(dāng)前自己的事務(wù)是可見的)；
   b. 若 row 的 trx_id 不在視圖數(shù)組中纵柿，表示這個(gè)版本是已經(jīng)提交了的事務(wù)生成的蜈抓，可見。

對(duì)于刪除的情況可以認(rèn)為是update的特殊情況昂儒，會(huì)將版本鏈上最新的數(shù)據(jù)復(fù)制一份沟使，然后將trx_id修改成刪除操作的trx_id，同時(shí)在該條記錄的頭信息（record header）里的（deleted_flag）標(biāo)記位寫上true渊跋，來表示當(dāng)前記錄已經(jīng)被刪除腊嗡，在查詢時(shí)按照上面的規(guī)則查到對(duì)應(yīng)的記錄如果delete_flag標(biāo)記位為true，意味著記錄已被刪除拾酝，則不返回?cái)?shù)據(jù)燕少。

注意：begin/start transaction 命令并不是一個(gè)事務(wù)的起點(diǎn)，在執(zhí)行到它們之后的第一個(gè)修改操作InnoDB表的語句蒿囤，事務(wù)才真正啟動(dòng)客们，才會(huì)向mysql申請(qǐng)事務(wù)id，mysql內(nèi)部是嚴(yán)格按照事務(wù)的啟動(dòng)順序來分配事務(wù)id的材诽。

總結(jié)：

MVCC機(jī)制的實(shí)現(xiàn)就是通過read-view機(jī)制與undo版本鏈比對(duì)機(jī)制底挫，使得不同的事務(wù)會(huì)根據(jù)數(shù)據(jù)版本鏈對(duì)比規(guī)則讀取同一條數(shù)據(jù)在版本鏈上的不同版本數(shù)據(jù)。

Innodb引擎SQL執(zhí)行的BufferPool緩存機(jī)制

image.png

為什么Mysql不能直接更新磁盤上的數(shù)據(jù)而且設(shè)置這么一套復(fù)雜的機(jī)制來執(zhí)行SQL呢脸侥？
因?yàn)閬硪粋€(gè)請(qǐng)求就直接對(duì)磁盤文件進(jìn)行隨機(jī)讀寫建邓，然后更新磁盤文件里的數(shù)據(jù)性能可能相當(dāng)差。

磁盤隨機(jī)讀寫的性能非常差睁枕，所以直接更新磁盤文件是不能讓數(shù)據(jù)庫抗住很高并發(fā)的官边。
Mysql這套機(jī)制看起來復(fù)雜，但它可以保證每個(gè)更新請(qǐng)求都是更新內(nèi)存BufferPool外遇，然后順序?qū)懭罩疚募⒉荆瑫r(shí)還能保證各種異常情況下的數(shù)據(jù)一致性。
更新內(nèi)存的性能是極高的跳仿，然后順序?qū)懘疟P上的日志文件的性能也是非常高的滩援，要遠(yuǎn)高于隨機(jī)讀寫磁盤文件。 正是通過這套機(jī)制塔嬉，才能讓我們的MySQL數(shù)據(jù)庫在較高配置的機(jī)器上每秒可以抗下幾干的讀寫請(qǐng)求。