MySQL事務及并發(fā)下所引發(fā)的問題詳解
環(huán)境：MySQL8.0.30
1 事務基本概念
1.1 基本概念
什么是事務：是可以提交或回滾的原子工作單元，它是由一個或多個操作形成的一組操作單元。
事務處理的原則：保證所有的操作都作為 一個工作單元來執(zhí)行，即使出現了異常，都不能改變這種執(zhí)行方式。當在一個事務中執(zhí)行多個操作時，要么所有的事務都被提交( commit )，這些修改永久地保存下來；要么放棄所有的修改 ，整個事務回滾( rollback )到最初狀態(tài)。
1.2 事務的四個特性ACID
1.2.1 原子性
Atomicity
是指事務是一個不可分割的工作單位( 最小的工作單位 )，要么全部提交，要么全部回滾。
1.2.2 一致性
Consistency
一致性是指事務執(zhí)行前后，數據從一個 合法性狀態(tài) 變換到另外一個 合法性狀態(tài)；而這種狀態(tài)應該是與具體的業(yè)務相關。
該特性是由其它3個特性 + 開發(fā)者共同來保證的。
如：張三給李四轉賬100，張三的賬戶必須扣減100元，李四的賬戶必須加100元。
1.2.3 隔離性
Isolation
事務的隔離性是指一個事務的執(zhí)行 不能被其他事務干擾 ，即一個事務內部的操作及使用的數據對并發(fā)的其他事務是隔離的，并發(fā)執(zhí)行的各個事務之間不能互相干擾。
如不考慮事務的隔離性，將會出現如下情況：
1.2.4 持久性
Durability
指一個事務一旦被提交，它對數據庫中數據的改變就是永久性的，即使系統(tǒng)服務器奔潰或者服務器宕機，只要數據庫能夠重新啟動，那么一定會將其恢復為事務提交成功結束后的狀態(tài)。
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)兩者的區(qū)別在于，start transaction [修飾符]
- READ ONLY：標識當前事務是一個只讀事務 ，也就是屬于該事務的數據庫操作只能讀取數據，而不能修改數據。
- READ WRITE：標識當前事務是一個讀寫事務 ，也就是屬于該事務的數據庫操作既可以讀取數據，也可以修改數據。
- WITH CONSISTENT SNAPSHOT ：啟動一致性快照讀。（唯一允許一致性讀的隔離級別是REPEATABLE READ，對于所有其他隔離級別，將忽略WITH CONSISTENT SNAPSHOT子句。當忽略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：
結論：
START TRANSACTION是在第一條select執(zhí)行完后，才得到事務的一致性快照，而START TRANSACTION WITH CONSISTENT SNAPSHOT則是立馬取得事務的一致性快照。
2.2 隱式事務
通過設置autocommit系統(tǒng)變量來控制事務，默認該值為：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 使用事務
創(chuàng)建數據庫及表
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 環(huán)境準備
創(chuàng)建表：
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 并發(fā)問題
當多個事務并發(fā)執(zhí)行修改相同數據時會出現如下問題：
3.2.1 臟寫
一個事務修改了另外一個事務修改了但未提交的數據。
臟寫非常的嚴重，以致所有的隔離級別都解決了臟寫問題。
3.2.2 臟讀
事務A讀取了事務B修改了但是未提交的數據，如果此時事務B回滾了，那么事務A讀取到的數據肯定是無效的。
3.2.3 不可重復讀
事務A讀取id為1的name為張三，緊接著事務B修改了id為1的name為李四，此時事務A再次讀取id為1的數據發(fā)現此時name為李四，事務A兩次讀取不一樣，這就是發(fā)生了不可重復讀。
3.2.4 幻讀
事務A讀取age為20的人人員信息返回了10條，緊接著事務B插入了5條age為20的數據，此時事務A再次讀取age為20的人員信息返回15條，這就是發(fā)生了幻讀。
3.3 隔離級別
MySQL支持4中隔離級別，Oracle支持兩種(READ COMMITTED(默認), SERIALIZABLE)
3.3.1 讀未提交
READ UNCOMMITTED：讀未提交，在該隔離級別，所有事務都可以看到其他未提交事務的執(zhí)行結果；不能避免臟讀、不可重復讀、幻讀。
3.3.2 讀已提交
READ COMMITTED：一個事務讀取到了，其它已提交的事務所修改的數據；可以避免臟讀，但不可重復讀、幻讀問題仍然存在。
3.3.3 可重復讀
REPEATABLE READ：事務A讀取一條數據后，事務B修改了該數據并且提交后，事務A再次讀取該條數據，讀取到的內容沒有發(fā)生變化；可以避免臟讀、不可重復讀，但幻讀問題仍
然存在。MySQL默認隔離級別
3.3.4 串行化
SERIALIZABLE：一個一個的按順序執(zhí)行；能避免臟讀、不可重復讀和幻讀。
總結，在不同隔離級別下，并發(fā)事務所引發(fā)的問題如下
不同隔離級別下所帶來的性能問題
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 串行化
排隊執(zhí)行，略
完畢?。?！
