DDL:數(shù)據(jù)定義，它用來(lái)定義數(shù)據(jù)庫(kù)對(duì)象，包括庫(kù)，表，列，通過(guò)ddl我們可以創(chuàng)建，刪除，修改數(shù)據(jù)庫(kù)和表結(jié)構(gòu)；
DML:數(shù)據(jù)操作語(yǔ)言，增加刪除修改數(shù)據(jù)表中的記錄；
DCL:數(shù)據(jù)控制語(yǔ)言，定義訪問(wèn)權(quán)限和安全級(jí)別；
DQL:數(shù)據(jù)查詢語(yǔ)言，用它來(lái)查詢想要的記錄。
SQL執(zhí)行順序:
- from;
- join
- on
- where;
- group by;
- avg,sum.... 使用聚集函數(shù)進(jìn)行計(jì)算;
- having;
- select;
- distinct;
- order by;
- limit;
今天來(lái)討論MySQL中的事物隔離級(jí)別
1事物概念
事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元。
事務(wù)具有以下4個(gè)屬性,通常簡(jiǎn)稱為事務(wù)的ACID屬性：
原子性：事務(wù)是一個(gè)原子操作單元,其對(duì)數(shù)據(jù)的修改,要么全都執(zhí)行,要么全都不執(zhí)行。
一致性：在事務(wù)開(kāi)始和完成時(shí),數(shù)據(jù)都必須保持一致?tīng)顟B(tài)。這意味著所有相關(guān)的數(shù)據(jù)規(guī)則都必須應(yīng)用于事務(wù)的修改,以保持?jǐn)?shù)據(jù)的完整性。
隔離性：數(shù)據(jù)庫(kù)系統(tǒng)提供一定的隔離機(jī)制,保證事務(wù)在不受外部并發(fā)操作影響的“獨(dú)立”環(huán)境執(zhí)行。這意味著事務(wù)處理過(guò)程中的中間狀態(tài)對(duì)外部是不可見(jiàn)的,反之亦然。
持久性：事務(wù)完成之后,它對(duì)于數(shù)據(jù)的修改是永久性的,即使出現(xiàn)系統(tǒng)故障也能夠保持。
事務(wù)的啟動(dòng)方式
- 顯式啟動(dòng) set autocommit=1 begin 或 start transaction。配套的提交語(yǔ)句是 commit，回滾語(yǔ)句是 rollback。
- set autocommit=0 自動(dòng)提交關(guān)掉,意味著如果你只執(zhí)行一個(gè) select 語(yǔ)句，這個(gè)事務(wù)就啟動(dòng)了，而且并不會(huì)自動(dòng)提交。這個(gè)事務(wù)持續(xù)存在直到你主動(dòng)執(zhí)行 commit 或 rollback 語(yǔ)句，或者斷開(kāi)連接。
- 用commit work and chAIn代替 commit可以提交一個(gè)事務(wù)，并且開(kāi)啟另一個(gè)新的事務(wù)。
2事物帶來(lái)的問(wèn)題
我們的數(shù)據(jù)庫(kù)一般都會(huì)并發(fā)執(zhí)行多個(gè)事務(wù)，多個(gè)事務(wù)可能會(huì)并發(fā)的對(duì)相同的一批數(shù)據(jù)進(jìn)行增刪改查操作，可能就會(huì)導(dǎo)致我們說(shuō)的臟寫(xiě)、臟讀、不可重復(fù)讀、幻讀這些問(wèn)題。
這些問(wèn)題的本質(zhì)都是數(shù)據(jù)庫(kù)的多事務(wù)并發(fā)問(wèn)題，為了解決多事務(wù)并發(fā)問(wèn)題，數(shù)據(jù)庫(kù)設(shè)計(jì)了事務(wù)隔離機(jī)制、鎖機(jī)制、MVCC多版本并發(fā)控制隔離機(jī)制，用一整套機(jī)制來(lái)解決多事務(wù)并發(fā)問(wèn)題。接下來(lái)，我們會(huì)深入講解這些機(jī)制，讓大家徹底理解數(shù)據(jù)庫(kù)內(nèi)部的執(zhí)行原理。
臟寫(xiě)
當(dāng)兩個(gè)或多個(gè)事務(wù)選擇同一行，然后基于最初選定的值更新該行時(shí)，由于每個(gè)事務(wù)都不知道其他事務(wù)的存在，就會(huì)發(fā)生丟失更新問(wèn)題，最后的更新覆蓋了由其他事務(wù)所做的更新。
臟讀
一個(gè)事務(wù)正在對(duì)一條記錄做修改，在這個(gè)事務(wù)完成并提交前，這條記錄的數(shù)據(jù)就處于不一致的狀態(tài)；這時(shí)，另一個(gè)事務(wù)也來(lái)讀取同一條記錄，如果不加控制，第二個(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ù)無(wú)效，不符合一致性要求。
不可重讀
一個(gè)事務(wù)在讀取某些數(shù)據(jù)后的某個(gè)時(shí)間，再次讀取以前讀過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變、或某些記錄已經(jīng)被刪除了！這種現(xiàn)象就叫做“不可重復(fù)讀”。
例：事務(wù)A內(nèi)部的相同查詢語(yǔ)句在不同時(shí)刻讀出的結(jié)果不一致，不符合隔離性
幻讀
一個(gè)事務(wù)按相同的查詢條件重新讀取以前檢索過(guò)的數(shù)據(jù)，卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象就稱為“幻讀”。
例：事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù)，不符合隔離性
不可重復(fù)讀與幻讀有什么區(qū)別？
不可重復(fù)讀的重點(diǎn)是修改：在同一事務(wù)中，同樣的條件，第一次讀的數(shù)據(jù)和第二次讀的「數(shù)據(jù)不一樣」。（因?yàn)橹虚g有其他事務(wù)提交了修改）
幻讀的重點(diǎn)在于新增或者刪除：在同一事務(wù)中，同樣的條件，第一次和第二次讀出來(lái)的「記錄數(shù)不一樣」。（因?yàn)橹虚g有其他事務(wù)提交了插入/刪除）
3事物的隔離級(jí)別
在 MySQL 中，事務(wù)支持是在引擎層實(shí)現(xiàn)的。你現(xiàn)在知道，MySQL 是一個(gè)支持多引擎的系統(tǒng)，但并不是所有的引擎都支持事務(wù)。比如 MySQL 原生的 MyISAM 引擎就不支持事務(wù)，這也是 MyISAM 被 InnoDB 取代的重要原因之一
InnoDB實(shí)現(xiàn)了四個(gè)標(biāo)準(zhǔn)的隔離級(jí)別，每一種級(jí)別都規(guī)定了一個(gè)事務(wù)中所做的修改，哪些在事務(wù)內(nèi)和事務(wù)間是可見(jiàn)的，哪些是不可見(jiàn)的。低級(jí)別的隔離級(jí)一般支持更高的并發(fā)處理，并擁有更低的系統(tǒng)開(kāi)銷(xiāo)。
查看當(dāng)前數(shù)據(jù)庫(kù)的事務(wù)隔離級(jí)別:
show variables like 'tx_isolation';
設(shè)置事務(wù)隔離級(jí)別：
set tx_isolation='REPEATABLE-READ';
查詢mysql的長(zhǎng)事務(wù)（大于60秒的事務(wù)）: select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60
Mysql默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀.
事務(wù)隔離級(jí)別
在談隔離級(jí)別之前，你首先要知道，你隔離得越嚴(yán)實(shí)，效率就會(huì)越低。因此很多時(shí)候，我們都要在二者之間尋找一個(gè)平衡點(diǎn)。
標(biāo)準(zhǔn)的事務(wù)隔離級(jí)別包括：
- 讀未提交:一個(gè)事務(wù)還沒(méi)提交時(shí)，它做的變更就能被別的事務(wù)看到
- 讀提交:一個(gè)事務(wù)提交之后，它做的變更才會(huì)被其他事務(wù)看到
- 可重復(fù)讀:一個(gè)事務(wù)執(zhí)行過(guò)程中看到的數(shù)據(jù)，總是跟這個(gè)事務(wù)在啟動(dòng)時(shí)看到的數(shù)據(jù)是一致的。當(dāng)然在可重復(fù)讀 隔離級(jí)別下，未提交變更對(duì)其他事務(wù)也是不可見(jiàn)的
- 串行化:顧名思義是對(duì)于同一行記錄，“寫(xiě)”會(huì)加“寫(xiě)鎖”，“讀”會(huì)加“讀鎖”。當(dāng)出現(xiàn)讀寫(xiě)鎖沖突的時(shí)候，后訪問(wèn)的事務(wù)必須等前一個(gè)事務(wù)執(zhí)行完成，才能繼續(xù)執(zhí)行
隔離級(jí)別是如何實(shí)現(xiàn)的呢？
在實(shí)現(xiàn)上，數(shù)據(jù)庫(kù)里面會(huì)創(chuàng)建一個(gè)視圖，訪問(wèn)的時(shí)候以視圖的邏輯結(jié)果為準(zhǔn)；
在“可重復(fù)讀”隔離級(jí)別下，這個(gè)視圖是在事務(wù)啟動(dòng)時(shí)創(chuàng)建的，整個(gè)事務(wù)存在期間都用這個(gè)視圖；
在“讀提交”隔離級(jí)別下，這個(gè)視圖是在每個(gè) SQL 語(yǔ)句開(kāi)始執(zhí)行的時(shí)候創(chuàng)建的；
在“讀未提交”隔離級(jí)別下直接返回記錄上的最新值，沒(méi)有視圖概念；
在“串行化”隔離級(jí)別下直接用加鎖的方式來(lái)避免并行訪問(wèn)。
圖片
在 MySQL 中，不同時(shí)刻啟動(dòng)的事務(wù)會(huì)有不同的一致性視圖read-view，并且每條記錄在更新的時(shí)候都會(huì)同時(shí)記錄一條回滾操作。記錄上的最新值，通過(guò)回滾操作，都可以得到前一個(gè)狀態(tài)的值，這就意味著同一條數(shù)據(jù)在數(shù)據(jù)庫(kù)中維護(hù)了多個(gè)版本，就是數(shù)據(jù)庫(kù)的多版本并發(fā)控制（MVCC），我們后續(xù)詳細(xì)討論MVCC機(jī)制。
從圖中可以看到每種隔離級(jí)別可以解決的問(wèn)題，我們可以看出可重復(fù)隔離級(jí)別下幻讀是沒(méi)有解決的，而且即便是加行鎖也解決不了問(wèn)題，我們上面說(shuō)了幻讀問(wèn)題說(shuō)的是新增刪除造成的問(wèn)題，而無(wú)論是可重復(fù)讀隔離級(jí)別還是行鎖操作的對(duì)象都是當(dāng)前行，所以幻讀問(wèn)題需要其他的方式解決。
注意：長(zhǎng)事務(wù)會(huì)造成回滾日志不斷增大，會(huì)有空間占用劇增的風(fēng)險(xiǎn)，盡量不要使用長(zhǎng)事務(wù)。
幻讀的解決
產(chǎn)生幻讀的原因是，行鎖只能鎖住行，但是新插入記錄這個(gè)動(dòng)作，要更新的是記錄之間的“間隙”。因此，為了解決幻讀問(wèn)題，InnoDB 只好引入新的鎖，也就是間隙鎖 (Gap Lock)，間隙鎖是在可重復(fù)讀隔離級(jí)別下才會(huì)生效的。所以，你如果把隔離級(jí)別設(shè)置為讀提交的話，就沒(méi)有間隙鎖了，間隙鎖是開(kāi)區(qū)間。間隙鎖和行鎖合稱 next-key lock，每個(gè) next-key lock 是前開(kāi)后閉區(qū)間。也就是說(shuō)，我們的表 t 初始化以后，如果用 select * from t for update 要把整個(gè)表所有記錄鎖起來(lái)，就形成了 7 個(gè) next-key lock，分別是 (-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。間隙鎖和 next-key lock 的引入，幫我們解決了幻讀的問(wèn)題，但同時(shí)也帶來(lái)了一些“困擾”，間隙鎖的引入，可能會(huì)導(dǎo)致同樣的語(yǔ)句鎖住更大的范圍，這其實(shí)是影響了并發(fā)度的。
簡(jiǎn)單總結(jié)
解決上述問(wèn)題其實(shí)就是依賴于mysql的MVCC機(jī)制和鎖機(jī)制，我們后續(xù)分別討論。
