MySQL事務及并發下所引發的問題詳解

作者：Springboot實戰案例錦集 2023-06-25 08:05:09

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

環境：MySQL8.0.30

1 事務基本概念

1.1 基本概念

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

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

1.2 事務的四個特性ACID

1.2.1 原子性

Atomicity

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

1.2.2 一致性

Consistency

一致性是指事務執行前后，數據從一個合法性狀態變換到另外一個合法性狀態；而這種狀態應該是與具體的業務相關。

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

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

1.2.3 隔離性

Isolation

事務的隔離性是指一個事務的執行不能被其他事務干擾，即一個事務內部的操作及使用的數據對并發的其他事務是隔離的，并發執行的各個事務之間不能互相干擾。

如不考慮事務的隔離性，將會出現如下情況：

1.2.4 持久性

Durability

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

1.3 MySQL事務支持

只有InnoDB引擎是支持事務的。

2 如何使用事務

兩種方式：顯式事務 和 隱式事務

2.1 顯示事務

顯示事務可以通過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語句
 
 #提交事務
 mysql> COMMIT;
 Query OK, 0 rows affected (0.00 sec)
 
 #回滾事務
 mysql> ROLLBACK;
 Query OK, 0 rows affected (0.00 sec)

兩者的區別在于，start transaction [修飾符]

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

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

創建如下表

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：

結論：

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

2.2 隱式事務

通過設置autocommit系統變量來控制事務，默認該值為：ON。

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

默認事務自動提交。、

通過如下方式進行關閉

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 使用事務

創建數據庫及表

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 事務保存點

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

通過設置保存點，事務回滾是回滾到指定的保存點，而不是回滾整個事務。

示例：

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 事務隔離級別

3.1 環境準備

創建表：

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

插入數據

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 并發問題

當多個事務并發執行修改相同數據時會出現如下問題：

3.2.1 臟寫

一個事務修改了另外一個事務修改了但未提交的數據。

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

3.2.2 臟讀

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

3.2.3 不可重復讀

事務A讀取id為1的name為張三，緊接著事務B修改了id為1的name為李四，此時事務A再次讀取id為1的數據發現此時name為李四，事務A兩次讀取不一樣，這就是發生了不可重復讀。

3.2.4 幻讀

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

3.3 隔離級別

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

3.3.1 讀未提交

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

3.3.2 讀已提交

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

3.3.3 可重復讀

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

然存在。MySQL默認隔離級別

3.3.4 串行化

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

總結，在不同隔離級別下，并發事務所引發的問題如下

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

3.4 隔離級別演示

MySQL中設置隔離級別方法如下：

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)
 #查看當前會話級別的隔離級別
 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 讀已提交

不可重復讀

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 可重復讀

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)

示例：

嚴格意義上看，可重復讀隔離級別并沒有解決幻讀問題

示例：

3.4.4 串行化

排隊執行，略

完畢！！！

責任編輯：武曉燕來源：實戰案例錦集

MySQL 事務并發

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看