MySQL中不得不提的事務處理

浪子程式設計走四方
作者:浪子程式設計走四方,勤記錄，懂分享,刻意練習，日精進!
記得前些日子分享過一篇有關MySQL中事務的知識點,但當時對MySQL中的事務只是純粹的知道如何使用,缺乏對理論的進一步認識,抽時間單獨去了解了一下,便在做一個較為全面的總結.

什麼是事務?

用MySQL官方的一句話來描述事務是什麼?MySQL 事務主要用於處理操作量大,複雜度高的資料.那何為資料量大?何為複雜度高呢?我用我自己的理解來描述一下吧.事務其實就是MySQL中處理資料的一種方式,主要用在資料完整性高,資料之間依賴性大的情況下的一種資料處理方式.舉個例子,小張向小李的銀行卡打200塊錢,在小張點選了確認轉賬的按鈕時,系統突然崩潰了.會出現這樣幾中不正確的情況:

1.小張的錢打到小李的賬戶上,但是自己的賬戶上的錢沒被扣.

2.小張的錢打沒到小李的賬戶上了,但是自己賬戶上的錢被扣.

這樣的業務場景就需要MySQL事務保持,即使機器出故障的情況下,資料仍然是正確的.

事務使用的條件

MySQL要使用事務,需要MySQL中的儲存引擎支援.現目前MySQL內建的儲存引擎支援事務的有InnoDB、NDB cluster,第三方的儲存引擎有PBXT和XtrDB.

事務有什麼特點?

MySQL中的事務有如下幾個特點(ACID):

原子性(atomicity):

一個事務必須被作為一個不可分割的最小工作單元,每個事務中的所有操作必須要麼成功,或者要麼失敗,永遠不可能一些操作失敗,一些操作成功,這就是所謂的原子性的概念.

一致性(consistency):

一致性就像上面舉的一個例子一樣,當發生異常情況下,資料仍然是正確的.就是說當一個事務執行失敗了,資料之間是不會受異常的情況而影響,永遠保持著他的正確性.

隔離性(isolation):

當一個事務還未提交,每個事務之間是相互隔離的,互補受到影響.

永續性(durability):

當一個事務進行提交之後,發生的變化就會永遠儲存在資料庫中.

事務的隔離級別

在談及到MySQL的隔離性的特點,就不得不說說隔離性的幾種級別.至於為什麼會涉及到這一點,可以這樣簡單的理解:如果同一時刻,有兩個請求在執行事務的操作,並且這兩個事務是對同一條資料做操作,那麼到底最終的結果是以誰的為準呢?不同的隔離級別導致的結果不一樣,因此事務的隔離級別也是一個非常重要的點.

隔離級別分為如下幾點:

1.未提交讀(READ UNCOMMITTED)

一個事務中對資料所做的修改,即使沒有提交,這個修改對其他的事務仍是可見的,這種情況下就容易出現髒讀,影響了資料的完整性.

舉例:小明在用支付寶支付時,檢視了銀行卡的餘額還有300塊,其實只有100塊,只是因為他女朋友正在向銀行卡存款了200塊,此時女朋友不想存了,點選了回滾操作,小明進行支付卻失敗了.

2.讀提交(READ COMMITTED)

一個事務開始時,只能看見其他已經提交過的事務.這種情況下容易出現不可重複讀(兩次讀的結果不一樣).

舉例:同樣用上面的例子舉例,當他女朋友在刷卡時卡里餘額有100塊,但是在點選最終支付時,提示餘額不足,此時看卡里的錢沒了.這是因為小明女朋友在支付時,小明操作的事務還未提交,所以小明女朋友兩次看到的結果不一樣.

3.可重複讀(REPEATABLE READ)

多次讀取記錄的結果都是一致的,可重複讀可以解決上面的不可重複讀的情況.但是有這樣一種情況,當一個事務在讀取某個範圍的記錄時,另外一個事務在這個範圍內插入了一條新的資料,當事務再次進行讀取資料時,發現比第一次讀取記錄多了一條,這就是所謂的幻讀,兩次讀取的結果不一致.

舉例:小明女朋友在檢視銀行卡的記錄時,看見有5條消費記錄,此時小明正在消費,這時候消費記錄裡面記錄了這條消費記錄,當女朋友再次讀取記錄時,發現有6條記錄了.

4.可序列(SERIALIZABLE)

序列就像一個佇列一個樣,每個事務都是排隊等候著執行,只有前一個事務提交之後,下一個事務才能進行操作.這種情況雖然可以解決上面的幻讀,但是他會在每一條資料上加一個鎖,容易導致大量的鎖超時和鎖競爭,特別不適用在一些高併發的業務場景下.

舉例:我們在銀行排隊存錢,只有前一個人全部操作完,下一個人才可以進行辦理.中間的人是不可以插隊的,只能一個一個的排對,事務的序列就是這樣的一個概念,其實所謂的序列模式都是這樣的一個概念.

| 事務隔離級別 | 髒讀 | 不可重複度 | 幻讀 | 加鎖度 |

| :—: | :—: | :—: | :—: | :—: |

| 未提交讀 | √ | √ | √ | × |

| 提交讀 | × | √ | √ | × |

| 可重複讀 | × | × | √ | × |

| 可重複讀 | × | × | × | √ |

隔離性總結

透過上面的舉例,我們不難發現.髒讀和不可重複讀重在更新資料,然後幻讀重在插入資料.

多種儲存引擎時事務的處理方式

根據上面事務使用的條件,我們可以得知有的儲存引擎是不支援事務的,例如MyISAM儲存引擎就不支援.那如果在一個事務中使用了事務性的儲存引擎和非事務性的儲存,提交是可以正常進行,但是回滾非事務性的儲存引擎則會顯示響應的錯誤資訊,具體資訊和儲存引擎有關.

如何使用事務

MySQL中事務隱式開啟的,也就是說,一個sql語句就是一個事務,當sql語句執行完畢,事務就提交了.在演示的過程中,我們顯式開啟.

MySQL中的自動提交

上面提到了MySQL中事務是隱式開啟的,則代表我們每一個sql是自動提交的,需要關閉則需要設定autocommit選項.

// 檢視autocommit配置值（1或者ON則表示開啟）

mysql root@127.0.0.1:(none)> show variables like '%autocommit%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | ON |

+---------------+-------+

1 row in set

Time: 0.018s

// 設定autocommit配置值

mysql root@127.0.0.1:(none)> set autocommit = 0;

Query OK, 0 rows affected

Time: 0.000s

mysql root@127.0.0.1:(none)> show variables like '%autocommit%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| autocommit | OFF |

+---------------+-------+

1 row in set

Time: 0.013s

1.表結構如下

mysql root@127.0.0.1:test> desc user;

+-------+--------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-------+--------------+------+-----+---------+----------------+

| id | int(11) | NO | PRI | <null> | auto_increment |

| name | varchar(255) | YES | | <null> | |

| age | int(2) | YES | | <null> | |

+-------+--------------+------+-----+---------+----------------+

3 rows in set

Time: 0.013s

SQL語句

CREATE TABLE `test`.`Untitled` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,

`age` int(2) NULL DEFAULT NULL,

PRIMARY KEY (`id`) USING BTREE

) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

2.使用事務

MySQL實現事務

下面的程式碼,我們主要做了如下幾個操作

a.開啟事務

b.修改資料

c.查詢資料是否改變

d.資料回滾

e.再次查詢資料,發現資料變回修改之前的狀態

f.修改資料

g.事務提交

h.查詢資料,發現資料變為最後一次修改的狀態

i.嘗試事務回滾

j.查詢驗證是否被回滾了,發現資料還是為最後一次修改的狀態,事務回滾失敗

// 我們先檢視錶中的資料,id為1的age欄位是12

mysql root@127.0.0.1:test> select * from user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 張三 | 12 |

| 2 | 李四 | 15 |

+----+------+-----+

2 rows in set

Time: 0.013s

// 開啟事務

mysql root@127.0.0.1:test> begin;

Query OK, 0 rows affected

Time: 0.001s

// 將id為1的age欄位改為10

mysql root@127.0.0.1:test> update user set age=10 where id=1;

Query OK, 1 row affected

Time: 0.001s

// 再次查詢資料時,發現資料改為修改後的值

mysql root@127.0.0.1:test> select * from user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 張三 | 10 |

| 2 | 李四 | 15 |

+----+------+-----+

2 rows in set

Time: 0.012s

// 此時我們進行回滾操作

mysql root@127.0.0.1:test> rollback;

Query OK, 0 rows affected

Time: 0.001s

// 再次查詢發現資料回到最初狀態

mysql root@127.0.0.1:test> select * from user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 張三 | 12 |

| 2 | 李四 | 15 |

+----+------+-----+

2 rows in set

Time: 0.019s

// 我們再次對資料進行修改

mysql root@127.0.0.1:test> update user set age=15 where id=1;

Query OK, 1 row affected

Time: 0.001s

// 此時將事務進行提交

mysql root@127.0.0.1:test> commit;

Query OK, 0 rows affected

Time: 0.000s

// 發現此時的資料變為我們最終提交的值

mysql root@127.0.0.1:test> select * from user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 張三 | 15 |

| 2 | 李四 | 15 |

+----+------+-----+

2 rows in set

Time: 0.012s

// 我們嘗試用剛才回滾的方式進行還原資料

mysql root@127.0.0.1:test> rollback;

Query OK, 0 rows affected

Time: 0.000s

// 發現資料無法回退了,仍然是提交後的資料

mysql root@127.0.0.1:test> select * from user;

+----+------+-----+

| id | name | age |

+----+------+-----+

| 1 | 張三 | 15 |

| 2 | 李四 | 15 |

+----+------+-----+

2 rows in set

Time: 0.017s

PHP實現事務例項程式碼

<?php

// 連線MySQL

$mysqli = new mysqli('127.0.0.1', 'root', '123456', 'test', 3306);

// 關閉事務自動提交

$mysqli->autocommit(false);

// 1.開啟事務

$mysqli->begin_transaction();

// 2.修改資料

$mysqli->query("update user set age=10 where id=1");

// 3.檢視資料

$mysqli->query("select * from user");

// 4.事務回滾

$mysqli->rollback();

// 5.檢視資料

$mysqli->query("select * from user");

// 7.修改資料

$mysqli->query("update user set age=15 where id=1");

// 8.事務提交

$mysqli->commit();

// 9.事務回滾

$mysqli->rollback();

// 10.檢視資料

$mysqli->query("select * from user");

如何設定事務的隔離級別

// 檢視當前的事務隔離級別

mysql root@127.0.0.1:test> select @@tx_isolation;

+-----------------+

| @@tx_isolation |

+-----------------+

| REPEATABLE-READ |

+-----------------+

1 row in set

Time: 0.015s

// 設定隔離級別

set session transaction isolation level 隔離級別(上面事務隔離級別中的英文單詞);