面试官：你说对 MySQL 事务很熟？那我问你 10 个问题

文末领取【MySQL面试题】

连接与线程

查看连接信息 show processlist

+----+------+------------------+-Q L _ 3-----+---------+------+-------O I o G 8 7---+------------------+| Id | User | Host | db | Command | Time | State | Info |+----+------+------------------+------+---------+----P R V 3 l : Z--+----------+------------------+| 3 | root | 172.17.0.1:60542 | test | Qu@ p m / V jery | 0 | starting | show processlist || 5 | root5 3 u P * . | 172.17.0.1` o D U 7 : ` ? E:60546C t } P = ~ 2 ? | test | Sleep | 4168 | | <> || 8 | root | 172.17.0.1:60552 | t| [ j A qest | Sleep | 4170 | | <> |+----+------+------------------+----p = ( d--+---------+------+--{ M ` ) h a--------+------------------+

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

查看线程模型 show variable) = ` u M q g as like \'thread_handW 8 ` & Gling\'

+--------------------------------------P W 4 . I V ( # ;---+--------i  C + Z ~-----------, z ; J q--+---------------------------+| thread_handling | oI R s U .ne-thread-per-connection |+------------i Y z l { . &------------h E F w P a-----------------+---------------------------+

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

• autocommit=1 Mysql session 级别的自动提交变量，所有 ORM 框架中的事务提交控制都会受到这个字段影响，默认情况下当前语句会自动提交，但是如果是显示 begin transaction 开启事务需要自行手动提交。有些时候} + v | ORM 框架会根据一些设置或者L 5 b Q *策略，将 autocommit 设置为0。

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

Next-Key Lock排查

Next-Key Lock 只发生在 RR（REP` ? { g x DEATABLE-READ） 隔离级别下。

MySQL 有很多类型对种锁：表} b T u E 3 y锁、record lockP n j d Y W s H、V 9 Sgap lou E ` h ~ . M V Lck、意向共? z ) 0 z b 9 4享/排他锁、插入意向锁、元数据锁、Auto_Incr自增锁。排除掉元数据锁、AuS ] { ` F f % ,to_Incr自增锁 之后，剩下的锁组合使用最多的就是在RR隔离级别下。

RR隔离级别是默认事务隔离级别，也是Mysql的强项v 2 j V Q C之一，在RR隔离级别下事务有最大的吞吐量，而且不会出现幻读问题。Next-Key Lock 就是为了解决这个问题，简单讲 record lock+gap lock 就是 Next-Key Lock。

幻读的根本问Y d + z题就是出现在记录的边界值上，比如我们统计年龄大于30岁的人数：select count(1) peoples where age>30 这个语句有可能每次查询得到的结果集都是不一样的，因为只要符合 age>30 的记录进到我们的 peoples 表中M 6 @就会被查询条件命中。

所以要想解决幻读不仅不允许记录的空隙被插入记录Q { ) ^ D外，还要防止两遍记录被修改，因为如果前后U E l P (两条记录被修改了那区间就会变大，就会有幻$ P h { Y % ?读出现。

我们看个例子：

CREATEo 6 N m Z F TABLE `peoples` ( `id` int(11) unsigned NOT  AUTz S & a , Y % fO_INCREMENT,  `age` int(11) DEFAUJ b f @ P A k RLT , PRIMARY KEY (`id`),  KEY `idx_peoples_M 8 S v b r * ] Yage` (`age`)) ENGINE=Inn. s + ( PoDB AUTO_I- ? T # 9 , HNp 9 0CREMENT=1 DEFAULT CHARSET=utf8mb4

+----+-----+|H 3 G o - / ~ L id | age |P r -+----+-----+| 1 | 20 || 2 | 30 || 3 | 35 || 4 | 40 |+----+-----+

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

show vari= w | x Zables like \'%innodb_lock_wait%\'set innodb_lock_wait_timeout=600

开启两个会: L ( b { Q W话：

session A id=8:beginselect count(z X v a j W y1) from peoples where age>30 for update;

sD . 7 (essi( m N c H  G ,on B id=5:begininsert into peoplesj G 3 . O y l =(aU o J K S ( v I uge) values(31)

show processlist 找到连接的W 4 / qid。

******************************************************Id | 3User | rootHost | 1L L / C c ?72.17.0.1:60542db | testCommand | QueryTime | 0State | startingInfo | show processlist*********************************** Z ^ R G g k 4********************Id | 5User | r{ F e t 1 mootHost | 172.17.0.1:60546db | testCommand | QueryTime | 394State | updateInfo | insert into peoples(age) vT : Q ; , ` LaluQ r k 8 L d Yes(31)*************= / k } E*****************************************Id | 8User | rootHost | 172.17.0.1:605w 5 r +52db |Q E H * # + F 3 h testCommand | SleepTime | 396State |Info | <>7 J O;

• 事务

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

***************************************************! b L P ? A ; G 5**% + ! ( 1 % ^ T*trx_id | 457240trx_state | LOCK WAITtrx_started | 2020-01-27 06:08:12trx_requested_lock_id | 457240:131:4:4trx_waitf  D_started | 2020-01-27 06:09:25trx_weight | 6trx_mysql_thread_id | 5trx_query | insert into peoples(as  X E &dified | 1trx_concurrency_tickets | 0tU ! V M f 8 jrxJ p J ! l ! Y H y_isolation_level | REPEATABLE READtrx_unique_checks | 1trx_foreign_key_checks | 1tz ( Prx_last_foreign_key_error | <>trx_adaptive_hash_latched | 0trx_adaptive_hash_timeout | 0trx_is_read_only | 0trx_autocommit_non_locking | 0************************************X y + n**********R e U + W 4 c @********trx_id; v T , ~ Q D $  | 457239trx_state | RUNNINGtrx_started | 2020-01-27 06a k p 1 8:07:59trx_requested_lock_id | <>trx_wait_started | <>trx_weight | 3trx_mysql_thread_id | 8trx_quero H e @ _ t !y | <>trx_operation_state | <>trx_tables_in_use | 0trx_tables_lockedx 6 { Z Q L ` | 1trx_lock_structs | 3trx_lock_memory_bytes | 1136trx_rows_locked | 5trx_rows_mR 2 6 w dodified | 0trx_concurrency_ticke{ K F * I Y `ts | 0trx_isolation_levelW s v 6 | REPEATABL + } H 0 ~ 6 z FLE READtrx_uniquf g 0e_checks | 1trx_foreign_key_checks | 1trx_last_foreign, s 6 o_key_error | <V g = (>trx_adaptive_hash_i a T ) , = m {lal ] R w 6 ztched | 0trx_adaptive_hash_timeout | 0trx_is_read_only | 0trx_autocomm* ! $ ; I Y m O `it_non_locking | 0

45: R h ) h b 5 [ X7240 事务状态是 LOCK WAIT 在h E 5 9 G f 3 ,等待3 l _ 0 5 ) s锁，457239事务状态是 RUNNING执行中，正在等待事务提交。

• 锁

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

******************************************************lock_it M f Kd | 457240:131:4:4lock_trx_id | 457240lock_m* Z l 2 + j W  ode |M m c - { X,GAPlock_type | RECORDlock_table | `test`.`peoples`lock_index | idx_peoples_agelocT ( 0 e 4 { a Nk_space | 131lock_page | 4B ] M olock_~ { $ t i ] L Krec | 4lock_data | 35, 7**********[ e . @ X Q********************************************lock_id | 457239:131:4:4lock_F 2 2trx_id | 457239lock_mode | Xlock_type | RECORDlw : iock_table | `test`.`peoples`lock_index | idx_peoples_agelock_space | 131lock_page | 4lock_rec | 4lock_data | 35, 7I k & &

innodb_lr 7 k f Gocks 表包含了已经获取到的锁信息和请求锁的信息。lock_index字段表示锁走的索引，record锁都是基于索引完成。

根据上面事务457240状态是获取锁，lock_data | 35, 7，表示请求的? 7 / J ! $ 8 T r数据。而事务457239占用了当前X锁。

• 锁等待

select * from information_schema.innodb_5 M H n J & P 7lock_waits 查看锁等待信息 F L k。

******************************************************requesting_trx_id | 4572X ~ ~ =40requested_lock_id | 457240:131:4:4blocking_trx_id | 457239blocking_lock_id | 457239:131:4:4

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

• innodb 监视器

show engine innodb status

LIST OF TRANSACTION} 2 9 h GS FOR EACH SESSION:---TRANSACTION 422032240994144, not started0 lock struct(s), heap size 1136, 0 row lock(s)---TRANSACTION 4572! 9 Q I n40, ACTIVE 394 sec insertingmysql tables in use 1, locked 1LOCK WAI0 M lT 5 lock struct(s), heap sizi L 4 n ae 1136, 7 row lock(s), undo log entrz @ 2 j qies 1MySQL thread id 5, OS thread handle 140556966967040, query id 105 172.17.0.1 root updateinsert into peoples(age) values(31)------- TRX HAS BEEN Wj } } - bAITING 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 `tJ z + 4 $ A 4 Iest: n z b ] C 7 ]`.`peoples` trx id 457240 lock_mode X locksA W ! = d w gap bei . - Jfore rec insert intention waitingRecord lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000023;7 i 7 _ x asc #;;  1: len 4; hex 00000007; asc ;;
---------------------TRANSACTION 457239, ACTIVE 407 sec3 lock struct(s), heap size 1136, 5 row lock(s)MySQL thread id 8, OS thread handle 1405R V o U J } 656966696704, query id 104 172.17.0.1 root

MySQL thk w [ ( tre) c 9 | Kad id 5 正在准备上插入意向锁，插入意向锁本质上是加间隙X z .锁，是为了保证v q 1 ! M最大并发插入，不相关的行插入不受到互斥。thread id 5 需要保证在b K U插入前加上间隙锁，主要4 9 s ^ u G是防止并发插入带来的一致性问题。

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

End.

作者：王清培

来源：博客园

长按下方海报领取【MySQL面试题】

零基础入职数据分析就业班

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

课程专享：

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

课程结束后能熟练掌握SQL、Python、Excel、PPT等数据分析工具

金3银4招聘季Z 7 % p，爱数据= : s ? b .带你成功入职数据分析！