home > DB > mysql >

再談Transaction——MySQL事務處理分析

author:[email protected] date:2015-10-22 09:40:08

在商務級的應用中,都必須考慮事務處理的!事物的定義、事務都應該具備ACID特征、事物的隔離級別、事物解決的問題、MYSQL的事務處理主要方法、Mysql默認使用REPEATABLE READ理由、事務開啟與結束的標志、transaction及其控制

MySQL 事務基礎概念/Definition of Transaction

事務(Transaction)是訪問和更新數據庫的程序執行單元;事務中可能包含一個或多個 sql 語句,這些語句要么都執行,要么都不執行

事務處理在各種管理系統中都有著廣泛的應用,比如人員管理系統,很多同步數據庫操作大都需要用到事務處理。比如說,在人員管理系統中,你刪除一個人員,你即需要刪除人員的基本資料,也要刪除和該人員相關的信息,如信箱,文章等等,這樣,這些數據庫操作語句就構成一個事務!

刪除的SQL語句
delete from userinfo where ~~~
delete from mail where ~~
delete from article where~~
~~ 
如果沒有事務處理,在你刪除的過程中,假設出錯了,只執行了第一句,那么其后果是難以想象的!

但用事務處理。如果刪除出錯,你只要rollback就可以取消刪除操作(其實是只要你沒有commit你就沒有確實的執行該刪除操作)

一般來說,在商務級的應用中,都必須考慮事務處理的

mysql邏輯架構和存儲引擎

MySQL 支持事務的存儲引擎有 InnoDB、NDB Cluster 等,其中 InnoDB 的使用最為廣泛;其他存儲引擎不支持事務,如 MyIsam、Memory 等

事務的特征

  • 一個最小的不可再分的工作單元;通常一個事務對應一個完整的業務(例如銀行賬戶轉賬業務,該業務就是一個最小的工作單元)

  • 一個完整的業務需要批量的DML(insert、update、delete)語句共同聯合完成

  • 事務只和DML語句有關,或者說DML語句才有事務。這個和業務邏輯有關,業務邏輯不同,DML語句的個數不同

先來明確一下事務涉及的相關知識:

事務都應該具備ACID特征

所謂ACID是Atomic(原子性)/Consistent(一致性)/Isolated(隔離性)/Durable(持續性)四個詞的首字母所寫。

一般來說,事務是必須滿足4個條件(ACID)
  • 原子性(Autmic):事務是最小單位,不可再分。即:組成事務處理的語句形成了一個邏輯單元,不能只執行其中的一部分。事務在執行性,要做到"要么不做,要么全做!",就是說不允許事務部分得執行。即使因為故障而使事務不能完成,在rollback時也要消除對數據庫得影響!

    比如:銀行轉帳過程中,必須同時從一個帳戶減去轉帳金額,并加到另一個帳戶中,只改變一個帳戶是不合理的。

    原子性由 Undo log 保證。Undo Log 會保存每次變更之前的記錄,從而在發生錯誤時進行回滾

  • 一致性(Consistency)在事務處理執行前后,數據庫是一致的。事務要求所有的DML語句操作的時候,必須保證同時成功或者同時失敗。事務得操作應該使使數據庫從一個一致狀態轉變倒另一個一致得狀態!

    比如:網上購物,你只有即讓商品出庫,又讓商品進入顧客得購物籃才能構成事務!

    比如:銀行轉帳過程中,要么轉帳金額從一個帳戶轉入另一個帳戶,要么兩個帳戶都不變,沒有其他的情況。

    一致性由原子性,隔離性,持久性來保證

  • 隔離性(Isolation)一個事務處理對另一個事務處理沒有影響。即:事務A和事務B之間具有隔離性。如果多個事務并發執行,應象各個事務獨立執行一樣!也就是說任何事務都不可能看到一個處在不完整狀態下的事務。

    隔離性追求的是并發情形下事務之間互不干擾。

    比如:銀行轉帳過程中,在轉帳事務沒有提交之前,另一個轉帳事務只能處于等待狀態。

    隔離性由 MVCC(Multi-Version Concurrency Control) 和 Lock(鎖機制) 保證

  • 持久性(Durability)事務處理的效果能夠被永久保存下來。反過來說,事務應當能夠承受所有的失敗,包括服務器、進程、通信以及媒體失敗等等。一個成功執行得事務對數據庫得作用是持久得,即使數據庫應故障出錯,也應該能夠恢復!持久性是事務的保證,事務終結的標志(內存的數據持久到硬盤文件中)。

    比如:銀行轉帳過程中,轉帳后帳戶的狀態要能被保存下來。

    持久性由 Redo Log 保證。每次真正修改數據之前,都會將記錄寫到 Redo Log 中,只有 Redo Log 寫入成功,才會真正的寫入到 B+ 樹中,如果提交之前斷電,就可以通過 Redo Log 恢復記錄

InnoDB存儲引擎兩種事務日志:

  • 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 存在的背景

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(Write-ahead logging,預寫式日志),所有修改先寫入日志,再更新到 Buffer Pool,保證了數據不會因 MySQL 宕機而丟失,從而滿足了持久性要求

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

主要有以下兩方面的原因:

  • 刷臟是隨機 IO,因為每次修改的數據位置隨機,但寫 redo log 是追加操作,屬于順序 IO。

  • 刷臟是以數據頁(Page)為單位的,MySQL 默認頁大小是 16KB,一個 Page 上一個小修改都要整頁寫入;而 redo log 中只包含真正需要寫入的部分,無效 IO 大大減少。

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 是邏輯日志,內容是一條條 sql。

寫入時機不同:

  • redo log 的寫入時機相對多元。前面曾提到,當事務提交時會調用 fsync 對 redo log 進行刷盤;這是默認情況下的策略,修改 innodb_flush_log_at_trx_commit 參數可以改變該策略,但事務的持久性將無法保證。

除了事務提交時,還有其他刷盤時機:如 master thread 每秒刷盤一次 redo log 等,這樣的好處是不一定要等到 commit 時刷盤,commit 速度大大加快。

  • binlog 在事務提交時寫入。

事務的隔離四種級別

ACID四大特征中,最難理解的不是一致性,而是事務的隔離性,數據庫權威專家針對事務的隔離性研究出來了事務的隔離四種級別,四種事務隔離級別就是為了解決數據在高并發下產生的問題(臟讀、不可重復讀、幻讀)

并發一致性問題

  • 臟讀:比如有兩個事務并行執行操作同一條數據庫記錄,A事務能讀取到B事務未提交的數據。比如:事務B操作了數據庫但是沒有提交事務,此時A讀取到了B沒有提交事務的數據。這就是臟讀的體現。

  • 不可重復讀:比如事務A在同一事務中多次讀取同一記錄,此時事務B修改了事務A正在讀的數據并且提交了事務,但是事務A讀取到了事務B所提交的數據,導致兩次讀取數據不一致。

  • 幻讀:事務A將數據表中所有數據都設置為100,此時事務B插入了一條值為200的數據并提交事務,當事務A修改完成提交事務后,發現還有一條數據的值不為100.這就是幻讀的一種體現方式。

臟讀示例不可重復讀示例幻讀示例

臟讀情況不可重復讀(Non-Repeatable Reads)幻讀流傳展示

"臟讀"、"不可重復讀"和"幻讀",其實都是數據庫讀一致性問題,必須由數據庫提供一定的事務隔離機制來解決

事務隔離級別

  • 未提交讀(RU),一個事務還沒提交時,它做的變更就能被別的事務看到。

    發生臟讀的原因:RU 原理是對每個更新語句的行記錄進行加鎖,而不是對整個事務進行加鎖,所以會發生臟讀。而 RC 和 RR 會對整個事務加鎖。

  • 已提交讀(RC),一個事務提交之后,它做的變更才會被其他事務看到。

    不能重復讀的原因:RC 每次執行 SQL 語句都會生成一個新的 Read View,每次讀到的都是不同的。而 RR 的事務從始至終都是使用同一個 Read View。

  • 可重復讀(RR), 一個事務執行過程中看到的數據,總是跟這個事務在啟動時看到的數據是一致的。當然在可重復讀隔離級別下,未提交變更對其他事務也是不可見的。

    默認是 MVCC 機制(“一致性非鎖定讀”)保證 RR 級別的隔離正確性,是不上鎖的。可以選擇手動上鎖:select xxxx for update (排他鎖); select xxxx lock in share mode(共享鎖),稱之為“一致性鎖定讀”。使用鎖之后,就能在 RR 級別下,避免幻讀。當然,默認的 MVCC 讀,也能避免幻讀。

  • 串行化(serializable),所有事務一個接著一個的執行,這樣可以避免幻讀 (phantom read),對于基于鎖來實現并發控制的數據庫來說,串行化要求在執行范圍查詢的時候,需要獲取范圍鎖,如果不是基于鎖實現并發控制的數據庫,則檢查到有違反串行操作的事務時,需回滾該事務。

    顧名思義是對于同一行記錄,“寫”會加“寫鎖”,“讀”會加“讀鎖”。當出現讀寫鎖沖突的時候,后訪問的事務必須等前一個事務執行完成,才能繼續執行。

數據庫權威專家針對事務的隔離性研究出來了事務的隔離四種級別

事務隔離級別越嚴格,越消耗計算機性能,效率也越低

數據庫的事務隔離越嚴格,并發副作用越小,但付出的代價也就越大,因為事務隔離實質上就是使事務在一定程度上 “串行化”進行,這顯然與“并發”是矛盾的。同時,不同的應用對讀一致性和事務隔離程度的要求也是不同的,比如許多應用對“不可重復讀”和“幻讀”并不敏感,可能更關心數據并發訪問的能力。

Mysql默認使用的數據隔離級別是REPEATABLE READ(可重復讀,允許幻讀)。MySql 的 REPEATABLE READ 級別不會導致幻讀。

REPEATABLE READ級別會導致幻讀,但是,由于 Mysql 的優化,在使用默認的 select 時,MySql 使用 MVCC 機制保證不會幻讀

也可以使用鎖,在使用鎖時,例如 for update(X 鎖),lock in share mode(S 鎖),MySql 會使用 Next-Key Lock 來保證不會發生幻讀。

前者稱為快照讀,后者稱為當前讀。

Mysql默認使用REPEATABLE READ理由

Mysql在5.0這個版本以前,binlog只支持STATEMENT這種格式!而這種格式在讀已提交(Read Commited)這個隔離級別下主從復制是有bug的,因此Mysql將可重復讀(Repeatable Read)作為默認的隔離級別!

有什么bug呢?具體閱讀《互聯網項目中mysql應該選什么事務隔離級別

mysql為什么不采默認用串行化(Serializable)?

因為每個次讀操作都會加鎖,快照讀失效,一般是使用mysql自帶分布式事務功能時才使用該隔離級別

主從復制,是基于什么復制的? 

是基于binlog復制的!

binlog有幾種格式?

  1. statement:記錄的是修改SQL語句

  2. row:記錄的是每行實際數據的變更

  3. mixed:statement和row模式的混合


事務開啟的標志?事務結束的標志?

開啟標志:任何一條DML語句(insert、update、delete)執行,標志事務的開啟

結束標志(提交或者回滾)

            提交:成功的結束,將所有的DML語句操作歷史記錄和底層硬盤數據來一次同步

            回滾:失敗的結束,將所有的DML語句操作歷史記錄全部清空

事物與數據庫底層數據:

在事物進行過程中,未結束之前,DML語句是不會更改底層數據,只是將歷史操作記錄一下,在內存中完成記錄。只有在事物結束的時候,而且是成功的結束的時候,才會修改底層硬盤文件中的數據

什么是transaction及其控制?

Transaction包含了一系列的任務操作。這些操作可能是創建更新刪除等等操作,是作為一個特定的結果來表現的。要么成功,要么不成功

transaction有4種控制

  1. commit,也就是提交

  2. rollback,也就是回調

  3. set transaction,對這個事務設定一個名字

  4. save point。這個控制是用來設定某個點用來回退的

MVCC解析

MVCC 全稱 Multi-Version Concurrency Control,即多版本的并發控制協議。

一致性非鎖定讀

一致性非鎖定讀(consistent nonlocking read)是指InnoDB存儲引擎通過多版本控制(MVCC)的方式來讀取當前執行時間數據庫中行的數據。如果讀取的這行正在執行DELETE或UPDATE操作,這時讀取操作不會向XS鎖一樣去等待鎖釋放,而是會去讀一個快照數據。

MVCC相關的知識

在事務隔離級別RC和RR下,InnoDB存儲引擎使用非鎖定的一致性讀。然而對于快照數據的定義卻不同,在RC級別下,對于快照數據,非一致性讀總是讀取被鎖定行的最新一份快照數據。而在RR級別下,對于快照數據,非一致性讀總是讀取事務開始時的行數據版本。

MVCC過程圖示

可以看到,第1步和第2步是非常容易理解的,而在第3步事務B插入一條新的數據后,在第4步事務A還是查不到,也就是利用了MVCC的特性來實現。當事務B提交后,第5步的查詢在RC和RR隔離級別下的輸出是不同的,這個的原因在另一篇博客中也說到了,是因為他們創建ReadView的時機不同。

但是很詭異的是在第6步的時候,事務A更新了一條它看不見的記錄,然后查詢就能夠查詢出來了。這里很多人容易迷惑,不可見不代表記錄不存在,它只是利用了可見性判斷忽略了而已。更新成功之后,事務A順其自然的記錄了這條記錄的Undo log,在隨后的查詢中,因為它能夠看見自己的改動這一個可見性的判斷,自然就能夠查詢出來了。這里很多名詞需要去深入讀一下此文:談談MySQL InnoDB存儲引擎事務的ACID特性

RR 解決臟讀、不可重復讀、幻讀等問題,使用的是 MVCC

高性能MySQL MVCC

MVCC是個 行級鎖 的變種,它在 普通讀情況下避免了加鎖操作,因此開銷更低 。雖然實現不同,但通常都是實現非阻塞讀,寫操作只鎖定必要的行。

MVCC 的特點

在同一時刻,不同的事務讀取到的數據可能是不同的(即多版本)

MVCC 最大的優點是讀不加鎖,因此讀寫不沖突,并發性能好。InnoDB 實現 MVCC,多個版本的數據可以共存,主要是依靠數據的隱藏列(也可以稱之為標記位)和 undo log。

其中數據的隱藏列包括了該行數據的版本號、刪除時間、指向 undo log 的指針等等。

當讀取數據時,MySQL 可以通過隱藏列判斷是否需要回滾并找到回滾需要的 undo log,從而實現 MVCC;隱藏列的詳細格式不再展開。

一致性鎖定讀

前面說到,在默認隔離級別RR下,InnoDB存儲引擎的SELECT操作使用一致性非鎖定讀。但是在某些情況下,用戶需要顯式地對數據庫讀取操作進行加鎖以保證數據邏輯的一致性。InnoDB存儲引擎對于SELECT語句支持兩種一致性的鎖定讀(locking read)操作。

  • SELECT … FOR UPDATE (X鎖)

  • SELECT … LOCK IN SHARE MODE (S鎖)

MySQL InnoDB 臟讀 不可重復讀 幻讀 

臟讀

臟讀情景分析

當事務 A 在 T3 時間節點讀取 zhangsan 的余額時,會發現數據已被其他事務修改,且狀態為未提交。

此時事務 A 讀取最新數據后,根據數據的 undo log 執行回滾操作,得到事務 B 修改前的數據,從而避免了臟讀。

不可重復讀

不可重復讀情景分析

當事務 A 在 T2 節點第一次讀取數據時,會記錄該數據的版本號(數據的版本號是以 row 為單位記錄的),假設版本號為 1;當事務 B 提交時,該行記錄的版本號增加,假設版本號為 2。

當事務 A 在 T5 再一次讀取數據時,發現數據的版本號(2)大于第一次讀取時記錄的版本號(1),因此會根據 undo log 執行回滾操作,得到版本號為 1 時的數據,從而實現了可重復讀。

幻讀

InnoDB 實現的 RR 通過 next-keylock 機制避免了幻讀現象。

next-keylock 是行鎖的一種,實現相當于 record lock(記錄鎖) + gap lock(間隙鎖);其特點是不僅會鎖住記錄本身(record lock 的功能),還會鎖定一個范圍(gap lock 的功能)。

當然,這里我們討論的是不加鎖讀:此時的 next-key lock 并不是真的加鎖,只是為讀取的數據增加了標記(標記內容包括數據的版本號等);準確起見姑且稱之為類 next-key lock 機制。

幻讀情景分析

這樣當 T5 時刻再次讀取 0

小結:概括來說,InnoDB 實現的 RR,通過鎖機制、數據的隱藏列、undo log 和類 next-key lock,實現了一定程度的隔離性,可以滿足大多數場景的需要。

不過需要說明的是,RR 雖然避免了幻讀問題,但是畢竟不是 Serializable,不能保證完全的隔離


MYSQL的事務處理主要方法

用begin,rollback,commit來實現

  • begin 開始一個事務
  • rollback 事務回滾
  • commit  事務確認

改變mysql的自動提交模式

MYSQL默認事務是自動提交的,也就是你提交一個QUERY,它就直接執行!也就是說,只要執行一條DML語句就開啟了事物,并且提交了事務。

MySQL自動提交模式

我們可以通過設置autocommit來實現事務的處理。

  • set autocommit=0 禁止自動提交

  • set autocommit=1 開啟自動提交

但注意當你用 set autocommit=0 的時候,你以后所有的SQL都將做為事務處理,直到你用commit確認或rollback結束,注意當你結束這個事務的同時也開啟了個新的事務!按第一種方法只將當前的作為一個事務!
個人推薦使用第一種方法!
MYSQL中只有INNODB和BDB類型的數據表才能支持事務處理!其他的類型是不支持的!(切記!)

再來看看哪些問題會用到事務處理:

先假設一下問題的背景:網上購書,某書(數據庫編號為123)只剩最后一本,而這個時候,兩個用戶對這本書幾乎同時發出了購買請求,讓我們看看整個過程:

在具體分析之前,先來看看數據表的定義:

-------------------------------------------------------------------------------

create table book
(
    book_id unsigned int(10) not null auto_increment,
    book_name varchar(100) not null,
    book_price float(5, 2) not null, #我假設每本書的價格不會超過999.99元
    book_number int(10) not null,
    primary key (book_id)
)
type = innodb; #engine = innodb也行

-------------------------------------------------------------------------------

對于用戶甲來說,他的動作稍微比乙快一點點,其購買過程所觸發的動作大致是這樣的:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

book_number大于零,確認購買行為并更新book_number

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

購書成功

-------------------------------------------------------------------------------

而對于用戶乙來說,他的動作稍微比甲慢一點點,其購買過程所觸發的動作和甲相同:

-------------------------------------------------------------------------------

1. SELECT book_number FROM book WHERE  book_id = 123;

這個時候,甲剛剛進行完第一步的操作,還沒來得及做第二步操作,所以book_number一定大于零

2. UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

購書成功

-------------------------------------------------------------------------------

表面上看甲乙的操作都成功了,他們都買到了書,但是庫存只有一本,他們怎么可能都成功呢?再看看數據表里book_number的內容,已經變成 -1了,這當然是不能允許的(實際上,聲明這樣的列類型應該加上unsigned的屬性,以保證其不能為負,這里是為了說明問題所以沒有這樣設置)

好了,問題陳述清楚了,再來看看怎么利用事務來解決這個問題,打開MySQL手冊,可以看到想用事務來保護你的SQL正確執行其實很簡單,基本就是三個語句:開始,提交,回滾。

  • 開始:START TRANSACTION或BEGIN語句可以開始一項新的事務

  • 提交:COMMIT可以提交當前事務,是變更成為永久變更

  • 回滾:ROLLBACK可以回滾當前事務,取消其變更

此外,SET AUTOCOMMIT = {0 | 1}可以禁用或啟用默認的autocommit模式,用于當前連接。

-------------------------------------------------------------------------------

那是不是只要用事務語句包一下我們的SQL語句就能保證正確了呢?比如下面代碼:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

答案是否定了,這樣依然不能避免問題的發生,如果想避免這樣的情況,實際應該如下:

-------------------------------------------------------------------------------

BEGIN;

SELECT book_number FROM book WHERE  book_id = 123 FOR UPDATE ;

// ...

UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123;

COMMIT;

-------------------------------------------------------------------------------

由于加入了FOR UPDATE,所以會在此條記錄上加上一個行鎖,如果此事務沒有完全結束,那么其他的事務在使用SELECT ... FOR UPDATE請求的時候就會處于等待狀態,直到上一個事務結束,它才能繼續,從而避免了問題的發生,需要注意的是,如果你其他的事務使用的是不帶FOR UPDATE的SELECT語句,將得不到這種保護。

MySQL事務操作

mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb;  # 創建數據表
Query OK, 0 rows affected (0.04 sec)

mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
 
mysql> begin;  # 開始事務
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
 
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
 
mysql> commit; # 提交事務
Query OK, 0 rows affected (0.01 sec)
 
mysql>  select * from runoob_transaction_test;
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)
 
mysql> begin;    # 開始事務
Query OK, 0 rows affected (0.00 sec)
 
mysql>  insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
 
mysql> rollback;   # 回滾
Query OK, 0 rows affected (0.00 sec)
 
mysql>   select * from runoob_transaction_test;   # 因為回滾所以數據沒有插入
+------+
| id   |
+------+
| 5    |
| 6    |
+------+
2 rows in set (0.01 sec)

PHP + MySQL事務操作的代碼演示:

<?php
$dbhost = 'localhost:3306';  // mysql服務器主機地址
$dbuser = 'root';            // mysql用戶名
$dbpass = '123456';          // mysql用戶名密碼
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('連接失敗: ' . mysqli_error($conn));
}
// 設置編碼,防止中文亂碼
mysqli_query($conn, "set names utf8");
mysqli_select_db( $conn, 'RUNOOB' );
mysqli_query($conn, "SET AUTOCOMMIT=0"); // 設置為不自動提交,因為MYSQL默認立即執行
mysqli_begin_transaction($conn);            // 開始事務定義

if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(8)"))
{
    mysqli_query($conn, "ROLLBACK");     // 判斷當執行失敗時回滾
}

if(!mysqli_query($conn, "insert into runoob_transaction_test (id) values(9)"))
{
    mysqli_query($conn, "ROLLBACK");      // 判斷執行失敗時回滾
}
mysqli_commit($conn);            //執行事務
mysqli_close($conn);
?>


PHP使用AdoDB操作MySQL事務的代碼演示:

實際LAMP應用中,一般PHP使用AdoDB操作MySQL,下面給出AdoDB相應的代碼方便大家查閱:

-------------------------------------------------------------------------------

// ...
$adodb -> startTrans
();

//實際,getOne所調用的查詢也可以直接放到rowLock來進行,這里只是為了演示效果能更明顯些。
$adodb -> rowLock ( 'book' 'book_id = 123'
);
$bookNumber  $adodb -> getOne ( "SELECT book_number FROM book WHERE  book_id = 123"
);
$adodb -> execute ( "UPDATE book SET book_number = book_number - 1 WHERE  book_id = 123"
);
$adodb -> completeTrans
();
// ...
?>

-------------------------------------------------------------------------------

其中,rowLock的方法就是調用的FOR UPDATE來實現的行鎖,你可能會想把&"FOR UPDATE&直接寫到$adodb->getOne()調用的那條SQL語句里面去實現行鎖的功能,不錯,那樣確實可以,但是并不是所有的數據庫 都使用&"FOR UPDATE&"語法來實現行鎖功能,比如Sybase使用&"HOLDLOCK&"的語法來實現行鎖功能,所以為了你的數據庫抽象層保持可移植性,我還是勸你 用rowLock來實現行鎖功能,至于可移植性就交給AdoDB好了,嗯,有點扯遠了,今兒就說到這里了。

-------------------------------------------------------------------------------

附:

AdoDB中存在一個setTransactionMode()方法,能夠設置事務的隔離級別,如下:

SetTransactionMode allows you to pass in the transaction mode to use for all subsequent transactions for that connection session. Note: if you have persistent connections and using mysql or mssql, you might have to explicitly reset your transaction mode at the beginning of each page request. This is only supported in postgresql, mssql, mysql with InnoDB and oci8 currently. For example:

$db->SetTransactionMode("SERIALIZABLE");
$db->BeginTrans();
$db->Execute(...); $db->Execute(...);
$db->CommiTrans();

$db->SetTransactionMode(""); // restore to default
$db->StartTrans();
$db->Execute(...); $db->Execute(...);
$db->CompleteTrans();

Supported values to pass in:

    * READ UNCOMMITTED (allows dirty reads, but fastest)
    * READ COMMITTED (default postgres, mssql and oci8)
    * REPEATABLE READ (default mysql)
    * SERIALIZABLE (slowest and most restrictive)

You can also pass in database specific values such as 'SNAPSHOT' for mssql or 'READ ONLY' for oci8/postgres.


參考鏈接:

MySQL——事務(Transaction)詳解 https://blog.csdn.net/w_linux/article/details/79666086

MySql 三大知識點——索引、鎖、事務 https://zhuanlan.zhihu.com/p/59764376

面試官問你:MYSQL事務和隔離級別,該如何回答 https://zhuanlan.zhihu.com/p/70701037

談談MySQL的鎖 https://zhuanlan.zhihu.com/p/65721606

一文說盡MySQL事務及ACID特性的實現原理 https://zhuanlan.zhihu.com/p/56874694

MySQL 事務 https://www.runoob.com/mysql/mysql-transaction.html


轉載本站文章《再談Transaction——MySQL事務處理分析》,
請注明出處:http://www.qsexmk.tw/html/DB/mysql/2015_1022_324.html