跑了4個(gè)實(shí)驗(yàn)，實(shí)戰(zhàn)講解 MySQL的行鎖、間隙鎖...?

大家好

今天跟大家聊一聊MySQL的事務(wù)隔離，并通過一些實(shí)驗(yàn)做了些總結(jié)。光說不練，假把式，沒有經(jīng)過實(shí)踐就沒有話語權(quán)。

我們都知道數(shù)據(jù)庫有四種隔離級(jí)別，分別是：

• 讀未提交（READ UNCOMMITTED）
• 讀已提交 （READ COMMITTED）
• 可重復(fù)讀 （REPEATABLE READ）
• 串行化 （SERIALIZABLE）

1、基礎(chǔ)環(huán)境

• 當(dāng)前的數(shù)據(jù)庫版本

mysql>?select?version();
+-----------+
|?version()?|
+-----------+
|?8.0.27????|
+-----------+
1?row?in?set?(0.00?sec)

• 當(dāng)前的事務(wù)隔離級(jí)別

mysql>?show?variables?like?'transaction_isolation';
+-----------------------+-----------------+
|?Variable_name?????????|?Value???????????|
+-----------------------+-----------------+
|?transaction_isolation?|?REPEATABLE-READ?|
+-----------------------+-----------------+
1?row?in?set?(0.00?sec)

2、創(chuàng)建個(gè)人收支表，并對(duì) income 字段創(chuàng)建索引，expend字段沒有索引

CREATE?TABLE?`person`?(
??`id`?bigint(20)?unsigned?NOT?NULL?AUTO_INCREMENT?COMMENT?'自增主鍵',
??`income`?bigint(20)?NOT?NULL?COMMENT?'收入',
??`expend`?bigint(20)?NOT?NULL?COMMENT?'支出',
??PRIMARY?KEY?(`id`),
??KEY?`idx_income`?(`income`)
)?ENGINE=InnoDB?AUTO_INCREMENT=1?DEFAULT?CHARSET=utf8?COMMENT='個(gè)人收支表';

3、初始化表數(shù)據(jù)，插入5條記錄

insert?into?person?values(100,1000,1000);
insert?into?person?values(200,2000,2000);
insert?into?person?values(300,3000,3000);
insert?into?person?values(400,4000,4000);
insert?into?person?values(500,5000,5000);

實(shí)驗(yàn)一：（事務(wù)A、B的條件字段沒有索引）

實(shí)驗(yàn)過程：

為了便于描述，我們定義時(shí)間軸坐標(biāo)，用T1、T2、T3... 表示當(dāng)前時(shí)刻。

T1：

事務(wù)A開啟事務(wù)，并執(zhí)行 select * from person where expend=4000 for update;

由于 expend 字段沒有索引，需要掃描全表。此時(shí)加的鎖是所有記錄的行鎖和它們之間的間隙鎖，也稱為 next-key lock，前開后閉區(qū)間。分別是 (-∞,100]、(100,200]、(200,300]、(300,400]、(400，500]、(500, +supremum]

T2：

事務(wù)B開啟事務(wù)，執(zhí)行插入語句 ?insert into person values(401,4001,4001); 此時(shí)一直被阻塞住，因?yàn)椴]有獲得鎖。

面的這種情況，有兩種選擇：一種等到事務(wù)A結(jié)束（提交或回滾）；另一種等事務(wù)鎖超時(shí)。

接著這個(gè)話題，我們稍微擴(kuò)展介紹下鎖超時(shí)：

MySQL數(shù)據(jù)庫采用InnoDB模式，默認(rèn)參數(shù)：innodb_lock_wait_timeout設(shè)置鎖等待的時(shí)間是50s，一旦數(shù)據(jù)庫鎖超過這個(gè)時(shí)間就會(huì)報(bào)錯(cuò)。

ERROR?1205?(HY000):?Lock?wait?timeout?exceeded;?try?restarting?transaction

當(dāng)然，我們也可以通過命令來查看、修改這個(gè)超時(shí)時(shí)間

#?查看超時(shí)時(shí)間
SHOW?GLOBAL?VARIABLES?LIKE?'innodb_lock_wait_timeout';

#?修改時(shí)間
SET?GLOBAL?innodb_lock_wait_timeout=120;

T3：

事務(wù)A ，執(zhí)行 commit 操作， 提交事務(wù)

T4：

事務(wù)B，插入一條記錄，insert into person values(401,4001,4001); 操作成功。

此時(shí) select * from person; 可以看到新插入的記錄

實(shí)驗(yàn)二：（事務(wù)A、B的條件字段有創(chuàng)建索引）

T1：

事務(wù)A，開啟事務(wù)，并執(zhí)行 ?select * from person where income=3000 for update，命中記錄且 income 有索引，此時(shí)的加鎖區(qū)間是 income=3000 的行記錄以及與下一個(gè)值4000之間的空隙（行鎖+間隙鎖），也就是[3000，4000]

T2：

事務(wù)B，開始事務(wù)，執(zhí)行 ?insert into person values(301,3001,3001); 沒有搶到鎖，線程被阻塞住，直到事務(wù)A提交事務(wù)并釋放鎖。

實(shí)驗(yàn)三：（自動(dòng)識(shí)別死鎖）

特別說明：

T3：事務(wù)A執(zhí)行insert操作，被事務(wù)B的鎖攔截住了

T4：同理，事務(wù)B執(zhí)行insert操作，被事務(wù)A攔截了，這里被系統(tǒng)自動(dòng)檢測(cè)到，拋出 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 。將事務(wù)B持有的鎖釋放掉，并重啟事務(wù)。

T5：事務(wù)A在T3時(shí)刻的insert可以繼續(xù)操作

實(shí)驗(yàn)四：（更新記錄鎖保護(hù)）

1、事務(wù)A在執(zhí)行后 update person set income=111 where ?income=3000; 開啟了鎖保護(hù)

2、這時(shí)，事務(wù)B再執(zhí)行 ?insert into person values(307,3000,3000) 或者 ?update person set income=3000 where id=100，都會(huì)重新去搶奪鎖，從而保證安全。

知識(shí)小結(jié)

1、對(duì)于事務(wù)，binlog 日志是在 commit 提交時(shí)才生成的

2、行鎖與間隙鎖有很大區(qū)別。

• 行鎖：如果事務(wù)A對(duì) id=1 添加行鎖，事務(wù)B則無法對(duì) id=1 添加行鎖
• 間隙鎖：如果 select .. from 表名 where d=6 for updata，事務(wù)A 和 事務(wù) B 都可以對(duì)（5，12）添加間隙鎖。間隙鎖是開區(qū)間。

3、行鎖和間隙鎖合稱 next-key lock，每個(gè) next-key lock 是前開后閉區(qū)間。

4、只有在可重復(fù)讀的隔離級(jí)別下，才會(huì)有間隙鎖

5、讀提交級(jí)別沒有間隙鎖，只有行鎖，但是如何保證一個(gè)間隙操作產(chǎn)生的 binlog 對(duì)主從數(shù)據(jù)同步產(chǎn)生的影響呢？我們需要把 binlog 的格式設(shè)置為 row。

其本質(zhì)就是將模糊操作改成了針對(duì)具體的主鍵id行操作

#?初始語句
delete?from?order?where?c?=?10

#?轉(zhuǎn)換后語句
delete?from?order?where?id?=?10

6、大部分公司的數(shù)據(jù)庫的隔離級(jí)別都是讀提交隔離級(jí)別加 binlog_format=row 的組合

7、 大多數(shù)數(shù)據(jù)庫的默認(rèn)級(jí)別就是讀提交（Read committed），比如Sql Server 、 Oracle。MySQL的默認(rèn)級(jí)別是 可重復(fù)讀（Repeatable Read ）