mysql并發處理機制(一)

1 什么是MVCC

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

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

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

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

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

MVCC有兩種實現方式,第一種實現方式是將數據記錄的多個版本保留在數據庫中,當這些不同版本數據不再需要時,垃圾收集器回收這些記錄.這個方式被PostgreSQL和Firebird/Interbase采用,SQL Server使用的類似機制,所不同的是舊版本數據不是保留在數據庫中,而保留在不同于主數據庫的另外一個數據庫tempdb中.第二種實現方式只在數據庫保留最新版本的數據,但是會在使用undo時動態重構舊版本數據,這種方式被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表示產生當前記錄項的事務ID,這個ID隨著事務的創建不斷增長；DATA _ROLL_PTR指向當前記錄項的undo信息.

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

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

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

4. 對于二級索引,更新操作無論更新primary key,或者是二級索引鍵值,都會導致二級索引發生新版本數據.

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

MVCC只工作在REPEATABLE READ和READ COMMITED隔離級別下.READ UNCOMMITED不是MVCC兼容的,因為查詢不克不及找到適合他們事務版本的行版本；它們每次都只能讀到最新的版本.SERIABLABLE也不與MVCC兼容,因為讀操作會鎖定他們返回的每一行數據 .

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

快照讀：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的環境)

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

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

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

3 Two Phase Locking

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

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

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

1. conservative

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

1. strict

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

1. strong strict

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

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

4 數據紛歧致情況

4.1 臟讀

讀取未提交事務中改動的數據,稱為臟讀.

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

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

4.2 喪失更新

多個更新操作并發執行,導致某些更新操作數據喪失.

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

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

4.3 弗成重復讀

同個事務多次讀取同一條存在的記錄,但是讀取的結構不一致,稱之為弗成重復讀.

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

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

4.4 幻讀

同個事務多次讀取某段段范圍內的數據,但是讀取到底行數紛歧致的情況,稱之為幻讀.

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

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

5 innodb的隔離級別

5.1 隔離級別先容

1. Read Unco妹妹ited

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

• 不支撐快照讀,都是當前讀

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

2. Read Co妹妹ited

• 不存在臟讀、弗成重復讀

• 存在幻讀問題；

• 簡稱 RC ,讀已提交記載

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

• 支持當前讀,讀取到的記載添加鎖

3. Read Repeatable

• 簡稱 RR ,可反復讀記錄

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

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

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

4. Read Serializable

• 簡稱 RS,序列化讀記載

• 不支撐快照讀,都是當前讀

• select數據添加S鎖,update\insert\delete數據添加X鎖

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

5.2 隔離級別測試

測試各個隔離級別下的數據紛歧致情況.

5.2.1 Read Unco妹妹itted

所有事務隔離級別設置： set session transaction isolation level read Unco妹妹ited ;

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

測試1：update數據不提交,另起查詢

測試成果：正常select可以查詢到不提交的事務內容,屬于臟讀

測試2：修改數據不提交,另發難務多次查詢

測試結果：同個事務多次讀取同一行記錄結果紛歧致,屬于重復讀

測試3：INSERT數據不提交,另發難務多次查詢

測試結果：同個事務多次讀取相同范圍的數據,但是行數紛歧樣,屬于幻讀

測試4：分歧事務對同一行數據進行update

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

總結：沒有快照讀,都是當前讀,所有讀都是讀可以讀未提交記錄,存在臟讀、弗成重復讀、幻讀等問題.

5.2.2 Read Co妹妹itted

所有事務隔離級別設置： set session transaction isolation level read co妹妹itted ;

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

測試1：update數據不提交,另起查詢

測試結果：由于當前讀持有S鎖,導致update申請X鎖處于等待情況,無法更新,同個事務內的多次查詢結果一致,無臟讀及弗成重復讀情況.

測試2：INSERT數據不提交,另發難務多次查詢

測試結果：同個事務多次讀取相同范圍的數據,但是行數不一樣,屬于幻讀(這里注意,如果insert 分為beigin；commit,一直不commit的話,3的查詢會處于等待情況,因為它必要申請的S鎖被 insert的X鎖所堵塞)

測試3：快照讀測試

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

總結：支持快照讀,快照讀 不存在臟讀及丟失更新情況,但是存在弗成重復讀及幻讀；而當前讀不存在臟讀、弗成重復讀問題,存在幻讀問題.

5.2.3 Read Repeatable

所有事務隔離級別設置： set session transaction isolation level repeatable read ;

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

測試1：update數據不提交,另起查詢

測試結果：由于當前讀持有S鎖,導致update申請X鎖處于等待情況,無法更新,同個事務內的多次查詢結果一致,無臟讀及弗成重復讀情況.

測試2：INSERT數據不提交,另發難務多次查詢

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

測試3：快照讀測試

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

總結：支持快照讀,快照讀跟當前讀不存在臟讀、弗成重復讀問題,存在幻讀問題.

5.2.4 Read Serializable

所有事務隔離級別設置： set session transaction isolation level Serializable ;

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

測試1：update數據不提交,另起查詢；INSERT數據不提交,另發難務多次查詢

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

總結：無快照讀,所有SELECT查詢都是當前讀,不存在臟讀、弗成重復讀問題,存在幻讀問題.

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

PS： semi-consistent read

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

測試表格及數據

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事務并發,分別update分歧行,update條件列無索引

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

測試2：update語句不提交,另發難務當前讀操作

測試成果：當前讀被堵塞,無法正常加X鎖

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

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

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

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

產生 semi consitent read的條件：

1. update語句

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

3. 表格為湊集索引

總結以下：