MySQL事務隔離級别與鎖

資料庫鎖

共享鎖(Shared lock)

例1：

T1: select * from table (請想象它需要執行1個小時之久，後面的sql語句請都這麼想象）

T2: update table set column1=‘hello’

過程：T1運作 （加共享鎖)

T2運作等待T1運作完之後再運作T2

之是以要等，是因為T2在執行update前，試圖對table表加一個排他鎖，而資料庫規定同一資源上不能同時共存共享鎖和排他鎖。是以T2必須等T1執行完，釋放了共享鎖，才能加上排他鎖，然後才能開始執行update語句。

例2：

T1: select * from table

T2: select * from table

這裡T2不用等待T1執行完，而是可以馬上執行。

分析：

T1運作，則table被加鎖，比如叫lockA

T2運作，再對table加一個共享鎖，比如叫lockB。

共享鎖是立即釋放的。

它其實是用來檢測某條記錄是否被其它事務加鎖用的，屬于資料庫内部控制，調用者無需考慮。

兩個鎖是可以同時存在于同一資源上的（比如同一個表上）。這被稱為共享鎖與共享鎖相容。這意味着共享鎖不阻止其它session同時讀資源，但阻止其它session update

例3：

T1: select * from table

T2: select * from table

T3: update table set column1=‘hello’

這次，T2不用等T1運作完就能運作，T3卻要等T1和T2都運作完才能運作。因為T3必須等T1和T2的共享鎖全部釋放才能進行加排他鎖然後執行update操作。

例4：（死鎖的發生）

T1:begin tran

select * from table (holdlock) (holdlock意思是加共享鎖，直到事務結束才釋放)

update table set column1=‘hello’

T2:begin tran

select * from table(holdlock)

update table set column1=‘world’

假設T1和T2同時達到select，T1對table加共享鎖，T2也對加共享鎖，當T1的select執行完，準備執行update時，根據鎖機制，T1的共享鎖需要更新到排他鎖才能執行接下來的update.在更新排他鎖前，必須等table上的其它共享鎖釋放，但因為holdlock這樣的共享鎖隻有等事務結束後才釋放，是以因為T2的共享鎖不釋放而導緻T1等(等T2釋放共享鎖，自己好更新成排他鎖），同理，也因為T1的共享鎖不釋放而導緻T2等。死鎖産生了。

例5：

T1:begin tran

update table set column1=‘hello’ where id=10

T2:begin tran

update table set column1=‘world’ where id=20

這種語句雖然最為常見，很多人覺得它有機會産生死鎖，但實際上要看情況，如果id是主鍵上面有索引，那麼T1會一下子找到該條記錄(id=10的記錄），然後對該條記錄加排他鎖，T2，同樣，一下子通過索引定位到記錄，然後對id=20的記錄加排他鎖，這樣T1和T2各更新各的，互不影響。T2也不需要等。

但如果id是普通的一列，沒有索引。那麼當T1對id=10這一行加排他鎖後，T2為了找到id=20，需要對全表掃描，那麼就會預先對表加上共享鎖或更新鎖或排他鎖(依賴于資料庫執行政策和方式，比如第一次執行和第二次執行資料庫執行政策就會不同）。但因為T1已經為一條記錄加了排他鎖，導緻T2的全表掃描進行不下去，就導緻T2等待。

死鎖怎麼解決呢？一種辦法是，如下：

例6：

T1:begin tran

select * from table(xlock) (xlock意思是直接對表加排他鎖)

update table set column1=‘hello’

T2:begin tran

select * from table(xlock)

update table set column1=‘world’

這樣，當T1的select 執行時，直接對表加上了排他鎖，T2在執行select時，就需要等T1事務完全執行完才能執行。排除了死鎖發生。但當第三個user過來想執行一個查詢語句時，也因為排他鎖的存在而不得不等待，第四個、第五個user也會是以而等待。在大并發情況下，讓大家等待顯得性能就太友好了，是以，這裡引入了更新鎖。

更新鎖(Update lock)

為解決死鎖，引入更新鎖

例7：

T1:begin tran

select * from table(updlock) (加更新鎖)

update table set column1=‘hello’

T2:begin tran

select * from table(updlock)

update table set column1=‘world’

更新鎖的意思是：“我現在隻想讀，你們别人也可以讀，但我将來可能會做更新操作，我已經擷取了從共享鎖（用來讀）到排他鎖（用來更新）的資格”。一個事務隻能有一個更新鎖獲此資格。

T1執行select，加更新鎖。

T2運作，準備加更新鎖，但發現已經有一個更新鎖在那兒了，隻好等。（T2加的是更新鎖，更新鎖與更新鎖不相容， 如果加的是共享鎖， 共享鎖和更新鎖可以相容，即T1，T2不可同時進行，但是T3，T4，T5隻要不是事務，還是可以正常查詢）

當後來有user3、user4…需要查詢table表中的資料時，并不會因為T1的select在執行就被阻塞，照樣能查詢，相比起例6，這提高了效率

例8:

T1:begin

select * from table(updlock) (加更新鎖）

update table set column1=‘hello’ (重點：這裡T1做update時，不需要等T2釋放什麼，而是直接把更新鎖更新為排他鎖，然後執行update)

T2:begin

select * from table (T1加的更新鎖不影響T2讀取）

update table set column1=‘world’ (T2的update需要等T1的update做完才能執行)

我們以這個例子來加深更新鎖的了解，

第一種情況：T1先達，T2緊接到達；在這種情況中，T1先對表加更新鎖，T2對表加共享鎖，假設T2的select先執行完，準備執行update，發現已有更新鎖存在，T2等。T1執行這時才執行完select，準備執行update，更新鎖更新為排他鎖，然後執行update，執行完成，事務結束，釋放鎖，T2才輪到執行update。

第二種情況：T2先達，T1緊接達；在這種情況，T2先對表加共享鎖，T1達後，T1對表加更新鎖，假設T2 select先結束，準備update，發現已有更新鎖，則等待，後面步驟就跟第一種情況一樣了。這個例子是說明：排他鎖與更新鎖是不相容的，它們不能同時加在同一子資源上。

排他鎖（獨占鎖，Exclusive Locks)

這個簡單，即其它事務既不能讀，又不能改排他鎖鎖定的資源。

例9

T1: update table set column1=‘hello’ where id<1000

T2: update table set column1=‘world’ where id>1000

假設T1先達，T2随後至，這個過程中T1會對id<1000的記錄施加排他鎖.但不會阻塞T2的update。

例10 (假設id都是自增長且連續的）

T1: update table set column1=‘hello’ where id<1000

T2: update table set column1=‘world’ where id>900

如同例9，T1先達，T2立刻也到，T1加的排他鎖會阻塞T2的update.

意向鎖(Intent Locks)

意向鎖就是說在屋（比如代表一個表）門口設定一個辨別，說明屋子裡有人（比如代表某些記錄）被鎖住了。另一個人想知道屋子裡是否有人被鎖，不用進屋子裡一個一個的去查，直接看門口辨別就行了。

當一個表中的某一行被加上排他鎖後，該表就不能再被加表鎖。資料庫程式如何知道該表不能被加表鎖？一種方式是逐條的判斷該表的每一條記錄是否已經有排他鎖，另一種方式是直接在表這一層級檢查表本身是否有意向鎖，不需要逐條判斷。顯然後者效率高。

例11：

T1: begin tran

select * from table (xlock) where id=10 --意思是對id=10這一行強加排他鎖

T2: begin tran

select * from table (tablock) --意思是要加表級鎖

假設T1先執行，T2後執行，T2執行時，欲加表鎖，為判斷是否可以加表鎖，資料庫系統要逐條判斷table表每行記錄是否已有排他鎖，如果發現其中一行已經有排他鎖了，就不允許再加表鎖了。隻是這樣逐條判斷效率太低了。

實際上，資料庫系統不是這樣工作的。當T1的select執行時，系統對表table的id=10的這一行加了排他鎖，還同時悄悄的對整個表加了意向排他鎖(IX)，當T2執行表鎖時，隻需要看到這個表已經有意向排他鎖存在，就直接等待，而不需要逐條檢查資源了。

計劃鎖(Schema Locks)

例12：

alter table … (加schema locks，稱之為Schema modification (Sch-M) locks

DDL語句都會加Sch-M鎖，該鎖不允許任何其它session連接配接該表。連都連不了這個表了，當然更不用說想對該表執行什麼sql語句了。

事務隔離級别（MySQL僅InnoDB引擎支援事務，是以也隻有InnoDB有事務隔離級别）

Read Uncommit (未送出讀。允許髒讀)

事例：老闆要給程式員發工資，程式員的工資是3.6萬/月。但是發工資時老闆不小心按錯了數字，按成3.9萬/月，該錢已經打到程式員的戶口，但是事務還沒有送出，就在這時，程式員去檢視自己這個月的工資，發現比往常多了3千元，以為漲工資了非常高興。但是老闆及時發現了不對，馬上復原差點就送出了的事務，将數字改成3.6萬再送出。

分析：實際程式員這個月的工資還是3.6萬，但是程式員看到的是3.9萬。他看到的是老闆還沒送出事務時的資料。這就是髒讀。

一級封鎖協定：

一級封鎖協定，事務在對需要修改的資料上面（就是在發生修改的瞬間） 對其加共享鎖（其他事務不能更改，但是可以讀取-導緻“髒讀”），直到事務結束才釋放。

Read committed（讀送出，顧名思義，就是一個事務要等另一個事務送出後才能讀取資料。）

事例：程式員拿着信用卡去享受生活（卡裡當然是隻有3.6萬），當他埋單時（程式員事務開啟），收費系統事先檢測到他的卡裡有3.6萬，就在這個時候！！程式員的妻子要把錢全部轉出充當家用，并送出。當收費系統準備扣款時，再檢測卡裡的金額，發現已經沒錢了（第二次檢測金額當然要等待妻子轉出金額事務送出完）。程式員就會很郁悶，明明卡裡是有錢的…

分析：這就是讀送出，若有事務對資料進行更新（UPDATE）操作時，讀操作事務要等待這個更新操作事務送出後才能讀取資料，可以解決髒讀問題。但在這個事例中，出現了一個事務範圍内兩個相同的查詢卻傳回了不同資料，這就是不可重複讀。

二級封鎖協定：

1）事務 在對需要更新的資料 上（就是發生更新的瞬間） 加 排他鎖 （直到事務結束） ， 防止其他事務讀取未送出的資料，這樣，也就避免了 “髒讀” 的情況。2）事務 對目前被讀取的資料 上面加共享鎖（當讀到時加上共享鎖），一旦讀完該行，立即 釋放該 該行的共享鎖

二級封鎖協定除防止了“髒讀”資料，但是不能避免 丢失更新，不可重複讀，幻讀 。

 但在二級封鎖協定中，由于讀完資料後立即 釋放共享鎖，是以它不能避免可重複讀 ，同時它也不能避免 丢失更新 ，如果事務A、B同時擷取資源X，然後事務A先發起更新記錄X，那麼 事務B 将等待事務 A 執行完成，然後獲得記錄X 的排他鎖，進行更改。這樣事務 A 的更新将會被丢失。
           

具體情況如下：

由此可以看到，事務A的送出被事務B覆寫了

Repeatable read（重複讀，就是在開始讀取資料（事務開啟）時，不再允許修改操作）

事例：程式員拿着信用卡去享受生活（卡裡當然是隻有3.6萬），當他埋單時（事務開啟，不允許其他事務的UPDATE修改操作），收費系統事先檢測到他的卡裡有3.6萬。這個時候他的妻子不能轉出金額了。接下來收費系統就可以扣款了。

分析：重複讀可以解決不可重複讀問題。寫到這裡，應該明白的一點就是，不可重複讀對應的是修改，即UPDATE操作。但是可能還會有幻讀問題。因為幻讀問題對應的是插入INSERT操作，而不是UPDATE操作。

什麼時候會出現幻讀？

事例：程式員某一天去消費，花了2千元，然後他的妻子去檢視他今天的消費記錄（全表掃描FTS，妻子事務開啟），看到确實是花了2千元，就在這個時候，程式員花了1萬買了一部電腦，即新增INSERT了一條消費記錄，并送出。當妻子列印程式員的消費記錄清單時（妻子事務送出），發現花了1.2萬元，似乎出現了幻覺，這就是幻讀。

三級封鎖協定：

三級封鎖協定是：二級封鎖協定加上事務 在讀取資料的瞬間 必須先對其加 共享鎖 ，但是 直到事務結束才釋放 ，這樣保證了可重複讀（既是其他的事務職能讀取該資料，但是不能更新該資料）。

三級封鎖協定除防止了“髒”資料 和不可重複讀 。但是這種情況不能避免 幻讀 和 丢失更新 的情況，在事務 A 沒有完成之前，事務 B 可以新增資料，那麼 當事務 A 再次讀取的時候，事務B 新增的資料會被讀取到

進階：repeatable read 導緻死鎖的情況，參考上面講解共享鎖的文章

Serializable 序列化

Serializable 是最高的事務隔離級别，在該級别下，事務串行化順序執行，可以避免髒讀、不可重複讀與幻讀。但是這種事務隔離級别效率低下，比較耗資料庫性能，一般不使用。

四級封鎖協定：

四級封鎖協定是對三級封鎖協定的增強，其實作機制也最為簡單，直接對 事務中 所 讀取 或者 更改的資料所在的表加表鎖，也就是說，其他事務不能 讀寫 該表中的任何資料。這樣所有的 髒讀，不可重複讀，幻讀 ，都得以避免！

值得一提的是：大多數資料庫預設的事務隔離級别是Read committed，比如Sql Server , Oracle。Mysql的預設隔離級别是Repeatable read。

樂觀鎖（ Optimistic Locking ） 相對悲觀鎖而言，樂觀鎖機制采取了更加寬松的加鎖機制。悲觀鎖大多數情況下依靠資料庫的鎖機制實作，以保證操作最大程度的獨占性。但随之而來的就是資料庫性能的大量開銷，特别是對長事務而言，這樣的開銷往往無法承受。而樂觀鎖機制在一定程度上解決了這個問題。樂觀鎖，大多是基于資料版本（ Version ）記錄機制實作。何謂資料版本？即為資料增加一個版本辨別，在基于資料庫表的版本解決方案中，一般是通過為資料庫表增加一個 “version” 字段來實作。讀取出資料時，将此版本号一同讀出，之後更新時，對此版本号加一。此時，将送出資料的版本資料與資料庫表對應記錄的目前版本資訊進行比對，如果送出的資料版本号大于資料庫表目前版本号，則予以更新，否則認為是過期資料。

悲觀鎖（Pessimistic Lock），正如其名，具有強烈的獨占和排他特性。它指的是對資料被外界（包括本系統目前的其他事務，以及來自外部系統的事務處理）修改持保守态度，是以，在整個資料處理過程中，将資料處于鎖定狀态。悲觀鎖的實作，往往依靠資料庫提供的鎖機制（也隻有資料庫層提供的鎖機制才能真正保證資料通路的排他性，否則，即使在本系統中實作了加鎖機制，也無法保證外部系統不會修改資料）。

事務的特性：

1.原子性 事務是資料庫邏輯的工作單元，事務包括的所有操作，要麼都做，要麼都不做。

2.一緻性 事務執行的結果是使資料庫從一個一緻性狀态變成另一個一緻性狀态。一緻性與原子性是密切相關的。

3.隔離性 一個事務的執行不能被其他事務幹擾。

4.持久性 一個事務一旦送出，它對資料庫中資料的改變應該是永久性的。

MyISAM 和 InnoDB 差別：

1、MyISAM類型的表強調的是性能，但是不支援事務、及外部鍵等進階功能。

MySQL預設采用的是MyISAM。

MyISAM不支援事務，是以MyISAM就不存在事務隔離級别了，而InnoDB支援，所有有4種事務隔離級别，InnoDB的AUTOCOMMIT預設是打開的，即每條SQL語句會預設被封裝成一個事務，自動送出，這樣會影響速度，是以最好是把多條SQL語句顯示放在begin和commit之間，組成一個事務去送出。

InnoDB支援資料行鎖定，MyISAM不支援行鎖定，隻支援鎖定整個表。即MyISAM同一個表上的讀鎖和寫鎖是互斥的，MyISAM并發讀寫時如果等待隊列中既有讀請求又有寫請求，預設寫請求的優先級高，即使讀請求先到，是以MyISAM不适合于有大量查詢和修改并存的情況，那樣查詢程序會長時間阻塞。因為MyISAM是鎖表，是以某項讀操作比較耗時會使其他寫程序餓死。

InnoDB支援外鍵，MyISAM不支援。

InnoDB的主鍵範圍更大，最大是MyISAM的2倍。

InnoDB不支援全文索引，而MyISAM支援。全文索引是指對char、varchar和text中的每個詞（停用詞除外）建立倒排序索引。MyISAM的全文索引其實沒啥用，因為它不支援中文分詞，必須由使用者分詞後加入空格再寫到資料表裡，而且少于4個漢字的詞會和停用詞一樣被忽略掉。

MyISAM支援GIS資料，InnoDB不支援。即MyISAM支援以下空間資料對象：Point,Line,Polygon,Surface等。

沒有where的count()使用MyISAM要比InnoDB快得多。因為MyISAM内置了一個計數器，count()時它直接從計數器中讀，而InnoDB必須掃描全表。是以在InnoDB上執行count()時一般要伴随where，且where中要包含主鍵以外的索引列。為什麼這裡特别強調“主鍵以外”？因為InnoDB中primary index是和raw data存放在一起的，而secondary index則是單獨存放，然後有個指針指向primary key。是以隻是count()的話使用secondary index掃描更快，而primary key則主要在掃描索引同時要傳回raw data時的作用較大。

2、并發

MyISAM讀寫互相阻塞：不僅會在寫入的時候阻塞讀取，MyISAM還會在讀取的時候阻塞寫入，但讀本身并不會阻塞另外的讀

InnoDB 讀寫阻塞與事務隔離級别相關

3、場景選擇

MyISAM

不需要事務支援（不支援）

并發相對較低（鎖定機制問題）

資料修改相對較少（阻塞問題），以讀為主

資料一緻性要求不是非常高

盡量索引（緩存機制）

調整讀寫優先級，根據實際需求確定重要操作更優先

啟用延遲插入改善大批量寫入性能

盡量順序操作讓insert資料都寫入到尾部，減少阻塞

分解大的操作，降低單個操作的阻塞時間

降低并發數，某些高并發場景通過應用來進行排隊機制

對于相對靜态的資料，充分利用Query Cache可以極大的提高通路效率

MyISAM的Count隻有在全表掃描的時候特别高效，帶有其他條件的count都需要進行實際的資料通路

InnoDB

需要事務支援（具有較好的事務特性）

行級鎖定對高并發有很好的适應能力，但需要確定查詢是通過索引完成

資料更新較為頻繁的場景

資料一緻性要求較高

硬體裝置記憶體較大，可以利用InnoDB較好的緩存能力來提高記憶體使用率，盡可能減少磁盤 IO

主鍵盡可能小，避免給Secondary index帶來過大的空間負擔

避免全表掃描，因為會使用表鎖

盡可能緩存所有的索引和資料，提高響應速度

在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動送出

合理設定innodb_flush_log_at_trx_commit參數值，不要過度追求安全性

避免主鍵更新，因為這會帶來大量的資料移動

4、其它細節

1）InnoDB 中不儲存表的具體行數，注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的

2）對于AUTO_INCREMENT類型的字段，InnoDB中必須包含隻有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引， 如果你為一個表指定AUTO_INCREMENT列，在資料詞典裡的InnoDB表句柄包含一個名為自動增長計數器的計數器，它被用在為該列賦新值。自動增長計數器僅被存儲在主記憶體中，而不是存在磁盤

3）DELETE FROM table時，InnoDB不會重建立立表，而是一行一行的删除

4）LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入資料後再改成InnoDB表，但是對于使用的額外的InnoDB特性(例如外鍵)的表不适用

5）如果執行大量的SELECT，MyISAM是更好的選擇，如果你的資料執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表

5、為什麼MyISAM會比Innodb 的查詢速度快

InnoDB 在做SELECT的時候，要維護的東西比MYISAM引擎多很多；

1）InnoDB 要緩存資料和索引，MyISAM隻緩存索引塊，這中間還有換進換出的減少

2）innodb尋址要映射到塊，再到行，MyISAM記錄的直接是檔案的OFFSET，定位比INNODB要快

3）InnoDB 還需要維護MVCC一緻；雖然你的場景沒有，但他還是需要去檢查和維護

MVCC ( Multi-Version Concurrency Control )多版本并發控制

InnoDB ：通過為每一行記錄添加兩個額外的隐藏的值來實作MVCC，這兩個值一個記錄這行資料何時被建立，另外一個記錄這行資料何時過期（或者被删除）。但是InnoDB并不存儲這些事件發生時的實際時間，相反它隻存儲這些事件發生時的系統版本号。這是一個随着事務的建立而不斷增長的數字。每個事務在事務開始時會記錄它自己的系統版本号。每個查詢必須去檢查每行資料的版本号與事務的版本号是否相同。讓我們來看看當隔離級别是REPEATABLE READ時這種政策是如何應用到特定的操作的

SELECT InnoDB必須每行資料來保證它符合兩個條件

1、InnoDB必須找到一個行的版本，它至少要和事務的版本一樣老(也即它的版本号不大于事務的版本号)。這保證了不管是事務開始之前，或者事務建立時，或者修改了這行資料的時候，這行資料是存在的。

2、這行資料的删除版本必須是未定義的或者比事務版本要大。這可以保證在事務開始之前這行資料沒有被删除。