MySQL

1、问题 线上出现很多添加 key 锁超时的 mysql 日志。 https://apm.example.com/app/apm/services/<service_name>/transactions/view?rangeFrom=now-24h%2Fh&rangeTo=now&environment=&transactionName=%23 2、排查 1）查看 APM，发现了一个耗时的请求/sync，做语料同步的。后面插入语料 key 都显Lock wait timeout exceeded。 2）查看同步请求的具体链路，发现有多次批量 INSERT 操作，而且每次批量插入消耗的时间会逐步增加。最终也是执行成功的，花了24 分钟。后面其他事务的 Insert 操作都给阻塞住了。 3）查看锁日志，可以发现并不是死锁，而是普通的锁等待，最开始的批量操作（/sync）占用了大量的锁 4253982 row locks。后面的insert（添加 key）申请插入意向锁都被阻塞，等待一段时间之后上报超时错误给应用。 LOCK WAIT 8 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id <tid_1>, OS thread handle <handle_1>, query id <qid_1> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, description) VALUES (1001, 2001, 'xx-XX', 'default', 'key_1', '<text>', ''); ------- TRX HAS BEEN WAITING 22 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 83690 n bits 136 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_waiting> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; ------------------ ---TRANSACTION <trx_long>, ACTIVE 1166 sec starting index read mysql tables in use 1, locked 1 102372 lock struct(s), heap size 10379472, 4253982 row lock(s), undo log entries 943 MySQL thread id <tid_long>, OS thread handle <handle_long>, query id <qid_long> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (2001, 3001, 'xx-XX', 'default', 'k1', 'v1', 2, 1), (2002, 3001, 'xx-XX', 'default', 'k2', 'v2', 2, 1); 4）查看代码，发现了调用 importJsonCorpus 进行多次批量操作，这跟 APM 看到的现象一致。这个方法上有事务注解，importJsonCorpus 方法的调用都在一个事务里，所以一次批量操作结束之后并不会释放锁。 ...

现象 补全翻译接口（填充空白的语料）接口执行失败，MySQL 检测到死锁快速抛异常，接口执行耗时 426ms。 无论是从接口、具体表现、根因，都与上个问题有明显区别。 分析 1、查看死锁日志，可以看到有两个事务，是对同一个项目的不同语言的批量插入更新操作（INSERT ON DUPLICATE KEY UPDATE）。 ------------------------ LATEST DETECTED DEADLOCK ------------------------ <timestamp> <os_thread_hex> *** (1) TRANSACTION: TRANSACTION <trx_1>, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 6 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id <tid_1>, OS thread handle <handle_1>, query id <qid_1> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (<item_id_1>, <corpus_id>, 'my-MM', 'default', '下一步', '', 0, 1) , (<item_id_2>, <corpus_id>, 'my-MM', 'default', '分组竞争', '', 0, 1) , (<item_id_3>, <corpus_id>, 'my-MM', 'default', '判断对错', '', 0, 1) , (<item_id_4>, <corpus_id>, 'my-MM', 'default', '完成', '', 0, 1) , (<item_id_5>, <corpus_id>, 'my-MM', 'default', '球球拼词', '', 0, 1) , (<item_id_6>, <corpus_id>, 'my-MM', 'default', '知识排序', '', 0, 1) , (<item_id_7>, <corpus_id>, 'my-MM', 'default', '知识配对', '', 0, 1) , (<item_id_8>, <corpus_id>, 'my-MM', 'default', '记忆卡片', '', 0, 1) , (<item_id_9>, <corpus_id>, 'my-MM', 'default', '试玩', '', 0, 1) , (<item_id_10>, <corpus_id>, 'my-MM', 'default', '超级分类', '', 0, 1) , (<item_id_11>, <corpus_id>, 'my-MM', 'default', '趣味分类', '', 0, 1) , (<item_id_12>, <corpus_id>, 'my-MM', 'default', '趣味选择', '', 0, 1) , (<item_id_13>, <corpus_id>, 'my-MM', 'default', '返回', '', 0, 1) , (<item_id_14>, <corpus_id>, 'my-MM', 'default', '选词填空', '', 0, 1) ON DUPLICATE KEY UPDATE status = IF(value = '', VALUES(status), status), value = IF(value = '', VALUES(value), value) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_1> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) TRANSACTION: TRANSACTION <trx_2>, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 7 lock struct(s), heap size 1136, 4 row lock(s) MySQL thread id <tid_2>, OS thread handle <handle_2>, query id <qid_2> <private-ip> <db_name> update INSERT INTO multilingual_item (item_id, corpus_id, lang, customize,`key`,value, status, type) VALUES (<item_id_a1>, <corpus_id>, 'ar-EG', 'default', '下一步', '', 0, 1) , (<item_id_a2>, <corpus_id>, 'ar-EG', 'default', '分组竞争', '', 0, 1) , (<item_id_a3>, <corpus_id>, 'ar-EG', 'default', '判断对错', '', 0, 1) , (<item_id_a4>, <corpus_id>, 'ar-EG', 'default', '完成', '', 0, 1) , (<item_id_a5>, <corpus_id>, 'ar-EG', 'default', '球球拼词', '', 0, 1) , (<item_id_a6>, <corpus_id>, 'ar-EG', 'default', '知识排序', '', 0, 1) , (<item_id_a7>, <corpus_id>, 'ar-EG', 'default', '知识配对', '', 0, 1) , (<item_id_a8>, <corpus_id>, 'ar-EG', 'default', '记忆卡片', '', 0, 1) , (<item_id_a9>, <corpus_id>, 'ar-EG', 'default', '试玩', '', 0, 1) , (<item_id_a10>, <corpus_id>, 'ar-EG', 'default', '超级分类', '', 0, 1) , (<item_id_a11>, <corpus_id>, 'ar-EG', 'default', '趣味分类', '', 0, 1) , (<item_id_a12>, <corpus_id>, 'ar-EG', 'default', '趣味选择', '', 0, 1) , (<item_id_a13>, <corpus_id>, 'ar-EG', 'default', '返回', '', 0, 1) , (<item_id_a14>, <corpus_id>, 'ar-EG', 'default', '选词填空', '', 0, 1) ON DUPLICATE KEY UPDATE status = IF(value = '', VALUES(status), status), value = IF(value = '', VALUES(value), value) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_2> lock_mode X Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 345 page no 93049 n bits 144 index PRIMARY of table `<db_name>`.`multilingual_item` trx id <trx_2> lock_mode X insert intention waiting Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; *** WE ROLL BACK TRANSACTION (1) 根据死锁日志画出时序图，从下面这个时序图可以看到死锁的形成过程。 ...

从数据结构理解 MySQL 联合索引 前言 索引的本质是一种通过特定数据结构来优化数据检索速度的机制。是我们开发岗接触 MySQL 最重要的概念之一，与我们的应用开发息息相关。 结合应用思考 1）在语料平台中的 Item 表中，假设我们的目标是快速搜索 key，只考虑完全匹配的情况下，如何建立索引？ 索引的字段（业务驱动、是否要覆盖） 索引顺序（最左前缀匹配、高选择性或离散性） 2）考虑大批量写的情况下，怎样的索引可以减少性能损耗？ 3）高选择性的字段是不是一定排最前？ 4）如何在离散性与随机性之间抉择？ 1、MySQL 索引的数据结构种类 索引类型 存储引擎 底层数据结构 特点与用途 普通索引 / 主键 / 唯一索引 InnoDB B+Tree 默认且绝对主流。聚簇索引叶子节点存数据，二级索引叶子节点存主键。 普通索引 / 主键 / 唯一索引 MyISAM B+Tree 非聚簇索引，叶子节点存数据行的物理地址。 哈希索引 Memory Hash Table 默认类型，等值查询极快，用于临时表和缓存。 自适应哈希索引 InnoDB Hash Table 内部自动功能，自动缓存热点数据，加速等值查询。 全文索引 (FULLTEXT) InnoDB, MyISAM 倒排索引 解决 LIKE ‘%…%’ 性能问题，用于全文检索。 空间索引 (SPATIAL) InnoDB, MyISAM R-Tree 专用于地理空间数据类型查询。 其他索引数据结构：Skip List跳表、红黑树、BitMap 位图、Trie 前缀树 2、B+树 B+Tree 是多路平衡搜索树，一种专为磁盘等外部存储设备设计的、多路的、平衡的搜索树。它的主要目标是减少磁盘 I/O 次数，从而高效地支持大规模数据的增删改查，尤其是范围查询。 ...

背景 开启课堂或者刷新课堂界面时会调用接口发送邀请链接公告，后台要限制一个课堂只能发送一次。 现象 同一个课堂出现了多条公告记录 解析 直接看代码，使用了分布式锁进行加锁校验，锁等待时间为0，也就是获取不到锁立刻返回。锁超时时间为课堂的超时时间，为12小时。 1、猜想一：发送公告有重试逻辑，有可能是重试逻辑有问题导致的。 在Google的接口调用中，使用动态代理进行接口的统一处理，如果调用Google接口返回了401状态码，就需要刷新AccessToken然后重新调用，如果刷新AccessToken失败就需要用户重新授权。 初步看代码逻辑没有问题，我们可以查看应用日志，如果是重试的问题导致的，那么这两次发送公告都应该是同一个请求，也就会是同一个TraceId，可以看到这两次发送成功是属于两个不同的请求，TraceId也是不一样的，那么可以排除这个猜想。 2、猜想二：锁没有生效，有并发问题。 我们使用 Jmeter 对这个接口进行压力测试，开启100个线程，结果是只有一个线程返回成功，其他线程都返回10304业务状态码，表示已经发送过公告了，而且始终没办法复现，返回成功状态码的请求始终只有一个。 3、猜想三：第一次获取锁成功了，后面删除了锁，所以第二次获取锁又成功了 查看Redis数据库也能看到锁对应的数据，TTL剩余9379秒，通过时间计算，可以得出这个Key的设置时间为9:49，跟第一次请求的时间是一致的，所以第二次获取锁的时候，这个锁是存在的。 4、猜想四：从以上的现象可以看出，这不是并发问题导致的，而且是属于不同的请求，两个请求基本没有什么关联，间隔也有8分钟左右。第二次获取锁的时候，锁的key也确实存在，确实是重复获取到了锁，那么基本确定只有一种可能，这是可重入锁，如果两个请求是同一个线程，那么就能重复获取到锁。查看应用日志，可以发现这两个请求确实属于同一个线程。 验证猜想：将Tomcat的工作线程数设置为1，这样每次请求都会是同一个请求，结果是每次请求都发送成功。 那么压力测试为什么复现不了这个问题呢，原因是压力测试时，100个请求基本同一时间发出，而Tomcat默认的最大线程池数量为200个线程，所以这100个请求都属于不同的线程。 解决方案 方案一：自实现不可重入锁，使用 setnx 指令简单实现。 方案二：加一层校验，在加锁之前判断这个 Key 是否存在。 这两种方案都可以，选择一种即可。 附 分布式锁加锁流程图