什么是數(shù)據(jù)庫(kù)的事務(wù)?
事務(wù)的典型場(chǎng)景
在項(xiàng)目里面,什么地方會(huì)開(kāi)啟事務(wù),或者配置了事務(wù)?無(wú)論是在方法上加注解,還是配置切面
<tx:advice id="txAdvice" transaction-manager="transactionManager">
<tx:attributes>
<tx:method name="save*" rollback-for="Throwable" />
<tx:method name="add*" rollback-for="Throwable" />
<tx:method name="send*" rollback-for="Throwable" />
<tx:method name="insert*" rollback-for="Throwable" />
</tx:attributes>
</tx:advice>
比如下單,會(huì)操作訂單表,資金表,物流表等等,這個(gè)時(shí)候我們需要讓這些操作都在一個(gè)事務(wù)里面完成.當(dāng)一個(gè)業(yè)務(wù)流程涉及多個(gè)表的操作的時(shí)候,我們希望它們要么是全部成功的,要么都不成功,這個(gè)時(shí)候我們會(huì)啟用事務(wù)
在金融的系統(tǒng)里面事務(wù)配置是很常見(jiàn)的,比如行內(nèi)轉(zhuǎn)賬的這種操作,如果我們把它簡(jiǎn)單地理解為一個(gè)賬戶的余額增加,另一個(gè)賬戶的余額減少的情況(當(dāng)然實(shí)際上要比這復(fù)雜),那么這兩個(gè)動(dòng)作一定是同時(shí)成功或者同時(shí)失敗的,否則就會(huì)造成銀行的會(huì)計(jì)科目不平衡
事務(wù)的定義
什么是事務(wù)?
維基百科的定義:事務(wù)是數(shù)據(jù)庫(kù)管理系統(tǒng)(DBMS)執(zhí)行過(guò)程中的一個(gè)邏輯單位,由一個(gè)有限的數(shù)據(jù)庫(kù)操作序列構(gòu)成
這里面有兩個(gè)關(guān)鍵點(diǎn),第一個(gè),它是數(shù)據(jù)庫(kù)最小的工作單元,是不可以再分的.第二個(gè),它可能包含了一個(gè)或者一系列的 DML 語(yǔ)句,包括 insertdeleteupdate.(單條 DDL(createdrop)和 DCL(grantrevoke)也會(huì)有事務(wù))
哪些存儲(chǔ)引擎支持事務(wù)
在我們第一天的課里面說(shuō)到了,InnoDB 支持事務(wù),這個(gè)也是它成為默認(rèn)的存儲(chǔ)引擎的一個(gè)重要原因:
https://dev.mysql.com/doc/refman/5.7/en/storage-engines.html
另一個(gè)是 NDB
事務(wù)的四大特性
事務(wù)的四大特性:ACID
- 原子性,Atomicity,也就是我們剛才說(shuō)的不可再分,也就意味著我們對(duì)數(shù)據(jù)庫(kù)的一系列的操作,要么都是成功,要么都是失敗,不可能出現(xiàn)部分成功或者部分失敗的情況.以轉(zhuǎn)賬的場(chǎng)景為例,一個(gè)賬戶的余額減少,對(duì)應(yīng)一個(gè)賬戶的增加,這兩個(gè)一定是同時(shí)成功或者同時(shí)失敗的,全部成功比較簡(jiǎn)單,問(wèn)題是如果前面一個(gè)操作已經(jīng)成功了,后面的操作失敗了,怎么讓它全部失敗呢?這個(gè)時(shí)候我們必須要回滾,原子性,在 InnoDB 里面是通過(guò) undolog 來(lái)實(shí)現(xiàn)的,它記錄了數(shù)據(jù)修改之前的值(邏輯日志),一旦發(fā)生異常,就可以用 undolog 來(lái)實(shí)現(xiàn)回滾操作
- 一致性,consistent,指的是數(shù)據(jù)庫(kù)的完整性約束沒(méi)有被破壞,事務(wù)執(zhí)行的前后都是合法的數(shù)據(jù)狀態(tài).比如主鍵必須是唯一的,字段長(zhǎng)度符合要求.除了數(shù)據(jù)庫(kù)自身的完整性約束,還有一個(gè)是用戶自定義的完整性.比如說(shuō)轉(zhuǎn)賬的這個(gè)場(chǎng)景,A 賬戶余額減少 1000,B 賬戶余額只增加了 500,這個(gè)時(shí)候因?yàn)閮蓚€(gè)操作都成功了,按照我們對(duì)原子性的定義,它是滿足原子性的,但是它沒(méi)有滿足一致性,因?yàn)樗鼘?dǎo)致了會(huì)計(jì)科目的不平衡.還有一種情況,A 賬戶余額為 0,如果這個(gè)時(shí)候轉(zhuǎn)賬成功了,A 賬戶的余額會(huì)變成-1000,雖然它滿足了原子性的,但是我們知道,借記卡的余額是不能夠小于 0 的,所以也違反了一致性.用戶自定義的完整性通常要在代碼中控制
- 隔離性,Isolation,我們有了事務(wù)的定義以后,在數(shù)據(jù)庫(kù)里面會(huì)有很多的事務(wù)同時(shí)去操作我們的同一張表或者同一行數(shù)據(jù),必然會(huì)產(chǎn)生一些并發(fā)或者干擾的操作,那么我們對(duì)隔離性的定義,就是這些很多個(gè)的事務(wù),對(duì)表或者行的并發(fā)操作,應(yīng)該是透明的,互相不干擾的.通過(guò)這種方式,我們最終也是保證業(yè)務(wù)數(shù)據(jù)的一致性
- 做持久性,Durable,事務(wù)的持久性是什么意思呢?我們對(duì)數(shù)據(jù)庫(kù)的任意的操作,增刪改,只要事務(wù)提交成功,那么結(jié)果就是永久性的,不可能因?yàn)槲覀兿到y(tǒng)宕機(jī)或者重啟了數(shù)據(jù)庫(kù)的服務(wù)器,它又恢復(fù)到原來(lái)的狀態(tài)了.這個(gè)就是事務(wù)的持久性
持久性怎么實(shí)現(xiàn)呢?數(shù)據(jù)庫(kù)崩潰恢復(fù)(crash-safe)是通過(guò)什么實(shí)現(xiàn)的?
持久性是通過(guò) redolog 和 doublewrite 雙寫(xiě)緩沖來(lái)實(shí)現(xiàn)的,我們操作數(shù)據(jù)的時(shí)候,會(huì)先寫(xiě)到內(nèi)存的 bufferpool 里面,同時(shí)記錄 redolog,如果在刷盤(pán)之前出現(xiàn)異常,在重啟后就可以讀取 redolog 的內(nèi)容,寫(xiě)入到磁盤(pán),保證數(shù)據(jù)的持久性
當(dāng)然,恢復(fù)成功的前提是數(shù)據(jù)頁(yè)本身沒(méi)有被破壞,是完整的,這個(gè)通過(guò)雙寫(xiě)緩沖(doublewrite)保證.原子性,隔離性,持久性,最后都是為了實(shí)現(xiàn)一致性
原子性,隔離性,持久性,最后都是為了實(shí)現(xiàn)一致性
數(shù)據(jù)庫(kù)什么時(shí)候會(huì)出現(xiàn)事務(wù)
無(wú)論是我們?cè)?Navicat 的這種工具里面去操作,還是在我們的 Java 代碼里面通過(guò) API 去操作,還是加上@Transactional 的注解或者 AOP 配置,其實(shí)最終都是發(fā)送一個(gè)指令到數(shù)據(jù)庫(kù)去執(zhí)行,Java 的 JDBC 只不過(guò)是把這些命令封裝起來(lái)了
我們先來(lái)看一下我們的操作環(huán)境.版本(5.7),存儲(chǔ)引擎(InnnoDB),事務(wù)隔離級(jí)別(RR)
select version();
show variables like '%engine%';
show global variables like "tx_isolation";
執(zhí)行這樣一條更新語(yǔ)句的時(shí)候,它有事務(wù)嗎?
update student set sname = '貓老公 111' where id=1;
實(shí)際上,它自動(dòng)開(kāi)啟了一個(gè)事務(wù),并且提交了,所以最終寫(xiě)入了磁盤(pán)
這個(gè)是開(kāi)啟事務(wù)的第一種方式,自動(dòng)開(kāi)啟和自動(dòng)提交.InnoDB 里面有一個(gè) autocommit 的參數(shù)(分成兩個(gè)級(jí)別,session 級(jí)別和 global 級(jí)別)
show variables like 'autocommit';
它的默認(rèn)值是 ON.autocommit 這個(gè)參數(shù)是什么意思呢?是否自動(dòng)提交.如果它的值是 true/on 的話,我們?cè)诓僮鲾?shù)據(jù)的時(shí)候,會(huì)自動(dòng)開(kāi)啟一個(gè)事務(wù),和自動(dòng)提交事務(wù)
否則,如果我們把 autocommit 設(shè)置成 false/off,那么數(shù)據(jù)庫(kù)的事務(wù)就需要我們手動(dòng)地去開(kāi)啟和手動(dòng)地去結(jié)束
手動(dòng)開(kāi)啟事務(wù)也有幾種方式,一種是用 begin；一種是用 starttransaction
那么怎么結(jié)束一個(gè)事務(wù)呢?我們結(jié)束也有兩種方式,第一種就是提交一個(gè)事務(wù),commit；還有一種就是 rollback,回滾的時(shí)候,事務(wù)也會(huì)結(jié)束.還有一種情況,客戶端的連接斷開(kāi)的時(shí)候,事務(wù)也會(huì)結(jié)束
后面我們會(huì)講到,當(dāng)我們結(jié)束一個(gè)事務(wù)的時(shí)候,事務(wù)持有的鎖就會(huì)被釋放,無(wú)論是提交還是回滾
我們用 begin 手工開(kāi)啟一個(gè)事務(wù),執(zhí)行第二個(gè) update,但是數(shù)據(jù)沒(méi)有寫(xiě)入磁盤(pán),因?yàn)槭聞?wù)還沒(méi)有提交,這個(gè)時(shí)候 commit 一下,再刷新一下,OK,寫(xiě)入了
這個(gè)就是我們開(kāi)啟和結(jié)束事務(wù)的兩種方式
事務(wù)并發(fā)會(huì)帶來(lái)什么問(wèn)題?
當(dāng)很多事務(wù)并發(fā)地去操作數(shù)據(jù)庫(kù)的表或者行的時(shí)候,如果沒(méi)有我們剛才講的事務(wù)的 Isolation 隔離性的時(shí)候,會(huì)帶來(lái)哪些問(wèn)題呢?
我們有兩個(gè)事務(wù),一個(gè)是 TransactionA,一個(gè)是 TransactionB,在第一個(gè)事務(wù)里面,它首先通過(guò)一個(gè) whereid=1 的條件查詢一條數(shù)據(jù),返回 name=Ada,age=16 的這條數(shù)據(jù).然后第二個(gè)事務(wù),它同樣地是去操作 id=1 的這行數(shù)據(jù),它通過(guò)一個(gè) update 的語(yǔ)句,把這行 id=1 的數(shù)據(jù)的 age 改成了 18,但是注意,它沒(méi)有提交
這個(gè)時(shí)候,在第一個(gè)事務(wù)里面,它再次去執(zhí)行相同的查詢操作,發(fā)現(xiàn)數(shù)據(jù)發(fā)生了變化,獲取到的數(shù)據(jù) age 變成了 18.那么,這種在一個(gè)事務(wù)里面,由于其他的時(shí)候修改了數(shù)據(jù)并且沒(méi)有提交,而導(dǎo)致了前后兩次讀取數(shù)據(jù)不一致的情況,這種事務(wù)并發(fā)的問(wèn)題,我們把它定義成什么?
這個(gè)叫做臟讀
如果在轉(zhuǎn)賬的案例里面,我們第一個(gè)事務(wù)基于讀取到的第二個(gè)事務(wù)未提交的余額進(jìn)行了操作,但是第二個(gè)事務(wù)進(jìn)行了回滾,這個(gè)時(shí)候就會(huì)導(dǎo)致數(shù)據(jù)不一致
這種讀取到其他事務(wù)未提交的數(shù)據(jù)的情況,我們把它叫做臟讀.我們?cè)賮?lái)看第二個(gè)
同樣是兩個(gè)事務(wù),第一個(gè)事務(wù)通過(guò) id=1 查詢到了一條數(shù)據(jù).然后在第二個(gè)事務(wù)里面執(zhí)行了一個(gè) update 操作,這里大家注意一下,執(zhí)行了 update 以后它通過(guò)一個(gè) commit 提交了修改.然后第一個(gè)事務(wù)讀取到了其他事務(wù)已提交的數(shù)據(jù)導(dǎo)致前后兩次讀取數(shù)據(jù)不一致的情況,就像這里,age 到底是等于 16 還是 18,那么這種事務(wù)并發(fā)帶來(lái)的問(wèn)題,我們把它叫做什么?
這種一個(gè)事務(wù)讀取到了其他事務(wù)已提交的數(shù)據(jù)導(dǎo)致前后兩次讀取數(shù)據(jù)不一致的情況,我們把它叫做不可重復(fù)讀
在第一個(gè)事務(wù)里面我們執(zhí)行了一個(gè)范圍查詢,這個(gè)時(shí)候滿足條件的數(shù)據(jù)只有一條.在第二個(gè)事務(wù)里面,它插入了一行數(shù)據(jù),并且提交了.重點(diǎn):插入了一行數(shù)據(jù).在第一個(gè)事務(wù)里面再去查詢的時(shí)候,它發(fā)現(xiàn)多了一行數(shù)據(jù).這種情況,我們把它叫做什么呢?
一個(gè)事務(wù)前后兩次讀取數(shù)據(jù)數(shù)據(jù)不一致,是由于其他事務(wù)插入數(shù)據(jù)造成的,這種情況我們把它叫做幻讀.
不可重復(fù)讀和幻讀,的區(qū)別在那里呢?
不可重復(fù)讀是修改或者刪除,幻讀是插入
小結(jié):我們剛才講了事務(wù)并發(fā)帶來(lái)的三大問(wèn)題,現(xiàn)在來(lái)給大家總結(jié)一下.無(wú)論是臟讀,還是不可重復(fù)讀,還是幻讀,它們都是數(shù)據(jù)庫(kù)的讀一致性的問(wèn)題,都是在一個(gè)事務(wù)里面前后兩次讀取出現(xiàn)了不一致的情況
讀一致性的問(wèn)題,必須要由數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決.就像我們?nèi)ワ埖瓿燥?基本的設(shè)施和衛(wèi)生保證都是飯店提供的.那么我們使用數(shù)據(jù)庫(kù),隔離性的問(wèn)題也必須由數(shù)據(jù)庫(kù)幫助我們來(lái)解決
SQL92 標(biāo)準(zhǔn)
所以,就有很多的數(shù)據(jù)庫(kù)專家聯(lián)合制定了一個(gè)標(biāo)準(zhǔn),也就是說(shuō)建議數(shù)據(jù)庫(kù)廠商都按照這個(gè)標(biāo)準(zhǔn),提供一定的事務(wù)隔離級(jí)別,來(lái)解決事務(wù)并發(fā)的問(wèn)題,這個(gè)就是 SQL92 標(biāo)準(zhǔn)
我們來(lái)看一下 SQL92 標(biāo)準(zhǔn)的官網(wǎng)
http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt
這里面有一張表格(搜索_iso),里面定義了四個(gè)隔離級(jí)別,右邊的 P1P2P3 就是代表事務(wù)并發(fā)的 3 個(gè)問(wèn)題,臟讀,不可重復(fù)讀,幻讀.Possible 代表在這個(gè)隔離級(jí)別下,這個(gè)問(wèn)題有可能發(fā)生,換句話說(shuō),沒(méi)有解決這個(gè)問(wèn)題.NotPossible 就是解決了這個(gè)問(wèn)題
我們?cè)敿?xì)地分析一下這4個(gè)隔離級(jí)別是怎么定義的
- 第一個(gè)隔離級(jí)別叫做:ReadUncommitted(未提交讀),一個(gè)事務(wù)可以讀取到其他事務(wù)未提交的數(shù)據(jù),會(huì)出現(xiàn)臟讀,所以叫做 RU,它沒(méi)有解決任何的問(wèn)題
- 第二個(gè)隔離級(jí)別叫做:ReadCommitted(已提交讀),也就是一個(gè)事務(wù)只能讀取到其他事務(wù)已提交的數(shù)據(jù),不能讀取到其他事務(wù)未提交的數(shù)據(jù),它解決了臟讀的問(wèn)題,但是會(huì)出現(xiàn)不可重復(fù)讀的問(wèn)題
- 第三個(gè)隔離級(jí)別叫做:RepeatableRead(可重復(fù)讀),它解決了不可重復(fù)讀的問(wèn)題,也就是在同一個(gè)事務(wù)里面多次讀取同樣的數(shù)據(jù)結(jié)果是一樣的,但是在這個(gè)級(jí)別下,沒(méi)有定義解決幻讀的問(wèn)題
- 最后一個(gè)就是:Serializable(串行化),在這個(gè)隔離級(jí)別里面,所有的事務(wù)都是串行執(zhí)行的,也就是對(duì)數(shù)據(jù)的操作需要排隊(duì),已經(jīng)不存在事務(wù)的并發(fā)操作了,所以它解決了所有的問(wèn)題
這個(gè)是 SQL92 的標(biāo)準(zhǔn),但是不同的數(shù)據(jù)庫(kù)廠商或者存儲(chǔ)引擎的實(shí)現(xiàn)有一定的差異,比如 Oracle 里面就只有兩種 RC(已提交讀)和 Serializable(串行化).那么 InnoDB 的實(shí)現(xiàn)又是怎么樣的呢?
MySQL InnoDB 對(duì)隔離級(jí)別的支持
在 MySQLInnoDB 里面,不需要使用串行化的隔離級(jí)別去解決所有問(wèn)題.那我們來(lái)看一下 MySQLInnoDB 里面對(duì)數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別的支持程度是什么樣的
InnoDB 支持的四個(gè)隔離級(jí)別和 SQL92 定義的基本一致,隔離級(jí)別越高,事務(wù)的并發(fā)度就越低.唯一的區(qū)別就在于,InnoDB 在 RR 的級(jí)別就解決了幻讀的問(wèn)題.這個(gè)也是 InnoDB 默認(rèn)使用 RR 作為事務(wù)隔離級(jí)別的原因,既保證了數(shù)據(jù)的一致性,又支持較高的并發(fā)度
兩大實(shí)現(xiàn)方案
那么大家想一下,如果要解決讀一致性的問(wèn)題,保證一個(gè)事務(wù)中前后兩次讀取數(shù)據(jù)結(jié)果一致,實(shí)現(xiàn)事務(wù)隔離,應(yīng)該怎么做?我們有哪一些方法呢?你的思路是什么樣的呢?
總體上來(lái)說(shuō),我們有兩大類的方案
LBCC
第一種,我既然要保證前后兩次讀取數(shù)據(jù)一致,那么我讀取數(shù)據(jù)的時(shí)候,鎖定我要操作的數(shù)據(jù),不允許其他的事務(wù)修改就行了.這種方案我們叫做基于鎖的并發(fā)控制 LockBasedConcurrencyControl(LBCC)
如果僅僅是基于鎖來(lái)實(shí)現(xiàn)事務(wù)隔離,一個(gè)事務(wù)讀取的時(shí)候不允許其他時(shí)候修改,那就意味著不支持并發(fā)的讀寫(xiě)操作,而我們的大多數(shù)應(yīng)用都是讀多寫(xiě)少的,這樣會(huì)極大地影響操作數(shù)據(jù)的效率
MVCC
所以我們還有另一種解決方案,如果要讓一個(gè)事務(wù)前后兩次讀取的數(shù)據(jù)保持一致,那么我們可以在修改數(shù)據(jù)的時(shí)候給它建立一個(gè)備份或者叫快照,后面再來(lái)讀取這個(gè)快照就行了.這種方案我們叫做多版本的并發(fā)控制 MultiVersionConcurrencyControl(MVCC)
MVCC 的核心思想是:我可以查到在我這個(gè)事務(wù)開(kāi)始之前已經(jīng)存在的數(shù)據(jù),即使它在后面被修改或者刪除了.在我這個(gè)事務(wù)之后新增的數(shù)據(jù),我是查不到的
問(wèn)題:這個(gè)快照什么時(shí)候創(chuàng)建?讀取數(shù)據(jù)的時(shí)候,怎么保證能讀取到這個(gè)快照而不是最新的數(shù)據(jù)?這個(gè)怎么實(shí)現(xiàn)呢?
InnoDB 為每行記錄都實(shí)現(xiàn)了兩個(gè)隱藏字段:
- DB_TRX_ID,6 字節(jié):插入或更新行的最后一個(gè)事務(wù)的事務(wù) ID,事務(wù)編號(hào)是自動(dòng)遞增的(我們把它理解為創(chuàng)建版本號(hào),在數(shù)據(jù)新增或者修改為新數(shù)據(jù)的時(shí)候,記錄當(dāng)前事務(wù) ID)
- DB_ROLL_PTR,7 字節(jié):回滾指針(我們把它理解為刪除版本號(hào),數(shù)據(jù)被刪除或記錄為舊數(shù)據(jù)的時(shí)候,記錄當(dāng)前事務(wù) ID)
我們把這兩個(gè)事務(wù) ID 理解為版本號(hào)
https://www.processon.com/view/link/5d29999ee4b07917e2e09298MVCC 演示圖
第一個(gè)事務(wù),初始化數(shù)據(jù)(檢查初始數(shù)據(jù))
Transaction 1
begin;
insert into mvcctest values(NULL,'qingshan');
insert into mvcctest values(NULL,'jack');
commit;
此時(shí)的數(shù)據(jù),創(chuàng)建版本是當(dāng)前事務(wù) ID,刪除版本為空:
| id | name | 創(chuàng)建版本 | 刪除版本 |
|---|---|---|---|
| 1 | qingshan | 1 | undefined |
| 2 | jack | 1 | undefined |
第二個(gè)事務(wù),執(zhí)行第 1 次查詢,讀取到兩條原始數(shù)據(jù),這個(gè)時(shí)候事務(wù) ID 是 2:
Transaction 2
begin;
select * from mvcctest; -- (1)第一次查詢
第三個(gè)事務(wù),插入數(shù)據(jù):
Transaction 3
begin;
insert into mvcctest values(NULL,'tom');
commit;
此時(shí)的數(shù)據(jù),多了一條 tom,它的創(chuàng)建版本號(hào)是當(dāng)前事務(wù)編號(hào),3:
| id | name | 創(chuàng)建版本 | 刪除版本 |
|---|---|---|---|
| 1 | qingshan | 1 | undefined |
| 2 | jack | 1 | undefined |
| 3 | tom | 3 | undefined |
第二個(gè)事務(wù),執(zhí)行第2次查詢:
Transaction 2
select * from mvcctest; (2)第二次查詢
MVCC 的查找規(guī)則:只能查找創(chuàng)建時(shí)間小于等于當(dāng)前事務(wù) ID 的數(shù)據(jù),和刪除時(shí)間大于當(dāng)前事務(wù) ID 的行(或未刪除)
也就是不能查到在我的事務(wù)開(kāi)始之后插入的數(shù)據(jù),tom 的創(chuàng)建 ID 大于 2,所以還是只能查到兩條數(shù)據(jù)
第四個(gè)事務(wù),刪除數(shù)據(jù),刪除了 id=2jack 這條記錄:
Transaction 4
begin;
delete from mvcctest where id=2;
commit;
此時(shí)的數(shù)據(jù),jack 的刪除版本被記錄為當(dāng)前事務(wù) ID,4,其他數(shù)據(jù)不變:
| id | name | 創(chuàng)建版本 | 刪除版本 |
|---|---|---|---|
| 1 | qingshan | 1 | undefined |
| 2 | jack | 1 | 4 |
| 3 | tom | 3 | undefined |
在第二個(gè)事務(wù)中,執(zhí)行第3次查詢:
Transaction 2
select * from mvcctest; (3)第三次查詢
查找規(guī)則:只能查找創(chuàng)建時(shí)間小于等于當(dāng)前事務(wù) ID 的數(shù)據(jù),和刪除時(shí)間大于當(dāng)前事務(wù) ID 的行(或未刪除)
也就是,在我事務(wù)開(kāi)始之后刪除的數(shù)據(jù),所以 jack 依然可以查出來(lái).所以還是這兩條數(shù)據(jù)
第五個(gè)事務(wù),執(zhí)行更新操作,這個(gè)事務(wù)事務(wù) ID 是 5:
Transaction 4
begin;
update mvcctest set name ='盆魚(yú)宴' where id=1;
commit;
此時(shí)的數(shù)據(jù),更新數(shù)據(jù)的時(shí)候,舊數(shù)據(jù)的刪除版本被記錄為當(dāng)前事務(wù) ID5(undo),產(chǎn)生了一條新數(shù)據(jù),創(chuàng)建 ID 為當(dāng)前事務(wù) ID5:
| id | name | 創(chuàng)建版本 | 刪除版本 | |
|---|---|---|---|---|
| 1 | qingshan | 1 | 5 | |
| 2 | jack | 1 | 4 | |
| 3 | tom | 3 | u | ndefined |
| 1 | 盆魚(yú)宴 | 5 | undefined |
第二個(gè)事務(wù),執(zhí)行第4次查詢:
Transaction 2
select * from mvcctest ; (4)第四次查詢
查找規(guī)則:只能查找創(chuàng)建時(shí)間小于等于當(dāng)前事務(wù) ID 的數(shù)據(jù),和刪除時(shí)間大于當(dāng)前事務(wù) ID 的行(或未刪除)
因?yàn)楦潞蟮臄?shù)據(jù) penyuyan 創(chuàng)建版本大于 2,代表是在事務(wù)之后增加的,查不出來(lái)
而舊數(shù)據(jù) qingshan 的刪除版本大于 2,代表是在事務(wù)之后刪除的,可以查出來(lái)
通過(guò)以上演示我們能看到,通過(guò)版本號(hào)的控制,無(wú)論其他事務(wù)是插入,修改,刪除,第一個(gè)事務(wù)查詢到的數(shù)據(jù)都沒(méi)有變化
在 InnoDB 中,MVCC 是通過(guò) Undolog 實(shí)現(xiàn)的
Oracle,Postgres 等等其他數(shù)據(jù)庫(kù)都有 MVCC 的實(shí)現(xiàn)
需要注意,在 InnoDB 中,MVCC 和鎖是協(xié)同使用的,這兩種方案并不是互斥的
第一大類解決方案是鎖,鎖又是怎么實(shí)現(xiàn)讀一致性的呢?
MySQL InnoDB 鎖的基本類型
https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html
官網(wǎng)把鎖分成了 8 類.所以我們把前面的兩個(gè)行級(jí)別的鎖(SharedandExclusiveLocks),和兩個(gè)表級(jí)別的鎖(IntentionLocks)稱為鎖的基本模式
后面三個(gè) RecordLocks,GapLocks,Next-KeyLocks,我們把它們叫做鎖的算法,也就是分別在什么情況下鎖定什么范圍
鎖的粒度
我們講到 InnoDB 里面既有行級(jí)別的鎖,又有表級(jí)別的鎖,我們先來(lái)分析一下這兩種鎖定粒度的一些差異
表鎖,顧名思義,是鎖住一張表；行鎖就是鎖住表里面的一行數(shù)據(jù).鎖定粒度,表鎖肯定是大于行鎖的
那么加鎖效率,表鎖應(yīng)該是大于行鎖還是小于行鎖呢?
大于.為什么?表鎖只需要直接鎖住這張表就行了,而行鎖,還需要在表里面去檢索這一行數(shù)據(jù),所以表鎖的加鎖效率更高
第二個(gè)沖突的概率?表鎖的沖突概率比行鎖大,還是小?
大于,因?yàn)楫?dāng)我們鎖住一張表的時(shí)候,其他任何一個(gè)事務(wù)都不能操作這張表.但是我們鎖住了表里面的一行數(shù)據(jù)的時(shí)候,其他的事務(wù)還可以來(lái)操作表里面的其他沒(méi)有被鎖定的行,所以表鎖的沖突概率更大
表鎖的沖突概率更大,所以并發(fā)性能更低,這里并發(fā)性能就是小于
InnoDB 里面我們知道它既支持表鎖又支持行鎖,另一個(gè)常用的存儲(chǔ)引擎 MyISAM 支持什么粒度的鎖?這是第一個(gè)問(wèn)題.第二個(gè)就是 InnoDB 已經(jīng)支持行鎖了,那么它也可以通過(guò)把表里面的每一行都鎖住來(lái)實(shí)現(xiàn)表鎖,為什么還要提供表鎖呢?
要搞清楚這個(gè)問(wèn)題,我們就要來(lái)了解一下 InnoDB 里面的基本的鎖的模式(lockmode),這里面有兩個(gè)行鎖和兩個(gè)表鎖
共享鎖
第一個(gè)行級(jí)別的鎖就是我們?cè)诠倬W(wǎng)看到的 SharedLocks(共享鎖),我們獲取了一行數(shù)據(jù)的讀鎖以后,可以用來(lái)讀取數(shù)據(jù),所以它也叫做讀鎖,注意不要在加上了讀鎖以后去寫(xiě)數(shù)據(jù),不然的話可能會(huì)出現(xiàn)死鎖的情況.而且多個(gè)事務(wù)可以共享一把讀鎖.那怎么給一行數(shù)據(jù)加上讀鎖呢?
我們可以用 select……lockinsharemode;的方式手工加上一把讀鎖
釋放鎖有兩種方式,只要事務(wù)結(jié)束,鎖就會(huì)自動(dòng)事務(wù),包括提交事務(wù)和結(jié)束事務(wù)
我們也來(lái)驗(yàn)證一下,看看共享鎖是不是可以重復(fù)獲取
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| SELECT*FROMstudentWHEREid=1LOCKINSHAREMODE; | |
| begin; | |
| SELECT*FROMstudentWHEREid=1LOCKINSHAREMODE;//OK |
排它鎖
第二個(gè)行級(jí)別的鎖叫做 ExclusiveLocks(排它鎖),它是用來(lái)操作數(shù)據(jù)的,所以又叫做寫(xiě)鎖.只要一個(gè)事務(wù)獲取了一行數(shù)據(jù)的排它鎖,其他的事務(wù)就不能再獲取這一行數(shù)據(jù)的共享鎖和排它鎖
排它鎖的加鎖方式有兩種,第一種是自動(dòng)加排他鎖.我們?cè)诓僮鲾?shù)據(jù)的時(shí)候,包括增刪改,都會(huì)默認(rèn)加上一個(gè)排它鎖
還有一種是手工加鎖,我們用一個(gè) FORUPDATE 給一行數(shù)據(jù)加上一個(gè)排它鎖,這個(gè)無(wú)論是在我們的代碼里面還是操作數(shù)據(jù)的工具里面,都比較常用
釋放鎖的方式跟前面是一樣的
排他鎖的驗(yàn)證:
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| UPDATEstudentSETsname='貓老公 555'WHEREid=1; | |
| begin; | |
| SELECT*FROMstudentWHEREid=1LOCKINSHAREMODE;//BLOCKED | |
| SELECT*FROMstudentwhereid=1FORUPDATE;//BLOCKED | |
| DELETEFROMstudentwhereid=1;//BLOCKED |
這個(gè)是兩個(gè)行鎖,接下來(lái)就是兩個(gè)表鎖
意向鎖
意向鎖是什么呢?我們好像從來(lái)沒(méi)有聽(tīng)過(guò),也從來(lái)沒(méi)有使用過(guò),其實(shí)他們是由數(shù)據(jù)庫(kù)自己維護(hù)的
也就是說(shuō),當(dāng)我們給一行數(shù)據(jù)加上共享鎖之前,數(shù)據(jù)庫(kù)會(huì)自動(dòng)在這張表上面加一個(gè)意向共享鎖
當(dāng)我們給一行數(shù)據(jù)加上排他鎖之前,數(shù)據(jù)庫(kù)會(huì)自動(dòng)在這張表上面加一個(gè)意向排他鎖
反過(guò)來(lái)說(shuō):
如果一張表上面至少有一個(gè)意向共享鎖,說(shuō)明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了共享鎖
如果一張表上面至少有一個(gè)意向排他鎖,說(shuō)明有其他的事務(wù)給其中的某些數(shù)據(jù)行加上了排他鎖
select * from t2 where id =4 for update;
TABLE LOCK table `gupao`.`t2` trx id 24467 lock mode IX RECORD LOCKS space id 64 page no 3 n bits 72 index PRIMARY of table `gupao`.`t2` trx id 24467 lock_mode X locks rec but not gap
那么這兩個(gè)表級(jí)別的鎖存在的意義是什么呢?第一個(gè),我們有了表級(jí)別的鎖,在 InnoDB 里面就可以支持更多粒度的鎖.它的第二個(gè)作用,我們想一下,如果說(shuō)沒(méi)有意向鎖的話,當(dāng)我們準(zhǔn)備給一張表加上表鎖的時(shí)候,我們首先要做什么?是不是必須先要去判斷有沒(méi)其他的事務(wù)鎖定了其中了某些行?如果有的話,肯定不能加上表鎖.那么這個(gè)時(shí)候我們就要去掃描整張表才能確定能不能成功加上一個(gè)表鎖,如果數(shù)據(jù)量特別大,比如有上千萬(wàn)的數(shù)據(jù)的時(shí)候,加表鎖的效率是不是很低?
但是我們引入了意向鎖之后就不一樣了.我只要判斷這張表上面有沒(méi)有意向鎖,如果有,就直接返回失敗.如果沒(méi)有,就可以加鎖成功.所以 InnoDB 里面的表鎖,我們可以把它理解成一個(gè)標(biāo)志.就像火車上廁所有沒(méi)有人使用的燈,是用來(lái)提高加鎖的效率的
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| SELECT*FROMstudentwhereid=1FORUPDATE; | |
| begin; | |
| LOCKTABLESstudentWRITE;//BLOCKED | |
| UNLOCKTABLES;//釋放表鎖的方式 |
以上就是 MySQL 里面的 4 種基本的鎖的模式,或者叫做鎖的類型
到這里我們要思考兩個(gè)問(wèn)題,首先,鎖的作用是什么?它跟 Java 里面的鎖是一樣的,是為了解決資源競(jìng)爭(zhēng)的問(wèn)題,Java 里面的資源是對(duì)象,數(shù)據(jù)庫(kù)的資源就是數(shù)據(jù)表或者數(shù)據(jù)行
所以鎖是用來(lái)解決事務(wù)對(duì)數(shù)據(jù)的并發(fā)訪問(wèn)的問(wèn)題的
那么,鎖到底鎖住了什么呢?
當(dāng)一個(gè)事務(wù)鎖住了一行數(shù)據(jù)的時(shí)候,其他的事務(wù)不能操作這一行數(shù)據(jù),那它到底是鎖住了這一行數(shù)據(jù),還是鎖住了這一個(gè)字段,還是鎖住了別的什么東西呢?
行鎖的原理
沒(méi)有索引的表(假設(shè)鎖住記錄)
首先我們有三張表,一張沒(méi)有索引的 t1,一張有主鍵索引的 t2,一張有唯一索引的 t3
我們先假設(shè) InnoDB 的鎖鎖住了是一行數(shù)據(jù)或者一條記錄
我們先來(lái)看一下 t1 的表結(jié)構(gòu),它有兩個(gè)字段,int 類型的 id 和 varchar 類型的 name.里面有 4 條數(shù)據(jù),1,2,3,4
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| SELECT*FROMt1WHEREid=1FORUPDATE; | |
| begin; | |
| select*fromt1whereid=3forupdate;//blocked | |
INSERTINTOt1(id,name)VALUES(5,'5');//blocked |
現(xiàn)在我們?cè)趦蓚€(gè)會(huì)話里面手工開(kāi)啟兩個(gè)事務(wù)
在第一個(gè)事務(wù)里面,我們通過(guò) whereid=1 鎖住第一行數(shù)據(jù)
在第二個(gè)事務(wù)里面,我們嘗試給 id=3 的這一行數(shù)據(jù)加鎖,大家覺(jué)得能成功嗎?
很遺憾,我們看到紅燈亮起,這個(gè)加鎖的操作被阻塞了.這就有點(diǎn)奇怪了,第一個(gè)事務(wù)鎖住了 id=1 的這行數(shù)據(jù),為什么我不能操作 id=3 的數(shù)據(jù)呢?
我們?cè)賮?lái)操作一條不存在的數(shù)據(jù),插入 id=5.它也被阻塞了.實(shí)際上這里整張表都被鎖住了.所以,我們的第一個(gè)猜想被推翻了,InnoDB 的鎖鎖住的應(yīng)該不是 Record
那為什么在沒(méi)有索引或者沒(méi)有用到索引的情況下,會(huì)鎖住整張表?這個(gè)問(wèn)題我們先留在這里
我們繼續(xù)看第二個(gè)演示
有主鍵索引的表
我們看一下 t2 的表結(jié)構(gòu).字段是一樣的,不同的地方是 id 上創(chuàng)建了一個(gè)主鍵索引.里面的數(shù)據(jù)是 1,4,7,10
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| select*fromt2whereid=1forupdate; | |
| begin; | |
| select*fromt2whereid=1forupdate;//blocked | |
| select*fromt2whereid=4forupdate;//OK |
第一種情況,使用相同的 id 值去加鎖,沖突江醇；使用不同的 id 加鎖,可以加鎖成功.那么,既然不是鎖定一行數(shù)據(jù),有沒(méi)有可能是鎖住了 id 的這個(gè)字段呢?
唯一索引(假設(shè)鎖住字段)
我們看一下 t3 的表結(jié)構(gòu).字段還是一樣的,id 上創(chuàng)建了一個(gè)主鍵索引,name 上創(chuàng)建了一個(gè)唯一索引.里面的數(shù)據(jù)是 1,4,7,10
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| select*fromt3wherename='4'forupdate; | |
| begin; | |
| select*fromt3wherename='4'forupdate;//blocked | |
| select*fromt3whereid=4forupdate;//blocked |
在第一個(gè)事務(wù)里面,我們通過(guò) name 字段去鎖定值是 4 的這行數(shù)據(jù)
在第二個(gè)事務(wù)里面,嘗試獲取一樣的排它鎖,肯定是失敗的,這個(gè)不用懷疑
在這里我們懷疑 InnoDB 鎖住的是字段,所以這次我換一個(gè)字段,用 id=4 去給這行數(shù)據(jù)加鎖,大家覺(jué)得能成功嗎?
很遺憾,又被阻塞了,說(shuō)明鎖住的是字段的這個(gè)推測(cè)也是錯(cuò)的,否則就不會(huì)出現(xiàn)第一個(gè)事務(wù)鎖住了 name,第二個(gè)字段鎖住 id 失敗的情況
既然鎖住的不是 record,也不是 column,InnoDB 里面鎖住的到底是什么呢?在這三個(gè)案例里面,我們要去分析一下他們的差異在哪里,也就是這三張表的結(jié)構(gòu),是什么區(qū)別導(dǎo)致了加鎖的行為的差異?其實(shí)答案就是索引.InnoDB 的行鎖,就是通過(guò)鎖住索引來(lái)實(shí)現(xiàn)的
那索引又是個(gè)什么東西?為什么它可以被鎖住?我們?cè)诘诙?jié)課里面已經(jīng)分析過(guò)了
那么我們還有兩個(gè)問(wèn)題沒(méi)有解決:
- 為什么表里面沒(méi)有索引的時(shí)候,鎖住一行數(shù)據(jù)會(huì)導(dǎo)致鎖表?或者說(shuō),如果鎖住的是索引,一張表沒(méi)有索引怎么辦?所以,一張表有沒(méi)有可能沒(méi)有索引
*如果我們定義了主鍵(PRIMARYKEY),那么 InnoDB 會(huì)選擇主鍵作為聚集索引
*如果沒(méi)有顯式定義主鍵,則 InnoDB 會(huì)選擇第一個(gè)不包含有 NULL 值的唯一索引作為主鍵索引
*如果也沒(méi)有這樣的唯一索引,則 InnoDB 會(huì)選擇內(nèi)置 6 字節(jié)長(zhǎng)的 ROWID 作為隱藏的聚集索引,它會(huì)隨著行記錄的寫(xiě)入而主鍵遞增
所以,為什么鎖表,是因?yàn)椴樵儧](méi)有使用索引,會(huì)進(jìn)行全表掃描,然后把每一個(gè)隱藏的聚集索引都鎖住了
- 為什么通過(guò)唯一索引給數(shù)據(jù)行加鎖,主鍵索引也會(huì)被鎖住?
大家還記得在 InnoDB 里面,當(dāng)我們使用輔助索引的時(shí)候,它是怎么檢索數(shù)據(jù)的嗎?輔助索引的葉子節(jié)點(diǎn)存儲(chǔ)的是什么內(nèi)容?
在輔助索引里面,索引存儲(chǔ)的是二級(jí)索引和主鍵的值.比如 name=4,存儲(chǔ)的是 name 的索引和主鍵 id 的值 4
而主鍵索引里面除了索引之外,還存儲(chǔ)了完整的數(shù)據(jù).所以我們通過(guò)輔助索引鎖定一行數(shù)據(jù)的時(shí)候,它跟我們檢索數(shù)據(jù)的步驟是一樣的,會(huì)通過(guò)主鍵值找到主鍵索引,然后也鎖定
現(xiàn)在我們已經(jīng)搞清楚 4 個(gè)鎖的基本類型和鎖的原理了,在官網(wǎng)上,還有 3 種鎖,我們把它理解為鎖的算法.我們也來(lái)看下 InnoDB 在什么時(shí)候分別鎖住什么范圍
鎖的算法
我們先來(lái)看一下我們測(cè)試用的表,t2,這張表有一個(gè)主鍵索引
我們插入了4行數(shù)據(jù),主鍵值分別是1,4,7,10
為了讓大家真正理解這三種行鎖算法的區(qū)別,我們需要了解一下三種范圍的概念
因?yàn)槲覀冇弥麈I索引加鎖,我們這里的劃分標(biāo)準(zhǔn)就是主鍵索引的值
這些數(shù)據(jù)庫(kù)里面存在的主鍵值,我們把它叫做 Record,記錄,那么這里我們就有 4 個(gè) Record
根據(jù)主鍵,這些存在的 Record 隔開(kāi)的數(shù)據(jù)不存在的區(qū)間,我們把它叫做 Gap,間隙,它是一個(gè)左開(kāi)右開(kāi)的區(qū)間
最后一個(gè),間隙(Gap)連同它左邊的記錄(Record),我們把它叫做臨鍵的區(qū)間,它是一個(gè)左開(kāi)右閉的區(qū)間
t2 的主鍵索引,它是整型的,可以排序,所以才有這種區(qū)間.如果我的主鍵索引不是整形,是字符怎么辦呢?字符可以排序嗎?用 ASCII 碼來(lái)排序
我們已經(jīng)弄清楚了三個(gè)范圍的概念,下面我們就來(lái)看一下在不同的范圍下,行鎖是怎么表現(xiàn)的
記錄鎖
第一種情況,當(dāng)我們對(duì)于唯一性的索引(包括唯一索引和主鍵索引)使用等值查詢,精準(zhǔn)匹配到一條記錄的時(shí)候,這個(gè)時(shí)候使用的就是記錄鎖
比如 whereid=14710
這個(gè)演示我們?cè)谇懊嬉呀?jīng)看過(guò)了.我們使用不同的 key 去加鎖,不會(huì)沖突,它只鎖住這個(gè) record
間隙鎖
第二種情況,當(dāng)我們查詢的記錄不存在,沒(méi)有命中任何一個(gè) record,無(wú)論是用等值查詢還是范圍查詢的時(shí)候,它使用的都是間隙鎖
舉個(gè)例子,whereid>4andid<7,whereid=6
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
INSERTINTOt2(id,name)VALUES(5,'5');//BLOCKED |
|
INSERTINTOt2(id,name)VALUES(6,'6');//BLOCKED |
|
| select*fromt2whereid=6forupdate;//OK | |
| select*fromt2whereid>20forupdate; | |
INSERTINTOt2(id,name)VALUES(11,'11');//BLOCKED |
重復(fù)一遍,當(dāng)查詢的記錄不存在的時(shí)候,使用間隙鎖
注意,間隙鎖主要是阻塞插入 insert.相同的間隙鎖之間不沖突
GapLock 只在 RR 中存在.如果要關(guān)閉間隙鎖,就是把事務(wù)隔離級(jí)別設(shè)置成 RC,并且把 innodb_locks_unsafe_for_binlog 設(shè)置為 ON
這種情況下除了外鍵約束和唯一性檢查會(huì)加間隙鎖,其他情況都不會(huì)用間隙鎖
臨鍵鎖
第三種情況,當(dāng)我們使用了范圍查詢,不僅僅命中了 Record 記錄,還包含了 Gap 間隙,在這種情況下我們使用的就是臨鍵鎖,它是 MySQL 里面默認(rèn)的行鎖算法,相當(dāng)于記錄鎖加上間隙鎖
其他兩種退化的情況:
唯一性索引,等值查詢匹配到一條記錄的時(shí)候,退化成記錄鎖
沒(méi)有匹配到任何記錄的時(shí)候,退化成間隙鎖
比如我們使用>5<9,它包含了記錄不存在的區(qū)間,也包含了一個(gè) Record7
| Transaction1 | Transaction2 |
|---|---|
| begin; | |
| select*fromt2whereid>5andid<9forupdate; | |
| begin; | |
| select*fromt2whereid=4forupdate;//OK | |
INSERTINTOt2(id,name)VALUES(6,'6');//BLOCKED |
|
INSERTINTOt2(id,name)VALUES(8,'8');//BLOCKED |
|
| select*fromt2whereid=10forupdate;//BLOCKED |
臨鍵鎖,鎖住最后一個(gè) key 的下一個(gè)左開(kāi)右閉的區(qū)間
select * from t2 where id >5 and id <=7 for update; -- 鎖住(4,7]和(7,10]
select * from t2 where id >8 and id <=10 for update; -- 鎖住 (7,10]绊袋，(10,+∞)
為什么要鎖住下一個(gè)左開(kāi)右閉的區(qū)間?——就是為了解決幻讀的問(wèn)題
小結(jié):隔離級(jí)別的實(shí)現(xiàn)
所以,我們?cè)倩剡^(guò)頭來(lái)看下這張圖片,為什么 InnoDB 的 RR 級(jí)別能夠解決幻讀的問(wèn)題,就是用臨鍵鎖實(shí)現(xiàn)的.我們?cè)倩剡^(guò)頭來(lái)看下這張圖片,這個(gè)就是 MySQLInnoDB 里面事務(wù)隔離級(jí)別的實(shí)現(xiàn)
最后我們來(lái)總結(jié)一下四個(gè)事務(wù)隔離級(jí)別的實(shí)現(xiàn):
Read Uncommited
RU 隔離級(jí)別:不加鎖
Serializable
Serializable 所有的 select 語(yǔ)句都會(huì)被隱式的轉(zhuǎn)化為 select...insharemode,會(huì)和 update,delete 互斥.這兩個(gè)很好理解,主要是 RR 和 RC 的區(qū)別?
Repeatable Read
RR 隔離級(jí)別下,普通的 select 使用快照讀(snapshotread),底層使用 MVCC 來(lái)實(shí)現(xiàn)
加鎖的 select(select...insharemode/select...forupdate)以及更新操作 update,delete 等語(yǔ)句使用當(dāng)前讀(currentread),底層使用記錄鎖,或者間隙鎖,臨鍵鎖
Read Commited
RC 隔離級(jí)別下,普通的 select 都是快照讀,使用 MVCC 實(shí)現(xiàn)
加鎖的 select 都使用記錄鎖,因?yàn)闆](méi)有 GapLock
除了兩種特殊情況——外鍵約束檢查(foreign-keyconstraintchecking)以及重復(fù)鍵檢查(duplicate-keychecking)時(shí)會(huì)使用間隙鎖封鎖區(qū)間
所以 RC 會(huì)出現(xiàn)幻讀的問(wèn)題
事務(wù)隔離級(jí)別怎么選?
https://dev.mysql.com/doc/refman/5.7/en/innodb-transaction-isolation-levels.html
RU 和 Serializable 肯定不能用.為什么有些公司要用 RC,或者說(shuō)網(wǎng)上有些文章推薦有 RC?
RC 和 RR 主要有幾個(gè)區(qū)別:
- RR 的間隙鎖會(huì)導(dǎo)致鎖定范圍的擴(kuò)大
- 條件列未使用到索引,RR 鎖表,RC 鎖行
- RC 的“半一致性”(semi-consistent)讀可以增加 update 操作的并發(fā)性
在 RC 中,一個(gè) update 語(yǔ)句,如果讀到一行已經(jīng)加鎖的記錄,此時(shí) InnoDB 返回記錄最近提交的版本,由 MySQL 上層判斷此版本是否滿足 update 的 where 條件.若滿足(需要更新),則 MySQL 會(huì)重新發(fā)起一次讀操作,此時(shí)會(huì)讀取行的最新版本(并加鎖)
實(shí)際上,如果能夠正確地使用鎖(避免不使用索引去枷鎖),只鎖定需要的數(shù)據(jù),用默認(rèn)的 RR 級(jí)別就可以了
在我們使用鎖的時(shí)候,有一個(gè)問(wèn)題是需要注意和避免的,我們知道,排它鎖有互斥的特性.一個(gè)事務(wù)或者說(shuō)一個(gè)線程持有鎖的時(shí)候,會(huì)阻止其他的線程獲取鎖,這個(gè)時(shí)候會(huì)造成阻塞等待,如果循環(huán)等待,會(huì)有可能造成死鎖
這個(gè)問(wèn)題我們需要從幾個(gè)方面來(lái)分析,一個(gè)是鎖為什么不釋放,第二個(gè)是被阻塞了怎么辦,第三個(gè)死鎖是怎么發(fā)生的,怎么避免
死鎖
鎖的釋放與阻塞
回顧:鎖什么時(shí)候釋放?
事務(wù)結(jié)束(commit,rollback)谴咸；客戶端連接斷開(kāi)
如果一個(gè)事務(wù)一直未釋放鎖,其他事務(wù)會(huì)被阻塞多久?會(huì)不會(huì)永遠(yuǎn)等待下去?如果是,在并發(fā)訪問(wèn)比較高的情況下,如果大量事務(wù)因無(wú)法立即獲得所需的鎖而掛起,會(huì)占用大量計(jì)算機(jī)資源,造成嚴(yán)重性能問(wèn)題,甚至拖跨數(shù)據(jù)庫(kù)
[Err] 1205 - Lock wait timeout exceeded; try restarting transaction MySQL
有一個(gè)參數(shù)來(lái)控制獲取鎖的等待時(shí)間,默認(rèn)是50秒
show VARIABLES like 'innodb_lock_wait_timeout';
對(duì)于死鎖,是無(wú)論等多久都不能獲取到鎖的,這種情況,也需要等待50秒鐘嗎?那不是白白浪費(fèi)了50秒鐘的時(shí)間嗎?
我們先來(lái)看一下什么時(shí)候會(huì)發(fā)生死鎖
死鎖的發(fā)生和檢測(cè)
死鎖演示:
| Session1 | Session2 |
|---|---|
| begin; | |
| select*fromt2whereid=1forupdate; | |
| begin; | |
| deletefromt2whereid=4; | |
| updatet2setname='4d'whereid=4; | |
| deletefromt2whereid=1; |
在第一個(gè)事務(wù)中,檢測(cè)到了死鎖,馬上退出了,第二個(gè)事務(wù)獲得了鎖,不需要等待50秒:
[Err] 1213 - Deadlock found when trying to get lock; try restarting transaction
為什么可以直接檢測(cè)到呢?是因?yàn)樗梨i的發(fā)生需要滿足一定的條件,所以在發(fā)生死鎖時(shí),InnoDB 一般都能通過(guò)算法(wait-forgraph)自動(dòng)檢測(cè)到
那么死鎖需要滿足什么條件?死鎖的產(chǎn)生條件:因?yàn)殒i本身是互斥的
- 同一時(shí)刻只能有一個(gè)事務(wù)持有這把鎖
- 其他的事務(wù)需要在這個(gè)事務(wù)釋放鎖之后才能獲取鎖,而不可以強(qiáng)行剝奪
- 當(dāng)多個(gè)事務(wù)形成等待環(huán)路的時(shí)候,即發(fā)生死鎖
舉例:
理發(fā)店有兩個(gè)總監(jiān).一個(gè)負(fù)責(zé)剪頭的 Tony 總監(jiān),一個(gè)負(fù)責(zé)洗頭的 Kelvin 總監(jiān)
Tony 不能同時(shí)給兩個(gè)人剪頭,這個(gè)就叫互斥
Tony 在給別人在剪頭的時(shí)候,你不能讓他停下來(lái)幫你剪頭,這個(gè)叫不能強(qiáng)行剝奪
如果 Tony 的客戶對(duì) Kelvin 總監(jiān)說(shuō):你不幫我洗頭我怎么剪頭?Kelvin 的客戶對(duì) Tony 總監(jiān)說(shuō):你不幫我剪頭我怎么洗頭?這個(gè)就叫形成等待環(huán)路
如果鎖一直沒(méi)有釋放,就有可能造成大量阻塞或者發(fā)生死鎖,造成系統(tǒng)吞吐量下降,這時(shí)候就要查看是哪些事務(wù)持有了鎖
查看鎖信息(日志)
SHOWSTATUS 命令中,包括了一些行鎖的信息:
show status like 'innodb_row_lock_%';
- Innodb_row_lock_current_waits:當(dāng)前正在等待鎖定的數(shù)量
- Innodb_row_lock_time:從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定的總時(shí)間長(zhǎng)度,單位 ms
- 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ù)
SHOW 命令是一個(gè)概要信息.InnoDB 還提供了三張表來(lái)分析事務(wù)與鎖的情況:
select * from information_schema.INNODB_TRX; -- 當(dāng)前運(yùn)行的所有事務(wù) ，還有具體的語(yǔ)句
select * from information_schema.INNODB_LOCKS; -- 當(dāng)前出現(xiàn)的鎖
select * from information_schema.INNODB_LOCK_WAITS; -- 鎖等待的對(duì)應(yīng)關(guān)系
找出持有鎖的事務(wù)之后呢?如果一個(gè)事務(wù)長(zhǎng)時(shí)間持有鎖不釋放,可以 kill 事務(wù)對(duì)應(yīng)的線程 ID,也就是 INNODB_TRX 表中的 trx_mysql_thread_id,例如執(zhí)行 kill4,kill7,kill8
當(dāng)然,死鎖的問(wèn)題不能每次都靠 kill 線程來(lái)解決,這是治標(biāo)不治本的行為.我們應(yīng)該盡量在應(yīng)用端,也就是在編碼的過(guò)程中避免有哪些可以避免死鎖的方法呢
死鎖的避免
- 在程序中,操作多張表時(shí),盡量以相同的順序來(lái)訪問(wèn)(避免形成等待環(huán)路)
- 批量操作單張表數(shù)據(jù)的時(shí)候,先對(duì)數(shù)據(jù)進(jìn)行排序(避免形成等待環(huán)路)
- 申請(qǐng)足夠級(jí)別的鎖,如果要操作數(shù)據(jù),就申請(qǐng)排它鎖
- 盡量使用索引訪問(wèn)數(shù)據(jù),避免沒(méi)有 where 條件的操作,避免鎖表
- 如果可以,大事務(wù)化成小事務(wù)剥哑；
- 使用等值查詢而不是范圍查詢查詢數(shù)據(jù),命中記錄,避免間隙鎖對(duì)并發(fā)的影響
