快捷導(dǎo)航

mysql中的事務(wù)全方位解析

更新時(shí)間：2023年08月15日 09:30:55 作者：Steve?lu

這篇文章主要介紹了mysql中的事務(wù)全方位解析,事務(wù)是一種機(jī)制、一個(gè)操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求,需要的朋友可以參考下

一、事務(wù)的概念

事務(wù)是一種機(jī)制、一個(gè)操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷操作請(qǐng)求，即這一組數(shù)據(jù)庫命令要么都執(zhí)行，要么都不執(zhí)行。
事務(wù)是一個(gè)不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時(shí)，事務(wù)是最小的控制單元。
事務(wù)適用于多用戶同時(shí)操作的數(shù)據(jù)庫系統(tǒng)的場(chǎng)景，如銀行、保險(xiǎn)公司及證券交易系統(tǒng)等等。
事務(wù)通過事務(wù)的整體性以保證數(shù)據(jù)的一致性。
事務(wù)能夠提高在向表中更新和插入信息期間的可靠性。

總的來說，事務(wù)就是一個(gè)操作序列，這些操作要么都執(zhí)行，要么都不執(zhí)行，它是一個(gè)不可分割的工作單位。

二、事務(wù)的ACID特點(diǎn)

ACID，是指在可靠數(shù)據(jù)庫管理系統(tǒng)（DBMS）中，事務(wù)（transaction）應(yīng)該具有的四個(gè)特性：

原子性（Atomicity）
一致性（Consistency）
隔離性（Isolation）
持久性（Durability）

2.1 原子性

事務(wù)是一個(gè)不可再分割的工作單位，事務(wù)中的操作要么都發(fā)生，要么都不發(fā)生。
事務(wù)是一個(gè)完整的操作，事務(wù)的各元素是不可分的。事務(wù)中的所有元素必須作為一個(gè)整體提交或回滾。
如果事務(wù)中的任何元素失敗，則整個(gè)事務(wù)將失敗。

案例： A給B轉(zhuǎn)帳100元錢的時(shí)候只執(zhí)行了扣款語句，就提交了，此時(shí)如果突然斷電，A賬號(hào)已經(jīng)發(fā)生了扣款，B賬號(hào)卻沒收到加款，在生活中就會(huì)引起糾紛。這種情況就需要事務(wù)的原子性來保證事務(wù)要么都執(zhí)行，要么就都不執(zhí)行。

2.2 一致性

指在事務(wù)開始之前和事務(wù)結(jié)束以后，數(shù)據(jù)庫的完整性約束沒有被破壞。
當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致狀態(tài)。
在事務(wù)開始前，數(shù)據(jù)庫中存儲(chǔ)的數(shù)據(jù)處于一致狀態(tài)。在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài)。
當(dāng)事務(wù)成功完成時(shí)，數(shù)據(jù)必須再次回到已知的一致狀態(tài)。

案例：對(duì)銀行轉(zhuǎn)帳事務(wù)，不管事務(wù)成功還是失敗，應(yīng)該保證事務(wù)結(jié)束后表中A和B的存款總額跟事務(wù)執(zhí)行前一致。

2.3 隔離性

指在并發(fā)環(huán)境中，當(dāng)不同的事務(wù)同時(shí)操縱相同的數(shù)據(jù)時(shí)，每個(gè)羽務(wù)都有各自的完整數(shù)據(jù)空間。
對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)。
修改數(shù)據(jù)的事務(wù)可在另一個(gè)使用相同數(shù)據(jù)的事務(wù)開始之前訪問這些數(shù)據(jù)，或者在另一個(gè)使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問這些數(shù)據(jù)。
也就是說并發(fā)訪問數(shù)據(jù)庫時(shí)，一個(gè)用戶的事務(wù)不被其他事務(wù)所干擾，各并發(fā)事務(wù)之間數(shù)據(jù)庫是獨(dú)立的。

2.3.1 一致性問題

當(dāng)多個(gè)客戶端并發(fā)地訪問同一個(gè)表時(shí)，可能出現(xiàn)下而的一致性問題

（1）臟讀： 當(dāng)一個(gè)事務(wù)正在訪問數(shù)據(jù)，并且對(duì)數(shù)據(jù)進(jìn)行了修改，而這種修改還沒有提交到數(shù)據(jù)庫中，這時(shí)，另外一個(gè)事務(wù)也訪問這個(gè)數(shù)據(jù)，然后使用了這個(gè)數(shù)據(jù)。

在這里插入圖片描述

（2）不可重復(fù)讀： 指在一個(gè)事務(wù)內(nèi)，多次讀同一數(shù)據(jù)。在這個(gè)事務(wù)還沒有結(jié)束時(shí)，另外一個(gè)事務(wù)也訪問該同一數(shù)據(jù)。那么，在第一個(gè)事務(wù)中的兩次讀數(shù)據(jù)之間，由于第二個(gè)事務(wù)的修改，那么第一個(gè)事務(wù)兩次讀到的的數(shù)據(jù)可能是不一樣的。這樣就發(fā)生了在一個(gè)事務(wù)內(nèi)兩次讀到的數(shù)據(jù)是不一樣的，因此稱為是不可重復(fù)讀。（即不能讀到相同的數(shù)據(jù)內(nèi)容）

在這里插入圖片描述

（3）幻讀： 一個(gè)事務(wù)對(duì)一個(gè)表中的數(shù)據(jù)進(jìn)行了修改，這種修改涉及到表中的全部數(shù)據(jù)行。同時(shí)，另一個(gè)事務(wù)也修改這個(gè)表中的數(shù)據(jù)，這種修改是向表中插入一行新數(shù)據(jù)。那么，操作前一個(gè)事務(wù)的用戶會(huì)發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行，就好象發(fā)生了幻覺一樣。

在這里插入圖片描述

（4）丟失更新： 兩個(gè)事務(wù)同時(shí)讀取同一條記錄，A先修改記錄，B也修改記錄（B不知道A修改過），B提交數(shù)據(jù)后B的修改結(jié)果覆蓋了A的修改結(jié)果。

在這里插入圖片描述

2.3.2 事務(wù)之間可見級(jí)別

事務(wù)的隔離級(jí)別決定了事務(wù)之間可見的級(jí)別

MySQL事務(wù)支持如下四種隔離，用以控制事務(wù)所做的修改，并將修改通告至其它并發(fā)的事務(wù)：

（1）未提交讀（Read Uncommitted（RU））：

允許臟讀，即允許一個(gè)事務(wù)可以看到其他事務(wù)未提交的修改。

（2）提交讀（Read Committed（RC））：

允許一個(gè)事務(wù)只能看到其他事務(wù)已經(jīng)提交的修改，未提交的修改是不可見的。防止臟讀。

（3）可重復(fù)讀（Repeatable Read（RR））：—mysql默認(rèn)的隔離級(jí)別

確保如果在一個(gè)事務(wù)中執(zhí)行兩次相同的SELECT語句，都能得到相同的結(jié)果，不管其他事務(wù)是否提交這些修改?？梢苑乐古K讀和不可重復(fù)讀

（4）串行讀（Serializable）：

相當(dāng)于鎖表完全串行化的讀，將一個(gè)事務(wù)與其他事務(wù)完全地隔離。

每次讀都需要獲得表級(jí)共享鎖，讀寫相互都會(huì)阻塞?？梢苑乐古K讀，不可重復(fù)讀取和幻讀，（事務(wù)串行化）會(huì)降低數(shù)據(jù)庫的效率。

在這里插入圖片描述

mysql默認(rèn)的事務(wù)處理級(jí)別是repeatable read，而oracle和SQL Server是read committed。

2.3.3 事務(wù)隔離級(jí)別的作用范圍

事務(wù)隔離級(jí)別的作用范圍分為兩種

全局級(jí)：對(duì)所有的會(huì)話有效
會(huì)話級(jí)：只對(duì)當(dāng)前的會(huì)話有效

#查詢?nèi)质聞?wù)隔離級(jí)別:
show global variables like '%isolation%';
SELECT @@global.tx_isolation;
#查詢會(huì)話事務(wù)隔離級(jí)別:
show session variables like '%isolation%';
SELECT @@session.tx_isolation;
SELECT @@tx_isolation;
#設(shè)置全局事務(wù)隔離級(jí)別:
set global transaction isolation level read committed;
#設(shè)置會(huì)話事務(wù)隔離級(jí)別:
set session transaction isolation level read committed;

在這里插入圖片描述

2.4 持久性

在事務(wù)完成以后，該事務(wù)所對(duì)數(shù)據(jù)庫所作的更改使持久的保存在數(shù)據(jù)庫之中，并不會(huì)被回滾。

指不管系統(tǒng)是否發(fā)生故障，事務(wù)處理的結(jié)果都是永久的。

一旦事務(wù)被提交，事務(wù)的效果會(huì)被永久地保留在數(shù)據(jù)庫中。

2.5 小結(jié)

在事務(wù)管理中，原子性是基礎(chǔ)、隔離性是手段，一致性是目的，持久性是結(jié)果。

三、事務(wù)控制語句

BEGIN 或 START TRANSACTION:顯式地開啟一個(gè)事務(wù).
COMMIT 或 COMMIT WORK:提交事務(wù),并使已對(duì)數(shù)據(jù)庫進(jìn)行的所有修改變?yōu)橛谰眯缘?
ROLLBACK 或 ROLLBACKWORK:回滾會(huì)結(jié)束用戶的事務(wù),并撤銷正在進(jìn)行的所有未提交的修改.
SAVEPOINT S1:使用SAVEPOINT允許在事務(wù)中創(chuàng)建一個(gè)回滾點(diǎn),一個(gè)事務(wù)中可以有多個(gè)SAVEPOINT;"S1"代表回滾點(diǎn)名稱.
ROLLBACK TO[SAVEPOINT] S1:把事務(wù)回滾到標(biāo)記點(diǎn).

#前期準(zhǔn)備
mysql> use stevelu;
Database changed
mysql> create table account (id int,name char(10),money double);
Query OK, 0 rows affected (0.00 sec)
mysql> desc account;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| id    | int(11)  | YES  |     | NULL    |       |
| name  | char(10) | YES  |     | NULL    |       |
| money | double   | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+
3 rows in set (0.01 sec)
mysql> insert into account values(1,'zhangsan',1000);
Query OK, 1 row affected (0.00 sec)
mysql> insert into account values(2,'lisi',1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1000 |
|    2 | lisi     |  1000 |
+------+----------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
#再開一個(gè)終端，分別設(shè)置兩個(gè)當(dāng)前會(huì)話事務(wù)隔離級(jí)別
mysql> set session transaction isolation level read committed;
Query OK, 0 rows affected (0.00 sec)
mysql> show session variables like '%isolation%';
+---------------+----------------+
| Variable_name | Value          |
+---------------+----------------+
| tx_isolation  | READ-COMMITTED |
+---------------+----------------+
1 row in set (0.00 sec)
---------------------------------------------------------------------------------------------------

#模擬zhangsan用戶存入100
#開啟事務(wù)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money+100 where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |  1000 |
+------+----------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
#再開一個(gè)終端查看
mysql> use stevelu;
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1000 |
|    2 | lisi     |  1000 |
+------+----------+-------+
2 rows in set (0.00 sec)
---------------------------------------------------------------------------------------------------
#第一個(gè)終端操作
#打快照點(diǎn)
mysql> savepoint S1;
Query OK, 0 rows affected (0.01 sec)
#lisi用戶減100元
mysql> update account set money=money-100 where name='lisi';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
#打快照點(diǎn)
mysql> savepoint S2;
Query OK, 0 rows affected (0.00 sec)
#查看表記錄
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)
#繼續(xù)插入記錄
mysql> insert into account values(3,'wangwu',1000);
Query OK, 1 row affected (0.01 sec)
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
|    3 | wangwu   |  1000 |
+------+----------+-------+
3 rows in set (0.00 sec)
#回退到快照點(diǎn)2
mysql> rollback to S2;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)

在這里插入圖片描述

rollback后這個(gè)事務(wù)就結(jié)束了

#第一個(gè)終端
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update account set money=money+100 where name='zhangsan';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> update account set money=money-100 where name='lisi';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
#第二個(gè)終端
mysql> select * from account;
+------+----------+-------+
| id   | name     | money |
+------+----------+-------+
|    1 | zhangsan |  1100 |
|    2 | lisi     |   900 |
+------+----------+-------+
2 rows in set (0.00 sec)

在這里插入圖片描述

四、使用set設(shè)置控制事務(wù)

SET AUTOCOMMIT=0;	#禁止自動(dòng)提交
SET AUTOCOMMIT=1;	#開啟自動(dòng)提交.Mysq1默認(rèn)為1
SHOW VARIABLES LIKE'AUTOCOMMIT';	#查看Mysq1中的AUTOCOMMIT值

如果沒有開啟自動(dòng)提交,當(dāng)前會(huì)話連接的mysql的所有操作都會(huì)當(dāng)成一個(gè)事務(wù)直到你輸入rollbacklcommit;當(dāng)前事務(wù)才算結(jié)束.當(dāng)前事務(wù)結(jié)束前新的mysql連接時(shí)無法讀取到任何當(dāng)前會(huì)話的操作結(jié)果

如果開起了自動(dòng)提交,mysql會(huì)把每個(gè)sq1語句當(dāng)成一個(gè)事務(wù),然后自動(dòng)的commit

當(dāng)然無論開啟與否,begin;commitlrollback;都是獨(dú)立的事務(wù)

在這里插入圖片描述