Mysql一分鐘定位 Next-Key Lock，你需要幾分鐘

連接配接與線程

檢視連接配接資訊

show processlist

+----+------+------------------+------+---------+------+----------+------------------+
| Id | User | Host             | db   | Command | Time | State    | Info             |
+----+------+------------------+------+---------+------+----------+------------------+
| 3  | root | 172.17.0.1:60542 | test | Query   | 0    | starting | show processlist |
| 5  | root | 172.17.0.1:60546 | test | Sleep   | 4168 |          | <null>           |
| 8  | root | 172.17.0.1:60552 | test | Sleep   | 4170 |          | <null>           |
+----+------+------------------+------+---------+------+----------+------------------+

mysql 非企業版本隻支援一個線程一個連結

檢視線程模型

show variables like 'thread_handling'

+-----------------------------------------+---------------------------+
| Variable_name                           | Value                     |
+-----------------------------------------+---------------------------+
| thread_handling                         | one-thread-per-connection |
+-----------------------------------------+---------------------------+

【事務送出政策】

有兩個隐藏事務送出時間點需要注意，第一個是

autocommit=1

Mysql session 級别的自動送出變量，所有 ORM 架構中的事務送出控制都會受到這個字段影響，預設情況下目前語句會自動送出，但是如果是顯示 begin transaction 開啟事務需要自行手動送出。有些時候 ORM 架構會根據一些設定或者政策，将 autocommit 設定為0。

第二個就是，DDL操作前都會隐式送出目前事務，有些腳本将DML和DDL混合在一起使用，這樣會有一緻性問題。DDL會自動送出目前事務。因為DDL在5.7之前都是不支援事務原則操作的。（Mysql8.0已經支援DDL事務性）

Next-Key Lock 排查

Next-Key Lock 隻發生在 RR（REPEATABLE-READ）隔離級别下。

Mysql 有很多類型對種鎖，

表鎖

、

record lock

gap lock

意向共享/排他鎖

插入意向鎖

中繼資料鎖

Auto_Incr自增鎖

，排除掉 _中繼資料鎖_、Auto_Incr自增鎖之後，剩下的鎖組合使用最多的就是在RR隔離級别下。

RR隔離級别是預設事務隔離級别，也是Mysql的強項之一，在RR隔離級别下事務有最大的吞吐量，而且不會出現幻讀問題。Next-Key Lock 就是為了解決這個問題，簡單講 record lock+gap lock 就是 _Next-Key Lock_。

幻讀_的根本問題就是出現在記錄的邊界值上，比如我們統計年齡大于30歲的人數：

select count(1) peoples where age>30

這個語句有可能每次查詢得到的結果集都是不一樣的，因為隻要符合 _age>30 的記錄進到我們的 peoples 表中就會被查詢條件命中。

是以要想解決幻讀不僅不允許記錄的空隙被插入記錄外，還要防止兩遍記錄被修改，因為如果前後兩條記錄被修改了那區間就會變大，就會有幻讀出現。

我們看個例子。

CREATE TABLE `peoples` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_peoples_age` (`age`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

+----+-----+
| id | age |
+----+-----+
| 1  | 20  |
| 2  | 30  |
| 3  | 35  |
| 4  | 40  |
+----+-----+

為了友善調試，将 innodb 擷取鎖的逾時時間調大點

show variables like '%innodb_lock_wait%'
set innodb_lock_wait_timeout=600

開啟兩個會話。

session A id=8:
begin
select count(1) from peoples where age>30 for update;

session B id=5:
begin
insert into peoples(age) values(31)

show processlist

找到連接配接的id。

***************************[ 1. row ]***************************
Id      | 3
User    | root
Host    | 172.17.0.1:60542
db      | test
Command | Query
Time    | 0
State   | starting
Info    | show processlist
***************************[ 2. row ]***************************
Id      | 5
User    | root
Host    | 172.17.0.1:60546
db      | test
Command | Query
Time    | 394
State   | update
Info    | insert into peoples(age) values(31)
***************************[ 3. row ]***************************
Id      | 8
User    | root
Host    | 172.17.0.1:60552
db      | test
Command | Sleep
Time    | 396
State   |
Info    | <null>

事務

select * from information_schema.innodb_trx \G

檢視事務執行情況。

***************************[ 1. row ]***************************
trx_id                     | 457240
trx_state                  | LOCK WAIT
trx_started                | 2020-01-27 06:08:12
trx_requested_lock_id      | 457240:131:4:4
trx_wait_started           | 2020-01-27 06:09:25
trx_weight                 | 6
trx_mysql_thread_id        | 5
trx_query                  | insert into peoples(age) values(31)
trx_operation_state        | inserting
trx_tables_in_use          | 1
trx_tables_locked          | 1
trx_lock_structs           | 5
trx_lock_memory_bytes      | 1136
trx_rows_locked            | 4
trx_rows_modified          | 1
trx_concurrency_tickets    | 0
trx_isolation_level        | REPEATABLE READ
trx_unique_checks          | 1
trx_foreign_key_checks     | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched  | 0
trx_adaptive_hash_timeout  | 0
trx_is_read_only           | 0
trx_autocommit_non_locking | 0
***************************[ 2. row ]***************************
trx_id                     | 457239
trx_state                  | RUNNING
trx_started                | 2020-01-27 06:07:59
trx_requested_lock_id      | <null>
trx_wait_started           | <null>
trx_weight                 | 3
trx_mysql_thread_id        | 8
trx_query                  | <null>
trx_operation_state        | <null>
trx_tables_in_use          | 0
trx_tables_locked          | 1
trx_lock_structs           | 3
trx_lock_memory_bytes      | 1136
trx_rows_locked            | 5
trx_rows_modified          | 0
trx_concurrency_tickets    | 0
trx_isolation_level        | REPEATABLE READ
trx_unique_checks          | 1
trx_foreign_key_checks     | 1
trx_last_foreign_key_error | <null>
trx_adaptive_hash_latched  | 0
trx_adaptive_hash_timeout  | 0
trx_is_read_only           | 0
trx_autocommit_non_locking | 0

457240 事務狀态是

LOCK WAIT

在等待鎖，457239事務狀态是

RUNNING

執行中，正在等待事務送出。

select * from information_schema.innodb_locks \G

檢視鎖的占用情況。

***************************[ 1. row ]***************************
lock_id     | 457240:131:4:4
lock_trx_id | 457240
lock_mode   | X,GAP
lock_type   | RECORD
lock_table  | `test`.`peoples`
lock_index  | idx_peoples_age
lock_space  | 131
lock_page   | 4
lock_rec    | 4
lock_data   | 35, 7
***************************[ 2. row ]***************************
lock_id     | 457239:131:4:4
lock_trx_id | 457239
lock_mode   | X
lock_type   | RECORD
lock_table  | `test`.`peoples`
lock_index  | idx_peoples_age
lock_space  | 131
lock_page   | 4
lock_rec    | 4
lock_data   | 35, 7

innodb_locks 表包含了已經擷取到的鎖資訊和請求鎖的資訊。lock_index字段表示鎖走的索引，record鎖都是基于索引完成。

根據上面事務457240狀态是擷取鎖，

lock_data   | 35, 7

，表示請求的資料。而事務457239占用了目前X鎖。

鎖等待

select * from information_schema.innodb_lock_waits

檢視鎖等待資訊。

***************************[ 1. row ]***************************
requesting_trx_id | 457240
requested_lock_id | 457240:131:4:4
blocking_trx_id   | 457239
blocking_lock_id  | 457239:131:4:4

457240 事務需要擷取131:4:4鎖，457239 事務占用了131:4:4鎖。

innodb 螢幕 show engine innodb status

LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422032240994144, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 457240, ACTIVE 394 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update
insert into peoples(age) values(31)
------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 131 page no 4 n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 80000023; asc    #;;
 1: len 4; hex 00000007; asc     ;;

------------------
---TRANSACTION 457239, ACTIVE 407 sec
3 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 8, OS thread handle 140556966696704, query id 104 172.17.0.1 root

MySQL thread id 5 正在準備上插入意向鎖，

插入意向鎖

本質上是加間隙鎖，是為了保證最大并發插入，不相關的行插入不受到互斥。thread id 5 需要保證在插入前加上間隙鎖，主要是防止并發插入帶來的一緻性問題。

session 5 和 session 8 都沒有操作到 id=3，age=35的記錄，但是卻被X+Gap Lock 鎖住，隻有這樣才能解決幻讀問題。

作者：王清培（趣頭條 Tech Leader）

Mysql一分鐘定位 Next-Key Lock，你需要幾分鐘

連接配接與線程

Next-Key Lock 排查

繼續閱讀

SQL優化SQL語句優化的目的

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

JAVA高效程式設計指南

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

關于SQL語言

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案

sqlServer根據經緯查距離