03 | 事務隔離:為什麼你改了我還看不見? 事務 Transaction TRX 事務就是要保證一組資料庫操作,要麼全部成功,要麼全部失敗。 MySQL 原生的 MyISAM 引擎不支持事務 隔離性與隔離級別 SQL 標準的事務隔離級別包括:讀未提交(read uncommitted)、讀提交(r ...
03 | 事務隔離:為什麼你改了我還看不見?
事務 Transaction TRX
事務就是要保證一組資料庫操作,要麼全部成功,要麼全部失敗。
MySQL 原生的 MyISAM 引擎不支持事務
隔離性與隔離級別
SQL 標準的事務隔離級別包括:讀未提交(read uncommitted)、讀提交(read committed)、可重覆讀(repeatable read)和串列化(serializable )。Mysql預設為可重覆讀。
在實現上,資料庫裡面會創建一個視圖,訪問的時候以視圖的邏輯結果為準。
CREATE VIEW ygb_view AS SELECT * FROM 員工表 WHERE 員工表.性別=‘女’
事務隔離的實現
在 MySQL 中,實際上每條記錄在更新的時候都會同時記錄一條回滾操作。記錄上的最新值,通過回滾操作,都可以得到前一個狀態的值。
假設一個值從 1 被按順序改成了 2、3、4,在回滾日誌裡面就會有類似下麵的記錄。當前值是 4,但是在查詢這條記錄的時候,不同時刻啟動的事務會有不同的 read-view。如圖中看到的,在視圖 A、B、C 裡面,這一個記錄的值分別是 1、2、4,同一條記錄在系統中可以存在多個版本,就是資料庫的多版本併發控制(MVCC)。
即使現在有另外一個事務正在將 4 改成 5,這個事務跟 read-view A、B、C 對應的事務是不會衝突的。
系統會判斷,當沒有事務再需要用到這些回滾日誌時,回滾日誌會被刪除。
什麼時候才不需要了呢?就是當系統里沒有比這個回滾日誌更早的 read-view 的時候。
有些客戶端連接框架會預設連接成功後先執行一個 set autocommit=0 的命令。這就導致接下來的查詢都在事務中,如果是長連接,就導致了意外的長事務。
儘量不要使用長事務:
- 長事務意味著系統裡面會存在很老的事務視圖。由於這些事務隨時可能訪問資料庫裡面的任何數據,所以這個事務提交之前,資料庫裡面它可能用到的回滾記錄都必須保留,這就會導致大量占用存儲空間。
- 除了對回滾段的影響,長事務還占用鎖資源,也可能拖垮整個庫
事務的啟動方式
MySQL 的事務啟動方式:
- 顯式啟動事務語句, begin 或 start transaction。配套的提交語句是 commit,回滾語句是 rollback。
- set autocommit=0,這個命令會將這個線程的自動提交關掉。意味著如果你只執行一個 select 語句,這個事務就啟動了,而且並不會自動提交。這個事務持續存在直到你主動執行 commit 或 rollback 語句,或者斷開連接。
正確做法:
在 autocommit 為 1 的情況下,用 begin 顯式啟動的事務,如果執行 commit 則提交事務。如果執行 commit work and chain,則是提交事務並自動啟動下一個事務,這樣也省去了再次執行 begin 語句的開銷。
可以在 information_schema 庫的 innodb_trx 這個表中查詢長事務
select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60