MySQL(InnoDB剖析):42---事務之（事務控制語句：begin、commit、rollback、savepoint、transaction）

一、事務的自動送出

• 預設情況下，沒有使用begin顯式開啟事務，事務都是自動送出的

autocommit變量

• 該變量用于控制SQL語句是否自動送出（auto commit）
• 預設值為1，表示自動送出
• 設定為0後，需要自己手動使用commit或rollback來結束事務

-- SQL語句自動送出（預設值）

set autocommit=1;

-- SQL語句不自動送出

set autocommit=0;

示範案例

• 建立一個表格t

drop table if exists t;

create table t(

a int,

primary key(a)

)engine=innodb;

• 設定autocommit為0，向表格中插入一條語句并查詢

set autocommit=0;

insert into t select 1;

select * from t;

• 此時進行復原，再次查詢表格，可以看到語句沒有插入成功（因為autocommit為0，每次執行都屬于一個事務）

rollback;

select * from t;

• 復原之後又開啟了一個新的事務（是以類推）

二、事務控制語句

• 顯式開啟一個事務

start transaction;

begin;      

• 送出事務，對事務中所做的修改進行永久性的儲存

-- 兩者的差別見下
commit;

commit work;      

• 復原事務，撤銷所有的事務并結束本次事務

-- 兩者的差別見下
rollback;

rollback work;      

• 設定儲存點

savepoint point_name;      

• 删除儲存點，如果删除一個不存在的儲存點會抛出異常

release savepoint point_name;      

• 復原至某個儲存點，從目前到該儲存點之間的事務會撤銷，儲存點之前的事務不撤銷

rollback to savepoint point_name;      

• 設定事務隔離級别，可以設定的隔離級别有：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

set transaction xxx;      

存儲過程的事務開啟

• 存儲過程中隻能使用start transaction開起來一個事務，因為begin在存儲過程中會被認為一對“begin...end”來使用

TRUNACTE TABLE指令不可以復原

• TRUNCATE TABLE清空表的語句不能進行復原

示範案例

• 建立一個表格t，并插入兩行資料

drop table if exists t;

create table t(

a int,

primary key(a)

)engine=innodb;

insert into t select 1;

insert into t select 2;

• 開啟一個事務，然後清空表，并復原事務

begin;

truncate table t;

rollback;

• 查詢表資料為空，是以TRUNCATE TABLE指令復原也無效

select * from t;

三、鍊事務（commit與commit work、rollback與rollback work）

• commit與commit work都是用來送出一個事務
• 但是commit work可以用來控制事務結束後的行為是CHAIN還是RELEASE的：

• 如果是CHAIN，那麼事務就變成了鍊事務（見下面的completion_type參數）

completion_type參數

• 該參數為0（NO_CHAIN）：此時COMMIT和COMMIT WORK都是相同的
• 設定為1（CHAIN）後：COMMIT WORK等同于COMMIT AND CHAIN，後面會立馬開啟一個相同隔離級别的事務（鍊事務）
• 設定為2（RELEASE）後：COMMIT WORK等同于COMMIT AND RELEASE，在事務送出後會自動斷開與伺服器的連接配接并重新開啟一個會話

completion_type為1的示範案例

• 建立一個表格，将completion_type設定為1（表示開啟鍊事務）

drop table if exists t;

create table t(

a int,

primary key(a)

)engine=innodb;

set @@completion_type=1;

• 此時開啟一個事務，然後向表中插入一行資料，使用COMMIT WORK送出事務（此時後面預設開啟一個事務）

begin;

insert into t select 1;

commit work;

• 此時再插入一行資料，然後進行復原操作

insert into t select 2;

rollback;

• 查詢表格，發現資料還有一行（這就是因為completion_type之後，commit work後面又自動開啟了一個事務，我們使用rollback将事務撤銷了）

select * from t;

completion_type為2的示範案例

• 接着上面的表格，将參數設定為2

set @@completion_type=2;

show variable like 'completion_type'\G

• 開啟一個事務，然後插入一條資料，并結束本次事務

begin;

insert into t select 3;

commit work;

• 然後随便執行一條SQL語句，發現抛出異常，錯誤的原因是目前會話已與伺服器斷開連接配接，并且重新開啟了一個會話

select version();

• roll和roll work的使用與commit和commit work相同

四、儲存點的使用示範案例

• 建立一張表格t

drop table if exists t;

create table t(
    a int,
    primary key(a)
)engine=innodb;      

• 開啟一個事務，然後插入一條語句，設定第一個儲存點t1

begin;

insert into t select 1;

savepoint t1;      

• 再插入一條語句，設定第二個儲存點t2

insert into t select 2;

savepoint t2;      

• 再插入一條語句，復原至儲存點t2

insert into t select 3;

rollback to savepoint t2;      

• 然後結束事務，檢視表格中的資料

commit;

select * from t;      

五、隐式送出的SQL語句

• 文章開頭說過，預設情況下執行的SQL語句會自動送出，下面列出了含有隐式COMMIT操作的語句

六、對于事務操作的統計

• 由于InnoDB存儲引擎是支援事務的，是以InnoDB存儲引擎的應用需要在考慮每秒請求書（QPS）的同時，應該關注每秒事務處理的能力（TPS）

com_commit、com_rollback變量

• 計算TPS的方法是：

(com_commit+com_rollback)/time

• 使用上面公式的前提是：所有的事務都是顯式送出的，如果存在隐式地送出和復原（預設autocommit=1），不會計算到com_commit和com_rollback變量中

示範案例

handler_commit、handler_rollback

• 這兩個參數也用于事務的統計操作
• 但是這兩個參數在MySQL 5.1中可以很好地統計InnoDB存儲引擎顯式和隐式的事務送出操作，但是在InnoDB Plugin中這兩個參數的表現有些“怪異”，并不能很好地統計事務的次數

• 是以，如果使用者的程式都是顯式控制事務的送出和復原，可以通過com_commit和com_rollback進行統計。如果不是，那麼情況就顯得有些複雜