Mysql事物與二階段送出

事務可以是一個非常簡單的SQL構成，也可以是一組複雜的SQL語句構成。事務是通路并且更新資料庫中資料的一個單元，在事務中的操作，要麼都修改，要麼都不做修改，這就是事務的目的，也是事務模型差別于其他模型的重要特征之一。

事務的原子性:原子是不可分割的,事務不可分割(沒有commit資料不能被讀到).

事務的持久性:在commit之後,不能丢資料.(就是在送出後,資料必須落盤redo落盤).

事務的隔離性:在資料庫裡面,各個事務之間不能互相影響.

事務的一緻性:事務前後,不能違反mysql的限制.

原子性是指是事務是不可分割的一部分，一個事務内的任務要麼全部執行成功，要麼全部不執行，不存在執行一部分的情況。

可以将整個取款流程當做原子操作，要麼取款成功，要麼取款失敗。

1. 我們可以使用取錢的例子，來講解這一特性

2. 登入ATM機器

3. 從遠端銀行的資料庫中，擷取賬戶的資訊

4. 使用者在ATM上輸入欲取出的金額

5. 從遠端銀行的資料庫中，更新賬戶資訊

6. ATM機器出款

7. 使用者取錢

整個取錢操作，應該視為原子操作，要麼都做，要麼都不做，不能使用者錢還沒出來，但是銀行卡的錢已經被扣除了。使用事務模型可以保證該操作的一緻性

是指事務的完整性限制沒有被破壞，如果遇到了違反限制的情況，資料庫會被自動復原。一緻性是指資料庫從一種狀态轉變為另一種狀态。在開始事務和結束事務後，資料庫的限制性沒有被破壞。例如，資料庫表中的使用者ID列為唯一性限制，即在表中姓名不能重複.

事物的隔離性要求每個事務的操作，不會受到另一個事務的影響。隔離狀态執行事務,使他們好像是系統在給定時間内執行的唯一操作.這種屬性有時稱為串行化.

事務一旦送出，那麼結果就是持久性的,不會被復原。即使發生當機，資料庫也可以做資料恢複。

重做日志（redo）用來實作事務的持久性，有兩部分組成，一是記憶體中的重做日志，一個是硬碟上的重做日志檔案。innodb是支援事務的存儲引擎，通過日志先行WAL，來實作資料庫的事務的特性，在一個事務送出的時候，并不是直接對記憶體的髒資料進行落盤，而是先把重做日志緩沖中的日志寫入檔案，然後再送出成功。這樣保證了即使在斷電的情況下，依然可以依靠redo log進行資料的恢複與重做。隻要是送出的事務，在redo中就會有記錄，資料庫斷電後重新開機，資料庫會對送出的事務，但是沒有寫入硬碟的髒資料，利用redo來進行重做。

還要一個保證事務的是undo，undo有兩個作用：

1.實作事務的復原

2.實作mvcc的快照讀取

redo是實體邏輯日志，計算頁的實體修改操作.

undo是邏輯記錄，記錄了行的操作内容.

兩階段送出:先寫redo -buffer再寫binlog 并落盤最後落盤redo.

LSN（log sequence number）日志序列号

檢視LSN資訊: show engine innodb status\G;

LSN實際上對應日志檔案的偏移量，新的LSN＝舊的LSN + 寫入的日志大小.

日志檔案重新整理後，LSN不會進行重置

Log sequence number:目前系統LSN最大值,新的事務日志LSN将在此基礎上生成(LSN1+新日志的大小）

Log flushed up to:目前已經寫入日志檔案的LSN

Pages flushed up to:目前最舊的髒頁資料對應的LSN,寫Checkpoint的時候直接将此LSN寫入到日志檔案

Last checkpoint at:目前已經寫入Checkpoint的LSN

redo作用

Redo介紹:

1. DML操作導緻的頁面變化，均需要記錄Redo日志（實體日志）

2. 在頁面修改完成之後，在髒頁刷出磁盤之前，寫入Redo日志;

3. 日志先行（WAL），日志一定比資料頁先寫回磁盤;

4. 聚簇索引/二級索引/Undo頁面修改，均需要記錄Redo日志;

為了管理髒頁，在 Buffer Pool 的每個instance上都維持了一個flush list，flush list 上的 page 按照修改這些page 的LSN号進行排序。是以定期做redo checkpoint點時，選擇的 LSN 總是所有 bp instance 的 flush list 上最老的那個page（擁有最小的LSN）。由于采用WAL的

政策，每次事務送出時需要持久化 redo log 才能保證事務不丢。而延遲刷髒頁則起到了合并多次修改的效果，避免頻繁寫資料檔案造成的性能問題。

REDO的作用:提高性能和做crash recovery

提高性能:

1. 日志用來記錄buffer pool中頁的page修改的，每次資料送出隻要寫redo日志就可以，不需要每次都寫髒頁。

2. 通常一個資料頁是16KB，如果不寫日志，每次的寫入還是16kb，即使修改很少資料，仍然要全部落盤，性能影響非常嚴重。

3. 如果沒有日志，每次都會刷髒頁，髒頁的位置導緻的IO是随機IO，而redo的資料頁的大小是512位元組，這樣非常契合硬碟的塊大小，可以進行順序IO，這樣可以保證順序IO，同時可以大大提高IOPS

做crash recovery:

1. 資料庫重新開機後，利用redo log進行資料庫恢複工作，比對redolog LSN和資料頁的LSN，如果資料頁LSN低于REDO LOG LSN就會進行資料頁執行個體恢複

1. 用于復原事務

2. 保證mvcc多版本高并發控制

innodb把undo分為兩類

1. 新增undo

2. 修改undo

分類依據就是是否需要做purge操作.

insert在事務執行完成後，復原記錄就可以丢掉了。但是對于更新和删除操作而言，在完成事務後，還需要為MVCC提供服務，這些日志就被放到一個history list，用于MVCC以及等待purge。

undo日志的正确性是通過redo來保證的，是以在資料庫恢複的時候，需要先恢複redo，在所有資料塊都保證一緻性的情況下，在進行undo的邏輯操作。

檢查點就是落髒頁的點,本質是一個特殊的lsn.

檢查點解決的問題:

1. 縮短資料庫恢複時間

2. 緩沖池不夠用的時候，重新整理髒頁到磁盤

3. 重做日志不夠用的時候，重新整理髒頁

當資料庫發生當機的時候，資料庫不需要恢複所有的頁面，因為檢查點之前的頁面都已經重新整理回磁盤了。故資料庫隻需要對檢查點以後的日志進行恢複，這就大大減少了恢複時間。

檢查點的類型:

檢查點分為兩種類型，一種是sharp檢查點，一種是fuzzy檢查點

sharp checkpoint落盤條件:

1. 關閉資料庫的時候設定 innodb_fast_shutdown=1，在關閉資料庫的時候，會重新整理所有髒頁到資料庫内。

fuzzy checkpoint在資料庫運作的時候，進行頁面的落盤操作，不過這種模式下，不是全部落盤，而是落盤一部分資料。

Fuzzy落盤的條件:

1. master thread checkpoint： master每一秒或者十秒落盤

2. sync check point： redo 不可用的時候，這時候重新整理到磁盤是從髒頁連結清單中重新整理的。

3. Flush_lru_list check point : 重新整理flush list的時候

落盤的操作是異步的，是以不會阻塞其他事務執行。

檢查點的作用:

縮短資料庫的恢複時間

緩沖池不夠用的時候，将髒頁重新整理到磁盤

重做日志不可用的時候，重新整理髒頁（循環使用redo檔案，當舊的redo要被覆寫的時候，需要重新整理髒頁，造成檢查點）

MySQL二階段送出流程：

事務的送出主要分三個主要步驟：

1.Storage Engine（InnoDB） transaction prepare階段：存儲引擎的準備階段,寫redo-buffer

此時SQL已經成功執行，并生成xid資訊及redo和undo的記憶體日志。

2.Binary log日志送出：寫binlog并落盤.

write()将binary log記憶體日志資料寫入檔案系統緩存。

fsync()将binary log檔案系統緩存日志資料永久寫入磁盤。

3.Storage Engine（InnoDB）内部送出：落盤redo日志.

修改記憶體中事務對應的資訊，并且将日志寫入重做日志緩沖。

調用fsync将確定日志都從重做日志緩沖寫入磁盤。

一旦步驟2中的操作完成，就確定了事務的送出，即使在執行步驟3時資料庫發送了當機。

即binlog落盤成功,就算redo未落盤成功,那麼事務也算是送出成功了.

binlog落盤條件：參數sync_binlog： 0每秒落盤，1每次commit落盤 n 每n個事物落盤

此外需要注意的是，每個步驟都需要進行一次fsync操作才能保證上下兩層資料的一緻性。步驟2的fsync參數由sync_binlog控制，步驟2的fsync由參數innodb_flush_log_at_trx_commit控制。(雙1配置)

兩階段送出:先寫redo -buffer再寫binlog 并落盤最後落盤redo-buffer.

最終:mysql在落盤日志的時候,先落盤binlog,再落盤redo.

當事務在binlog階段crash，此時日志還沒有成功寫入到磁盤中，啟動時會rollback此事務。

當事務在binlog日志已經fsync()到磁盤後crash，但是InnoDB沒有來得及commit，此時MySQL資料庫recovery的時候将會從二進制日志的Xid（MySQL資料庫内部分布式事務XA）中擷取送出的資訊重新将該事務重做并commit使存儲引擎和二進制日志始終保持一緻。

總結起來說就是如果一個事物在prepare log階段中落盤成功，并在MySQL Server層中的binlog也寫入成功，那這個事務必定commit成功。

隔離級别事物問題縮寫

READ UNCOMMITED 未送出讀髒讀 RU

READ COMMITED 送出讀幻讀 RC

REPEATEABLE READ 可重複讀 RR

SERIALIZABLE 序列化

Mysql會自動送出 (mysql在mysql用戶端是自動送出,但是java python裡面不自動送出)

取消自動送出: set autocommit=off;

show variables like '%iso%';

設定全局參數:

set global transaction isolation level read uncommitted;

Set global transaction isolation level read committed;

Set global transaction isolation level repeatable read;

設定會話級别參數:

set session transaction isolation level read uncommitted;

Set session transaction isolation level read committed;

Set session transaction isolation level repeatable read;

檢視全局的MySQL GLOBAL的配置的隔離級别。

global參數設定後，需要新開啟session才能生效.

RU --------- 産生髒讀問題

RC ---------- 解決髒讀問題,但産生幻讀和不可重複讀問題.

RR --------- 避免幻讀和不可重複讀問題.,待容易鎖等待.

SERIALIZABLE -----------序列化,串行讀寫.

在 REPEATEABLE READ下，其他事務對于資料的修改（update，delete）不會影響本事務對于資料的讀取，會避免幻讀的産生，幻讀就是在一個事務内，讀取到了不同的資料行數結果。

資料越安全，相對來說，資料庫的并發能力越弱（并不代表總體性能越弱）。

髒讀(Drity Read)：事務T1修改了一行資料，事務T2在事務T1送出之前讀到了該行資料。

不可重複讀(Non-repeatable read): 事務T1讀取了一行資料。事務T2接着修改或者删除了該行資料，當T1再次讀取同一行資料的時候，讀到的資料時修改之後的或者發現已經被删除。(針對update/delete操作).

在READ COMMITED下，未被送出的事務不會被讀到，隻有被送出的事務的資料，才會被讀取到。（執行兩次相同的SQL得到了不同的結果。),這就造成了幻讀和不可重複讀問題.

幻讀(Phantom Read): 事務T1讀取了滿足某條件的一個資料集，事務T2插入了一行或者多行資料滿足了T1的選擇條件，導緻事務T1再次使用同樣的選擇條件讀取的時候，得到了比第一次讀取更多的資料集。(針對insert操作).

幻讀和可重複讀的差別:

幻讀更多的是針對于insert來說，即在一個事務之中，先後的兩次select查詢到了新的資料，新的資料來自于另一個事務的insert，一般稱之為幻讀，通過gap_lock(間隙鎖)來防止産生幻讀（雖然record可以避免資料行被修改，但是卻無法阻止insert，gap_lock鎖定索引間隙，防止了在事務查詢的範圍内的insert情況）

在ru隔離級别下造成髒讀,在rc隔離級别下造成幻讀和不可重複讀.

可重複讀:一般是針對于update和delete來說，可重複讀采用了mvcc多版本控制來實作資料查詢結果本身的不變。

1.ru(READ-UNCOMMITED 未送出讀)

Ru級别造成了髒讀: session2可以讀取到session1的沒有送出的事務的資料(記憶體中沒有送出的髒頁).

髒讀的發生至少在RU下，而目前幾乎所有的資料庫幾乎都在RC級以上的隔離界别上。

髒讀執行個體:

兩個session修改隔離級别: set session transaction isolation level read uncommitted;

session1 session2

begin; begin;

insert into t1 values(null,'testru')

select * from test1.t1;#查詢到了testru資料,造成了髒讀

commit; commit;

########################################################

2.rc(read committed 已送出讀)

Rc級别解決了髒讀.但會造成不可重複讀和幻讀.兩者發生方式一樣,但由于解決方法不同而區分.

解決了髒讀執行個體:(針對select.)

修改隔離級别: Set session transaction isolation level read committed;

session1 session2

begin; begin;

insert into test1.t1 values(null,'testrc');

select * from test1.t1;#沒有查到

commit;

select * from test1.t1; 查詢到了

commit;

################################################################

不可重複讀:針對于update/delete來說.

幻讀:針對于insert來說的.

幻讀執行個體: Set session transaction isolation level read committed;

#####################################################

session1 session2

begin; begin;

select * from test1.t1;--7條

insert into t1 values()

commit;

select * from test1.t1 --8

commit;

不可重複讀執行個體: Set session transaction isolation level read committed;

select * from test1.t1 where name=’aa’;--找到1條

Update t1 set name=’bb’ where name=’aa’;

select * from test1.t1 where name=’aa’ --沒有找到.

commit;

###################################################################

加鎖執行個體1:(加鎖隻針對指定的行,不影響update/insert操作)

session1 session2

begin; begin;

update test1.t1 set name='bbb' ;

insert into test1.t1 values(null,'test_rr') 加了鎖,但插入成功.

select * from test1.t1 1000w+1

Commit;

#############################################################################

3.rr(repeatable read 可重複讀)

RR隔離級别:可重複讀 .repeatable read

在RR模式下GAP_LOCK是預設開啟的.

避免幻讀執行個體:(insert)

set session transaction isolation level repeatable read;

session1 session2

begin; begin;

select * from test1.t1;

insert into t1 values(null,'testrr');

select * from test1.t1;查詢不到testrr

commit;

select * from test1.t1;查詢到testrr

####################################################

避免不可重複讀執行個體:(update /delete)

session1 session2

select * from test1.t1;

update test1.t1 set name='testrr_upd' where name='testrr'

select * from test1.t1;查詢不到testrr_upd

commit;

select * from test1.t1;查詢到testrr_upd

鎖等待執行個體:(一定會鎖,update後,除了select,其他操作如insert/update都會被鎖.)

session1 session2

begin; begin;

update test1.t1 set name='ccc';

insert into test1.t1 values(null,'test_rr')插入被阻塞,進入鎖等待狀态

commit;

select * from test1.t1 1000w+1

MVCC在MySQL的InnoDB中的實作原理: 基于UNDO的多版本快照日志

MySQL InnoDB存儲引擎，實作的是基于多版本的并發控制協定——MVCC (Multi-Version Concurrency Control)

(注：與MVCC相對的，是基于鎖的并發控制，Lock-Based Concurrency Control)。

1.MVCC的好處:讀不加鎖,讀寫不沖突.

讀不加鎖，讀寫不沖突。在讀多寫少的OLTP應用中，讀寫不沖突是非常重要的，極大的增加了系統的并發性能，這也是為什麼現階段，幾乎所有的RDBMS，都支援了MVCC。

2.讀的類型:快照讀和目前讀.

在MVCC并發控制中，讀操作可以分成兩類：快照讀 (snapshot read)與目前讀 (current read)。

快照讀: 讀取的是記錄的可見版本 (有可能是曆史版本)，不用加鎖。例如 select 就為快照讀.

目前讀:讀取的是記錄的最新版本，并且，目前讀傳回的記錄，都會加上鎖，保證其他事務不會再并發修改這條記錄。例如删除/更新/删除操作。

過程: 在MVCC讀取資料的過程中，會先對目前的事務和資料行的事務号進行對比，如果發現事務行的事務版本号，已經增長，則說明該行資料已經被其他事務修改，那麼就需要根據undo，讀取undo内的曆史版本的相關邏輯操作資訊，然後根據邏輯操作資訊建構符合目前查詢的資料版本，然後傳回結果集（在RC和RR模式下，對于UNDO建構資料塊的版本選擇不同）

通過MVCC，雖然每行記錄都需要額外的存儲空間，更多的行檢查工作以及一些額外的維護工作，但可以減少鎖的使用，大多數讀操作都不用加鎖，讀資料操作很簡單，性能很好，并且也能保證隻會讀取到符合标準的行，也隻鎖住必要行。

3.一緻性非鎖定讀

一緻性的非鎖定讀就是INNODB存儲引擎通過行多版本控制的方式來讀取目前執行時間資料庫中的資料。如果讀取的行正在執行DELETE或者UPDATE，這個時候讀取操作不會等待所在行的鎖的釋放。INNODB這個時候會讀取一個快照資料。（若讀的資料加鎖了，則讀取其前一個版本的undo日志。）

一緻性非鎖定讀取的原理是這樣的：

Innodb通過隐藏的復原指針儲存前一個版本的undo日志，通過目前記錄加上undo日志可以構造出記錄的前一個版本，進而實作同一記錄的多個版本。

快照資料就是目前資料行的曆史版本，每個行記錄可能有多個版本

在RC模式下，MVCC會一直讀取最新的快照資料。

在RR模式下，MVCC會讀取本事務開始時候的快照資料。

對于一緻性非鎖定讀取，即使被讀取的行已經SELECT ⋯ FOR UPDATE，也是可以進行讀取的。

4.一緻性鎖定讀取

有些使用者需要采用鎖定讀取的方式來進行讀取保證資料的一緻性。

手動在查詢中添加鎖

查詢中使用S鎖:

select * from test1.t1 where id=1 lock in share mode;

查詢中添加X鎖:

select * from test1.t1 where id=1 for update;

關于鎖等待的參數：參數支援範圍為Session和Global,并且支援動态修改

事務等待擷取資源等待的最長時間，超過這個時間還未配置設定到資源則會傳回應用失敗。

設定鎖等待時間參數: innodb_lock_wait_timeout 30 --機關秒.

Mysql鎖加在索引上.

鎖的作用:将并行的事務變成串行的.

從鎖的顆粒度來說,鎖分:表鎖, 頁鎖, 行鎖。

MySQL中鎖的概念可以等同于：并發控制，序列化，隔離性.

這種用隔離性來描述鎖，就是因為是事務ACID特性中的I，而鎖就是用來實作事務一緻性和隔離性的一種常用技術。

當資料庫事務并發各自運作的時候，每個事務的運作不受到其他事務的影響。

簡單的加鎖技術就是對對象加上一個鎖，若通路該事務的時候，發現已經有鎖，則等待該事務鎖的釋放。

通過多粒度鎖定，保證了資料庫中事務的并發性。

1.意向鎖

意向鎖:打算向這個表裡的資料加鎖,會提前在表級别加一個意向鎖,加在聚簇索引的根節點.

1. 揭示下一層級請求的鎖的類型

2. IS:事物想要獲得一張表中某幾行的共享鎖

3. IX:事物想要獲得一張表中某幾行的排他鎖

4. InnoDB存儲引擎中意向鎖都是表鎖

假如此時有事物tx1 需要在記錄A 上進行加 X鎖 :

1. 在該記錄所在的資料庫上加一把意向鎖IX 2. 在該記錄所在的表上加一把意向鎖IX

3. 在該記錄所在的頁上加一把意向鎖IX

4. 最後在該記錄A上加上一把X鎖

假如此時有事物tx2 需要對記錄B (假設和記錄A在同一個頁中)加 S鎖 :

1. 在該記錄所在的資料庫上加一把意向鎖IS 2. 在該記錄所在的表上加一把意向鎖IS

3. 在該記錄所在的頁上加一把意向鎖IS

4. 最後在該記錄B上加上一把S鎖

加鎖是從上往下,一層一層進行加的.

意向鎖存在意義:

· 意向鎖是為了實作多粒度的鎖，表示在資料庫中不但能實作行級别的鎖，還可以實作頁級别的鎖，表級别的鎖以及資料庫級别的鎖

· 如果沒有意向鎖，當你去鎖一張表的時候，你就需要對表下的所有記錄都進行加鎖操作，且對其他事物剛剛插入的記錄(遊标已經掃過的範圍)就沒法在上面加鎖了，此時就沒有實作鎖表的功能。

IX，IS是表級鎖，不會和行級的X，S鎖發生沖突。隻會和表級的X，S發生沖突，行級别的X和S按照普通的共享、排他規則即可。是以隻要寫操作不是同一行，就不會發生沖突。

InnoDB 沒有資料庫級别的鎖，也沒有頁級别的鎖(InnoDB隻能在表和記錄上加鎖)，是以InnoDB的意向鎖隻能加在表上，即InnoDB存儲引擎中意向鎖都是表鎖.

2.行鎖(X和S鎖)

對于行鎖，根據其作用類型，可以分為兩類：

共享鎖(S lock) ,允許讀取一個資料，同時允許其他事務對該事務進行更改。

排他鎖(X lock),允許删除或者更新一條資料，同時不允許其他事務對該事務進行操作。

鎖相容:當一行擷取S鎖的時候，也可以擷取另一個事務的S鎖，這稱之為鎖相容.

Mysql事物與二階段送出

1.事務的四種特性(ACID)

2.一緻性(Consistency)

3.隔離性(isolation)

4.持久性(Durability)

4.事務隔離級别對應問題:(髒讀,幻讀,不可重複讀)

1.ru(READ-UNCOMMITED 未送出讀)

2.rc(read committed 已送出讀)

3.rr(repeatable read 可重複讀)

5.MVCC的簡單實作

1.MVCC的好處:讀不加鎖,讀寫不沖突.

2.讀的類型:快照讀和目前讀.

3.一緻性非鎖定讀

4.一緻性鎖定讀取

鎖

1.意向鎖

2.行鎖(X和S鎖)