当前位置:Gxlcms > 数据库问题 > Mysql一分钟定位 Next-Key Lock,你需要几分钟

Mysql一分钟定位 Next-Key Lock,你需要几分钟

时间:2021-07-01 10:21:17 帮助过:8人阅读

一分钟定位 Next-Key Lock,你需要几分钟

标签: Mysql、Next-KeyLock、插入意向锁

连接与线程

查看连接信息 show processlist

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

mysql 非企业版本只支持一个线程一个链接

查看线程模型 show variables like ‘thread_handling‘

  1. <code>+-----------------------------------------+---------------------------+
  2. | Variable_name | Value |
  3. +-----------------------------------------+---------------------------+
  4. | thread_handling | one-thread-per-connection |
  5. +-----------------------------------------+---------------------------+</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 lockgap 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 表中就会被查询条件命中。

所以要想解决幻读不仅不允许记录的空隙被插入记录外,还要防止两遍记录被修改,因为如果前后两条记录被修改了那区间就会变大,就会有幻读出现。

我们看个例子。

  1. <code> CREATE TABLE `peoples` (
  2. `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  3. `age` int(11) DEFAULT NULL,
  4. PRIMARY KEY (`id`),
  5. KEY `idx_peoples_age` (`age`)
  6. ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4</code>
  1. <code>+----+-----+
  2. | id | age |
  3. +----+-----+
  4. | 1 | 20 |
  5. | 2 | 30 |
  6. | 3 | 35 |
  7. | 4 | 40 |
  8. +----+-----+</code>

为了方便调试,将 innodb 获取锁的超时时间调大点

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

开启两个会话。

  1. <code>session A id=8:
  2. begin
  3. select count(1) from peoples where age>30 for update;</code>
  1. <code>session B id=5:
  2. begin
  3. insert into peoples(age) values(31)</code>

show processlist 找到连接的id。

  1. <code>***************************[ 1. row ]***************************
  2. Id | 3
  3. User | root
  4. Host | 172.17.0.1:60542
  5. db | test
  6. Command | Query
  7. Time | 0
  8. State | starting
  9. Info | show processlist
  10. ***************************[ 2. row ]***************************
  11. Id | 5
  12. User | root
  13. Host | 172.17.0.1:60546
  14. db | test
  15. Command | Query
  16. Time | 394
  17. State | update
  18. Info | insert into peoples(age) values(31)
  19. ***************************[ 3. row ]***************************
  20. Id | 8
  21. User | root
  22. Host | 172.17.0.1:60552
  23. db | test
  24. Command | Sleep
  25. Time | 396
  26. State |
  27. Info | <null></code>
  • 事务

select * from information_schema.innodb_trx \G 查看事务执行情况。

  1. <code>***************************[ 1. row ]***************************
  2. trx_id | 457240
  3. trx_state | LOCK WAIT
  4. trx_started | 2020-01-27 06:08:12
  5. trx_requested_lock_id | 457240:131:4:4
  6. trx_wait_started | 2020-01-27 06:09:25
  7. trx_weight | 6
  8. trx_mysql_thread_id | 5
  9. trx_query | insert into peoples(age) values(31)
  10. trx_operation_state | inserting
  11. trx_tables_in_use | 1
  12. trx_tables_locked | 1
  13. trx_lock_structs | 5
  14. trx_lock_memory_bytes | 1136
  15. trx_rows_locked | 4
  16. trx_rows_modified | 1
  17. trx_concurrency_tickets | 0
  18. trx_isolation_level | REPEATABLE READ
  19. trx_unique_checks | 1
  20. trx_foreign_key_checks | 1
  21. trx_last_foreign_key_error | <null>
  22. trx_adaptive_hash_latched | 0
  23. trx_adaptive_hash_timeout | 0
  24. trx_is_read_only | 0
  25. trx_autocommit_non_locking | 0
  26. ***************************[ 2. row ]***************************
  27. trx_id | 457239
  28. trx_state | RUNNING
  29. trx_started | 2020-01-27 06:07:59
  30. trx_requested_lock_id | <null>
  31. trx_wait_started | <null>
  32. trx_weight | 3
  33. trx_mysql_thread_id | 8
  34. trx_query | <null>
  35. trx_operation_state | <null>
  36. trx_tables_in_use | 0
  37. trx_tables_locked | 1
  38. trx_lock_structs | 3
  39. trx_lock_memory_bytes | 1136
  40. trx_rows_locked | 5
  41. trx_rows_modified | 0
  42. trx_concurrency_tickets | 0
  43. trx_isolation_level | REPEATABLE READ
  44. trx_unique_checks | 1
  45. trx_foreign_key_checks | 1
  46. trx_last_foreign_key_error | <null>
  47. trx_adaptive_hash_latched | 0
  48. trx_adaptive_hash_timeout | 0
  49. trx_is_read_only | 0
  50. trx_autocommit_non_locking | 0</code>

457240 事务状态是 LOCK WAIT 在等待锁,457239事务状态是 RUNNING执行中,正在等待事务提交。

select * from information_schema.innodb_locks \G 查看锁的占用情况。

  1. <code>***************************[ 1. row ]***************************
  2. lock_id | 457240:131:4:4
  3. lock_trx_id | 457240
  4. lock_mode | X,GAP
  5. lock_type | RECORD
  6. lock_table | `test`.`peoples`
  7. lock_index | idx_peoples_age
  8. lock_space | 131
  9. lock_page | 4
  10. lock_rec | 4
  11. lock_data | 35, 7
  12. ***************************[ 2. row ]***************************
  13. lock_id | 457239:131:4:4
  14. lock_trx_id | 457239
  15. lock_mode | X
  16. lock_type | RECORD
  17. lock_table | `test`.`peoples`
  18. lock_index | idx_peoples_age
  19. lock_space | 131
  20. lock_page | 4
  21. lock_rec | 4
  22. lock_data | 35, 7</code>

innodb_locks 表包含了已经获取到的锁信息和请求锁的信息。lock_index字段表示锁走的索引,record锁都是基于索引完成。

根据上面事务457240状态是获取锁,lock_data | 35, 7,表示请求的数据。而事务457239占用了当前X锁。

  • 锁等待

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

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

457240 事务需要获取131:4:4锁,457239 事务占用了131:4:4锁。

  • innodb 监视器
    show engine innodb status
  1. <code>LIST OF TRANSACTIONS FOR EACH SESSION:
  2. ---TRANSACTION 422032240994144, not started
  3. 0 lock struct(s), heap size 1136, 0 row lock(s)
  4. ---TRANSACTION 457240, ACTIVE 394 sec inserting
  5. mysql tables in use 1, locked 1
  6. LOCK WAIT 5 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
  7. MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root update
  8. insert into peoples(age) values(31)
  9. ------- TRX HAS BEEN WAITING 165 SEC FOR THIS LOCK TO BE GRANTED:
  10. 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
  11. Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  12. 0: len 4; hex 80000023; asc #;;
  13. 1: len 4; hex 00000007; asc ;;
  14. ------------------
  15. ---TRANSACTION 457239, ACTIVE 407 sec
  16. 3 lock struct(s), heap size 1136, 5 row lock(s)
  17. 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 锁住,只有这样才能解决幻读问题。

作者:王清培(趣头条 Tech Leader)

Mysql一分钟定位 Next-Key Lock,你需要几分钟

标签:模型   memory   cts   组合   repeat   begin   作者   策略   schema   

人气教程排行