MySQL事務與鎖範例教學詳解

2022-11-07 14:01:52

MySQL事務和鎖

事務

說到關係型的資料庫的事務，相信大家對四大特性都不陌生，分別是原子性、一致性、隔離性、永續性，簡稱為ACID特性。

MySQL中支援3種不同的儲存引擎：

MyISAM儲存引擎、Memory儲存引擎、和InnoDB儲存引擎

注：只有InnoDB才支援事務。

事務的控制語句

控制語句	作用
begin或者start transaction	開啟一個事務
commit 或者 commit work	提交事務，進行永續性修改
rollback 或者 rollback work	回滾事務，復原已經進行修改但未提交的操作
savepoint [儲存點]	在事務中建立一個儲存點，一個事務可以有多個儲存點
releasavepoint [儲存點]	回滾到指定的儲存點
set transaction	設定事務的隔離級別

事務隔離級別設定

先複習一個事務的四大隔離級別

讀未提交（READ-UNCOMMITTED)
讀已提交（READ-COMMITTED)
可重複讀（RE-PEATABLE-READ)
可序列化讀（SERIALIZABLE)

下面是操作過程。

首先，檢視預設的事務隔離級別，可以看到是可重複讀（REPEATABLE-READ），

show variables like '%isolation%';

髒讀

我們來演示一下髒讀的場景，下面這張圖是展示了我原先已經建立好的兩個使用者的賬號都為100元。

分別開啟兩個連線mysql的對談視窗，其中一個對談的隔離級別為READ-UNCOMMITED，然後再另一個視窗中開啟一個事務，例如，lisi給zhangsan轉賬100元，

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

我們在另外一個讀未提交的視窗中檢視，zhangsan看到錢已經轉過來了，但是實際上lisi的事務還沒有提交，假如這個時候，lisi不想轉賬了，回滾事務，那zhangsan就讀到髒資料了。

不可重複讀

下面來展示一下不可重複讀的場景。

首先我們將zhangsan的視窗的事務隔離級別設定成READ-COMMITTED，並且在兩個視窗都開啟事務

set session transaction isolation level read committed;

假設zhangsan現在想統計全部人的錢有多少，很明顯200；

但是這個時候lisi往賬戶裡面存了100元，並提交事務，但是這個時候，zhangsan再次查詢總和，我們會查詢到總金額為300，但是這次查詢是處於同一個事務中，查詢到兩次不一樣的結果，屬於不可重複讀的情況。

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

幻讀

為了解決不可重複讀的問題，我們將事務的隔離等級設定成RE-PEATABLE-READ，即MySQL預設的事務隔離等級，然後在兩邊都開啟一個事務。

set session transaction isolation level repeatable read;

我們先在一個視窗插入一條資料並提交，然後在另外一個視窗檢視，此時是查詢不到這個記錄的，但是假如這個時候我們新插入一條主鍵和剛插入的記錄一樣的話，我們就可以發現

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

這樣也算是一種幻讀的現象，但是網上也有一種說法在可重複讀的等級下，幻讀是可避免的，這種說法不是非常準確的，如果在進行更新和插入時就可能會出現幻讀的情況，如果想要解決幻讀的情況，可以將事務隔離等級設定到SERIALIZABLE

鎖機制

InnoDB的行級鎖

InnoDB預設採用的行級鎖，分為以下這兩種，分別為共用鎖和排他鎖。

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

共用鎖（S鎖）：也叫讀鎖，如果在該資料物件上加了共用鎖，該事務可以讀取但是不能修改資料。其他事務也可以在該物件上加共用鎖，但是不能修改資料。

排他鎖（X鎖）：也叫寫鎖，在一個資料物件只有一把排他鎖，獲取到該鎖的事務可以讀取資料和修改資料。

（加鎖：一般的查詢語句不會加任何的鎖型別，當然也可以為資料加鎖，比如在select * from … for update，這樣可以為資料新增排他鎖，而使用select … lock in share mode 可以為資料新增共用鎖。

鎖實戰

首先關閉事務自動提交

set autocommit=0;

我們先在一個視窗輸入一條獲取到排他鎖，雖然操作的是一條資料，但是鎖的是整張表，因為我們沒有新增索引。

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

這個時候我們對user這一列新增索引，就可以看到我們對其進行加鎖就不會出現阻塞的情況了。

alter table user add index(user);

注：在MySQL的行級鎖是針對索引加的鎖，而不是針對表中的行加級鎖，雖然存取不同行的記錄，但是如果不存在對應的索引，或者使用相同的索引的話，就會造成鎖的衝突而鎖住整張表。

死鎖

死鎖是指兩個或者兩個以上的額事務在執行過程中，因為互相的等待或者因為爭搶相同的資源而造成的互相等待現象。

我們還是採用剛剛的例子，還是將事務的自動提交關閉掉，首先先在對談1中，更新id為1的記錄，然後在對談2中更新id為2的記錄，這個時候我們再回來在對談1更新id為2的記錄，在對談2中更新id為1的記錄，就會產生一個死鎖；

總結

本文主要介紹了事務的隔離等級和事務的鎖機制，主要更加偏向於實戰部分，我前面也有一些文章涉及到。

到此這篇關於MySQL事務與鎖範例教學詳解的文章就介紹到這了,更多相關MySQL事務與鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！