MySQL8锁的问题
关键字
mysql 8、lock
问题描述
项目上反馈，一个简单的提交操作需要 40 秒。
抓取 SQL 发现
update gl_credit_bill set `verifystate`=2 where id='2761279790403840'
执行耗时近40秒
解决问题思路
手动执行 SQL，发现非常快，基本排除数据库本身问题，update 耗时长，一般可能为锁的问题。
MySQL 8上查询锁等待select * from performance_schema.data_lock_waits说明：
ENGINE：请求的锁的引擎
REQUESTING_ENGINE_LOCK_ID：请求的锁在存储引擎中的锁ID
REQUESTING_ENGINE_TRANSACTION_ID：请求锁的事务对应的事务ID
REQUESTING_THREAD_ID：请求锁的线程ID
REQUESTING_EVENT_ID：请求锁的EVENT ID
REQUESTING_OBJECT_INSTANCE_BEGIN：请求的锁的内存地址
BLOCKING_ENGINE_LOCK_ID：阻塞的锁的ID，对应data_locks表的ENGINE_LOCK_ID列
BLOCKING_ENGINE_TRANSACTION_ID：锁阻塞的事务ID
BLOCKING_THREAD_ID：锁阻塞的线程ID
BLOCKING_EVENT_ID：锁阻塞的EVENT ID
BLOCKING_OBJECT_INSTANCE_BEGIN：阻塞的锁内存地址INNODB 140673663789728:8133:4:54:140673323028856 99747470 3685 389 1.40673E+14 140673663817200:8133:4:54:140673323233088 99747454 3886 52 1.40673E+14可知3685的线程在等待3886的线程。查看线程表
select * from performance_schema.threads3886 thread/sql/one_connection FOREGROUND 3544 oper_dc 172.16.10.86 greescm_db Sleep 4 YES YES SSL/TLS 123775 USR_default
3685 thread/sql/one_connection FOREGROUND 3371 oper_dc 172.16.10.86 greescm_db Query 4 updating update gl_credit_bill set `verifystate`=2 where id='2761279790403840' YES YES SSL/TLS 38574 USR_default可知3685的线程在执行update，处于updating状态，3886的线程sleep状态。我们查询当前锁
select * from performance_schema.data_locks说明：
ENGINE：持有或请求锁定的存储引擎
ENGINE_LOCK_ID：存储引擎持有或请求的锁的ID，锁ID格式是内部的，随时可能更改。
ENGINE_TRANSACTION_ID：请求锁定的事务存储引擎内部ID，可以将其视为锁的所有者
THREAD_ID：对应事务的线程ID，如果需要获取更详细的信息，需要关联threads表的THREAD_ID
EVENT_ID：指明造成锁的EVENT_ID，THREAD_ID+EVENT_ID对应parent EVENT，可以在以下几张表内获得信息events_waits_xx表查看等待事件events_stages_xxx查看到了哪个阶段events_statements_xx表查看对应的SQL语句events_transactions_current对应查看事务信息
OBJECT_SCHEMA：对应锁表的schema名称
OBJECT_NAME：对应锁的表名
PARTITION_NAME：对应锁的分区名
SUBPARTITION_NAME：对应锁的子分区名
INDEX_NAME：锁对应的索引名称，InnoDB表不会为NULL
OBJECT_INSTANCE_BEGIN：锁对应的内存地址
LOCK_TYPE：对应的锁类型，对InnoDB而言，可为表锁或者行锁
LOCK_MODE：锁模式，对应值可能为S[,GAP], X[, GAP], IS[,GAP], IX[,GAP], AUTO_INC和UNKNOWN
LOCK_STATUS：锁状态，可能为GRANTED或者WAITING
LOCK_DATA：锁对应的数据，例如如果锁定的是主键，那么该列对应的就是加锁的主键值INNODB 140673663789728:7549:4:47:140673323028512 99747470 3685 386 greescm_db gl_customer_transaction_ledger PRIMARY 1.40673E+14 RECORD X,REC_NOT_GAP GRANTED '6f48a9c1cb7f11eca9e5fefcfe472a26'
INNODB 140673663789728:8133:4:54:140673323028856 99747470 3685 389 greescm_db gl_credit_bill PRIMARY 1.40673E+14 RECORD X,REC_NOT_GAP WAITING '2761279790403840'
INNODB 140673663817200:10053:140673323236192 99747454 3886 52 greescm_db gl_credit_bill 1.40673E+14 TABLE IX GRANTED
INNODB 140673663817200:8133:4:54:140673323233088 99747454 3886 52 greescm_db gl_credit_bill PRIMARY 1.40673E+14 RECORD X,REC_NOT_GAP GRANTED '2761279790403840'3886 持有GRANTED表gl_credit_bill 主键为2761279790403840 记录排它锁RECORD X,REC_NOT_GAP
3685 等待 WAITING 表gl_credit_bill 主键为2761279790403840 记录排它锁RECORD X,REC_NOT_GAP要从代码层面分析业务逻辑。
问题总结
update 执行时间长，大概率是锁的问题。