康師傅YYDS MySQL中只有InnoDB支持事務 1 SHOW ENGINES; 事務基礎知識事務的ACID特性原子性（atomicity）：原子性是指事務是一個不可分割的工作單位，要麼全部提交，要麼全部失敗回滾。一致性（consistency）：根據定義，一致性是指事務執行前後，數據 ...

康師傅YYDS

MySQL中只有InnoDB支持事務

1 SHOW ENGINES;

事務基礎知識

事務的ACID特性

原子性（atomicity）：原子性是指事務是一個不可分割的工作單位，要麼全部提交，要麼全部失敗回滾。一致性（consistency）：根據定義，一致性是指事務執行前後，數據從一個合法性狀態變換到另外一個合法性狀態。這種狀態是語義上的而不是語法上的，跟具體的業務有關。那什麼是合法的數據狀態呢？滿足預定的約束的狀態就叫做合法的狀態。通俗一點，這狀態是由你自己來定義的（比如滿足現實世界中的約束）。滿足這個狀態，數據就是一致的，不滿足這個狀態，數據就是不一致的！如果事務中的某個操作失敗了，系統就會自動撤銷當前正在執行的事務，返回到事務操作之前的狀態。隔離性（isolation）：事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的數據對併發的其他事務是隔離的，併發執行的各個事務之間不能互相干擾。持久性（durability）：持久性是指一個事務一旦被提交，它對資料庫中數據的改變就是永久性的，接下來的其他操作和資料庫故障不應該對其有任何影響。持久性是通過事務日誌來保證的。日誌包括了重做日誌和回滾日誌。當我們通過事務對數據進行修改的時候，首先會將資料庫的變化信息記錄到重做日誌中，然後再對資料庫中對應的行進行修改。這樣做的好處是，即使資料庫系統崩潰，資料庫重啟後也能找到沒有更新到資料庫系統中的重做日誌，重新執行，從而使事務具有持久性。

事務的狀態

事務是一個抽象的概念，它其實對應著一個或多個資料庫操作，MySQL根據這些操作所執行的不同階段把事務大致劃分成幾個狀態：活動的（active）事務對應的資料庫操作正在執行過程中時，我們就說該事務處在活動的狀態。部分提交的（partially committed）當事務中的最後一個操作執行完成，但由於操作都在記憶體中執行，所造成的影響並沒有刷新到磁碟時，我們就說該事務處在部分提交的狀態。失敗的（failed）當事務處在活動的或者部分提交的狀態時，可能遇到了某些錯誤（資料庫自身的錯誤、操作系統錯誤或者直接斷電等）而無法繼續執行，或者人為的停止當前事務的執行，我們就說該事務處在失敗的狀態。中止的（aborted）如果事務執行了一部分而變為失敗的狀態，那麼就需要把已經修改的事務中的操作還原到事務執行前的狀態。換句話說，就是要撤銷失敗事務對當前資料庫造成的影響。我們把這個撤銷的過程稱之為回滾。當回滾操作執行完畢時，也就是資料庫恢復到了執行事務之前的狀態，我們就說該事務處在了中止的狀態。提交的（committed）當一個處在部分提交的狀態的事務將修改過的數據都同步到磁碟上之後，我們就可以說該事務處在了提交的狀態。

使用事務

使用事務有兩種方式，分別為顯式事務和隱式事務。

顯式事務

步驟一、START TRANSACTION 或者 BEGIN ，作用是顯式開啟一個事務。

BEGIN;
#或者
START TRANSACTION;

START TRANSACTION 語句相較於 BEGIN 特別之處在於，後邊能跟隨幾個修飾符： ① READ ONLY ：標識當前事務是一個只讀事務，也就是屬於該事務的資料庫操作只能讀取數據，而不能修改數據。 ② READ WRITE ：標識當前事務是一個讀寫事務，也就是屬於該事務的資料庫操作既可以讀取數據，也可以修改數據。 ③ WITH CONSISTENT SNAPSHOT ：啟動一致性讀。步驟2：一系列事務中的操作（主要是DML，不含DDL）步驟3：提交事務或中止事務（即回滾事務）

# 提交事務。當提交事務後，對資料庫的修改是永久性的。
COMMIT;
# 回滾事務。即撤銷正在進行的所有沒有提交的修改
ROLLBACK;
# 將事務回滾到某個保存點。
ROLLBACK TO [SAVEPOINT]

隱式事務

MySQL中有一個系統變數 autocommit

1 SHOW VARIABLES LIKE 'autocommit';

如果我們想關閉這種自動提交的功能，可以使用下邊兩種方法之一：顯式的的使用 START TRANSACTION 或者 BEGIN 語句開啟一個事務。這樣在本次事務提交或者回滾前會暫時關閉掉自動提交的功能。把系統變數 autocommit 的值設置為 OFF ，就像這樣：

1 SET autocommit = OFF;
2 #或
3 SET autocommit = 0;

隱式提交數據的情況

1、數據定義語言（Data definition language，縮寫為：DDL） 2、隱式使用或修改mysql資料庫中的表 3、事務控制或關於鎖定的語句 ① 當我們在一個事務還沒提交或者回滾時就又使用 START TRANSACTION 或者 BEGIN 語句開啟了另一個事務時，會隱式的提交上一個事務。即： ② 當前的 autocommit 系統變數的值為 OFF ，我們手動把它調為 ON 時，也會隱式的提交前邊語句所屬的事務。 ③ 使用 LOCK TABLES 、 UNLOCK TABLES 等關於鎖定的語句也會隱式的提交前邊語句所屬的事務。 4、載入數據的語句 5、關於MySQL複製的一些語句 6、其它的一些語句

當我們設置 autocommit=0 時，不論是否採用 START TRANSACTION 或者 BEGIN 的方式來開啟事務，都需要用 COMMIT 進行提交，讓事務生效，使用 ROLLBACK 對事務進行回滾。當我們設置 autocommit=1 時，每條 SQL 語句都會自動進行提交。不過這時，如果你採用 START TRANSACTION 或者 BEGIN 的方式來顯式地開啟事務，那麼這個事務只有在 COMMIT 時才會生效，在 ROLLBACK 時才會回滾。

事務隔離級別

問題

訪問相同數據的事務在不保證串列執行（也就是執行完一個再執行另一個）的情況下可能會出現哪些問題：嚴重性來排序：臟寫 > 臟讀 > 不可重覆讀 > 幻讀臟寫（ Dirty Write ）對於兩個事務 Session A、Session B，如果事務Session A 修改了另一個未提交事務Session B 修改過的數據，那就意味著發生了臟寫臟讀（ Dirty Read ）對於兩個事務 Session A、Session B，Session A 讀取了已經被 Session B 更新但還沒有被提交的欄位。之後若 Session B 回滾，Session A 讀取的內容就是臨時且無效的。 Session A和Session B各開啟了一個事務，Session B中的事務先將studentno列為1的記錄的name列更新為'張三'，然後Session A中的事務再去查詢這條studentno為1的記錄，如果讀到列name的值為'張三'，而Session B中的事務稍後進行了回滾，那麼Session A中的事務相當於讀到了一個不存在的數據，這種現象就稱之為臟讀。不可重覆讀（ Non-Repeatable Read ）對於兩個事務Session A、Session B，Session A 讀取了一個欄位，然後 Session B 更新了該欄位。之後Session A 再次讀取同一個欄位，值就不同了。那就意味著發生了不可重覆讀。我們在Session B中提交了幾個隱式事務（註意是隱式事務，意味著語句結束事務就提交了），這些事務都修改了studentno列為1的記錄的列name的值，每次事務提交之後，如果Session A中的事務都可以查看到最新的值，這種現象也被稱之為不可重覆讀。幻讀（ Phantom ）對於兩個事務Session A、Session B, Session A 從一個表中讀取了一個欄位, 然後 Session B 在該表中插入了一些新的行。之後, 如果 Session A 再次讀取同一個表, 就會多出幾行。那就意味著發生了幻讀。 Session A中的事務先根據條件 studentno > 0這個條件查詢表student，得到了name列值為'張三'的記錄；之後Session B中提交了一個隱式事務，該事務向表student中插入了一條新記錄；之後Session A中的事務再根據相同的條件 studentno > 0查詢表student，得到的結果集中包含Session B中的事務新插入的那條記錄，這種現象也被稱之為幻讀。我們把新插入的那些記錄稱之為幻影記錄。

SQL中的四種隔離級別

SQL標準中設立了4個隔離級別： READ UNCOMMITTED ：讀未提交，在該隔離級別，所有事務都可以看到其他未提交事務的執行結果。不能避免臟讀、不可重覆讀、幻讀。 READ COMMITTED ：讀已提交，它滿足了隔離的簡單定義：一個事務只能看見已經提交事務所做的改變。這是大多數資料庫系統的預設隔離級別（但不是MySQL預設的）。可以避免臟讀，但不可重覆讀、幻讀問題仍然存在。 REPEATABLE READ ：可重覆讀，事務A在讀到一條數據之後，此時事務B對該數據進行了修改並提交，那麼事務A再讀該數據，讀到的還是原來的內容。可以避免臟讀、不可重覆讀，但幻讀問題仍然存在。這是MySQL的預設隔離級別。 SERIALIZABLE ：可串列化，確保事務可以從一個表中讀取相同的行。在這個事務持續期間，禁止其他事務對該表執行插入、更新和刪除操作。所有的併發問題都可以避免，但性能十分低下。能避免臟讀、不可重覆讀和幻讀。

隔離級別	臟讀可能性	不可重覆讀可能性	幻讀可能性	加鎖讀
READ UNCOMMITTED	Y	Y	Y	N
READ COMMITTED	N	Y	Y	N
REPEATABLE READ	N	N	Y	N
SERIALIZABLE	N	N	N	Y

臟寫太嚴重了所有事務都解決了。讀未提交沒解決啥事一般不選。讀已提交解決了臟讀。可重覆讀解決了不可重覆讀問題。串列化解決了幻讀問題。

查詢當前預設隔離級別

SELECT @@transaction_isolation;
SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = '隔離級別'
#其中，隔離級別格式：
> READ-UNCOMMITTED
> READ-COMMITTED
> REPEATABLE-READ
> SERIALIZABLE

使用 GLOBAL 關鍵字（在全局範圍影響）當前已經存在的會話無效，只對執行完該語句之後產生的會話起作用。使用 SESSION 關鍵字（在會話範圍影響）對當前會話的所有後續的事務有效。如果在事務之間執行，則對後續的事務有效。該語句可以在已經開啟的事務中間執行，但不會影響當前正在執行的事務。

MySQL事務日誌

事務的隔離性由鎖機制實現。而事務的原子性、一致性和持久性由事務的 redo 日誌和undo 日誌來保證。 REDO LOG 稱為重做日誌，提供再寫入操作，恢復提交事務修改的頁操作，用來保證事務的持久性。 UNDO LOG 稱為回滾日誌，回滾行記錄到某個特定版本，用來保證事務的原子性、一致性。 UNDO 不是 REDO 的逆過程

redo日誌 (重做日誌)

為什麼需要REDO日誌

一方面，緩衝池可以幫助我們消除CPU和磁碟之間的鴻溝，checkpoint機制可以保證數據的最終落盤，然而由於checkpoint 並不是每次變更的時候就觸發的，而是master線程隔一段時間去處理的。所以最壞的情況就是事務提交後，剛寫完緩衝池，資料庫宕機了，那麼這段數據就是丟失的，無法恢復。另一方面，事務包含持久性的特性，就是說對於一個已經提交的事務，在事務提交後即使系統發生了崩潰，這個事務對資料庫中所做的更改也不能丟失。那麼如何保證這個持久性呢？一個簡單的做法：在事務提交完成之前把該事務所修改的所有頁面都刷新到磁碟，但是這個簡單粗暴的做法有些問題另一個解決的思路：我們只是想讓已經提交了的事務對資料庫中數據所做的修改永久生效，即使後來系統崩潰，在重啟後也能把這種修改恢復出來。所以我們其實沒有必要在每次事務提交時就把該事務在記憶體中修改過的全部頁面刷新到磁碟，只需要把修改了哪些東西記錄一下就好。比如，某個事務將系統表空間中第10號頁面中偏移量為 100 處的那個位元組的值 1 改成 2 。我們只需要記錄一下：將第0號表空間的10號頁面的偏移量為100處的值更新為 2 。

好處、特點

1.好處 redo日誌降低了刷盤頻率 redo日誌占用的空間非常小 2. 特點 redo日誌是順序寫入磁碟的事務執行過程中，redo log不斷記錄

redo的組成

重做日誌的緩衝 (redo log buffer) ，保存在記憶體中，是易失的。 show variables like '%innodb_log_buffer_size%'; 重做日誌文件 (redo log file) ，保存在硬碟中，是持久的。

redo的整體流程

第1步：先將原始數據從磁碟中讀入記憶體中來，修改數據的記憶體拷貝第2步：生成一條重做日誌並寫入redo log buffer，記錄的是數據被修改後的值。事務一邊執行，一邊就在記錄了第3步：當事務commit時，將redo log buffer中的內容刷新到 redo log file，對 redo log file採用追加寫的方式第4步：定期將記憶體中修改的數據刷新到磁碟中體會： Write-Ahead Log(預先日誌持久化)：在持久化一個數據頁之前，先將記憶體中相應的日誌頁持久化。

redo log的刷盤策略

針對上面第三步。

redo log的寫入並不是直接寫入磁碟的，InnoDB引擎會在寫redo log的時候先寫redo log buffer，之後以一定的頻率刷入到真正的redo log file 中。這裡的一定頻率怎麼看待呢？這就是我們要說的刷盤策略。註意，redo log buffer刷盤到redo log file的過程並不是真正的刷到磁碟中去，只是刷入到文件系統緩存（page cache）中去（這是現代操作系統為了提高文件寫入效率做的一個優化），真正的寫入會交給系統自己來決定（比如page cache足夠大了）。那麼對於InnoDB來說就存在一個問題，如果交給系統來同步，同樣如果系統宕機，那麼數據也丟失了（雖然整個系統宕機的概率還是比較小的）。針對這種情況，InnoDB給出 innodb_flush_log_at_trx_commit 參數，該參數控制 commit提交事務時，如何將 redo log buffer 中的日誌刷新到 redo log file 中。它支持三種策略：設置為0 ：表示每次事務提交時不進行刷盤操作。（系統預設master thread每隔1s進行一次重做日誌的同步）設置為1 ：表示每次事務提交時都將進行同步，刷盤操作（預設值）設置為2 ：表示每次事務提交時都只把 redo log buffer 內容寫入 page cache，不進行同步。由os自己決定什麼時候同步到磁碟文件。 0的情況下，有可能事務還沒有提交，就被刷盤進磁碟了。也有可能丟失一秒鐘的數據。 1：可靠，效率低

Undo日誌 (回滾日誌)

在事務中更新數據的前置操作其實是要先寫入一個 undo log 。

理解Undo日誌

事務需要保證原子性，也就是事務中的操作要麼全部完成，要麼什麼也不做。但有時候事務執行到一半會出現一些情況，比如：　　情況一：事務執行過程中可能遇到各種錯誤，比如伺服器本身的錯誤，操作系統錯誤，甚至是突然斷電導致的錯誤。　　情況二：程式員可以在事務執行過程中手動輸入 ROLLBACK 語句結束當前事務的執行。以上情況出現，我們需要把數據改回原先的樣子，這個過程稱之為回滾，這樣就可以造成一個假象：這個事務看起來什麼都沒做，所以符合原子性要求。當插入的時候記錄主鍵方便回滾刪除，修改記錄舊值，刪除記錄整條記錄方便回滾插入。 undo日誌也有持久化的要求，也會記錄redo日誌。

Undo日誌的作用

作用1：回滾數據　　不是物理意義上的時間回溯到了事務開始之前，只是邏輯上數據回到開始之前。比如A給B一百塊，回滾就是B把一百還給A，不能是時間回溯到沒有發生這件事。作用2：MVCC 多版本併發控制機制　　當用戶讀取一行數據時，若該記錄已經被其他事務占用，當前事務可以通過undo日誌讀取之前的行版本信息，以此實現非鎖定讀取。

鎖

事務的隔離性由鎖來實現。

對併發操作進行控制，因此產生了鎖。同時鎖機制也為實現MySQL的各個隔離級別提供了保證。鎖衝突也是影響資料庫併發訪問性能的一個重要因素。

事務併發訪問

情況分類

讀-讀、讀-寫、寫-寫

讀讀沒事，寫寫加鎖串列，讀寫複雜。

當前事務是T1，沒有處於等待狀態。

小結幾種說法：不加鎖　　意思就是不需要在記憶體中生成對應的鎖結構，可以直接執行操作。獲取鎖成功，或者加鎖成功　　意思就是在記憶體中生成了對應的鎖結構，而且鎖結構的 is_waiting 屬性為 false ，也就是事務可以繼續執行操作。獲取鎖失敗，或者加鎖失敗，或者沒有獲取到鎖　　意思就是在記憶體中生成了對應的鎖結構，不過鎖結構的 is_waiting 屬性為 true ，也就是事務需要等待，不可以繼續執行操作。一個事務進行讀取操作，另一個進行改動操作。這種情況下可能發生臟讀、不可重覆讀、幻讀的問題。各個資料庫廠商對 SQL標準的支持都可能不一樣。比如MySQL在 REPEATABLE READ 隔離級別上就已經解決了幻讀問題。

解決方案

怎麼解決臟讀、不可重覆讀、幻讀方案一：讀操作利用多版本併發控制（ MVCC ，下章講解），寫操作進行加鎖。　　普通的SELECT語句在READ COMMITTED和REPEATABLE READ隔離級別下會使用到MVCC讀取記錄。　　　　在 READ COMMITTED 隔離級別下，一個事務在執行過程中每次執行SELECT操作時都會生成一個ReadView，ReadView的存在本身就保證了事務不可以讀取到未提交的事務所做的更改，也就是避免了臟讀現象；　　　　在 REPEATABLE READ 隔離級別下，一個事務在執行過程中只有第一次執行SELECT操作才會生成一個ReadView，之後的SELECT操作都復用這個ReadView，這樣也就避免了不可重覆讀和幻讀的問題。方案二：讀、寫操作都採用加鎖的方式。小結對比發現：採用 MVCC 方式的話，讀-寫操作彼此並不衝突，性能更高。採用加鎖方式的話，讀-寫操作彼此需要排隊執行，影響性能。一般情況下我們當然願意採用 MVCC 來解決讀-寫操作併發執行的問題，但是業務在某些特殊情況下，要求必須採用加鎖的方式執行。

MySQL鎖分類

數據操作類型分

讀鎖：也稱為共用鎖、英文用S 表示。針對同一份數據，多個事務的讀操作可以同時進行而不會互相影響，相互不阻塞的。

寫鎖：也稱為排他鎖、英文用X 表示。當前寫操作沒有完成前，它會阻斷其他寫鎖和讀鎖。這樣就能確保在給定的時間里，只有一個事務能執行寫入，並防止其他用戶讀取正在寫入的同一資源。

需要註意的是對於 InnoDB 引擎來說，讀鎖和寫鎖可以加在表上，也可以加在行上。

從數據操作的粒度劃分：表級鎖、頁級鎖、行鎖

本文來自博客園，作者：長壽奉孝，轉載請註明原文鏈接：https://www.cnblogs.com/tyt0o0/p/17767718.html

MySQL的InnoDB引擎的事務