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