快捷導(dǎo)航

MySQL事務(wù)與鎖實(shí)例教程詳解

更新時(shí)間：2022年11月05日 11:26:03 作者：宏遠(yuǎn)十一冠王

事務(wù)是指滿足ACID特性的的一組操作，可以通過Commit提交事務(wù)，也可以也可以通過Rollback進(jìn)行回滾。會(huì)存在中間態(tài)和一致性狀態(tài)，也是真正在數(shù)據(jù)庫表中存在的狀態(tài)

MySQL事務(wù)和鎖

事務(wù)

說到關(guān)系型的數(shù)據(jù)庫的事務(wù)，相信大家對(duì)四大特性都不陌生，分別是原子性、一致性、隔離性、持久性，簡稱為ACID特性。

MySQL中支持3種不同的存儲(chǔ)引擎：

MyISAM存儲(chǔ)引擎、Memory存儲(chǔ)引擎、和InnoDB存儲(chǔ)引擎

注：只有InnoDB才支持事務(wù)。

事務(wù)的控制語句

控制語句	作用
begin或者start transaction	開啟一個(gè)事務(wù)
commit 或者 commit work	提交事務(wù)，進(jìn)行持久性修改
rollback 或者 rollback work	回滾事務(wù)，撤銷已經(jīng)進(jìn)行修改但未提交的操作
savepoint [保存點(diǎn)]	在事務(wù)中創(chuàng)建一個(gè)保存點(diǎn)，一個(gè)事務(wù)可以有多個(gè)保存點(diǎn)
releasavepoint [保存點(diǎn)]	回滾到指定的保存點(diǎn)
set transaction	設(shè)置事務(wù)的隔離級(jí)別

事務(wù)隔離級(jí)別設(shè)置

先復(fù)習(xí)一個(gè)事務(wù)的四大隔離級(jí)別

讀未提交（READ-UNCOMMITTED)
讀已提交（READ-COMMITTED)
可重復(fù)讀（RE-PEATABLE-READ)
可序列化讀（SERIALIZABLE)

下面是操作過程。

首先，查看默認(rèn)的事務(wù)隔離級(jí)別，可以看到是可重復(fù)讀（REPEATABLE-READ），

show variables like '%isolation%';

臟讀

我們來演示一下臟讀的場景，下面這張圖是展示了我原先已經(jīng)創(chuàng)建好的兩個(gè)用戶的賬號(hào)都為100元。

分別打開兩個(gè)連接mysql的會(huì)話窗口，其中一個(gè)會(huì)話的隔離級(jí)別為READ-UNCOMMITED，然后再另一個(gè)窗口中開啟一個(gè)事務(wù)，例如，lisi給zhangsan轉(zhuǎn)賬100元，

set session transaction isolation level read uncommitted;
begin;
update user set money=money-100 where user=lisi;

我們?cè)诹硗庖粋€(gè)讀未提交的窗口中查看，zhangsan看到錢已經(jīng)轉(zhuǎn)過來了，但是實(shí)際上lisi的事務(wù)還沒有提交，假如這個(gè)時(shí)候，lisi不想轉(zhuǎn)賬了，回滾事務(wù)，那zhangsan就讀到臟數(shù)據(jù)了。

不可重復(fù)讀

下面來展示一下不可重復(fù)讀的場景。

首先我們將zhangsan的窗口的事務(wù)隔離級(jí)別設(shè)置成READ-COMMITTED，并且在兩個(gè)窗口都開啟事務(wù)

set session transaction isolation level read committed;

假設(shè)zhangsan現(xiàn)在想統(tǒng)計(jì)全部人的錢有多少，很明顯200；

但是這個(gè)時(shí)候lisi往賬戶里面存了100元，并提交事務(wù)，但是這個(gè)時(shí)候，zhangsan再次查詢總和，我們會(huì)查詢到總金額為300，但是這次查詢是處于同一個(gè)事務(wù)中，查詢到兩次不一樣的結(jié)果，屬于不可重復(fù)讀的情況。

update user set money=money+100 where user='lisi';
commit;

幻讀

為了解決不可重復(fù)讀的問題，我們將事務(wù)的隔離等級(jí)設(shè)置成RE-PEATABLE-READ，即MySQL默認(rèn)的事務(wù)隔離等級(jí)，然后在兩邊都開啟一個(gè)事務(wù)。

set session transaction isolation level repeatable read;

我們先在一個(gè)窗口插入一條數(shù)據(jù)并提交，然后在另外一個(gè)窗口查看，此時(shí)是查詢不到這個(gè)記錄的，但是假如這個(gè)時(shí)候我們新插入一條主鍵和剛插入的記錄一樣的話，我們就可以發(fā)現(xiàn)

insert into user values('zly1',100);
# 另外一個(gè)窗口
insert into user values('zly1',100);
ERROR 1062 (23000): Duplicate entry 'zly1' for key 'user.PRIMARY'

這樣也算是一種幻讀的現(xiàn)象，但是網(wǎng)上也有一種說法在可重復(fù)讀的等級(jí)下，幻讀是可避免的，這種說法不是非常準(zhǔn)確的，如果在進(jìn)行更新和插入時(shí)就可能會(huì)出現(xiàn)幻讀的情況，如果想要解決幻讀的情況，可以將事務(wù)隔離等級(jí)設(shè)置到SERIALIZABLE

鎖機(jī)制

InnoDB的行級(jí)鎖

InnoDB默認(rèn)采用的行級(jí)鎖，分為以下這兩種，分別為共享鎖和排他鎖。

這兩個(gè)概念我在這篇文章中也有介紹。

共享鎖（S鎖）：也叫讀鎖，如果在該數(shù)據(jù)對(duì)象上加了共享鎖，該事務(wù)可以讀取但是不能修改數(shù)據(jù)。其他事務(wù)也可以在該對(duì)象上加共享鎖，但是不能修改數(shù)據(jù)。

排他鎖（X鎖）：也叫寫鎖，在一個(gè)數(shù)據(jù)對(duì)象只有一把排他鎖，獲取到該鎖的事務(wù)可以讀取數(shù)據(jù)和修改數(shù)據(jù)。

（加鎖：一般的查詢語句不會(huì)加任何的鎖類型，當(dāng)然也可以為數(shù)據(jù)加鎖，比如在select * from … for update，這樣可以為數(shù)據(jù)添加排他鎖，而使用select … lock in share mode 可以為數(shù)據(jù)添加共享鎖。

鎖實(shí)戰(zhàn)

首先關(guān)閉事務(wù)自動(dòng)提交

set autocommit=0;

我們先在一個(gè)窗口輸入一條獲取到排他鎖，雖然操作的是一條數(shù)據(jù)，但是鎖的是整張表，因?yàn)槲覀儧]有添加索引。

select * from user where user='zly1' for update;

這個(gè)時(shí)候我們對(duì)user這一列添加索引，就可以看到我們對(duì)其進(jìn)行加鎖就不會(huì)出現(xiàn)阻塞的情況了。

alter table user add index(user);

注：在MySQL的行級(jí)鎖是針對(duì)索引加的鎖，而不是針對(duì)表中的行加級(jí)鎖，雖然訪問不同行的記錄，但是如果不存在對(duì)應(yīng)的索引，或者使用相同的索引的話，就會(huì)造成鎖的沖突而鎖住整張表。

死鎖

死鎖是指兩個(gè)或者兩個(gè)以上的額事務(wù)在執(zhí)行過程中，因?yàn)榛ハ嗟牡却蛘咭驗(yàn)闋帗屜嗤馁Y源而造成的互相等待現(xiàn)象。

我們還是采用剛剛的例子，還是將事務(wù)的自動(dòng)提交關(guān)閉掉，首先先在會(huì)話1中，更新id為1的記錄，然后在會(huì)話2中更新id為2的記錄，這個(gè)時(shí)候我們?cè)倩貋碓跁?huì)話1更新id為2的記錄，在會(huì)話2中更新id為1的記錄，就會(huì)產(chǎn)生一個(gè)死鎖；