TCL 事務控制語言
SQL的四種語言
- DDL（
Data Definition Language）資料庫定義語言 建表, 建庫, 修改表結構 - DML（
Data Manipulation Language）資料操縱語言 增刪改查, select, insert, update, delete - DCL（
Data Control Language）資料庫控制語言 修改使用者許可權, 重置密碼 - TCL（
Transaction Control Language）事務控制語言 建立事務, 回滾
什麼是事務
一組SQL語句組成的執行單元, 這一組SQL語句, 要麼全部執行, 要麼全部不執行.
複製程式碼舉例
- 郭靖向黃蓉轉賬(交工資)
- 郭靖這個月發了5000, 銀行卡餘額5000
- 黃蓉, 銀行卡餘額5000,000
- update set 郭靖的銀行卡餘額 - 5000;
- update set 黃蓉的銀行卡餘額 + 5000;
- 我們需要兩條SQL語句都要成功, 否則我們寧願沒執行過, 所以就是, 要麼全部執行, 要麼全部不執行.
事務：
- 事務由單獨單元的一個或多個SQL語句組成
- 在這個單元中，每個MySQL語句是相互依賴的
- 而整個單獨單元作為
一個不可分割的整體 - 如果單元中某條SQL語句一旦執行失敗或產生錯誤，整個單元將會
回滾 - 所有受到影響的資料將返回到事物開始以前的狀態
- 如果單元中的所有SQL語句均執行成功，則事物被順利執行
舉例2:
- 軟體安裝, 會有很多
下一步, 如果點選取消, 則全部回滾.
事務 vs 儲存引擎(表型別)
什麼是儲存引擎(表型別):
- MySQL中的資料用各種不同的技術儲存在檔案(或者記憶體)中。
- 這些技術中的每一種技術都使用不同的儲存機制、索引技巧、鎖定水平並且最終提供廣泛的不同的功能和能力。
- 通過選擇不同的技術，你能夠獲得額外的速度或者功能，從而改善你的應用的整體功能。
- 如果你在研究大量的臨時資料，你也許需要使用記憶體MySQL儲存引擎(
memory)。 - 記憶體儲存引擎能夠在記憶體中儲存所有的表格資料。
- 又或者，你需要一個支援
事務處理的資料庫(以確保事務處理不成功時資料的回退能力), 那就用innodb。 - 這些不同的技術以及配套的相關功能在 MySQL中被稱作
儲存引擎(也稱作表型別)。
為什麼需要多個儲存引擎?
- 每種引擎都有各自的優勢和不足, 沒有一種完美的儲存引擎
- 所以MySQL可以針對不同的表, 選擇不同的儲存引擎
- 就像雷達圖
如何設定表的儲存引擎?
create table tb (id int) engine = myisam;
-- 或者
alter table tb engine = memory;
複製程式碼檢視所有儲存引擎
show engines;
複製程式碼
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;
複製程式碼事務的建立
-
開啟事務
set autocommit = 0; start transaction; // 可選 複製程式碼 -
編寫事務中的sql語句(
select,insert,update,delete) 不包括DDL(create,drop,alter) -
結束事務
- 提交事務
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;
複製程式碼同時執行多個事務的時候...
對於同時執行的多個事務, 如果沒有采用必要的隔離機制, 就會導致各種併發問題
髒讀
- 對於兩個事務T1,T2, T1讀取了已經被T2
更新但是還沒有被提交的欄位之後 - 如果T2回滾, T1讀取的內容就是臨時並且無效的
- 比如, 有一天, 你查詢餘額, 發現多了100萬!
- 你難以壓抑自己激動的心情, 給親朋好友挨個打電話, 約他們吃飯
- 等打完電話, 再看餘額, 發現100萬又沒有了...
- 原來是銀行把錢打錯了, 剛剛撤銷了之前的轉賬...
幻讀
- 對於兩個事務T1,T2, T1從一個表中讀取了一些資料
- 然後T2在該表中
插入/刪除了一些新的行之後, 如果T1再次讀取同一個表, 就會多出/少了幾行. - 彷彿出現了幻覺
- 比如, 你看見屋裡只有兩個人, 乾乾巴巴, 麻麻賴賴, 一點都不圓潤
- 知道吃得少, 所以你想耍一下大方
- 就說"我今天請所有人吃飯!"
- 剛說完, 又從外面進來兩個, 他們問你:"你說請所有人吃飯? 好的! 好的!"
- 你不敢相信自己的眼睛, 彷彿出現了
幻覺...
不可重複讀
- 對於兩個事務T1,T2, T1讀取了一個欄位,
- 然後T2
更新了該欄位並提交, T1再次讀取同一個欄位, 值就不同了. - 比如上午你去淘寶買東西, 一個手機1999, 果斷下單
- 到下午再看的時候, 手機已經發貨, 但是售價變成了1799
- 發生悲劇的原因是, 上午賣家修改了價格, 但是沒有提交, 下午已經提交了
- 所以上午和下午, 你看到的價格不一樣...
資料庫事務的隔離性:
- 資料庫系統必須具有隔離併發執行各個事務的能力, 使他們不會互相影響, 避免各種併發問題.
- 一個事務與其他事務隔離的程度稱為隔離級別
- 資料庫規定了多種事務隔離級別, 不同隔離級別對應不同的干擾程度
- 隔離級別越高, 資料一致性越好, 但併發性越弱
- 就像
一次只做一件事, 沒有其他事情的干擾, 肯定不容易出錯, 但是效率也會比較低
4種隔離級別
- 讀未提交資料(
read uncommitted)- 允許事務讀取未被其他事務提交的變更
髒讀,不可重複讀和幻讀, 都會出現
- 讀已提交資料(
read commited)- 只允許事務讀取已經被其他事務提交的變更, 可以避免髒讀
- 但
不可重複讀和幻讀問題仍然可能出現
- 可重複讀(
repeatable read)- 確保事務可以從一個欄位中讀取相同的值, 在這個事務持續期間, 禁止其他事務對這個欄位進行更新
- 可以避免
髒讀和不可重複讀, 但是幻讀問題讓然存在
- 序列化(
serializable)- 確保事務可以從一個表中讀取相同的行
- 在這個事務持續期間, 禁止其他事務對該表執行插入, 更新和刪除操作,
所有併發問題都可以避免 - 但是效能十分低下
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;
複製程式碼