MySQL 中事務(wù)的隔離

在 mysql 中事務(wù)的隔離級(jí)別有以下 4 種：

　　讀未提交(read uncommitted)

　　讀已提交(read committed)

　　可重復(fù)讀(repeatable read)

　　序列化(serializable)

　　mysql 默認(rèn)的事務(wù)隔離級(jí)別是可重復(fù)讀(repeatable read)，這 4 種隔離級(jí)別的說(shuō)明如下。

1.read uncommitted

　　讀未提交，也叫未提交讀，該隔離級(jí)別的事務(wù)可以看到其他事務(wù)中未提交的數(shù)據(jù)。該隔離級(jí)別因?yàn)榭梢宰x取到其他事務(wù)中未提交的數(shù)據(jù)，而未提交的數(shù)據(jù)可能會(huì)發(fā)生回滾，因此我們把該級(jí)別讀取到的數(shù)據(jù)稱之為臟數(shù)據(jù)，把這個(gè)問(wèn)題稱之為臟讀。

2.read committed

　　讀已提交，也叫提交讀，該隔離級(jí)別的事務(wù)能讀取到已經(jīng)提交事務(wù)的數(shù)據(jù)，因此它不會(huì)有臟讀問(wèn)題。但由于在事務(wù)的執(zhí)行中可以讀取到其他事務(wù)提交的結(jié)果，所以在不同時(shí)間的相同 sql 查詢中，可能會(huì)得到不同的結(jié)果，這種現(xiàn)象叫做不可重復(fù)讀。

3.repeatable read

　　可重復(fù)讀，是 mysql 的默認(rèn)事務(wù)隔離級(jí)別，它能確保同一事務(wù)多次查詢的結(jié)果一致。但也會(huì)有新的問(wèn)題，比如此級(jí)別的事務(wù)正在執(zhí)行時(shí)，另一個(gè)事務(wù)成功的插入了某條數(shù)據(jù)，但因?yàn)樗看尾樵兊慕Y(jié)果都是一樣的，所以會(huì)導(dǎo)致查詢不到這條數(shù)據(jù)，自己重復(fù)插入時(shí)又失敗(因?yàn)槲ㄒ患s束的原因)。明明在事務(wù)中查詢不到這條信息，但自己就是插入不進(jìn)去，這就叫幻讀 (phantom read)。

4.serializable

　　序列化，事務(wù)最高隔離級(jí)別，它會(huì)強(qiáng)制事務(wù)排序，使之不會(huì)發(fā)生沖突，從而解決了臟讀、不可重復(fù)讀和幻讀問(wèn)題，但因?yàn)閳?zhí)行效率低，所以真正使用的場(chǎng)景并不多。?

　　簡(jiǎn)單總結(jié)一下，mysql 的 4 種事務(wù)隔離級(jí)別對(duì)應(yīng)臟讀、不可重復(fù)讀和幻讀的關(guān)系如下：

事務(wù)隔離級(jí)別	臟讀	不可重復(fù)讀	幻讀
讀未提交（read uncommitted）	√	√	√
讀已提交（read committed）	×	√	√
可重復(fù)讀（repeatable read）	×	×	√
串行化（serializable）	×	×	×

　　只看以上概念會(huì)比較抽象，接下來(lái)，咱們一步步通過(guò)執(zhí)行的結(jié)果來(lái)理解這幾種隔離級(jí)別的區(qū)別。

前置知識(shí)

1.事務(wù)相關(guān)的常用命令

# 查看 mysql 版本
select version();
# 開(kāi)啟事務(wù)
start transaction;
# 提交事務(wù)
commit;
# 回滾事務(wù)
rollback;

2.mysql 8 之前查詢事務(wù)的隔離級(jí)別

查看全局 mysql 事務(wù)隔離級(jí)別和當(dāng)前會(huì)話的事務(wù)隔離級(jí)別的 sql 如下：

select @@global.tx_isolation,@@tx_isolation;

以上 sql 執(zhí)行結(jié)果如下圖所示：

3.mysql 8 之后查詢事務(wù)的隔離級(jí)別

select @@global.transaction_isolation,@@transaction_isolation;

4.查看連接的客戶端詳情

　　每個(gè) mysql 命令行窗口就是一個(gè) mysql 客戶端，每個(gè)客戶端都可以單獨(dú)設(shè)置(不同的)事務(wù)隔離級(jí)別，這也是演示 mysql 并發(fā)事務(wù)的基礎(chǔ)。以下是查詢客戶端連接的 sql 命令：

show processlist;

以上 sql 執(zhí)行結(jié)果如下：

5.查詢連接客戶端的數(shù)量

可以使用以下 sql 命令，查詢連當(dāng)前接 mysql 服務(wù)器的客戶端數(shù)量：

show status like 'threads%';

以上 sql 執(zhí)行結(jié)果如下：

6.設(shè)置客戶端的事務(wù)隔離級(jí)別

通過(guò)以下 sql 可以設(shè)置當(dāng)前客戶端的事務(wù)隔離級(jí)別：

set session transaction isolation level 事務(wù)隔離級(jí)別;

事務(wù)隔離級(jí)別的值有 4 個(gè)：

read uncommitted、read committed、repeatable read、serializable。

7.新建數(shù)據(jù)庫(kù)和測(cè)試數(shù)據(jù)

創(chuàng)建測(cè)試數(shù)據(jù)庫(kù)和表信息，執(zhí)行 sql 如下：

-- 創(chuàng)建數(shù)據(jù)庫(kù)
drop database if exists testdb;
create database testdb;
use testdb;
-- 創(chuàng)建表
create table userinfo(
id int primary key auto_increment,
name varchar(250) not null,
balance decimal(10,2) not null default 0
);
-- 插入測(cè)試數(shù)據(jù)
insert into userinfo(id,name,balance) values(1,'java',100),(2,'mysql',200);

創(chuàng)建的表結(jié)構(gòu)和數(shù)據(jù)如下：

8.名稱約定

　　接下來(lái)會(huì)使用兩個(gè)窗口(兩個(gè)客戶端)來(lái)演示事務(wù)不同隔離級(jí)別中臟讀、不可重復(fù)讀和幻讀的問(wèn)題。其中左邊的黑底綠字的客戶端下文將使用“窗口 1”來(lái)指代，而右邊的藍(lán)底白字的客戶端下文將用“窗口 2”來(lái)指代，

如下圖所示：

臟讀

　　一個(gè)事務(wù)讀到另外一個(gè)事務(wù)還沒(méi)有提交的數(shù)據(jù)，稱之為臟讀。 臟讀演示的執(zhí)行流程如下：

執(zhí)行步驟	客戶端1（窗口1）	客戶端2（窗口2）	說(shuō)明
第 1 步		set session transaction isolation level read uncommitted;start transaction;select * from userinfo;	設(shè)置事務(wù)隔離級(jí)別為讀未提交；開(kāi)啟事務(wù)；查詢用戶列表，其中 java 用戶的余額為 100 元。
第 2 步	start transaction;update userinfo set balance=balance+50 where name='java';		開(kāi)啟事務(wù)；給 java 用戶的賬戶加 50 元；
第 3 步		select * from userinfo;	查詢用戶列表，其中 java 用戶的余額變成了 150 元。

1.臟讀演示步驟1

設(shè)置窗口 2 的事務(wù)隔離級(jí)別為讀未提交，設(shè)置命令如下：

set session transaction isolation level read uncommitted;

　　ps：事務(wù)隔離級(jí)別讀未提交存在臟讀的問(wèn)題。

然后使用命令來(lái)檢查當(dāng)前連接窗口的事務(wù)隔離界別，如下圖所示：

開(kāi)啟事務(wù)并查詢用戶列表信息，如下圖所示：

2.臟讀演示步驟2

在窗口 1 中開(kāi)啟一個(gè)事務(wù)，并給 java 賬戶加 50 元，但不提交事務(wù)，執(zhí)行的 sql 如下：

3.臟讀演示步驟3

在窗口 2 中再次查詢用戶列表，執(zhí)行結(jié)果如下：

　　從上述結(jié)果可以看出，在窗口 2 中讀取到了窗口 1 中事務(wù)未提交的數(shù)據(jù)，這就是臟讀。

4.不可重復(fù)讀

　　不可重復(fù)讀是指一個(gè)事務(wù)先后執(zhí)行同一條 sql，但兩次讀取到的數(shù)據(jù)不同，就是不可重復(fù)讀。

不可重復(fù)讀演示的執(zhí)行流程如下：

執(zhí)行步驟	客戶端1（窗口1）	客戶端2（窗口2）	說(shuō)明
第 1 步		set session transaction isolation level read committed;start transaction;select * from userinfo;	設(shè)置事務(wù)隔離級(jí)別為讀已提交；開(kāi)啟事務(wù)；查詢用戶列表，其中 java 用戶的余額是 100 元。
第 2 步	start transaction;update userinfo set balance=balance+20 where name='java';commit;		開(kāi)啟事務(wù)；給 java 用戶的余額加 20 元；提交事務(wù)。
第 3 步		select * from userinfo;	查詢用戶列表，其中 java 用戶的余額變成了 120 元。

　　窗口 2 同一個(gè)事務(wù)中的兩次查詢，得到了不同的結(jié)果這就是不可重復(fù)讀，具體執(zhí)行步驟如下。

5.不可重復(fù)讀演示步驟1

設(shè)置窗口 2 的事務(wù)隔離級(jí)別為讀已提交，設(shè)置命令如下：

set session transaction isolation level read committed;

　　ps：讀已提交可以解決臟讀的問(wèn)題，但存在不可重復(fù)讀的問(wèn)題。

使用命令來(lái)檢查當(dāng)前連接窗口的事務(wù)隔離界別，如下圖所示：

在窗口 2 中開(kāi)啟事務(wù)，并查詢用戶表，執(zhí)行結(jié)果如下：

　　此時(shí)查詢的列表中，java 用戶的余額為 100 元。

6.不可重復(fù)讀演示步驟2

　　在窗口 1 中開(kāi)啟事務(wù)，并給 java 用戶添加 20 元，但不提交事務(wù)，再觀察窗口 2 中有沒(méi)有臟讀的問(wèn)題，

具體執(zhí)行結(jié)果如下圖所示：

執(zhí)行結(jié)果如下圖所示：

從上述結(jié)果可以看出，當(dāng)把窗口的事務(wù)隔離級(jí)別設(shè)置為讀已提交，已經(jīng)不存在臟讀問(wèn)題了。 接下來(lái)在窗口 1 中提交事務(wù)，

7.不可重復(fù)讀演示步驟3

切換到窗口 2 中再次查詢用戶列表，執(zhí)行結(jié)果如下：

　　從上述結(jié)果可以看出，此時(shí) java 用戶的余額已經(jīng)變成 120 元了。在同一個(gè)事務(wù)中，先后查詢的兩次結(jié)果不一致就是不可重復(fù)讀。

8.不可重復(fù)讀和臟讀的區(qū)別

　　臟讀可以讀到其他事務(wù)中未提交的數(shù)據(jù)，而不可重復(fù)讀是讀取到了其他事務(wù)已經(jīng)提交的數(shù)據(jù)，但前后兩次讀取的結(jié)果不同。

幻讀

　　幻讀名如其文，它就像發(fā)生了某種幻覺(jué)一樣，在一個(gè)事務(wù)中明明沒(méi)有查到主鍵為 x 的數(shù)據(jù)，但主鍵為 x 的數(shù)據(jù)就是插入不進(jìn)去，就像某種幻覺(jué)一樣。

幻讀演示的執(zhí)行流程如下：

執(zhí)行步驟	客戶端1（窗口1）	客戶端2（窗口2）	說(shuō)明
第 1 步		set session transaction isolation level repeatable read;start transaction;select * from userinfo where id=3;	設(shè)置事務(wù)隔離級(jí)別為可重復(fù)讀；開(kāi)啟事務(wù)；查詢用戶編號(hào)為 3 的數(shù)據(jù)，查詢結(jié)果為空。
第 2 步	start transaction;insert into userinfo(id,name,balance) values(3,'spring',100);commit;		開(kāi)啟事務(wù)；添加用戶，用戶編號(hào)為 3；提交事務(wù)。
第 3 步		insert into userinfo(id,name,balance) values(3,'spring',100);	窗口 2 添加用戶編號(hào)為 3 的數(shù)據(jù)，執(zhí)行失敗。
第 4 步		select * from userinfo where id=3;	查詢用戶編號(hào)為 3 的數(shù)據(jù)，查詢結(jié)果為空。

　　具體執(zhí)行結(jié)果如下步驟所示!

1.幻讀演示步驟1

　　設(shè)置窗口 2 為可重復(fù)讀，可重復(fù)有幻讀的問(wèn)題，查詢編號(hào)為 3 的用戶，具體執(zhí)行 sql 如下：

set session transaction isolation level repeatable read;
start transaction;
select * from userinfo where id=3;

以上 sql 執(zhí)行結(jié)果如下圖所示：

　　從上述結(jié)果可以看出，查詢的結(jié)果中 id=3 的數(shù)據(jù)為空。

2.幻讀演示步驟2

　　開(kāi)啟窗口 1 的事務(wù)，插入用戶編號(hào)為 3 的數(shù)據(jù)，然后成功提交事務(wù)，執(zhí)行 sql 如下：

start transaction;
insert into userinfo(id,name,balance) values(3,'spring',100);
commit;

以上 sql 執(zhí)行結(jié)果如下圖所示：

3.幻讀演示步驟3

在窗口 2 中插入用戶編號(hào)為 3 的數(shù)據(jù)，執(zhí)行 sql 如下：

1	insert into userinfo(id,name,balance) values(3,'spring',100);

以上 sql 執(zhí)行結(jié)果如下圖所示：

　　添加用戶數(shù)據(jù)失敗，提示表中已經(jīng)存在了編號(hào)為 3 的數(shù)據(jù)，且此字段為主鍵，不能添加多個(gè)。

4.幻讀演示步驟4

在窗口 2 中，重新執(zhí)行查詢：

select * from userinfo where id=3;

以上 sql 執(zhí)行結(jié)果如下圖所示：

　　/ 在此事務(wù)中查詢明明沒(méi)有編號(hào)為 3 的用戶，但插入的時(shí)候卻卻提示已經(jīng)存在了，這就是幻讀。

5.不可重復(fù)讀和幻讀的區(qū)別

　　二者描述的則重點(diǎn)不同，不可重復(fù)讀描述的側(cè)重點(diǎn)是修改操作，而幻讀描述的側(cè)重點(diǎn)是添加和刪除操作。

總結(jié)

本文演示了 mysql 的 4 種事務(wù)隔離級(jí)別：

讀未提交(有臟讀問(wèn)題)、讀已提交(有不可重復(fù)讀的問(wèn)題)、可重復(fù)讀(有幻讀的問(wèn)題)和序列化，其中可重復(fù)讀是 mysql 默認(rèn)的事務(wù)隔離級(jí)別。臟讀是讀到了其他事務(wù)未提交的數(shù)據(jù)，而不可重復(fù)讀是讀到了其他事務(wù)已經(jīng)提交的數(shù)據(jù)，但前后查詢的結(jié)果不同，而幻讀則是明明查詢不到，但就是插入不了。

　　到此這篇關(guān)于一文搞懂mysql臟讀,幻讀和不可重復(fù)讀的文章就介紹到這了