MySQL資料庫數據庫事務處理舉例說明

事務處理用於有效記錄某機構感興趣的業務活動(稱為事務)的數據處理(例如銷售、供貨的定購或貨幣傳輸)。通常,聯機事務處理 (OLTP) 系統執行大量的相對較小的事務。

事務處理是將多個操作或者命令一起執行,所有命令全部成功執行才意味著該事務的成功,任何一個命令失敗都意味著該事務的失敗。

以銀行轉賬為例(100塊都不給),

A要給B 轉賬100元,

A轉賬的指令已經成功發出,而B 由於未知的原因接收失敗,

如果兩個命令單獨執行,那麼A賬戶少瞭100塊,但是B又沒收到100塊,顯而易見是不合理的;

如果將A向B 轉賬100元當成一個事務處理,那麼由於B 接收的失敗,整個轉賬事務都將失敗,A不會少100,B更不會增加100,這個時候“100塊都不給”才是合理的情況。

因此,事務處理是不是一榮俱榮,而是一毀全毀。

接下來介紹MySQL資料庫中如何進行事務處理以及鎖定。

1.資料庫事務處理相關命令

2.存儲引擎

MySQL資料庫的存儲引擎是可以選擇改變和替換的(可替換存儲引擎構架,Pluggable Storage Engine Architecture)。MySQL主要有8種存儲引擎:

MySQL的存儲引擎種類和特征

(1)查看存儲引擎SHOW CREATE TABLE 表名;

查看某表使用的存儲引擎,語法代碼如下:

`SHOW CREATE TABLE 表名;`

如,要查看表customer的存儲引擎,可以輸入代碼:

`SHOW CREATE TABLE customer;`

如圖表所示ENGINE=後面顯示的就是存儲引擎。

(2)更改存儲引擎ALTER TABLE 表名 ENGINE=新引擎名;

若要更改存儲引擎,可以使用代碼:

>ALTER TABLE 表名 ENGINE=新引擎名;

eg:將表customer的存儲引擎修改為MyISAM,輸入指令:

ALTER TABLE customer ENGINE=MyISAM;

3. 事務處理

之前講到,事務處理是一毀全毀,因此事務中任意一個任務或指令失敗,整個事務都將失敗。那是怎麼實現的呢?方法是時間中多個任務全部成功,則任務成功結束,並且會進行提交(COMMIT),如果任何一件任務失敗,則強制回滾(ROLLBACK)到初始狀態。

事務處理涉及到三個最重要的命令:BEGIN,ROLLBACK,COMMIT,分別表示聲明事務開始,回滾和確認提交。

(1)回滾演示(ROLLBACK)

首先將表格customer 的存儲引擎設置為InnoDB,

確認表格數據;SELECT * FROM customer;

事務開始;BEGIN;

刪除表格數據;DELETE FROM customer;

再次查看表格數據;SELECT * FROM customer;

回滾到初始狀態;ROLLBACK;

再次查看表格數據;SELECT * FROM customer;

可以看到,當執行ROLLBACK;之後,刪除的記錄又恢復到瞭BEGIN之前的狀態,如果將ROLLBACK 換成COMMIT,那麼事務將會提交,刪除的記錄就不能恢復瞭。

(2)自動提交

當搜尋引擎為MyISAM時,因為不支持事務處理,因此命令一旦執行,就一定會提交,這種默認的提交方式被稱為自動提交。

而當搜尋引擎設置為InnoDB時,可以設置自動提交功能是否開啟,當自動提交功能為ON時,命令執行就會提交(COMMIT),而自動提交設置為OFF 時,必須執行COMMIT才提交,可以使用ROLLBACK進行回滾。

查詢當前自動提交功能狀態:

>SELECT @@AUTOCOMMIT;

設置自動提交功能:

>SET AUTOCOMMIT=0或1;

如圖,將自動提交設置為OFF,插入一條記錄,然後使用回滾ROLLBACK,再次查看記錄,會發現不見瞭

(3)部分回滾 SAVEPOINT

直接ROLLBACK會回滾到BEGIN開始之前的地方,而通過SAVEPOINT可以保存一個點,通過ROLLBACK TO SAVEPOINT就可以回滾到保存點瞭,也就實現瞭“想去哪就去瞭哪”。

部分回滾主要有兩個步驟:

①保存點

>SAVEPOINT 保存點名;

②回滾到保存點

>ROLLBACK TO SAVEPOINT 保存點名;

eg:

(4)不能事務處理的命令(直接提交)

大部分命令都可以通過事務處理(BEGIN -ROLLBACK- COMMIT)進行操作,但是:

DROP DATABASE;DROP TABLE;DROP;ALTER TABLE

不能通過事務處理,會直接COMMIT;

4. 鎖定與事務處理

前面講到的ROLLBACK 等操作指令都是基於一個用戶進行的。但是事務類型往往不隻一個用戶,多個用戶同時操作,如被人廣泛詬病的“12306”火車票購票系統,全國各地的售票窗口以及互聯網購票註冊賬戶,成千上萬的用戶同時使用,因此事務處理必須能夠處理多個用戶同時操作的情況,這就需要鎖定。

舉個例子,如果某班火車隻剩最後一張票,A和B 同時登陸網站購票,得到的反饋是還剩一張,於是A,B 都趕緊下單,處理這種沖突事件,就需要對該事務進行鎖定(LOCK),接觸鎖定被稱為解鎖(Unlock)。

3.1 鎖定

(1)鎖定的分類

鎖定分為共享鎖定(Shared Lock)和排他鎖定(Exclusive Lock):

共享鎖定是將對象數據變為隻讀形式,不能進行更新,所以也成為讀取鎖定;排他鎖定是當執行INSERT/UPDATE/DELETE的時候,其它事務不能讀取該數據,因此也成為寫入鎖定。

(2)鎖定的粒度

鎖定對象的大小是鎖定的粒度,有三種粒度:

記錄表資料庫

3.2 事務處理的分離水平

需要使用鎖定來有效解決事務沖突的情況,但是鎖定也會使性能下降(因為別人無法訪問),因此頻繁鎖定不一定合理,資料庫中,使用分離水平來表示事務處理之間的影響程度。

事務處理的分離水平對應的數據整合情況:

分離水平非提交讀取不可重復讀取幻象讀取

READ UNCOMMITED√√√

READ COMMITED×√√

REPEATABLE READ××√

SERIALIZABLE×××

設置分離水平可以使用命令:

>SET SESSION TRANSACTION ISOLATION LEVEL 分離水平;

為瞭模擬多個用戶對資料庫進行訪問和操作,我們打開兩個命令窗口接入MySQL。

(1)非提交讀取

非提交讀取指的是別的事務能夠讀取到還沒有提交的更新數據,隻發生在分離水平為READ UNCOMMITED的情況下。

因為對事務處理的讀取沒有任何限制,所以一般不推薦使用。

eg.

兩個窗口的執行順序如圖紅色序號所示:

①首先再A窗口對Id為g001的記錄的nam進行修改;

②然後B進行訪問,發現已經能夠讀取新的nam;

③A執行ROLLBACK,回滾到初始狀態(nam恢復原來的記錄);

④B再次查詢,又得到OLD記錄(舊記錄);

⑤A再次UPDATE,並COMMIT;

⑥B再次SELECT,得到新紀錄(new)。

可以看到,當A還沒提交,B就可以看到更新的數據,這個時候很可能出現問題,比如A後來執行ROLLBACK,B看到的數據實際上是錯誤的數據。

(2)不可重復讀取

不可重復讀取是指在某事務處理過程中對數據進行讀取,由於該事務更新操作導致多次讀取數據時發生瞭改變。

不可重復讀取發生在READ COMMITED 一下的分離水平。

eg:

命令順序依然如圖紅色數字所示:

①A更新id為g001的nam;

②B查詢,結果是OLD數據;

③A提交更新;

④B再次查詢,得到更新後的NEW數據。

B先後兩次查詢,結果不一致。

(3)幻象讀取

幻象讀取指的是,在某事物處理數據過程中對數據多次讀取,由於該事務的插入/刪除操作而導致在多次讀取過程中讀取到不存在或者消失的數據。

下圖是幻象讀取發生的例子:

①A事務開始;

②B查詢;

③A插入一條記錄並提交;

④B再次查詢;

可以看到B連續執行兩次的查詢,前後結果不一致。

當設置分離水平為SERIALIZABLE時,可以消除幻象讀取。

①A事務開始;

②B查詢;

③A插入一條數據;(此時A會一直等待,直到B提交)

④B提交;

⑤A提交;

⑥B再次查詢;

特別需要註意的是,由於B正在讀取數據,當A 執行插入命令時,無法立馬得到結果,而是一直等待,直到B提交。當B提交時,A的插入命令自動完成。

3.3 死鎖 Dead Lock(狹路相逢勇者勝)

死鎖指的是兩個事務互相對待對方釋放鎖定,則永遠也不可能接觸鎖定的狀態。

如A,B兩個用戶都對表customer中記錄’g001’和’g002’實施瞭排他鎖定,由於兩個事務互相等待對方釋放鎖定,所以形成瞭死鎖。

A和B執行命令順序紅色字體所示:

①A事務開始,並對’g001’進行瞭更新;

②B事務開始,並對’g002’進行瞭更新;

③A對’g002’進行更新;(A等待B提交釋放鎖定)

④B對’g001’進行更新;(B等待A提交釋放鎖定,A、B互相等待陷入死鎖)

此時,MySQL資料庫讓B強制解除鎖定,A繼續執行;

⑤A提交;

⑥B回滾並查詢,得到A事務更新的數據;

發佈留言