mysql并發(fā)處理機(jī)制(一)

1 什么是MVCC

MVCC全稱是： Multiversion concurrency control,多版本并發(fā)控制,提供并發(fā)拜訪數(shù)據(jù)庫時,對事務(wù)內(nèi)讀取的到的內(nèi)存做處理,用來避免寫操作堵塞讀操作的并發(fā)問題.

舉個例子,程序員A正在讀數(shù)據(jù)庫中某些內(nèi)容,而程序員B正在給這些內(nèi)容做修改(假設(shè)是在一個事務(wù)內(nèi)修改,大概持續(xù)10s左右),A在這10s內(nèi) 則可能看到一個紛歧致的數(shù)據(jù),在B沒有提交前,如何讓A能夠一直讀到的數(shù)據(jù)都是一致的呢?

有幾種處理辦法,第一種： 基于鎖的并發(fā)控制,程序員B開始修改數(shù)據(jù)時,給這些數(shù)據(jù)加上鎖,程序員A這時再讀,就發(fā)現(xiàn)讀取不了,處于等待情況,只能等B操作完才能讀數(shù)據(jù),這保證A不會讀到一個不一致的數(shù)據(jù),但是這個會影響程序的運(yùn)行效率.還有一種就是：MVCC,每個用戶連接數(shù)據(jù)庫時,看到的都是某一特定時刻的數(shù)據(jù)庫快照,在B的事務(wù)沒有提交之前,A始終讀到的是某一特定時刻的數(shù)據(jù)庫快照,不會讀到B事務(wù)中的數(shù)據(jù)修改情況,直到B事務(wù)提交,才會讀取B的修改內(nèi)容.

一個支持MVCC的數(shù)據(jù)庫,在更新某些數(shù)據(jù)時,并非使用新數(shù)據(jù)覆蓋舊數(shù)據(jù),而是標(biāo)志舊數(shù)據(jù)是過時的,同時在其他地方新增一個數(shù)據(jù)版本.因此,同一份數(shù)據(jù)有多個版本存儲,但只有一個是最新的.

MVCC提供了 時間一致性的 處理思路,在MVCC下讀事務(wù)時,通常使用一個時間戳或者事務(wù)ID來確定拜訪哪個狀態(tài)的數(shù)據(jù)庫及哪些版本的數(shù)據(jù).讀事務(wù)跟寫事務(wù)彼此是隔離開來的,彼此之間不會影響.假設(shè)同一份數(shù)據(jù),既有讀事務(wù)拜訪,又有寫事務(wù)操作,實(shí)際上,寫事務(wù)會新建一個新的數(shù)據(jù)版本,而讀事務(wù)拜訪的是舊的數(shù)據(jù)版本,直到寫事務(wù)提交,讀事務(wù)才會拜訪到這個新的數(shù)據(jù)版本.

MVCC有兩種實(shí)現(xiàn)方式,第一種實(shí)現(xiàn)方式是將數(shù)據(jù)記錄的多個版本保留在數(shù)據(jù)庫中,當(dāng)這些不同版本數(shù)據(jù)不再需要時,垃圾收集器回收這些記錄.這個方式被PostgreSQL和Firebird/Interbase采用,SQL Server使用的類似機(jī)制,所不同的是舊版本數(shù)據(jù)不是保留在數(shù)據(jù)庫中,而保留在不同于主數(shù)據(jù)庫的另外一個數(shù)據(jù)庫tempdb中.第二種實(shí)現(xiàn)方式只在數(shù)據(jù)庫保留最新版本的數(shù)據(jù),但是會在使用undo時動態(tài)重構(gòu)舊版本數(shù)據(jù),這種方式被Oracle和MySQL/InnoDB使用.

這部門可以查閱維基百科：https://en.wikipedia.org/wiki/Multiversion_concurrency_control

2 Innodb的MVCC

在Innodb db中,無論是聚簇索引,還是二級索引,每一行記錄都包含一個 DELETE bit,用于表示該記錄是否被刪除, 同時,聚簇索引還有兩個暗藏值：DATA_TRX_ID,DATA_ROLL_PTR.DATA _TRX_ID表示產(chǎn)生當(dāng)前記錄項(xiàng)的事務(wù)ID,這個ID隨著事務(wù)的創(chuàng)建不斷增長；DATA _ROLL_PTR指向當(dāng)前記錄項(xiàng)的undo信息.

1. 無論是聚簇索引,還是二級索引,只要其鍵值更新,就會發(fā)生新版本.將老版本數(shù)據(jù)deleted bti設(shè)置為1；同時插入新版本.

2. 對于聚簇索引,如果更新操作沒有更新primary key,那么更新不會發(fā)生新版本,而是在原有版本上進(jìn)行更新,老版本進(jìn)入undo表空間,通過記錄上的undo指針進(jìn)行回滾.

3. 對于二級索引,如果更新操作沒有更新其鍵值,那么二級索引記錄堅持不變.

4. 對于二級索引,更新操作無論更新primary key,或者是二級索引鍵值,都會導(dǎo)致二級索引發(fā)生新版本數(shù)據(jù).

5. 聚簇索引設(shè)置記載deleted bit時,會同時更新DATA_TRX_ID列.老版本DATA_TRX_ID進(jìn)入undo表空間；二級索引設(shè)置deleted bit時,不寫入undo.

MVCC只工作在REPEATABLE READ和READ COMMITED隔離級別下.READ UNCOMMITED不是MVCC兼容的,因?yàn)椴樵儾豢瞬患罢业竭m合他們事務(wù)版本的行版本；它們每次都只能讀到最新的版本.SERIABLABLE也不與MVCC兼容,因?yàn)樽x操作會鎖定他們返回的每一行數(shù)據(jù) .

在MVCC中,讀操作分為兩類：當(dāng)前讀跟快照讀,當(dāng)前讀返回最新記錄,會加鎖,保證該記錄不會被其他事務(wù)改動；快照讀,讀取的是記錄的某個版本(有可能是最新版本也有可能是舊版本),不加鎖.

快照讀：RU,RC,RR隔離級別下,select * from tbname where ....

以后讀：

1. select * from tbname where .... for update (加X鎖)

2. select * from tbname where .... lock in share mode(加S鎖)

3. insert into tbname .... (加X鎖,注意如果有unique key的環(huán)境)

4. delete from tbname ... (加X鎖)

5. update tbname set ... where .. (加X鎖)

本部門參考：http://hedengcheng.com/?p=148

3 Two Phase Locking

2-PL,也便是兩階段鎖,鎖的操作分為兩個階段：加鎖、解鎖.先加鎖,后解鎖,不相交.加鎖時,讀操作會申請并占用S鎖,寫操作會申請并占用X鎖,如果對所在記錄加鎖有沖突,那么會處于等待狀態(tài),知道加鎖成功才驚醒下一步操作.解鎖時,也便是事務(wù)提交或者回滾的時候,這個階段會釋放該事務(wù)中所有的加鎖情況,進(jìn)行一一釋放鎖.

假設(shè)事務(wù)對記錄A和記錄B都有操作,那么,其加鎖解鎖依照逐行加鎖解鎖順序,如下：

兩階段鎖還有幾種特殊情況：conservative(保守)、strict(嚴(yán)格)、strong strict(強(qiáng)嚴(yán)格),這三種類型在加鎖和釋放鎖的處理有些紛歧樣.

1. conservative

• 在事務(wù)開始的時候,獲取必要的記錄的鎖,避免在操作期間逐個申請鎖可能造成的鎖等待,conservative 2PL 可以避免死鎖

1. strict

• 僅在事務(wù)停止的時候(commit or rollback),才釋放所有 write lock,read lock 則正常釋放

1. strong strict

• 僅在事務(wù)結(jié)束的時候(commit or rollback),才釋放所有鎖,包含write lock 跟 read lock 都是結(jié)束后才釋放.

這部門可以查看維基百科：https://en.wikipedia.org/wiki/Two-phase_locking,

4 數(shù)據(jù)紛歧致情況

4.1 臟讀

讀取未提交事務(wù)中改動的數(shù)據(jù),稱為臟讀.

舉例,表格 A (name,age),記載1為name='xinysu',age=188

這里,事務(wù)2 中讀出來的數(shù)據(jù)是 (name,age)=('xinysu',299),這一條是 事務(wù)1中未提交的記載,屬于臟數(shù)據(jù).

4.2 喪失更新

多個更新操作并發(fā)執(zhí)行,導(dǎo)致某些更新操作數(shù)據(jù)喪失.

舉例,表格 A (name,age),記載1為name='xinysu',age=188.并發(fā)2個更新操作如下：

正常情況下,如果是事務(wù)1操作后,age為288,事務(wù)2再進(jìn)行288+100=388,然則實(shí)際上,事務(wù)2的操作覆蓋事務(wù)1的操作,造成了事務(wù)1的更新丟失.

4.3 弗成重復(fù)讀

同個事務(wù)多次讀取同一條存在的記錄,但是讀取的結(jié)構(gòu)不一致,稱之為弗成重復(fù)讀.

舉例,表格 A (name,age),記載1為name='xinysu',age=188.操作如下：

事務(wù)1第一次讀出來的結(jié)構(gòu)是name='xinysu',age=188,第二次讀出來的結(jié)果是name='xinysu',age=288,同個事務(wù)中,多次讀取同一行存在的記錄,但結(jié)果不一致的情況,則為弗成重復(fù)讀.

4.4 幻讀

同個事務(wù)多次讀取某段段范圍內(nèi)的數(shù)據(jù),但是讀取到底行數(shù)紛歧致的情況,稱之為幻讀.

舉例,表格 A (name,age),記載1為name='xinysu',age=188.操作如下：

事務(wù)1中,第一次讀取的結(jié)果行數(shù)有1行,如果事務(wù)2執(zhí)行的是delete,則事務(wù)1第二次讀取的為0行；如果事務(wù)2執(zhí)行的是INSERT,則事務(wù)2第二次讀取的行數(shù)是2行,前跋文錄數(shù)不一致,稱之為幻讀.

5 innodb的隔離級別

5.1 隔離級別先容

1. Read Unco妹妹ited

• 簡稱 RU,讀未提交記載,始終是讀最新記載

• 不支撐快照讀,都是當(dāng)前讀

• 可能存在臟讀、弗成重復(fù)讀、幻讀等問題；

2. Read Co妹妹ited

• 不存在臟讀、弗成重復(fù)讀

• 存在幻讀問題；

• 簡稱 RC ,讀已提交記載

• 支撐快照讀,讀取版本有可能不是最新版本

• 支持當(dāng)前讀,讀取到的記載添加鎖

3. Read Repeatable

• 簡稱 RR ,可反復(fù)讀記錄

• 支撐快照讀,讀取版本有可能不是最新版本

• 支持當(dāng)前讀,讀取到的記錄添加鎖,而且對讀取的范圍枷鎖,保證滿足查詢條件的記錄不能夠被insert進(jìn)來

• 不存在臟讀、弗成重復(fù)讀及幻讀情況；

4. Read Serializable

• 簡稱 RS,序列化讀記載

• 不支撐快照讀,都是當(dāng)前讀

• select數(shù)據(jù)添加S鎖,update\insert\delete數(shù)據(jù)添加X鎖

• 并發(fā)度最差,除非明確業(yè)務(wù)需求及性能影響,才使用,一般不建議在innodb中利用

5.2 隔離級別測試

測試各個隔離級別下的數(shù)據(jù)紛歧致情況.

5.2.1 Read Unco妹妹itted

所有事務(wù)隔離級別設(shè)置： set session transaction isolation level read Unco妹妹ited ;

該隔離級別沒有的快照讀,所有讀操作都是讀最新版本,可以讀未提交事務(wù)的數(shù)據(jù).

測試1：update數(shù)據(jù)不提交,另起查詢

測試成果：正常select可以查詢到不提交的事務(wù)內(nèi)容,屬于臟讀

測試2：修改數(shù)據(jù)不提交,另發(fā)難務(wù)多次查詢

測試結(jié)果：同個事務(wù)多次讀取同一行記錄結(jié)果紛歧致,屬于重復(fù)讀

測試3：INSERT數(shù)據(jù)不提交,另發(fā)難務(wù)多次查詢

測試結(jié)果：同個事務(wù)多次讀取相同范圍的數(shù)據(jù),但是行數(shù)紛歧樣,屬于幻讀

測試4：分歧事務(wù)對同一行數(shù)據(jù)進(jìn)行update

測試結(jié)果：由于INNODB有鎖機(jī)制,所有所有update都會持有X鎖互斥,并不會出現(xiàn)事務(wù)都提交勝利情況下的丟失更新,所以四個隔離級別都可以避免丟失更新問題.

總結(jié)：沒有快照讀,都是當(dāng)前讀,所有讀都是讀可以讀未提交記錄,存在臟讀、弗成重復(fù)讀、幻讀等問題.

5.2.2 Read Co妹妹itted

所有事務(wù)隔離級別設(shè)置： set session transaction isolation level read co妹妹itted ;

由于該隔離級別支持快照讀,不添加for update跟lock in share mode的select 查詢語句,使用的是快照讀,讀取已提交記載,不添加鎖.所以測試使用當(dāng)前讀的模式測試,添加lock in share mode,添加S鎖.

測試1：update數(shù)據(jù)不提交,另起查詢

測試結(jié)果：由于當(dāng)前讀持有S鎖,導(dǎo)致update申請X鎖處于等待情況,無法更新,同個事務(wù)內(nèi)的多次查詢結(jié)果一致,無臟讀及弗成重復(fù)讀情況.

測試2：INSERT數(shù)據(jù)不提交,另發(fā)難務(wù)多次查詢

測試結(jié)果：同個事務(wù)多次讀取相同范圍的數(shù)據(jù),但是行數(shù)不一樣,屬于幻讀(這里注意,如果insert 分為beigin；commit,一直不commit的話,3的查詢會處于等待情況,因?yàn)樗匾暾埖腟鎖被 insert的X鎖所堵塞)

測試3：快照讀測試

測試結(jié)果：同個事務(wù)多次讀取相同記錄,讀取的都是已提交記錄,不存在臟讀及丟失更新情況,但是存在弗成重復(fù)讀及幻讀.

總結(jié)：支持快照讀,快照讀 不存在臟讀及丟失更新情況,但是存在弗成重復(fù)讀及幻讀；而當(dāng)前讀不存在臟讀、弗成重復(fù)讀問題,存在幻讀問題.

5.2.3 Read Repeatable

所有事務(wù)隔離級別設(shè)置： set session transaction isolation level repeatable read ;

由于該隔離級別支持快照讀,不添加for update跟lock in share mode的select 查詢語句,使用的是快照讀,不添加鎖.以是測試使用當(dāng)前讀的模式測試,添加lock in share mode,添加S鎖.

測試1：update數(shù)據(jù)不提交,另起查詢

測試結(jié)果：由于當(dāng)前讀持有S鎖,導(dǎo)致update申請X鎖處于等待情況,無法更新,同個事務(wù)內(nèi)的多次查詢結(jié)果一致,無臟讀及弗成重復(fù)讀情況.

測試2：INSERT數(shù)據(jù)不提交,另發(fā)難務(wù)多次查詢

測試成果：同個事務(wù)多次讀取相同范圍的數(shù)據(jù),會有GAP鎖鎖定,故同個事務(wù)多次當(dāng)前讀成果記錄數(shù)都是一致的,不存在幻讀情況.

測試3：快照讀測試

測試結(jié)果：同個事務(wù)多次讀取相同記錄,不存在臟讀及丟失更新、弗成重復(fù)讀及幻讀等情況.

總結(jié)：支持快照讀,快照讀跟當(dāng)前讀不存在臟讀、弗成重復(fù)讀問題,存在幻讀問題.

5.2.4 Read Serializable

所有事務(wù)隔離級別設(shè)置： set session transaction isolation level Serializable ;

該隔離級別不支持快照讀,所有SELECT查詢都是當(dāng)前讀,而且持有S鎖.

測試1：update數(shù)據(jù)不提交,另起查詢；INSERT數(shù)據(jù)不提交,另發(fā)難務(wù)多次查詢

測試結(jié)果：該隔離級別下所有select語句持有S鎖,導(dǎo)致update申請X鎖處于等待情況,INSERT申請X也被堵塞,同個事務(wù)內(nèi)的多次查詢結(jié)果一致,不存在臟讀、弗成重復(fù)讀及幻讀情況.

總結(jié)：無快照讀,所有SELECT查詢都是當(dāng)前讀,不存在臟讀、弗成重復(fù)讀問題,存在幻讀問題.

以為沒了,not,還有一個概念這里沒有提交,這里彌補(bǔ)介紹下：semi-consistent read

PS： semi-consistent read

在read committed或者read uncommitted 隔離級別下,有如許的測試現(xiàn)象：

測試表格及數(shù)據(jù)

CREATE TABLE `tblock` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(10) DEFAULT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

insert into tblock(name) select 'su';

insert into tblock(name) select 'xin';

測試1：兩個update事務(wù)并發(fā),分別update分歧行,update條件列無索引

測試成果：兩條update互不干擾,正常執(zhí)行.

測試2：update語句不提交,另發(fā)難務(wù)當(dāng)前讀操作

測試成果：當(dāng)前讀被堵塞,無法正常加X鎖

問題點(diǎn)：為啥兩個測試中的sql序號2,都是申請X鎖,測試1可以正常申請環(huán)境,而測試2不行呢?

正常環(huán)境下,where條件中的name列沒有索引,故這個update操作是對全表做scan掃描加X鎖,正常環(huán)境下,在第一個事務(wù)中,update語句沒有提交的環(huán)境下,這個表格有一個表鎖X,對每一行數(shù)據(jù)都無法申請S鎖或者X鎖,那么為什么 測試1 可以正常申請呢?

在這里,必要引入semi-constent-read,半一致性讀.官網(wǎng)解釋如下：

semi consistent read：

A type of read operation used for UPDATE statements, that is a combination of read co妹妹itted and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest co妹妹itted version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the read co妹妹itted isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.

semi-consistent read是update語句在讀數(shù)據(jù)的一種操作, 是read committed與consistent read兩者的結(jié)合.update語句A在沒有提交時,另外一個update語句B讀到一行已經(jīng)被A加鎖的記錄,但是這行記錄不在A的where條件內(nèi),此時InnoDB返回記錄最近提交的版本給B,由MySQL上層判斷此版本是否滿足B的update的where條件.若滿足(必要更新),則MySQL會重新發(fā)起一次讀操作,此時會讀取行的最新版本(并加鎖).semi-consistent read只會發(fā)生在read committed及read uncommitted隔離級別,或者是參數(shù)innodb_locks_unsafe_for_binlog被設(shè)置為true. 對update起作用,對select insert delete 不起作用.這就導(dǎo)致了update 不堵塞,但是當(dāng)前讀的select則被堵塞的現(xiàn)象.

產(chǎn)生 semi consitent read的條件：

1. update語句

2. 執(zhí)行計劃時scan,range scan or table scan,不克不及時unique scan

3. 表格為湊集索引

總結(jié)以下：