跑了四個(gè)實(shí)驗(yàn)，實(shí)戰(zhàn)講解 MySQL的行鎖、間隙鎖...
大家好，我是Tom哥~
今天跟大家聊一聊MySQL的事務(wù)隔離，并通過一些實(shí)驗(yàn)做了些總結(jié)。光說不練，假把式，沒有經(jīng)過實(shí)踐就沒有話語權(quán)。
我們都知道數(shù)據(jù)庫有四種隔離級(jí)別，分別是：
- 讀未提交(READ UNCOMMITTED)
- 讀已提交 (READ COMMITTED)
- 可重復(fù)讀 (REPEATABLE READ)
- 串行化 (SERIALIZABLE)
實(shí)驗(yàn)前的準(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ù)隔離級(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 )
本文轉(zhuǎn)載自微信公眾號(hào)「微觀技術(shù)」
