MySQL 事務操作

雲崖先生發表於2020-09-05
MySql

基礎知識

　　 事務是指對一組SQL語句進行一個原子化的操作，即如果這一組SQL語句中有一條發生錯誤，那麼其他的同組SQL就都不會被執行。

　　 你可以把它當作一個測試，當你執行完一組SQL語句後，可以檢視一下結果是否正確，如果正確後可以選擇提交，如果不正確則可以進行回滾，恢復到原本的狀態。

　　 在MySQL中，所有的操作預設都是自動進行提交，當開啟事務後則變為手動提交。

基本使用

單獨開啟

　　 單獨開啟是指對某一組的SQL語句開啟事務。

CREATE TABLE user(
        id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
        name CHAR(12) NOT NULL,
        balance INT UNSIGNED 
); -- 建立使用者表

INSERT INTO user(name,balance) VALUES
        ("Yunya",1000),
        ("Ken",500); -- 插入資料


start transaction; -- 開啟事務，增刪改操作均要手動提交

        UPDATE user SET balance = 500 WHERE name = "Yunya"; -- Yunya對Ken轉賬500
        UPDATE user SET balance = 1000 WHERE name = "Ken";
        SELECT * FROM user; -- 驗證是否出錯

        COMMIT; -- 提交事務：手動提交上面兩條UPDATE
        -- ROLLBACK; -- 事務回滾：轉賬金額不對時使用回滾

BEGIN -- 關閉事務，增刪改操作均自動提交

全域性開啟

　　 如果所有SQL都使用事務操作，我們可以通過 SET AUTOCOMMIT=0 關閉自動提交來開啟事務機制，這樣所有語句都是事務型別。

-- 關閉自動提交
SET AUTOCOMMIT = 0;

INSERT INTO user(name,balance) VALUES
	('Jack',8000);
	
COMMIT;

-- 開啟自動提交
SET AUTOCOMMIT = 1;

事務隔離

併發問題

　　 當高併發訪問會遇到多個事務的隔離問題，可能會出現以下：

  1. 髒讀：事務A讀取了事務B更新的資料，然後B回滾操作，那麼A讀取到的資料是髒資料
  2. 不可重複讀：事務A多次讀取同一資料，事務B在事務A多次讀取的過程中，對資料作了更新並提交，導致事務A多次讀取同一資料時，結果不一致。
  3. 幻讀：系統管理員A將資料庫中所有學生的成績從具體分數改為ABCDE等級，但是系統管理員B就在這個時候插入了一條具體分數的記錄，當系統管理員A改結束後發現還有一條記錄沒有改過來，就好像發生了幻覺一樣，這就叫幻讀。

　　 不可重複讀的和幻讀很容易混淆，不可重複讀側重於修改，幻讀側重於新增或刪除。解決不可重複讀的問題只需鎖住滿足條件的行，解決幻讀需要鎖表

隔離級別

　　 系統預設隔離級別為3級，可能出現幻讀的情況。

隔離級別中文釋義髒讀不可重複讀幻讀說明
read uncommitted 讀未提交 是 是 是 最低的事務隔離級別，一個事務還沒提交時，它做的變更就能被別的事務看到
read committed 不可重複讀 否 是 是 保證一個事物提交後才能被另外一個事務讀取。另外一個事務不能讀取該事物未提交的資料
repeatable read 可重複讀 否 否 是 多次讀取同一範圍的資料會返回第一次查詢的快照，即使其他事務對該資料做了更新修改。事務在執行期間看到的資料前後必須是一致的
serializable 序列化 否 否 否 事務 100% 隔離，可避免髒讀、不可重複讀、幻讀的發生。花費最高代價但最可靠的事務隔離級別

　　 一般來說系統預設的3級就足以應付大部分應用場景，但是設計金融類資料時一定要慎重。

查詢設定

　　 查詢隔離級別

select @@tx_isolation;

　　 設定隔離級別

set session transaction isolation level read uncommitted; -- set session只對當前會話有效，set global則對全域性有效

相關文章