transaction是使用DB繞不開的技巧之一,要如何避免一個連續操作的完整性就是transaction的精華之所在!後續衍伸的併發問題等下篇文章再來好好介紹orz
p.s. 不是所有資料庫都有transaction功能,至少在MySQL 8.0版本以前只有InnoDB才有
簡介
transaction功能的在於讓一個連續性的DB操作要嘛全部成功,不然就全失敗,transaction都愛舉的例子就是轉帳:A轉帳給B,會先從A的帳號扣錢再把B的帳號加錢,如果扣錢成功而加錢失敗,帳目就會有問題,這時transaction就派上用場了!
至於講到transaction一定離不開的四個特性:ACID,這裡就不贅述,有興趣的可參考這篇
使用
autocommit
在MySQL預設中,每一筆SQL語句都被當成獨立的transaction,也就是autocommit
。因此在不改動原先設定的情況下,要使用transaction就只能手動開啟一個transaction: start transcation
,否則即使在一連串的SQL語句中設置savepoint
,之後下rollback
指令也沒有用。
可透過語法修改autocommit:
SET autocommit = 0|1|ON|OFF;
將autocommit改為關閉時,所有對DB的DML(insert/update/delete)操作都需要commit才會生效,此時就可以做到transaction的效果。不僅如此,原先每一次的DML操作都會自動commit一次,但改autocommit為關閉時,通過程序來控制僅需commit一次就夠了,對性能多少有些優化。
但要注意的是,將autocommit改為關閉後,操作的確都被包在同一個transaction裡,可是如果你在未將這些操作commit的情況下,切回autocommit為開啟的話,之前所做的操作就會自動commit;
又或是將autocommit改為關閉後,未提交commit就直接關閉資料庫或命令提示列,則之前所有的動作都會直接消失。因此改autocommit這個操作真的要小心。
start transaction / begin
上面有先提過,要手動開啟transaction是用start transcation
語法,除此之外,其實單純用begin
指令也可以達到開啟transaction的目的,當然begin可以做到的遠不只如此,在許多複雜的流程控制、函數、SP都可以看到begin的身影
savepoint
在transaction過程中,可設立多個不同的savepoint:savepoint name
,就像玩單機RPG儲存記錄一樣,可以隨時rollback回特定savepoint:rollback to savepoint name
。如果建立重名的savepoint,新的則會覆蓋掉舊的,也可以手動刪除savepoint:relesase savepoint name
commit / rollback
不論是手動開啟transaction,還是將autocommit改為關閉而開啟transaction,要結束transaction只有兩個選擇:commit
(提交)或是rollback
(回滾)
補充
cannot rollback
有一些指令是不能被roolback的,像是data definition language(DDL)的指令通常都無法roolback,因此若要在transaction中用到相關指令,可能需要替換語法,像是alter
(DDL)可以改成update
(DML),或是drop
可以改成delete
,當然為數較多的DDL還有太多無法被取代的語法 ex: truncate
、create
,因此若在交易中使用到則要多注意
implicit commit
有些操作在執行時,隱含著commit的行為,也就是說在執行特定命令前,直接先commit一次,會終止該行為前的transaction。對於這樣的描述是不是覺得有點眼熟?沒錯本文這是第二次提到這樣的行為,除了先前提到的將autocommit設為開啟(set autocommit = 1
)之外,還有許多指令會有一樣的效果,ex: DDL 指令、begin、start transaction、……
The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a
COMMIT
before executing the statement.