MySQL--事務

事務

1.事務理解

1.1 事務的概念

事務處理可以確保除非事務性單元內的所有操作都成功完成，否則不會永遠更新面向資料的資源，一個邏共湊的單元要成為事務，必須滿足所謂的ACID屬性，即：
1.原子性（A）

對於修改的資料要麼全部執行，要麼全部不執行

2.隔離性（C）

在所有的操作沒有執行之前，其他會話不能夠看到中間改變的過程

3.一致性（I）

事務發生前後，根據資料的規則，總額應該匹配

4.永續性

事務一旦提交，其結果就是永久的，系統崩潰也不會影響
事務例子：

 start transaction; //開啟事務  
 insert into count (prefix,count,historycount) values('dy1','0','2');  
 select * from count
 commit;

1.2 事務的實現

Mysql 在進行事務處理的時候使用的是日誌現行的方式來保證事務可快速和持久執行的，也就是先寫日誌，在寫資料庫。一個事務開始時，會記錄該事務的一個LSN日誌序列號；當執行事務時，會往InnoDB_log_buffer 日誌緩衝區裡插入事務日誌（redo log）;當事務提交時，會將日誌緩衝區裡的事務日誌刷入磁碟。這個動作是由 innodb_flush_log_at_trx_commit 這個引數控制的。

發出commit動作時，是否刷日誌由變數 innodb_flush_log_at_trx_commit控制
每秒刷一次。這個重新整理日誌的頻率是由變數 innodb_flush_log_at_timeout值決定，預設是1秒，這個值與commit無關。
當log buffer 中已經使用的記憶體超過一半時？？？？
當有 checkpoint時，checkpoint在一定程度上代表了重新整理磁碟時日誌所處的LS位置

可以透過命令show engine innodb status \G 檢視

Log sequence number 8619676075 (表示當前的LSN日誌序列號)

Log flushed up to 8619676075 (表示重新整理到事物日誌的LSN日誌序列號)

Last checkpoint at 8619676075 (表示重新整理到磁碟的LSN日誌序列號)

除了記錄事務日誌意外，資料庫還會記錄一定量的撤銷日誌(undo log)， undo與redo(事務日誌)正好相反，在對資料進行修改時，由於某種原因失敗了，或者人為執行了rollback回滾語句，就可以利用這些撤銷日誌將資料回滾到修改之前的樣子。redo日誌儲存在ib_logfile0/1/2裡，而undo日誌儲存在ibdata1裡，在MySQL5.6裡還可以把undo日誌單拆分出去。

1.3 分散式事務（初步瞭解）

資源管理器：管理事務的提交和回滾，向事務提供資源
事務管理器：與資源管理通訊，協調完成事務的處理

用於執行分散式事務的過程的倆個階段；

第一階段：所有分支被預備。他們被事務管理告知要準備提交，每個分支資源管理器記錄分支的行動並指示認為的可行性。
第二階段：事務管理器告知資源管理器是否要提交或者回滾。如果預備分支時，所有的指示他們能夠提交，那麼所有的分支被告知提交。如果有一個分支出錯，則全部回滾。特殊情況下，只有一個分支的時候，第二階段則被省略。

分散式事務主要作用在於確保事務的一致性和完整性。他利用分散式的計算環境，將多個事務性的活動合併成一個事務單元，這些事務組合在一起構成原子操作，這些事務的活動要麼一起執行並提交事務，要麼回滾所有的操作，從而保證了多個活動之間的一致性和完整性。

2.事務與日誌

`
– 檢視事務日誌：
show engine innodb status\G;

– 檢視日誌檔案設定狀態

show variables like ‘innodb_%’;
`

2.1 事務特性理解

事務日誌檔案
innodb_log_files_in_group:DB中設定幾組事務日誌，預設是2；
innodb_log_group_home_dir:事務日誌存放目錄，不設定，ib_logfile0…存在在資料檔案目錄下
Innodb儲存引擎可將所有資料存放於ibdata*的共享表空間，也可將每張表存放於獨立的.ibd檔案的獨立表空間\

注意：在MySQL中對於資料來說，最為重要的是日誌檔案
redo log => ib_logfile0
undo log => ibdata\

2.1.1原子性:

事務的一系列操作要麼全部執行，要麼不執行
原子性與回滾日誌：
想要保證事務的原子性，就需要在異常發生時，對已經執行的操作進行回滾，而在 MySQL 中，恢復機制是透過回滾日誌（undo log）實現的，所有事務進行的修改都會先記錄到這個回滾日誌中，然後在對資料庫中的對應行進行寫入。

注意：系統發生崩潰、資料庫程式直接被殺死後，當使用者再次啟動資料庫程式時，還能夠立刻透過查詢回滾日誌將之前未完成的事務進行回滾，這也就需要回滾日誌必須先於資料持久化到磁碟上，是我們需要先寫日誌後寫資料庫的主要原因。

在日誌檔案中:在事務中使用的每一條 INSERT 都對應了一條 DELETE，每一條 UPDATE 也都對應一條相反的 UPDATE 語句。\

2.1.2 永續性:

事務被提交，資料一定會被寫入到資料庫中並持久儲存起來，通常來說當事務已經被提交之後，就無法再次回滾了。
永續性性與重做日誌：
與原子性一樣，事務的永續性也是透過日誌來實現的，MySQL 使用重做日誌（redo log）實現事務的永續性，重做日誌由兩部分組成，一是記憶體中的重做日誌緩衝區，因為重做日誌緩衝區在記憶體中，所以它是易失的，另一個就是在磁碟上的重做日誌檔案，它是持久的\

2.1.3 回滾日誌與重做日誌:

回滾日誌（undo log）和重做日誌（redo log）；在資料庫系統中，事務的原子性和永續性是由事務日誌（transaction log）保證的，在實現時也就是上面提到的兩種日誌，前者用於對事務的影響進行撤銷，後者在錯誤處理時對已經提交的事務進行重做，它們能保證兩點：
1. 發生錯誤或者需要回滾的事務能夠成功回滾（原子性）；
2. 在事務提交後，資料沒來得及寫會磁碟就當機時，在下次重新啟動後能夠成功恢復資料（永續性）；
在資料庫中，這兩種日誌經常都是一起工作的，我們可以將它們整體看做一條事務日誌，其中包含了事務的 ID、修改的行元素以及修改前後的值。

2.1.4 事務的流程

Mysql 的checkpoint 參考

www.cnblogs.com/lintong/p/4381578....
checkpoint，即檢查點。在undolog中寫入檢查點，表示在checkpoint前的事務都已經完成commit或者rollback了，也就是檢查點前面的事務已經不存在資料一致性的問題了。

Innodb的事務日誌是指Redo log，簡稱Log,儲存在日誌檔案ib_logfile裡面（去mysql資料目錄下看下）。Innodb還有另外一個日誌Undo log，但Undo log是存放在共享表空間裡面的（ibdata*檔案，儲存的是check point日誌序列號）。

Innodb的一條事務日誌共經歷4個階段
1）建立階段：事務建立一條日誌；
2）日誌重新整理：日誌寫入到磁碟的日誌檔案；
3）資料重新整理：日誌對應的髒資料寫入磁碟的資料檔案
4) 寫入CKP：日誌被當作Checkpoint寫入到日誌檔案；\

innodb_flush_log_at_trx_commit 引數解析

//檢視日誌檔案設定狀態
show variables like 'innodb_%';

//修改
set @@global.innodb_flush_log_at_trx_commit = 0; -- 0，1，2

show variables like 'innodb_flush_log_at_trx_commit';

效能對比：0>2>1
資料安全對比：1最好,所以預設選1

3.鎖機制

MySQL的不同儲存引擎，支援不同的鎖定機制

3.1 鎖型別。

MyISAM 和 Memory 儲存引擎使用的是表級鎖,BDB 引擎使用的是頁級鎖，也支援表級鎖。由於 BDB 引擎基本已經成為歷史，因此就不再介紹了。
InnoDB 儲存引擎既支援行級鎖，也支援表級鎖，預設情況下使用行級鎖。
所謂表級鎖，它直接鎖住的是一個表，開銷小，加鎖快，不會出現死鎖的情況，鎖定粒度大，發生鎖衝突的機率更高，併發度最低。
所謂行級鎖，它直接鎖住的是一條記錄，開銷大，加鎖慢，發生鎖衝突的機率較低，併發度很高。
所謂頁級鎖，它是鎖住的一個頁面，在 InnoDB 中一個頁面為16KB，它的開銷介於表級鎖和行級鎖中間，也可能會出現死鎖，鎖定粒度也介於表級鎖和行級鎖中間，併發度也介於表級鎖和行級鎖中間。
僅僅從鎖的角度來說，表級鎖更加適合於以查詢為主的應用，只有少量按照索引條件更新資料的應用
行級鎖更適合大量按照索引條件併發更新少量不同的資料，同時還有併發查詢的應用

3.2 innoDB行級鎖

InnoDB有兩種型別的行級鎖，兩種內部使用的意向鎖；

共享鎖(S):允許一個事務讀一行資料時，阻止其他的事務讀取相同資料的排他鎖（update,insert,delete）。
排他鎖(X):允許獲得排他鎖的事務更新資料，阻止其他事務取得相同資料的共享鎖和排他鎖。
意向共享鎖(IS):事務打算給資料行加行共享鎖。事務在給一個資料行加共享鎖前必須先取得該表的IS鎖。
意向排他鎖(IX):事務打算給資料行加行排他鎖。事務在給一個資料行加排他鎖前必須先取得該表的IX鎖。
悲觀鎖(抽象，不真實存在的鎖)：指運算元據庫時(更新操作)，想法很樂觀，認為這次的操作不會導致衝突，在運算元據時，並不進行任何其他的特殊處理（也就是不加鎖），而在進行更新後，再去判斷是否有衝突了
樂觀鎖(抽象，不真實存在的鎖)：觀鎖就是在運算元據時，認為此操作會出現資料衝突，所以在進行每次操作時都要透過獲取鎖才能進行對相同資料的操作，所以悲觀鎖需要耗費較多的時間。另外與樂觀鎖相對應的，悲觀鎖是由資料庫自己實現了的，要用的時候，我們直接呼叫資料庫的相關語句就可以了

4種鎖的共存邏輯關係表

鎖模式	共享鎖(S)	排他鎖(X)	意向共享鎖(IS)	意向排他鎖(IX)
共享鎖(S)	相容	衝突	相容	衝突
排他鎖(X)	衝突	衝突	衝突	衝突
意向共享鎖(IS)	相容	衝突	相容	相容
意向排他鎖(IX)	衝突	衝突	相容	相容

意向鎖是InnoDB儲存引擎自動加的，對於普通select語句，InnoDB不會加任何鎖，對於insert，update，delete語句，InnoDB會自動改涉及的資料加排他鎖，InnoDB以透過以下語句顯示新增的共享鎖和排他鎖

共享鎖語句

select * from table_name lock in share mode;

排他鎖語句

select * from table_name for update;

關於意向鎖；

意向鎖是表級鎖，其設計目的主要是為了在一個事務中揭示下一行將要被請求鎖的型別。InnoDB 中的兩個表鎖：

意向共享鎖（IS）：表示事務準備給資料行加入共享鎖，也就是說一個資料行加共享鎖前必須先取得該表的 IS 鎖。如果需要對記錄 A 加共享鎖，那麼此時 InnoDB 會先找到這張表，對該表加意向共享鎖之後，再對記錄 A 新增共享鎖

意向排他鎖（IX）：類似上面，表示事務準備給資料行加入排他鎖，也就是說事務在給一個資料行加排他鎖前必須先取得該表的 IX 鎖。如果需要對記錄 A 加排他鎖，那麼此時 InnoDB 會先找到這張表，對該表加意向排他鎖之後，再對記錄 A 新增排他鎖
意向鎖是 InnoDB 自動加的，不需要使用者干預

共享鎖和排他鎖，系統在特定的條件下會自動新增共享鎖或者排他鎖，也可以手動新增共享鎖或者排他鎖。
意向共享鎖和意向排他鎖都是系統自動新增和自動釋放的，整個過程無需人工干預

共享鎖和排他鎖都是鎖的行記錄，意向共享鎖和意向排他鎖鎖定的是表

3.3 innodb行鎖與表鎖的轉變與注意

InnoDB 行級鎖是透過給索引項加鎖來實現的，InnoDB 行級鎖只有透過索引條件檢索資料，才能使用行級鎖；否則，使用的是表級鎖。

在不透過索引（主鍵）條件查詢的時候，InnoDB是表鎖而不是行鎖。

通常begin-end用於定義一組語句塊

注意：如果查詢的條件沒有帶索引，那麼行鎖則會轉為表鎖，即使表中欄位有主鍵；所以在查詢的時候建議使用索引欄位查詢

3.4 innodb間隙鎖

可以理解為是對於一定範圍內的資料進行鎖定，如果說這個區間沒有這條資料的話也是會鎖住的；主要是解決幻讀的問題,如果沒有新增間隙鎖，如果其他事物中新增id在1到100之間的某條記錄，此時會發生幻讀；另一方面，視為了滿足其恢復和賦值的需求。

MySQL官網間隙鎖的使用 dev.mysql.com/doc/refman/5.7/en/in...

MySQL官網間隙鎖屬性解釋 dev.mysql.com/doc/refman/5.7/en/in...

預設情況下，innodb_locks_unsafe_for_binlog是0(禁用)，這意味著啟用了間隙鎖定：InnoDB使用下一個鍵鎖進行搜尋和索引掃描。若要啟用該變數，請將其設定為1。這將導致禁用間隙鎖定：InnoDB只使用索引記錄鎖進行搜尋和索引掃描。

3.5 MySQL對於死鎖的處理方式

官方定義如下：兩個事務都持有對方需要的鎖，並且在等待對方釋放，並且雙方都不會釋放自己的鎖

MySQL有兩種死鎖處理方式：

等待，直到超時（innodb_lock_wait_timeout=50s）。
發起死鎖檢測，主動回滾一條事務，讓其他事務繼續執行（innodb_deadlock_detect=on）

死鎖檢測：
死鎖檢測的原理是構建一個以事務為頂點、鎖為邊的有向圖，判斷有向圖是否存在環，存在即有死鎖。
檢測到死鎖之後，選擇插入更新或者刪除的行數最少的事務回滾，基於 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 欄位來判斷。

3.6 對於鎖與事務的建議

3.6.1 對於鎖的建議

收集死鎖資訊：
利用命令 SHOW ENGINE INNODB STATUS檢視死鎖原因。
除錯階段開啟 innodb_print_all_deadlocks，收集所有死鎖日誌。

減少死鎖：
使用事務，不使用 lock tables 。
保證沒有長事務。
操作完之後立即提交事務，特別是在互動式命令列中。
如果在用 (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE)，嘗試降低隔離級別。
修改多個表或者多個行的時候，將修改的順序保持一致。
建立索引，可以使建立的鎖更少。
最好不要用 (SELECT … FOR UPDATE or SELECT … LOCK IN SHARE MODE)。
如果上述都無法解決問題，那麼嘗試使用 lock tables t1, t2, t3 鎖多張表

3.6.2 對於事務的建議

innodb儲存引擎由於實現了行幾所，顆粒更小，實現更復雜。但是innodb行鎖在併發效能上遠遠要高於表鎖頁鎖。在使用方面可以儘量做到以下幾點；

控制事務大小，減少鎖定的資源量和鎖定時間長度。
所有的資料檢索都透過索引來完成，從而避免因為無法透過索引加鎖而升級為表鎖。
減少基於範圍的資料檢索過濾條件，避免因為間隙鎖帶來的負面影響而鎖定了不該鎖定的資料。
在業務條件允許下，儘量使用較低隔離級別的事務隔離。減少隔離級別帶來的附加成本。
合理使用索引，讓innodb在索引上面加鎖的時候更加準確。
在應用中儘可能做到訪問的順序執行
如果容易死鎖，就可以考慮使用表鎖來減少死鎖的機率

本作品採用《CC 協議》，轉載必須註明作者和本文連結