MySQL定位Next-Key Lock只要1分鐘

文末領取【MySQL面試題】

連接與線程

查看連接信息 show processlist

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

MySQL 非企業版本只支持一個線程一個鏈接

查看線程模型 show variables like 'thread_handling'

<code>+-----------------------------------------+---------------------------+/<code><code>| Variable_name | Value |/<code><code>+-----------------------------------------+---------------------------+/<code><code>| thread_handling | one-thread-per-connection |/<code><code>+-----------------------------------------+---------------------------+/<code>

以下是事務提交策略，有兩個隱藏事務提交時間點需要注意：

• 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 表中就會被查詢條件命中。

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

我們看個例子：

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

<code>+----+-----+/<code><code>| id | age |/<code><code>+----+-----+/<code><code>| 1 | 20 |/<code><code>| 2 | 30 |/<code><code>| 3 | 35 |/<code><code>| 4 | 40 |/<code><code>+----+-----+/<code>

為了方便調試，將 innodb 獲取鎖的超時時間調大點

<code>show variables like '%innodb_lock_wait%'/<code><code>set innodb_lock_wait_timeout=600/<code>

開啟兩個會話：

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

<code>session B id=5:/<code><code>begin/<code><code>insert into peoples(age) values(31)/<code>

show processlist 找到連接的id。

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

• 事務

select * from information_schema.innodb_trx \\G 查看事務執行情況。

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

457240 事務狀態是 LOCK WAIT 在等待鎖，457239事務狀態是 RUNNING執行中，正在等待事務提交。

• 鎖

select * from information_schema.innodb_locks \\G 查看鎖的佔用情況。

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

innodb_locks 表包含了已經獲取到的鎖信息和請求鎖的信息。lock_index字段表示鎖走的索引，record鎖都是基於索引完成。

根據上面事務457240狀態是獲取鎖，lock_data | 35, 7，表示請求的數據。而事務457239佔用了當前X鎖。

• 鎖等待

select * from information_schema.innodb_lock_waits 查看鎖等待信息。

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

457240 事務需要獲取131:4:4鎖，457239 事務佔用了131:4:4鎖。

• innodb 監視器

show engine innodb status

<code>LIST OF TRANSACTIONS FOR EACH SESSION:/<code><code>---TRANSACTION 422032240994144, not started/<code><code>0 lock struct(s), heap size 1136, 0 row lock(s)/<code><code>---TRANSACTION 457240, ACTIVE 394 sec inserting/<code><code>mysql tables in use 1, locked 1/<code><code>LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1/<code><code>MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update/<code><code>insert into peoples(age) values(31)/<code><code>------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED:/<code><code>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 /<code><code>rec insert intention waiting/<code><code>Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0/<code><code> 0: len 4; hex 80000023; asc #;; /<code><code> 1: len 4; hex 00000007; asc ;;/<code> 

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

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

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

End.

長按下方海報領取【MySQL面試題】

· 零基礎入職數據分析就業班 ·

課程形式主為“直播+錄播”

課程專享：

課程結束後能熟練掌握SQL、Python、Excel、PPT等數據分析工具

金3銀4招聘季，愛數據帶你成功入職數據分析！

閱讀更多 愛數據網 的文章

關鍵字: 腳本語言 分鐘 只要