MySQL InnoDB锁机制及锁超时案例分析

InnoDB锁机制

1. 在不通过索引条件查询（更新）的时候，InnoDB试用的是表锁。

例如： update xxx where a = 1 ，若a无索引，那么锁定的并非a=1这一行，而是整个表。

2. 由于MySQL的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然我们可能认为我们是锁的不同记录，但是对于MySQL来说，锁的实际上是一组索引。

3. 当表有多个索引的时候，不同的事务可以用不同的索引来锁定不同的行。另外，不论是使用主键索引，唯一索引或者普通索引，InnoDB都会使用行锁来对数据加锁。

4. 即便在条件中使用了索引字段，但具体是否使用索引，使用哪个索引，是由MySQL执行计划来判断的，如果MySQL认为全表扫效率较高，比如对于一些很小的表，那么它就不会使用索引，这种情况下InnoDB就将使用表锁，而不是行锁。所以在分析锁冲突时，我们可以使用explain来查看MySQL的执行计划，以及是否使用了索引，使用了哪些索引。

tips：尝试获取锁默认50秒超时，会提示 Lock wait timeout exceeded; try restarting transaction

案例

最近在负责排查和优化一些慢SQL，有同事报说，有接口压测时老是会出现超时的情况，几乎必现。但以前压测时没有出现过，也没有过改动任何代码。

背景（非实际线上场景）：

1. 表结构大致为

CREATE TABLE `user_transation_stores` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL COMMENT '用户id',
  `storeCode` varchar(32) COLLATE utf8_bin DEFAULT NULL COMMENT '门店code',
  `consumeTime` char(14) COLLATE utf8_bin DEFAULT NULL COMMENT '消费日期',
  `times` int(11) DEFAULT NULL COMMENT '消费次数',
  PRIMARY KEY (`id`),
  KEY `idx_userId` (`userId`) USING BTREE, 
  KEY `idx_storeCode` (`storeCode`) USING BTREE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin

主要作用是：记录用户在门店某天消费的次数。

2. 更新语句为： update user_transation_stores set times = times + 1 where userId = xxx and storeCode = xxx and consumeTime=xxx

3. 采用MyCat分库分表，有dbs1-12个库

4. 存在更新其他表的操作，所以由于MyCAT的分布式事务保证，也就存在XA事务

询问了下，压测数据为不同的用户ID散列，但门店Code比较集中。

也就是在压测过程中，可能多个用户ID，同一个门店Code的情况。

首先，本次我们不去讨论这样去记录用户每日消费次数是否合理，而是要摸清为何会出现死锁的情况。

找DBA捞了下锁日志，自己也可以用 SQL:

show engine innodb status;\G

锁日志:

mysql tables in use 3, locked 3
225 lock struct(s), heap size 41168, 3 row lock(s)
MySQL thread id 212861, OS thread handle 140366957156096, query id 78804036 10.210.0.4 uat_member updating
update user_transation_stores set times=7 where userId=68 and consumeTime='20211127' and storeCode='ccab'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 5210 page no 85408 n bits 344 index PRIMARY of table `dbs9`.`user_transation_stores` trx id 1698745253 lock_mode X locks rec but not gap
Record lock, heap no 253 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
 0: len 8; hex 80000000015d407f; asc      ]@ ;;
 1: len 6; hex 000065386b32; asc   e8k2;;
 2: len 7; hex 35000f800c01ed; asc 5      ;;
 3: len 4; hex 80000044; asc    D;;
 4: len 4; hex 30303837; asc 0087;;
 5: len 14; hex 3230323131313237313433373533; asc 20211127143753;;
 6: len 4; hex 80000006; asc     ;;
 
 *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 5210 page no 155179 n bits 904 index index_storeCode of table `dbs9`.`user_transation_stores` trx id 1698745253 lock_mode X locks rec but not gap waiting
Record lock, heap no 820 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 30303837; asc 0087;;
 1: len 8; hex 80000000015d407f; asc      ]@ ;;

其中我们可以注意到一行：

WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 5210 page no 155179 n bits 904 index index_storeCode of table `dbs9`.`user_transation_stores`

我们看到，这里锁的时候，是以index_storeCode索引执行加锁的。这里我们会有疑问，按正常逻辑来讲，不应该走userId（用户ID）索引么。这里其实是MySQL执行计划认为走storeCode的索引，快于走userId索引，也就是storeCode的筛选度高于userId，在dbs9这个库上。

上面我们提到，我们数据是userId散列，但storeCode相对集中，那么如果走了storeCode索引作为锁，那么就会导致大量的锁竞争。

同时，我们用的是MyCAT，所以还存在一个分布式事务，那就有大量的XA事务卡在这个表的storeCode锁上。

事务堆积得越多，就越容易会导致锁超时，对于此次压测来说，由于storeCode相对集中，那么几乎的是必现的。

优化方案

1. 让测试调整数据，但治标不治本，对于系统的健壮性没有任何帮助，只是掩耳盗铃。

2. 调整索引，经过看源码我们发现，实际上所有SQL都是这个条件。那么，我们直接删除index_storeCode索引，改为 (userId,storeCode,consumeTime) 联合索引。这样锁粒度最小，也就不容易导致锁冲突。

3. 调整记录方案，问了下应用场景，只需做当天的拦截，那么可以考虑用redis。

4. 调整为主键更新。

结论

1.由于系统代码已经很久没有迭代，要求尽量不要改动代码，我们最终采取了方案2，去除了 index_userId, index_storeCode索引，改为(userId,storeCode,consumeTime) 联合索引。