[MySQL光速入門]020 事務

貓哥的技術部落格發表於2019-04-18
MySql

TCL 事務控制語言

SQL的四種語言

  1. DDL（Data Definition Language）資料庫定義語言 建表, 建庫, 修改表結構
  2. DML（Data Manipulation Language）資料操縱語言 增刪改查, select, insert, update, delete
  3. DCL（Data Control Language）資料庫控制語言 修改使用者許可權, 重置密碼
  4. TCL（Transaction Control Language）事務控制語言 建立事務, 回滾

什麼是事務

一組SQL語句組成的執行單元, 這一組SQL語句, 要麼全部執行, 要麼全部不執行.
複製程式碼

舉例

  • 郭靖向黃蓉轉賬(交工資)
  • 郭靖這個月發了5000, 銀行卡餘額5000
  • 黃蓉, 銀行卡餘額5000,000
  • update set 郭靖的銀行卡餘額 - 5000;
  • update set 黃蓉的銀行卡餘額 + 5000;
  • 我們需要兩條SQL語句都要成功, 否則我們寧願沒執行過, 所以就是, 要麼全部執行, 要麼全部不執行.

事務：

  • 事務由單獨單元的一個或多個SQL語句組成
  • 在這個單元中，每個MySQL語句是相互依賴的
  • 而整個單獨單元作為一個不可分割的整體
  • 如果單元中某條SQL語句一旦執行失敗或產生錯誤，整個單元將會回滾
  • 所有受到影響的資料將返回到事物開始以前的狀態
  • 如果單元中的所有SQL語句均執行成功，則事物被順利執行

舉例2:

  • 軟體安裝, 會有很多下一步, 如果點選取消, 則全部回滾.

image.png

事務 vs 儲存引擎(表型別)

什麼是儲存引擎(表型別):

  • MySQL中的資料用各種不同的技術儲存在檔案(或者記憶體)中。
  • 這些技術中的每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。
  • 通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。
  • 如果你在研究大量的臨時資料，你也許需要使用記憶體MySQL儲存引擎(memory)。
  • 記憶體儲存引擎能夠在記憶體中儲存所有的表格資料。
  • 又或者，你需要一個支援事務處理的資料庫(以確保事務處理不成功時資料的回退能力), 那就用innodb。
  • 這些不同的技術以及配套的相關功能在 MySQL中被稱作儲存引擎(也稱作表型別)。

為什麼需要多個儲存引擎?

  • 每種引擎都有各自的優勢和不足, 沒有一種完美的儲存引擎
  • 所以MySQL可以針對不同的表, 選擇不同的儲存引擎
  • 就像雷達圖

image.png

image.png

image.png

如何設定表的儲存引擎?

create table tb (id int) engine = myisam;
-- 或者
alter table tb engine = memory;
複製程式碼

檢視所有儲存引擎

show engines;
複製程式碼

image.png

innodb:
  • 支援事務, 查詢速度沒有myisam快, 容量沒有myisam大
myisam:
  • 速度快, 容量大, 不支援事務
memory:
  • 速度超快, 容量取決於記憶體, 所以比較小, 不支援事務

事務的屬性(ACID)

  • 原子性 atomicity

    • 事務是一個整體, 不可分割, (因為原子不可再分)
    • 要麼全部執行, 要麼全部不執行

  • 一致性 consistency

    • 參見能量守恆定律
    • 能量既不會憑空產生，也不會憑空消失，只能從一個物體傳遞給另一個物體，從一種形式變成另一種形式, 在變換過程中, 系統的總能量保持不變。
    • 事務必須是資料庫從一個一致性狀態到另一個一致性狀態
    • 比如轉賬(事務)前後, 郭靖, 黃蓉的銀行卡餘額之和是固定不變的.

  • 隔離性(isolation)

    • 一個事務的執行, 不受其他事務的干擾
    • 郭靖向黃蓉轉賬的時候, 黃藥師也可以向黃蓉轉賬(需要配置隔離級別)

  • 永續性(durability)

    • 事務一旦成功, 不可撤銷
    • 刪除就是一個事務, 刪庫只能跑路
    • 轉賬成功, 不能撤銷, 除非對方再轉給你, 這是另一個事務

事務通常包括多條SQL語句(DML), 其實單獨的DML語句, 也是一個事務

  • 隱式事務(自動提交)

    • 事務沒有明顯的開啟和結束標記
    • 比如 insert,update,delete

  • 顯式事務

    • 資料具有明顯的開始和結束標記
    • 前提, 必須設定自動提交功能為禁用 set autocommit = 0;
    • 否則每條語句都是一個事務

建立事務

檢視變數

show variables like 'autocommit';
-- 或者
select @@autocommit;
複製程式碼

關閉自動提交

-- 當前會話有效
set autocommit = 0; 
-- 或者
set session autocommit = 0; 
-- 或者
set @@autocommit = 0;
-- 或者
set @@session.autocommit = 0;
複製程式碼

事務的建立

  1. 開啟事務

    set autocommit = 0;
start transaction; // 可選
複製程式碼

  2. 編寫事務中的sql語句(select, insert, update, delete) 不包括DDL(create, drop, alter)

  3. 結束事務

    • 提交事務 commit
    • 回滾事務 rollback

事務實操

drop table if exists test_tb;

CREATE TABLE `test_tb` (
  `id` int(5) unsigned NOT NULL,
  `age` tinyint(5) unsigned NOT NULL,
  `account` int(11) unsigned NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=innodb DEFAULT CHARSET=utf8;

INSERT INTO `test_tb`(`id`, `age`, `account`, `name`) VALUES (1, 11, 5000, '張三');
INSERT INTO `test_tb`(`id`, `age`, `account`, `name`) VALUES (2, 12, 5000, '李四');
複製程式碼

事務正常提交

set autocommit = 0;
start TRANSACTION;
update test_tb set account = account - 4000 where name = '張三';
update test_tb set account = account + 4000 where name = '李四';
commit;
複製程式碼

事務回滾

set autocommit = 0;
start TRANSACTION;
update test_tb set account = account - 4000 where name = '張三';
update test_tb set account = account + 4000 where name = '李四';
rollback;
複製程式碼

同時執行多個事務的時候...

對於同時執行的多個事務, 如果沒有采用必要的隔離機制, 就會導致各種併發問題

事務衝突.png

髒讀

  • 對於兩個事務T1,T2, T1讀取了已經被T2更新但是還沒有被提交的欄位之後
  • 如果T2回滾, T1讀取的內容就是臨時並且無效的
  • 比如, 有一天, 你查詢餘額, 發現多了100萬!
  • 你難以壓抑自己激動的心情, 給親朋好友挨個打電話, 約他們吃飯
  • 等打完電話, 再看餘額, 發現100萬又沒有了...
  • 原來是銀行把錢打錯了, 剛剛撤銷了之前的轉賬...

幻讀

  • 對於兩個事務T1,T2, T1從一個表中讀取了一些資料
  • 然後T2在該表中插入/刪除了一些新的行之後, 如果T1再次讀取同一個表, 就會多出/少了幾行.
  • 彷彿出現了幻覺
  • 比如, 你看見屋裡只有兩個人, 乾乾巴巴, 麻麻賴賴, 一點都不圓潤
  • 知道吃得少, 所以你想耍一下大方
  • 就說"我今天請所有人吃飯!"
  • 剛說完, 又從外面進來兩個, 他們問你:"你說請所有人吃飯? 好的! 好的!"
  • 你不敢相信自己的眼睛, 彷彿出現了幻覺...

image.png

不可重複讀

  • 對於兩個事務T1,T2, T1讀取了一個欄位,
  • 然後T2更新了該欄位並提交, T1再次讀取同一個欄位, 值就不同了.
  • 比如上午你去淘寶買東西, 一個手機1999, 果斷下單
  • 到下午再看的時候, 手機已經發貨, 但是售價變成了1799
  • 發生悲劇的原因是, 上午賣家修改了價格, 但是沒有提交, 下午已經提交了
  • 所以上午和下午, 你看到的價格不一樣...

資料庫事務的隔離性:

  • 資料庫系統必須具有隔離併發執行各個事務的能力, 使他們不會互相影響, 避免各種併發問題.
  • 一個事務與其他事務隔離的程度稱為隔離級別
  • 資料庫規定了多種事務隔離級別, 不同隔離級別對應不同的干擾程度
  • 隔離級別越高, 資料一致性越好, 但併發性越弱
  • 就像一次只做一件事, 沒有其他事情的干擾, 肯定不容易出錯, 但是效率也會比較低

4種隔離級別

  1. 讀未提交資料(read uncommitted)
    • 允許事務讀取未被其他事務提交的變更
    • 髒讀, 不可重複讀和幻讀, 都會出現
  2. 讀已提交資料(read commited)
    • 只允許事務讀取已經被其他事務提交的變更, 可以避免髒讀
    • 但不可重複讀和幻讀問題仍然可能出現
  3. 可重複讀(repeatable read)
    • 確保事務可以從一個欄位中讀取相同的值, 在這個事務持續期間, 禁止其他事務對這個欄位進行更新
    • 可以避免髒讀和不可重複讀, 但是幻讀問題讓然存在
  4. 序列化(serializable)
    • 確保事務可以從一個表中讀取相同的行
    • 在這個事務持續期間, 禁止其他事務對該表執行插入, 更新和刪除操作, 所有併發問題都可以避免
    • 但是效能十分低下

image.png

MySQL支援以上全部四種事務隔離級別, 預設 repeatable read(可重複讀)

檢視當前隔離級別

select @@tx_isolation;
-- 或者
show variables like "tx_isolation";
複製程式碼

設定隔離級別

set session transaction isolation level repeatable read; // 當前會話
-- 或者
set global transaction isolation level repeatable read; // 全域性
複製程式碼

隔離級別驗證

感興趣的小夥伴們, 可以測試一下各個隔離級別的不同 下面是測試需要時, 用到的資料

DROP TABLE IF EXISTS test;
CREATE TABLE test (
	id INT auto_increment PRIMARY KEY,
	NAME VARCHAR ( 10 ) NOT NULL,
	account INT ( 11 ) NOT NULL,
	age TINYINT ( 1 ) NOT NULL,
	sex CHAR ( 1 ) NOT NULL DEFAULT '男' 
);
INSERT INTO test ( NAME, account, age )
VALUES
	( '張三', 3000, 18 ),
	( '李四', 4000, 28 ),
	( '王五', 5000, 38 ),
	( '趙六', 6000, 48 ),
	( '孫七', 2000, 19 ),
	( '周八', 1000, 29 ),
	( '吳老九', 9000, 39 ),
	( '馮老十', 8000, 49 );
複製程式碼

衝突的級別

髒讀、不可重複讀、幻讀的級別高低是：

髒讀 < 不可重複讀 < 幻讀。

所以，設定了最高階別的serializable就不用在設定repeatable read和read committed了

回滾點(savepoint)

就像玩遊戲時的存檔點, 如果遊戲人物死了, 就會在存檔點復活

直接上例子, 以上面的資料為例

set autocommit = 0;
start TRANSACTION;
update test set account = 9999 where id = 1;
SAVEPOINT a;
update test set account = 9999 where id = 2;
rollback to a;

select * from test;
複製程式碼

image.png

快速跳轉

相關文章