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

2024-07-24 12:36:19

字體：大中小

來源：轉載

供稿：網友

　　連接與線程
　　查看連接信息 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 鎖住，只有這樣才能解決幻讀問題。

（編輯：武林網）

上一篇：JDBC對Mysql utf8mb4字符集的處置

下一篇：mysql 外連接示例