MySQL事務(wù)及并發(fā)下所引發(fā)的問題詳解 環(huán)球動態(tài)

環(huán)境：MySQL8.0.30

1 事務(wù)基本概念1.1 基本概念

什么是事務(wù)：是可以提交或回滾的原子工作單元，它是由一個或多個操作形成的一組操作單元。

(資料圖片僅供參考)

事務(wù)處理的原則：保證所有的操作都作為 一個工作單元來執(zhí)行，即使出現(xiàn)了異常，都不能改變這種執(zhí)行方式。當(dāng)在一個事務(wù)中執(zhí)行多個操作時，要么所有的事務(wù)都被提交( commit )，這些修改永久地保存下來；要么放棄所有的修改 ，整個事務(wù)回滾( rollback )到最初狀態(tài)。

1.2 事務(wù)的四個特性ACID1.2.1 原子性

Atomicity

是指事務(wù)是一個不可分割的工作單位( 最小的工作單位 )，要么全部提交，要么全部回滾。

1.2.2 一致性

Consistency

一致性是指事務(wù)執(zhí)行前后，數(shù)據(jù)從一個 合法性狀態(tài) 變換到另外一個 合法性狀態(tài)；而這種狀態(tài)應(yīng)該是與具體的業(yè)務(wù)相關(guān)。

該特性是由其它3個特性 + 開發(fā)者共同來保證的。

如：張三給李四轉(zhuǎn)賬100，張三的賬戶必須扣減100元，李四的賬戶必須加100元。

1.2.3 隔離性

Isolation

事務(wù)的隔離性是指一個事務(wù)的執(zhí)行 不能被其他事務(wù)干擾 ，即一個事務(wù)內(nèi)部的操作及使用的數(shù)據(jù)對并發(fā)的其他事務(wù)是隔離的，并發(fā)執(zhí)行的各個事務(wù)之間不能互相干擾。

如不考慮事務(wù)的隔離性，將會出現(xiàn)如下情況：

1.2.4 持久性

Durability

指一個事務(wù)一旦被提交，它對數(shù)據(jù)庫中數(shù)據(jù)的改變就是永久性的，即使系統(tǒng)服務(wù)器奔潰或者服務(wù)器宕機，只要數(shù)據(jù)庫能夠重新啟動，那么一定會將其恢復(fù)為事務(wù)提交成功結(jié)束后的狀態(tài)。

1.3 MySQL事務(wù)支持

只有InnoDB引擎是支持事務(wù)的。

2 如何使用事務(wù)

兩種方式：顯式事務(wù)和隱式事務(wù)

2.1 顯示事務(wù)

顯示事務(wù)可以通過2中方式：start transaction 或 beign。

mysql> START TRANSACTION; Query OK, 0 rows affected (0.00 sec) 或者 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec)  #這里是一組DML語句  #提交事務(wù) mysql> COMMIT; Query OK, 0 rows affected (0.00 sec)  #回滾事務(wù) mysql> ROLLBACK; Query OK, 0 rows affected (0.00 sec)

兩者的區(qū)別在于，start transaction [修飾符]

READ ONLY：標(biāo)識當(dāng)前事務(wù)是一個只讀事務(wù) ，也就是屬于該事務(wù)的數(shù)據(jù)庫操作只能讀取數(shù)據(jù)，而不能修改數(shù)據(jù)。READ WRITE：標(biāo)識當(dāng)前事務(wù)是一個讀寫事務(wù) ，也就是屬于該事務(wù)的數(shù)據(jù)庫操作既可以讀取數(shù)據(jù)，也可以修改數(shù)據(jù)。WITH CONSISTENT SNAPSHOT：啟動一致性快照讀。（唯一允許一致性讀的隔離級別是REPEATABLE READ，對于所有其他隔離級別，將忽略WITH CONSISTENT SNAPSHOT子句。當(dāng)忽略WITH CONSISTENT SNAPSHOT子句時，將生成一個警告。）

什么是一致性快照讀？如下示例：

創(chuàng)建如下表

mysql> create table test (id int primary key, name varchar(32)); Query OK, 0 rows affected (0.05 sec)  mysql> select * from test; Empty set (0.00 sec)

試驗1：

試驗2：

結(jié)論：

START TRANSACTION是在第一條select執(zhí)行完后，才得到事務(wù)的一致性快照，而START TRANSACTION WITH CONSISTENT SNAPSHOT則是立馬取得事務(wù)的一致性快照。

2.2 隱式事務(wù)

通過設(shè)置autocommit系統(tǒng)變量來控制事務(wù)，默認該值為：ON。

mysql> SHOW VARIABLES LIKE "%autocommit%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | ON    | +---------------+-------+ 1 row in set (0.01 sec)

默認事務(wù)自動提交。、

通過如下方式進行關(guān)閉

mysql> SET AUTOCOMMIT=OFF; Query OK, 0 rows affected (0.00 sec)  mysql> SHOW VARIABLES LIKE "%autocommit%"; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit    | OFF   | +---------------+-------+ 1 row in set (0.01 sec) #或者 mysql> SET AUTOCOMMIT=0;

2.3 使用事務(wù)

創(chuàng)建數(shù)據(jù)庫及表

mysql> create database pack; Query OK, 1 row affected (0.01 sec)  mysql> use pack; Database changed mysql> create table test (id int primary key, name varchar(32)); Query OK, 0 rows affected (0.09 sec)

示例1：

mysql> begin; Query OK, 0 rows affected (0.00 sec)  mysql> insert into test values (1, "zs"); Query OK, 1 row affected (0.01 sec)  mysql> insert into test values (2, "ls"); Query OK, 1 row affected (0.00 sec)  mysql> commit; Query OK, 0 rows affected (0.01 sec)  mysql> select * from test; +----+------+ | id | name | +----+------+ |  1 | zs   | |  2 | ls   | +----+------+ 2 rows in set (0.00 sec)

示例2：

mysql> begin; Query OK, 0 rows affected (0.00 sec)  mysql> insert into test values (3, "ww"); Query OK, 1 row affected (0.00 sec)  mysql> insert into test values (4, "zl"); Query OK, 1 row affected (0.00 sec)  mysql> rollback; Query OK, 0 rows affected (0.00 sec)  mysql> select * from test; +----+------+ | id | name | +----+------+ |  1 | zs   | |  2 | ls   | +----+------+ 2 rows in set (0.00 sec)

2.4 事務(wù)保存點

MySQL支持SAVEPOINT、ROLLBACK TO SAVEPOINT、RELEASE SAVEPOINT。

通過設(shè)置保存點，事務(wù)回滾是回滾到指定的保存點，而不是回滾整個事務(wù)。

示例：

mysql> select * from test; +----+------+ | id | name | +----+------+ |  1 | zs   | |  2 | ls   | +----+------+ 2 rows in set (0.00 sec)  mysql> begin; Query OK, 0 rows affected (0.00 sec)  mysql> insert into test values (3, "zl"); Query OK, 1 row affected (0.00 sec)  mysql> savepoint p1; Query OK, 0 rows affected (0.00 sec)  mysql> insert into test values (4, "ww"); Query OK, 1 row affected (0.00 sec)  mysql> rollback to p1; Query OK, 0 rows affected (0.00 sec)  mysql> commit; Query OK, 0 rows affected (0.02 sec)  mysql> select * from test; +----+------+ | id | name | +----+------+ |  1 | zs   | |  2 | ls   | |  3 | zl   | +----+------+ 3 rows in set (0.00 sec)

3 事務(wù)隔離級別3.1 環(huán)境準(zhǔn)備

創(chuàng)建表：

mysql> create table account (     -> id int primary key,     -> name varchar(32),     -> balance int     -> ); Query OK, 0 rows affected (0.06 sec)

插入數(shù)據(jù)

mysql> insert into account values (1, "zs", 1000);  mysql> select * from account; +----+------+---------+ | id | name | balance | +----+------+---------+ |  1 | zs       |    1000  | +----+------+---------+ 1 row in set (0.00 sec)

3.2 并發(fā)問題

當(dāng)多個事務(wù)并發(fā)執(zhí)行修改相同數(shù)據(jù)時會出現(xiàn)如下問題：

3.2.1 臟寫

一個事務(wù)修改了另外一個事務(wù)修改了但未提交的數(shù)據(jù)。

臟寫非常的嚴(yán)重，以致所有的隔離級別都解決了臟寫問題。

3.2.2 臟讀

事務(wù)A讀取了事務(wù)B修改了但是未提交的數(shù)據(jù)，如果此時事務(wù)B回滾了，那么事務(wù)A讀取到的數(shù)據(jù)肯定是無效的。

3.2.3 不可重復(fù)讀

事務(wù)A讀取id為1的name為張三，緊接著事務(wù)B修改了id為1的name為李四，此時事務(wù)A再次讀取id為1的數(shù)據(jù)發(fā)現(xiàn)此時name為李四，事務(wù)A兩次讀取不一樣，這就是發(fā)生了不可重復(fù)讀。

3.2.4 幻讀

事務(wù)A讀取age為20的人人員信息返回了10條，緊接著事務(wù)B插入了5條age為20的數(shù)據(jù)，此時事務(wù)A再次讀取age為20的人員信息返回15條，這就是發(fā)生了幻讀。

3.3 隔離級別

MySQL支持4中隔離級別，Oracle支持兩種(READ COMMITTED(默認),SERIALIZABLE)

3.3.1 讀未提交

READ UNCOMMITTED：讀未提交，在該隔離級別，所有事務(wù)都可以看到其他未提交事務(wù)的執(zhí)行結(jié)果；不能避免臟讀、不可重復(fù)讀、幻讀。

3.3.2 讀已提交

READ COMMITTED：一個事務(wù)讀取到了，其它已提交的事務(wù)所修改的數(shù)據(jù)；可以避免臟讀，但不可重復(fù)讀、幻讀問題仍然存在。

3.3.3 可重復(fù)讀

REPEATABLE READ：事務(wù)A讀取一條數(shù)據(jù)后，事務(wù)B修改了該數(shù)據(jù)并且提交后，事務(wù)A再次讀取該條數(shù)據(jù)，讀取到的內(nèi)容沒有發(fā)生變化；可以避免臟讀、不可重復(fù)讀，但幻讀問題仍

然存在。MySQL默認隔離級別

3.3.4 串行化

SERIALIZABLE：一個一個的按順序執(zhí)行；能避免臟讀、不可重復(fù)讀和幻讀。

總結(jié)，在不同隔離級別下，并發(fā)事務(wù)所引發(fā)的問題如下

不同隔離級別下所帶來的性能問題

3.4 隔離級別演示

MySQL中設(shè)置隔離級別方法如下：

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL 隔離級別; #其中，隔離級別格式： 1. READ UNCOMMITTED 2. READ COMMITTED 3. REPEATABLE READ 4. SERIALIZABLE

或者

SET [GLOBAL|SESSION] TRANSACTION_ISOLATION = "隔離級別" #其中，隔離級別格式： 1. READ-UNCOMMITTED 2. READ-COMMITTED 3. REPEATABLE-READ 4. SERIALIZABLE

示例：

mysql> SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ; Query OK, 0 rows affected (0.00 sec) #或者 mysql> SET SESSION TRANSACTION_ISOLATION ="REPEATABLE-READ"; Query OK, 0 rows affected (0.00 sec) #查看當(dāng)前會話級別的隔離級別 mysql> SELECT @@TRANSACTION_ISOLATION; +-------------------------+ | @@TRANSACTION_ISOLATION | +-------------------------+ | REPEATABLE-READ         | +-------------------------+ 1 row in set (0.00 sec)

3.4.1 讀未提交

臟讀問題

mysql> set session transaction_isolatinotallow="READ-UNCOMMITTED"; Query OK, 0 rows affected (0.00 sec)  mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-UNCOMMITTED        | +-------------------------+ 1 row in set (0.00 sec)

示例：

3.4.2 讀已提交

不可重復(fù)讀

mysql> set session transaction_isolatinotallow="read-committed"; Query OK, 0 rows affected (0.00 sec)  mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | READ-COMMITTED          | +-------------------------+ 1 row in set (0.00 sec)

示例：

3.4.3 可重復(fù)讀

mysql> set session transaction_isolatinotallow="REPEATABLE-READ"; Query OK, 0 rows affected (0.00 sec)  mysql> select @@transaction_isolation; +-------------------------+ | @@transaction_isolation | +-------------------------+ | REPEATABLE-READ         | +-------------------------+ 1 row in set (0.00 sec)

示例：

嚴(yán)格意義上看，可重復(fù)讀隔離級別并沒有解決幻讀問題

示例：

3.4.4 串行化

排隊執(zhí)行，略

完畢！！！