數據庫必備知識-了解Mysql事務

什么是事務

事務(Transaction)是訪問和更新數據的執行單元。事務中包含有個或者多個sql語句,要么都執行,要么都不執行。

sql語句的執行順序

from
join
on
where
group by(開始使用select中的別名,后面的語句都可以使用)
avg,sum
having
select
distinct
order by
limit

MySQL的邏輯架構

MySQL數據庫主要分兩個層級:服務層和存儲引擎層

• 服務層:包含連接器,查詢緩存,分析器,優化器,執行器。大多數核心功能和所有的跨存儲引擎的功能也在這一層實現,包括存儲過程,觸發器,視圖。
• 存儲引擎層:包含了MySQL常見的存儲引擎,包括MyISAM,InnoDB和Memory等,最常用的是InnoDB,也是MySQL默認的存儲引擎。存儲引擎在建表的時候也可以指定。

> create table rumenz(id int primary key auto_increment)engine=innodb;

SQL語句的執行過程

• 連接器:從MySQL客戶端登錄,需要連接器來連接用戶和MySQL數據庫,mysql -uroot -p123456進行MySQL登錄,在完成TCP握手之后,連接器會根據輸入的用戶名和密碼驗證身份,若錯誤會提示:Access denied for user,若賬號密碼正確,MySQL會根據權限表中的記錄來判定權限。
• 查詢緩存:MySQL在收到一個請求后,會先去緩存中查找,是否執行過這條SQL語句,之前執行過的SQL語句結果會以key-value的形式直接存儲到緩存中,key是查詢的語句,value是查詢結果集,如果能通過SQL語句直接查詢到緩存中的結果集,則直接返回。優點是效率高,但是不建議使用,原因是在MySQL中對表進行了更新操作,緩存就失效了,在頻繁更新的系統中,緩存的命中率并不高。在MySQL8.0中查詢緩存功能就被刪除了,不存在查詢緩存的功能了。
• 分析器:分為詞法分析和語法分析
• 詞法分析:MySQL會解析sql語句,分詞器會先做詞法分析,SQL語句一般由字符串和空格組成,MySQL要識別出字符串代表什么。
• 語法分析:根據詞法分析的結果,按照語法規則看看SQL語句是否正確。如果SQL語句語法不正確,就提示:You have an error in your SQL syntax
• 優化器:SQL語句語法正確后,優化器會判斷你使用了哪種索引,哪種鏈接,優化器的作用就是確定效率最高的執行方案。
• 執行器:在執行階段,MySQL會判斷有沒有執行語句的權限,若沒有權限,會返回沒有權限的錯誤,若有權限,則打開表繼續執行。打開表時,執行器會根據你使用的存儲引擎,去使用該引擎對接的接口。

MySQL執行流程

提交和回滾

MySQL事務是如下操作的

begin; -- 或者start transaction
-- 1條或者n條sql語句
commit;

begin或start transaction都是手動開啟一個事務。commit是手動提交一個事務,將執行結果寫入數據庫,如果這個過程中出現錯誤會調用rollback,回滾所有已經執行成功的sql。當然也可以在事務中直接使用rollback語句進行回滾。

自動提交

MySQL默認采用的是自動提交

> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+

在自動提交的模式下,如果沒有顯示的start transaction或begin開啟一個事務,那么每個sql語句都會被當做一個事務執行提交操作。通過如下方式,可以關閉autocommit,需要注意的是,autocommit是針對連接的,在一個連接中修改了此參數,不會影響其他的連接。

> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%autocommit%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

如果關閉了autocommit,所有的sql語句都在一個事務中,直到執行了commit或rollback,該事務結束,并且開啟了下一個事務。

特殊操作

在MySQL中,存在一些特殊的命令,如果在事務中執行了這些命令,會馬上強制執行commit提交事務;如DDL語句(create table/drop table/alter table)、lock tables語句等等。

DML語句(insert,delete,update,select)等都不會強制提交事務。

ACID特性

ACID是衡量事務的四個特性

• 原子性(Atomicity,或稱為不可分割性)
• 一致性(Consistency)
• 隔離性(Isolation)
• 持久性(Durability)

按照嚴格標準,只有同時滿足ACID才是事務;但是在各大數據庫廠商實現中,完全滿足ACID的少之又少,例如MySQL的NDB Cluster事務不滿足持久性和隔離性；InnoDB默認事務隔離級別是可重復讀，不滿足隔離性；Oracle默認的事務隔離級別為READ COMMITTED，不滿足隔離性……因此與其說ACID是事務必須滿足的條件，不如說它們是衡量事務的四個維度。

原子性

原子性是指一個事務中一個不可分割的工作單位,其中的操作要么都做,要么都不做,如果事務中的一個sql語句執行失敗,則已執行的sql語句也必須都要回滾,數據庫回到事務前的狀態。

實現原理undo log

MySQL的日志分很多種,如二進制日志,錯誤日志,慢查詢日志,查詢日志,另外InnoDB存儲引擎還有兩種事務日志,redo log(重做日志),undo log(回滾日志),其中redo log用于保證事務持久性,undo log則是事務原子性和隔離性的基礎。

undo log 是原子性的關鍵,當事務回滾時,能撤銷所有已經成功執行的sql語句。InnoDB能實現回滾就是靠undo log:當事務對數據庫進行修改時,InnoDB會生成對應的undo log;如果事務執行失敗或者進行了回滾(rollback),導致事務回滾,便可以利用undo log中的信息將數據恢復到回滾前的樣子。

undo log屬于邏輯日志,它記錄的是sql執行相關的信息。當發生回滾時,InnoDB會根據undo log做相反的事情,對于每個insert,回滾做delete;對于每個delete,回滾做insert;對于update,回滾會執行一個相反的update,把數據改回去。

以update為例:當事務執行update時,其中生成的undo log會包含被修改數據的主鍵(以便知道修改了哪些行),修改了哪些列,這些列在修改前后的值信息,回滾時便可以利用這些信息將數據恢復到update之前的狀態。

redo log

redo log和undo log都是InnoDB的事務日志. InnoDB作為MySQL的存儲引擎,數據是永久存放在磁盤中的,但是每次讀寫數據都要磁盤IO,效率會很低。為此,InnoDB提供了緩存(Buffer Pool),Buffer Pool中包含了磁盤中部分數據的映射,作為數據庫訪問的緩沖,當需要從數據庫讀取數據時,會先從Buffer Pool中讀取,如果Buffer Pool中沒有,則會從磁盤中讀上來并放到Buffer Pool,當向數據庫寫入數據時,會首先寫入Buffer Pool,Buffer Pool中修改的數據會被定期刷新到磁盤中去(這一過程被稱為臟讀) Buffer Pool極大的提高了數據的讀寫效率,但是也帶來了新的問題,如果MySQL宕機,而此時Buffer Pool中被修改的數據還沒有被刷新到磁盤,就會導致數據丟失,就無法保證數據的持久性。

為了解決上面的問題,redo log就出現了,當數據被修改時,除了修改Buffer Pool中的數據,還會在redo log日志中記錄這次操作,當事務提交時,會調用fsync接口對redo log進行刷盤。如果MySQL宕機,重啟時可以讀取redo log對數據庫數據進行恢復。redo log采用的是預寫入模式(WAL),所以修改先寫入redo log,然后更新到Buffer Pool,保證數據不會因為MySQL宕機而丟失,從而滿足持久性的要求。

redo log 也需要在事務提交的時候將日志寫入磁盤,為什么比直接將Buffer Pool中修改的數據寫入磁盤(刷臟)要快？

• 刷臟是隨機IO,每次更新數據位置是隨機的,但寫redo log是追加操作,屬于順序IO。
• 刷臟是以數據頁為單位的,MySQL默認頁的大小是16KB,一個Page上修改很少的數據也要整頁寫入,而redo log中只包含真正需要寫入的數據,無效IO大大減小。

SQL語句的日志記錄

日志記錄用到的WAL技術,全稱Write-Ahead-loggingredo log是InnoDB引擎中的日志模塊,只有InnoDB有,它是物理日志,記錄這個數據頁做了什么改動。

redo log日志文件是固定大小的,比如可以配置為一組4個文件，每個文件大小是1GB，那么這塊日志就可以記錄4GB的內容，可以理解為一個環形結構，有一個write pos標識當前記錄的位置，一邊寫入一邊后移，有一個check point記錄當前要擦除的位置（當然擦除之前要寫入數據文件中），也是往后推移，并且循環的。當write pos追上 check point的時候表示日志已經滿了， 當前需要停下來先擦除一些記錄，存到數據文件中，為需要寫入的日志騰出空間。

有了redo log,InnoDB就能保證數據庫發生異常重啟,之前提交的記錄也不會丟失,這個能力叫做Crash-safe

redo log和binlog的區別

在MySQL中還存在binlog(二進制日志)也可以記錄寫操作并用于數據恢復,但是二者有根本的不同.

• 作用不同,redo log是用于crash recovery,保證MySQL宕機也不會影響持久性;binlog是用于point-in-time recovery,保證服務器可以基于時間點恢復數據,此外binlog還用于主從復制.
• 層次不同,redo log是InnoDB存儲引擎實現的,而binlog是MySQL的服務器層實現的,同時支持InnoDB和其它存儲引擎.
• 內容不同,redo log是物理日志,內容基于磁盤的Page,binlog是二進制的,根據binlog_format參數的不同,可以是基于sql語句,基于數據本身或者二者結合.
• 寫入時機不同,binlog日志提交時寫入,redo log寫入時機相對比較多.

當事務提交時會調用fsync對redo log進行刷盤,這是默認情況下的策略,修改innodb_flush_log_at_trx_commit參數可以修改此策略,但事物的持久性無法得到保證 除了事務提交還有其它的刷盤時機,如master thread 每秒刷盤一次redo log,好處是不一定要等到commit才刷盤,commit速度會大大加快.

隔離性

原子性和持久性研究的是一個事務本身,而隔離性研究的是不同事務之間的影響。隔離性是指事務內部的操作和其它事務是隔離的,并發執行的各個事務不能相互影響,嚴格的隔離性,對應的事務隔離級別為Serializable (可串行化),但是實際應用中出于性能方面的考慮很少會使用可串行化。

隔離性追求的是并發情況下事務之間不相互影響,我們這里只考慮讀操作和寫操作。

• 一個事務寫對另一個事務寫操作的影響,鎖機制保證隔離性
• 一個事務寫對另外一個事務讀操作的影響,MVCC保證隔離性

鎖機制

兩個事務寫操作之間的相互影響。隔離性要求同一時刻只能有一個事務對數據庫進行寫操作。InnoDB通過鎖機制來保證這一點。

鎖機制的基本原理:事務在修改數據之前,首先要獲得相應的鎖;或者鎖之后,事務可以修改數據;該事務操作期間,這部分數據是被鎖定的,其它事務如果想修改數據,只有等當前事務提交或者回滾后釋放鎖。

行鎖和表鎖

按照鎖的顆粒度可以劃分為:行鎖和表鎖。表鎖在操作數據時會鎖定整張表,并發性較差,行鎖只鎖定需要操作的數據,并發性好。但是加鎖本身要消耗資源(獲得鎖,檢查鎖,釋放鎖都要消耗系統資源),因此在鎖定數據較多的情況下可以適用表鎖可以節省大量的資源。MySQL中不同存儲引擎支持的鎖是不一樣的,例如:MyISAM只支持表鎖,而InnoDB即支持表鎖也支持行鎖,處于性能考慮,絕大多數情況下使用的都是行鎖。

查看InnoDB中鎖的情況

> select * from information_schema.innodb_locks; #鎖的概況
> show engine innodb status; #InnoDB整體狀態，其中包括鎖的情況

select * from information_schema.innodb_locks;

session3查詢結果

image-20210712151333584

• lock_type為RECORD，代表鎖為行鎖(記錄鎖)
• lock_mode為X，代表排它鎖(寫鎖)

臟讀,不可重復讀,幻讀

臟讀

當前事務可以讀到其它事務未提交的數據(臟數據),這種現象是臟讀。

提交事務

不可重復讀:在同一個事務中兩次讀取同一個數據不一樣,這種現象被稱為不可重復讀。臟讀可不可重復讀的區別在于,臟讀讀到的是其它事務未提價的數據,而不可重復讀讀到的是其它事務已經提價的數據

幻讀

在一個事務中兩次查詢查到的數據條數不同,這種現象被稱為幻讀。不可重復讀和幻讀的區別在于,不可重復度是數據變了,幻讀是數據行數變了。

事務隔離級別

SQL標準中定義了4種隔離級別,并規定每種隔離級別的特點。一般來說隔離級別越低,系統開銷就越低,可支持并發越高,但隔離性也越差。

隔離級別臟讀不可重復讀幻讀Read Uncommitted讀未提交可能可能可能Read committed讀已提交不可能可能可能Repeatable Read可重復讀不可能不可能可能Serializable可串行化不可能不可能不可能

在實際使用過程中,讀未提交并發會導致很多的問題,而且性能相對其他隔離級別提高有限,因此很少使用。可串行化,并發效率很低,只有當對數據一致性要求極高且可以接受沒有并發時使用，因此使用也較少。

在大多數數據庫系統中，默認的隔離級別是讀已提交(如Oracle)或可重復讀（后文簡稱RR）,InnoDB默認的隔離級別是RR。

查看MySQL當前的提交級別

mysql> show variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)

MVCC

RR解決臟讀, 不可重復讀,幻讀等問題,使用的是MVCC,MVCC全稱Multi-Version Concurrency Control，即多版本的并發控制協議。下面的例子很好的體現了MVCC的特點：在同一時刻，不同的事務讀取到的數據可能是不同的(即多版本)——在T5時刻，事務A和事務C可以讀取到不同版本的數據。

查詢rumenz的余額是200

MVCC最大的優點就是讀不加鎖,因此讀寫不沖突,并發性好。InnoDB實現MVCC,多個版本的數據就可以共存。用到的技術和數據結構。

• 隱藏列:InnoDB每行都有隱藏列,隱藏列中包含了本行數據的事務ID, 指向undo log的指針等。
• 基于undo log的版本鏈,每條數據都會包含指向undo log的指針,而每條undo log也會指向更早版本的undo log,從而形成一個版本鏈。
• ReadView通過隱藏列和版本鏈,MySQL可以將數據恢復到指定版本;但是具體要恢復到哪個版本,則需要根據ReadView來確定。所謂ReadView,是指事務(事務A)在某一時刻給整個事務系統(trx_sys)打快照,之后進行讀操作的時候,會將讀到數據中的事務ID與(trx_sys)快照比較,從而判斷數據對該ReadView可見,即事務A是否可見。

trx_sys主要內容

• low_limit_id表示生成ReadView系統應該分給下一個事務的ID。如果事務的ID大于等于low_limit_id,則對該ReadVIew不可見。
• up_limit_id表示生成ReadView時當前系統中活躍的讀寫事務中最小的事務ID,如果數據的事務ID小于up_limit_id,則對該ReadView可見。
• rw_trx_ids表示生成ReadView時當前系統中活躍讀寫事務的事務ID列表,如果數據的事務ID在low_limit_id和up_limit_id之間,則需要判斷事務ID在不在rw_trx_ids中,如果在則說明生成ReadView時事務仍然在活躍中,因此數據對ReadView不可見,如果不在說明生成ReadView時,事務已經提交了。

臟讀

提交事務

當事務A在T3時刻讀取rumenz余額時,會生成ReadView由于此時事務B沒有提交仍然活躍,因此其事務ID一定在ReadView的rw_trx_ids中,根據上面的規則,事務B的修改對ReadView不可見。接下來,事務A根據指針指向undo log查詢上一版本的數據,得到rumenz的余額為100,這樣事務A就避免了臟讀。

不可重復讀

當事務A在T2時刻讀取rumenz的余額前會生成一個ReadView,此時事務B分兩種情況討論，一種是如圖中所示，事務已經開始已經提交，此時其事務id在ReadView的rw_trx_ids中；一種是事務B還沒有開始，此時其事務id大于等于ReadView的low_limit_id。無論是哪種情況，根據前面介紹的規則，事務B的修改對ReadView都不可見。

當事務A在T5時刻再次讀取rumenz的余額時，會根據T2時刻生成的ReadView對數據的可見性進行判斷，從而判斷出事務B的修改不可見；因此事務A根據指針指向的undo log查詢上一版本的數據，得到rumenz的余額為100，從而避免了不可重復讀。

臟讀

MVCC避免幻讀的機制與避免不可重復讀非常類似。

當事務A在T2時刻讀取0

當事務A在T5時刻再次讀取0

RC 與 RR的區別

RC與RR一樣,都使用了MVCC

• RR在事務開始的時候后第一次執行select前就創建ReadView,直到事物提交都不會再創建。RR可以解決臟度,不可重復讀,幻讀
• RC每次執行select都會創建一個ReadView,因此如果事物A第一次select之后,事務B對數據進行了修改并提交,那么事務A第二次select時會重新建立ReadView,因此事務B的修改對事務A可見的。因此RC隔離級別可以避免臟讀,但是無法避免不可重復讀和幻讀。

加鎖讀和非加鎖讀

按照是否加鎖，MySQL的讀可以分為兩種,一種是非加鎖讀，也稱作快照讀、一致性讀，使用普通的select語句，這種情況下使用MVCC避免了臟讀、不可重復讀、幻讀，保證了隔離性。

#共享鎖讀取
select...lock in share mode
#排它鎖讀取
select...for update

加鎖讀在查詢時會對查詢的數據加鎖（共享鎖或排它鎖）。由于鎖的特性，當某事務對數據進行加鎖讀后，其他事務無法對數據進行寫操作，因此可以避免臟讀和不可重復讀。而避免幻讀，則需要通過next-key lock。next-key lock是行鎖的一種，實現相當于record lock(記錄鎖) + gap lock(間隙鎖)；其特點是不僅會鎖住記錄本身(record lock的功能)，還會鎖定一個范圍(gap lock的功能)。因此，加鎖讀同樣可以避免臟讀、不可重復讀和幻讀，保證隔離性。

總結

InnoDB實現的RR,通過鎖機制(包含netxt-key lock),MVCC(包含隱藏數據列,基于undo log的版本鏈,ReadView)等,實現了一定程度的隔離性,可以滿足大多數場景的需要。

RR雖然避免了幻讀問題，但是畢竟不是Serializable，不能保證完全的隔離

如果在事務中第一次讀取采用非加鎖讀，第二次讀取采用加鎖讀，則如果在兩次讀取之間數據發生了變化，兩次讀取到的結果不一樣，因為加鎖讀時不會采用MVCC。

出現了幻讀問題

RR模式下 日志中記錄的是：先commit的記錄在前面，后commit的記錄在后面，與執行時間點無關；就單個 session 來說，好理解，執行順序就是記錄順序；多個 session 之間的話，先 commit 的先記錄主庫對數據庫的更改是按執行時間的先后順序進行的，而 binlog 卻是按 commit 的先后順序記錄的。