MySQL事務
先來看一個例子
有一張balance表:
需求：將tom的100塊錢轉到King賬戶中
執行的操作是：
update balance set money = money -100 where id = 100
update balance set money = money +100 where id = 200
這時，如果第一條陳述句執行成功，但第二條陳述句執行失敗，就會出現問題，
這里引出一個需求，將多個dml陳述句（update，insert，delete）當做一個整體，要么全部成功，要么全部失敗
--->使用事務來解決
1.什么是事務
- 什么是事務
事務用于保證資料的一致性，它由一組相關的dml陳述句(update，insert，delete)組成，該組的dml陳述句要么全部成功，要么全部失敗，如：轉賬就要用事務來處理，用以保證資料的一致性，
- 事務和鎖
當執行事務操作時（dml陳述句），mysql會在表上加鎖，防止其他用戶修改表的資料，這對用戶來講是非常重要的，
- mysql資料庫控制臺事務的幾個重要操作（基本操作）
注意：當直接回退到保存點a時，會洗掉中間的保存點b
1.start transaction --開始一個事務
2.savepoint 保存點名 -- 設定保存點
3.rollback to 保存點名 -- 回退事務
4.rollback -- 回退全部事務
5.commit -- 提交事務，所有的操作生效，不能回退
細節：
- 沒有設定保存點
- 多個保存點
- 存盤引擎
- 開始事務方式
- 回退事務
在介紹回退事務前，先介紹一下保存點（savepoint），保存點是事務中的點，用于取消部分事務，當結束事務時（commit），會自動地洗掉該事務所定義的所有保存點，
當執行回退事務時，通過指定保存點可以回退到指定的點
- 提交事務
使用commit陳述句可以提交事務，當執行了commit陳述句之后，會確認事務的變化、結束事務、洗掉保存點、釋放鎖，資料生效，
當使用了commit陳述句結束事務之后，其它會話[其他連接] 可以查看到事務變化后的新資料 [所有的資料正式生效]
例子
-- 事務的演示操作
-- 1.創建一張測驗表
CREATE TABLE t27(
id INT ,
`name` VARCHAR(32)
);
SELECT * FROM t27;
-- 2.開始事務
START TRANSACTION;
-- 3.設定保存點
SAVEPOINT a;
-- 4.執行dml操作1
INSERT INTO t27 VALUES(100,'tom');
-- 設定保存點b
SAVEPOINT b;
-- 執行dml操作2
INSERT INTO t27 VALUES(200,'jack');
-- 回退到b
ROLLBACK TO b
-- 繼續回退a
ROLLBACK TO a
-- 如果是rollback，表示直接回退到事務開始的狀態
ROLLBACK
COMMIT
2.事務注意事項
- 如果不開始事務，默認情況下，dml操作是自動提交的，不能回滾
- 如果開始一個事務，你沒有創建保存點，也可以執行rollback，默認就是回到事務開始的狀態
- 可以在事務中（還沒有提交時），創建多個保存點，比如：savepoint aaa；執行dml，savepoint bbb；
- 可以在事務沒有提交前，選擇回退到哪個保存點
- innodb的存盤引擎支持事務，myisam不支持
- 開始一個事務的方式 start transaction或者set autocommit = off;
例子
-- 討論事務細節
-- 1. 如果不開始事務，默認情況下，dml操作是自動提交的，不能回滾
INSERT INTO t27 VALUES(300,'milan'); -- 自動提交 commit
SELECT * FROM t27;
-- 2. 如果開始一個事務，你沒有創建保存點，也可以執行rollback，
-- 默認就是回到事務開始的狀態
START TRANSACTION
INSERT INTO t27 VALUES(400,'king');
INSERT INTO t27 VALUES(500,'scott');
ROLLBACK -- 表示直接回退到事務開始的狀態
COMMIT
-- 3. 可以在事務中（還沒有提交時），創建多個保存點，
-- 比如：savepoint aaa；執行dml，savepoint bbb；
-- 4. 可以在事務沒有提交前，選擇回退到哪個保存點
-- 5. innodb的存盤引擎支持事務，myisam不支持
-- 6. 開始一個事務的方式 start transaction或者set autocommit = off；
SET autocommit = off
3.事務的四種隔離級別
- 事務隔離級別介紹
- 多個連接開啟各自的事務，操作資料庫中的資料時，資料庫系統要負責隔離操作，以保證各個連接在獲取資料時的準確性，
- 如果不考慮隔離性，可能會引發如下問題：
- 臟讀（dirty read）：當一個事務讀取另一個事務尚未提交的改變（delete,insert,update）時，產生臟讀
- 不可重復讀（nonrepeatable read）：同一個查詢在同一事務中多次進行，由于其他已提交事務所做的修改或洗掉，每次回傳不同的結果集，此時發生不可重復讀
- 幻讀（phantom read）：虛讀，同一查詢在同一事務中多次進行，由于其他已提交事務所做的插入操作，每次回傳不同的結果集，此時發生幻讀
- 事務隔離級別
概念：MySQL隔離級別定義了事務與事務之間的隔離程度
| MySQL隔離級別（4種） | 臟讀 | 不可重復讀 | 幻讀 | 加鎖讀 |
|---|---|---|---|---|
| 讀未提交（read uncommitted） | 會出現 | 會出現 | 會出現 | 不加鎖 |
| 讀已提交（read committed） | 不會出現 | 會出現 | 會出現 | 不加鎖 |
| 可重復讀（repeatable read） | 不會出現 | 不會出現 | 不會出現 | 不加鎖 |
| 可串行化（serializable） | 不會出現 | 不會出現 | 不會出現 | 加鎖 |
可重復讀實際上會發生幻讀？
3.1讀未提交（read uncommitted）
MySQL的事務隔離級別--案例
我們舉例一個案例來說明mysql的事務隔離級別，以account表進行操作為例，（id、name、money）
-
開啟兩個mysql的控制臺
-
查看當前mysql的隔離級別，均為可重復讀
mysql> select @@tx_isolation; +-----------------+ | @@tx_isolation | +-----------------+ | REPEATABLE-READ | +-----------------+ 1 row in set (0.00 sec)
-
將其中一個連接的隔離級別設定為 read uncommitted（讀未提交）
-- 把其中一個控制臺的隔離級別設定為read uncommitted SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
此時的情況：左邊的隔離級別為讀未提交；右邊的隔離級別為可重復讀
-
然后兩邊都開啟事務
-
在兩個連接控制臺中選擇資料庫，在隔離級別為 可重復讀 的連接中 創建表account
-
再在隔離級別為 可重復讀 的連接中插入一條資料（但未提交）
在另一個連接（隔離級別為 讀未提交 READ-UNCOMMITTED）中查詢該表，發現可以查詢到另一事務尚未提交的插入的資料，這時就發生了臟讀
臟讀：當一個事務讀取另一個事務尚未提交的改變（delete,insert,update）時，產生臟讀
-
在隔離級別為 可重復讀 的連接中更新一條資料，同時插入一條資料，然后提交commit
此時在另一個連接（隔離級別為 讀未提交）中，查詢同一張表，可以看到在這個（隔離級別為 讀未提交的）連接中，已經可以看到另一個事務中提交的資料
即一個事務的操作影響了另一個事務的查詢，這時候就發生了不可重復讀和幻讀
這將會導致，當有多個連接開啟事務時，某一連接的事務的查詢會受到其他所有連接的事務的影響，這無疑將會導致混亂
最佳情況應該是：一個連接 連接到資料庫，操作account表的時候，希望看到的資料應該是，開啟事務的這一時刻的資料
-
在連接（隔離級別為 讀未提交）中提交commit結束一個事務，此時兩個連接中的事務均已結束
3.2讀已提交（read committed）
例子
-
在上個例子開啟的兩個連接中，將其中一個連接的隔離級別修改為 讀已提交，
另一個保持隔離級別為可重復讀
-
兩邊都開啟事務
-
在隔離級別為可重復讀的連接中插入一條資料
然后在隔離級別為讀已提交的連接中 插詢表account，可以看到查詢到的資料還是本連接開啟事務時的資料
即，讀已提交的隔離級別不會出現臟讀現象
-
在隔離級別為可重復讀的連接中更新一條資料
? 然后在隔離級別為讀已提交的連接中 插詢表account，可以看到查詢到的資料變成了其他連接的事務提交的 資料，說明，在隔離級別為讀已提交下，出現了不可重復讀和幻讀
3.3可重復讀（repeatable read）
-
重新開啟兩個連接，兩個連接的隔離級別均為可重復讀
-
然后兩邊均開啟事務
-
在一個連接中選擇資料庫，然后在account表中插入一條資料，再更新一條資料（未提交）
此時該連接中的表情況為：
在另一個連接中選擇資料庫，查詢表account，可以看到查詢到的表資料依舊是開啟事務時的樣子，沒有受到其他事務的影響，即沒有產生臟讀
-
在原先修改資料的連接中輸入commit提交
在另一個連接中再查詢表account，可以看到資料依舊是開啟事務的時刻的樣子
即，沒有產生不可重復讀和幻讀
綜上，隔離級別為可重復讀的情況下 既不會出現臟讀，也不會出現不可重復讀和幻讀
3.4可串行化（serializable）
-
將上面兩個連接其中一個重新啟動，將新連接設定隔離級別為可串行化（serializable）
此時兩個連接的隔離級別分別為 可重復讀 和可串行化（serializable）
-
這時分別在兩個連接中均開啟事務
-
在隔離級別為可重復讀的連接中分別插入、更新資料（未提交）
? 在另一個隔離級別為可串行化的連接中選擇資料庫，然后查詢表account，回車時會發現卡住了，這是因為 可串行化會加鎖
A連接在操作表的時候，事務還沒有結束，這時B連接也嘗試操作該表，此時將會檢查A的事務有沒有結束，如果沒有結束，B連接的操作就會進行等待，直到A連接的事務提交
? 這時，在隔離級別為可重復讀的連接中提交事務
? 可以看到可串行化級別的連接中可以成功操作表了
?
綜上說明，可串行化級別下，不僅不會出現臟讀、不可重復讀、幻讀，還會加鎖讀
4.設定隔離
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/511105.html
標籤：其他
上一篇：MySQL 視窗函式