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 执行时间长，大概率是锁的问题。