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招聘季，爱数据带你成功入职数据分析！