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

文末领取【MySQL面试题】

连接与线程

查看连接信息 show processlist

+----+------+------------------+------+---------+------+----------+--------------+ K ! $----+| Id | User | Host | db | Command | Time | State | Info |+----+------+-N A v h 0 #-----------------+------+---------+------+----------+---| u ` + ` /-------( U 4 @ E w l - ]--------+| 3 | root | 172.17.0.1:60542 | tes9 $ - b m y j }t | Query | 0 | starting | show proc2 T V K 2 d Y gessli q 6 # P h 5 ! eist || 5 | root | 172.17.0.1:60546 | test | Sleep | 4168 | | <> || 8 | root | 172.17.0.1:60552 | test | Sleep | 4170 | | <> |+----+------+------------------+------+---------+------+----------+--c $ w k w----------------6 f l W t b ,+

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

查看线程模型 show variables like \'thrw ? n @ead_handling\'

+----------------------^ W P ,  & 8 g-------------------+---------------------------+| V* 5 4 {ariable_naa C U 0 & Tme | Value |+--------S k ) ) R d P---------------------------------+---------------------------+| thread_handlij Q p X Mng | one-thread-per-connecti F Von |+-----------------------------------------+---------------------------+

以下是事务提交策略，有两个隐藏事务提交时间点需要注意：

• autocommit=1 Mysql session 级别的自动提交变量，所有 ORM 框架中的事务提交控制都会受到这个字段影响，默认情况下当1 N T x $ S前语句会自动提交，但是如果是显示 b` s g V : Megin transactior q B ! ` Q ~ f Qn 开启事务需要自行手动提交。有些时候 ORM 框架会h M o r ; * n根据一些设置p ? y或者策略，将 autocommit 设置为0。

• DDL操作前都会隐式提交当前事务，有些脚本m v ,将DML和DDL混合在一起使用，这样会有一致性问题。DDL会自动提交当前事务。因为DDL在5.7之前都是不支持事务原则操作的。（Mysql8.0已经支持DDL事务性）

Next-Key Lock排查

Next-Key Lock 只发生在 RR（REPE0 ; u { S IATABLE-READ） 隔离级别下。

MySQL 有很多类型对种锁：表锁、record locV m e *k、gap lK v Rock、意向共享/排他锁、插入意向H 4 3 A c O K &锁、元数据锁、Auto_Incr自增@ 4 6 P L锁。排除掉元数据锁、Auto_Incr自增锁 之后，剩下的锁组合使用最多的就是在RR隔离级别下。0 1 6 | Z 3 O O

RR隔离级别是默认事务隔离级别，也是Mysql的强项之一，在RR隔离级别下事务有最大的吞吐量，而且不会出现幻读问题。NextJ 7 & a g ! O - G-Key Lock 就是为了解决这个问n d { 题，简单讲 record lock+gap lock 就是 Ne5 : y G xt-Key Lock。

幻读的根本问题就是出现在记录的边界值上，比 h\'%innoq ~ , # i I E ,db_lock_wait%\'set in{ 1 i F [ Z : 2 Dnodb_lock_wait_timeout=600

开启两个会话：

session A id=8:begE T 9 Xinselect count(1) from peoples where age>30 for update;

session B id=5:begU 2 7 ? / D 8ininsn 4 , U J eert into peoplh 0 8 K  z E  $es(age)O e P 6 vao [ R [ = Z -lues(31)

show processlisto ? J . J A Y @ % 找到连接的id。

******************************************************Id | 3User | rootHost | 172.17.0.1:60542db | testCommand | QueryTime | 0State | startingInfo | show processlist****c { 5 [ . N********************************************p ^ (******Id | 5User | rootHost | 172.17.0.1:60546db | testCommand | QueryTime | 394State | updateInfo | inserB ; C x .t into peoples(age) vaL . Hlues(31)***********************t ? 1 % y D -********************t A . _ i / H y Y***********Id | 8User | rootHost | 172.17.0.1:60552l A n a Gdb | testCommand | S0 ! ? Z H J P [leepTime | 396State |Info | <>

• 事务

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

******************************************************trx_id | 457240trx_state | LOCK WAITtrx_started | 2020-01-27 06:08:12trx_requested_lock_id | 457240:131:4:4trx_wait_started | 2020-01-27 06d y J ] E _:09:25trx_weight | 6trx_mysql_thread_id | 5trx_que^ j Y S ,ry | insc S N 2 C Rert into peoples(age) values(3k K T C v n z 1 B1)trx_operation_state | insertingtrx_tables_in_use | 1trx_taT c i ] :bles_locked | 1trx_lock_structs | 5trx_lock_memory_bytes | 1136trx_rows_locked | 4trx_1 ^ - ( / 2 2rows_modified | 1trx_concurrency_tickets | 0trx_isolatiot i D h *n_level | REPEATAB1 ) # A `L: # ~ i I l / O GE READtrxL t } ) X 4 4 v_unique_checks | 1trx_foreign_key_checks | 1tL # srx_last_fom a c k r H _reign_key_error | <>trx_adaptiv P a 8 M ~ i pe_hash_latched | 0trx_adaptive_hash_timeout | 0trx_isS 6 l q [ ~ |_read_only | 0trx_autocv p lommit_non_locking | 0******************************************************trx_id | 457239trx_state | RUNNINGtrx_started | 2020-01-27 06:07:59trx_requested_lock_id | <>trx_wait_started | <>trx_wei s q z s 2 = ight | 3trB , o xx_mysql_thread_id | 8trx_query | <>trx_operation_state | <g H # s j;>C U ~ f h z;trx_tables_in_use | 0trx_tables_locked | 1trx_lock_structs | 3trx_lock_memory_bytes | 1136trx_rows_lockeh g p ] j Wd | 5trx_rows_modified | 0trx_concurrency_tickets | 0trx_isolation_level | REPEATABLE READtrx_unique_checks | 1trx_foreign_ka s mey_checks | 1trx_3 j c M ~ O G *last_foreign# D y X y  Q +****************************************************lock_id | 457240:131:3 U K4:4lock_trx_id | 457240lock_mode |@ -  e . N j ] X,GAPlock_type | RECORDlock_table | `test`.`peoples`lock_index | idx_peoples_agelock_space | 131lock_page | 4lock_rec | 4lock_data | 35, 7***********************************D = q ; G F |*******************lock_id | 457239:131:4:4lock_trx_id | 457239lo) f } . c { [ck_mode | Xlock_type | RECORDlock_table | `test`.`peoples`lock_index | idx_peoples_agelock_space | 131lock_page | 4lock_rec | 4lock_data | 35, 7

innodb_locks 表包含了已经获取到的锁信息和请求锁的信息。lock s + t u i u_index字段表示锁走的索引，recoW I V krd锁都是基于索引完成。

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

• 锁等待

select * from information_schema.innodb_loY j 4 cck_waits 查看锁等待信息。

****X # E Q**************# ) 9 7 U { 6************y . v ~ E**********$ M ( 6 7 P Z m****f @ ` } $ 8 D**********requesting_trx_id | 457240reques* P { A qted_lock_id | 45P  e B , Y T x page no 4 n bits 72 index idx_peoples_age of table `test`.`peoples` trx id 457240 lock_mo@  q Q @ B 2 ] de X locks gap before rec ih  # 1 gnsert intention waitingRecord 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 ;;
---------------------TRANSACU , { P ? ;TION 457239, ACTIVE 407 sec3 lock struct(s), heap size 1136, 5 row lock(s)MySQL thread id 8, OS thread handle 14055696669e g ?6704, query id 104 17K S W p Z B  R v2.17.0.1 root

MySQL thread id 5 正在准备上插入意向锁，插入意向锁本质上是加间隙锁，是为了保证最C M & F 5 j :大并发插入，不相关的行插入U | Z D e 4 |不受到互斥。thread id 5 需要保证在插入前加上间隙锁，主要是防止并发插入带来的一致性问题。

session 5 和 session 8 都没有操作到 id=3，age=35的记录，但是却被X+Gap Lock 锁住，只有这样才能解决幻读问题。

End.

作者：王清培

来源：博客园

长按下方# F ! } j 3海报领取【MySQL面试题】

零基础% 4 G g o n入职数据分析就业班

课程形式主为“直播+录播”

课程专享：

月考测试通关 + 课程项目作业 + 1v1职场生涯规划 + 班_ u i ! { m u i主任辅导学习 + 资深讲师答疑

课程结束后能熟练掌握SQL、Pq h N d K =ython、Excel、PPT等数据分析工具

金3银4招聘季，爱数据带你成功入职数据分析！