[[440226]]

大家好，我是Tom哥~

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

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

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

實驗前的準(zhǔn)備工作

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ù)隔離級別

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

2、創(chuàng)建個人收支表，并對 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='個人收支表'; 

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); 

實驗一：(事務(wù)A、B的條件字段沒有索引)

實驗過程：

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

T1：

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

由于 expend 字段沒有索引，需要掃描全表。此時加的鎖是所有記錄的行鎖和它們之間的間隙鎖，也稱為 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); 此時一直被阻塞住，因為并沒有獲得鎖。

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

接著這個話題，我們稍微擴展介紹下鎖超時：

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

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

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

# 查看超時時間 
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout'; 
 
# 修改時間 
SET GLOBAL innodb_lock_wait_timeout=120; 

T3：

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

T4：

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

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

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

T1：

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

T2：

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

實驗三：(自動識別死鎖)

特別說明：

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

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

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

實驗四：(更新記錄鎖保護)

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

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

知識小結(jié)

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

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

行鎖：如果事務(wù)A對 id=1 添加行鎖，事務(wù)B則無法對 id=1 添加行鎖

間隙鎖：如果 select .. from 表名 where d=6 for updata，事務(wù)A 和 事務(wù) B 都可以對(5，12)添加間隙鎖。間隙鎖是開區(qū)間。

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

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

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

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

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

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

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

本文轉(zhuǎn)載自微信公眾號「微觀技術(shù)」