跑了四個實驗，實戰講解 MySQL的行鎖、間隙鎖...
大家好，我是Tom哥~
今天跟大家聊一聊MySQL的事務隔離，并通過一些實驗做了些總結。光說不練，假把式，沒有經過實踐就沒有話語權。
我們都知道數據庫有四種隔離級別，分別是：
- 讀未提交(READ UNCOMMITTED)
- 讀已提交 (READ COMMITTED)
- 可重復讀 (REPEATABLE READ)
- 串行化 (SERIALIZABLE)
實驗前的準備工作
1、基礎環境
當前的數據庫版本
- mysql> select version();
- +-----------+
- | version() |
- +-----------+
- | 8.0.27 |
- +-----------+
- 1 row in set (0.00 sec)
當前的事務隔離級別
- mysql> show variables like 'transaction_isolation';
- +-----------------------+-----------------+
- | Variable_name | Value |
- +-----------------------+-----------------+
- | transaction_isolation | REPEATABLE-READ |
- +-----------------------+-----------------+
- 1 row in set (0.00 sec)
2、創建個人收支表，并對 income 字段創建索引，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、初始化表數據，插入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);
實驗一：(事務A、B的條件字段沒有索引)
實驗過程：
為了便于描述，我們定義時間軸坐標，用T1、T2、T3... 表示當前時刻。
T1：
事務A開啟事務，并執行 select * from person where expend=4000 for update;
由于 expend 字段沒有索引，需要掃描全表。此時加的鎖是所有記錄的行鎖和它們之間的間隙鎖，也稱為 next-key lock，前開后閉區間。分別是 (-∞,100]、(100,200]、(200,300]、(300,400]、(400，500]、(500, +supremum]
T2：
事務B開啟事務，執行插入語句 insert into person values(401,4001,4001); 此時一直被阻塞住，因為并沒有獲得鎖。
面的這種情況，有兩種選擇：一種等到事務A結束(提交或回滾);另一種等事務鎖超時。
接著這個話題，我們稍微擴展介紹下鎖超時：
MySQL數據庫采用InnoDB模式，默認參數：innodb_lock_wait_timeout設置鎖等待的時間是50s，一旦數據庫鎖超過這個時間就會報錯。
- ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
當然，我們也可以通過命令來查看、修改這個超時時間
- # 查看超時時間
- SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';
- # 修改時間
- SET GLOBAL innodb_lock_wait_timeout=120;
T3：
事務A ，執行 commit 操作， 提交事務
T4：
事務B，插入一條記錄，insert into person values(401,4001,4001); 操作成功。
此時 select * from person; 可以看到新插入的記錄
實驗二：(事務A、B的條件字段有創建索引)
T1：
事務A，開啟事務，并執行 select * from person where income=3000 for update，命中記錄且 income 有索引，此時的加鎖區間是 income=3000 的行記錄以及與下一個值4000之間的空隙(行鎖+間隙鎖)，也就是[3000，4000]
T2：
事務B，開始事務，執行 insert into person values(301,3001,3001); 沒有搶到鎖，線程被阻塞住，直到事務A提交事務并釋放鎖。
實驗三：(自動識別死鎖)
特別說明：
T3：事務A執行insert操作，被事務B的鎖攔截住了
T4：同理，事務B執行insert操作，被事務A攔截了，這里被系統自動檢測到，拋出 ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction 。將事務B持有的鎖釋放掉，并重啟事務。
T5：事務A在T3時刻的insert可以繼續操作
實驗四：(更新記錄鎖保護)
1、事務A在執行后 update person set income=111 where income=3000; 開啟了鎖保護
2、這時，事務B再執行 insert into person values(307,3000,3000) 或者 update person set income=3000 where id=100，都會重新去搶奪鎖，從而保證安全。
知識小結
1、對于事務，binlog 日志是在 commit 提交時才生成的
2、行鎖與間隙鎖有很大區別。
行鎖：如果事務A對 id=1 添加行鎖，事務B則無法對 id=1 添加行鎖
間隙鎖：如果 select .. from 表名 where d=6 for updata，事務A 和 事務 B 都可以對(5，12)添加間隙鎖。間隙鎖是開區間。
3、行鎖和間隙鎖合稱 next-key lock，每個 next-key lock 是前開后閉區間。
4、只有在可重復讀的隔離級別下，才會有間隙鎖
5、讀提交級別沒有間隙鎖，只有行鎖，但是如何保證一個間隙操作產生的 binlog 對主從數據同步產生的影響呢?我們需要把 binlog 的格式設置為 row。
其本質就是將模糊操作改成了針對具體的主鍵id行操作
- # 初始語句
- delete from order where c = 10
- # 轉換后語句
- delete from order where id = 10
6、大部分公司的數據庫的隔離級別都是讀提交隔離級別加 binlog_format=row 的組合
7、 大多數數據庫的默認級別就是讀提交(Read committed)，比如Sql Server 、 Oracle。MySQL的默認級別是 可重復讀(Repeatable Read )
本文轉載自微信公眾號「微觀技術」
