Innodb引擎频繁出现Waiting for table level lock 怎么排查

表引擎Innode 按理不应该出现表级锁

执行 SHOW FULL PROCESSLIST 命令可以看到当前阻塞的大量进程 都是Waiting for table level lock,但是不清楚出现的原因

个人怀疑是这条SQL引起的

SELECT apitype.*, (SELECT COUNT(*) FROM pshop WHERE expires_at > NOW() AND apitype=apitype.Id) AS shop_total FROM apitype

统计一张表所有数据在另外一张表对应条件的总数,只是怀疑不敢确定

网上的相关内容都搜了一下,确定没有触发器什么的影响 表引擎都是innodb,卡住的进程卡住的sql都是update为主

还望有人指定迷津,给一个排查方向

CREATE TABLE `pshop` (
  `Id` int(100) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `groupid` int(11) NOT NULL DEFAULT '0' COMMENT '分组ID',
  `mmsUsername` varchar(255) NOT NULL DEFAULT '' COMMENT '店铺用户名',
  `mallId` int(11) NOT NULL DEFAULT '0' COMMENT '店铺ID',
  `mallName` varchar(255) NOT NULL DEFAULT '' COMMENT '店铺名称',
  `merchantType` varchar(255) DEFAULT '0' COMMENT '商户类型 1:个人 2:企业 3:旗舰店 4:专卖店 5:专营店 6:普通店',
  `cookie` text COMMENT 'cookie',
  `jcookie` text COMMENT 'jcookie',
  `ua` varchar(255) DEFAULT NULL COMMENT 'ua信息',
  `access_token` varchar(255) DEFAULT '' COMMENT 'token值',
  `refresh_token` varchar(255) DEFAULT '' COMMENT 'refresh_token',
  `shop_token` varchar(255) DEFAULT NULL COMMENT '店铺工作台token',
  `islock` int(11) NOT NULL DEFAULT '0' COMMENT '店铺是否开启',
  `remarks` varchar(255) DEFAULT NULL COMMENT '店铺备注',
  `expires_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '授权到期日期',
  `ltime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '在线时间',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '到期时间',
  `upnum` int(11) DEFAULT '0' COMMENT '当日上传数量',
  `pid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '直属代理ID',
  `pid2` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '顶级代理ID',
  `apitype` int(11) NOT NULL DEFAULT '1' COMMENT 'api类型,apitype的id',
  `dd_usn` int(10) NOT NULL DEFAULT '0',
  `sd_usn` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `mmsUsername` (`mmsUsername`) USING BTREE,
  UNIQUE KEY `mallId` (`mallId`) USING BTREE,
  KEY `apitype` (`apitype`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=126272 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='店铺列表'
[
{ 
"Id":4903746,
"User":"pddzs",
"Host":"localhost:35856",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903747,
"User":"pddzs",
"Host":"localhost:35868",
"db":"pddzs",
"Command":"Execute",
"Time":303,
"State":"Waiting for table level lock",
"Info":"update `yzshop` set `upnum` = 876300 where `shopid` = 105475339 and `upnum` < 876300"
},
{ 
"Id":4903748,
"User":"pddzs",
"Host":"localhost:35880",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"insert into `loginlog` (`user`, `sip`, `msg`, `pname`, `ver`) values ('13666556684', '1.181.191.173', '登陆', '火盟助手', '1.1.6.7')"
},
{ 
"Id":4903749,
"User":"pddzs",
"Host":"localhost:35882",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"update `puser` set `ltime` = '2022-07-25 18:01:03' where `Id` = 3025"
},
{ 
"Id":4903750,
"User":"pddzs",
"Host":"localhost:35900",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903751,
"User":"pddzs",
"Host":"localhost:35914",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903752,
"User":"pddzs",
"Host":"localhost:35916",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903760,
"User":"pddzs",
"Host":"localhost:36064",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903761,
"User":"pddzs",
"Host":"localhost:36078",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"update `puser` set `ltime` = '2022-07-25 18:01:04' where `Id` = 4476"
},
{ 
"Id":4903762,
"User":"pddzs",
"Host":"localhost:36098",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903764,
"User":"pddzs",
"Host":"localhost:36104",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903766,
"User":"pddzs",
"Host":"localhost:36118",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903767,
"User":"pddzs",
"Host":"localhost:36130",
"db":"pddzs",
"Command":"Execute",
"Time":303,
"State":"Waiting for table level lock",
"Info":"update `puser` set `ltime` = '2022-07-25 18:01:02' where `Id` = 8325"
},
{ 
"Id":4903769,
"User":"pddzs",
"Host":"localhost:36170",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903770,
"User":"pddzs",
"Host":"localhost:36176",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903772,
"User":"pddzs",
"Host":"localhost:36182",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903774,
"User":"pddzs",
"Host":"localhost:36202",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903777,
"User":"pddzs",
"Host":"localhost:36278",
"db":"pddzs",
"Command":"Execute",
"Time":303,
"State":"Waiting for table level lock",
"Info":"update `puser` set `ltime` = '2022-07-25 18:01:02' where `Id` = 5917"
},
{ 
"Id":4903778,
"User":"pddzs",
"Host":"localhost:36298",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4903781,
"User":"pddzs",
"Host":"localhost:36322",
"db":"pddzs",
"Command":"Execute",
"Time":301,
"State":"Waiting for table level lock",
"Info":"select * from `yzshop` where `enabled` = 1 and `upnum` < 1950000 and type=3 and apitype=0 order by `upnum` asc limit 100"
},
{ 
"Id":4940937,
"User":"pddzs",
"Host":"localhost:45468",
"db":"pddzs",
"Command":"Execute",
"Time":302,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6104, '116.117.60.30', 'dl/users/data', 'POST', '{\"page\":\"1\",\"limit\":\"10\",\"total\":\"0\",\"user_type\":\"1\",\"user\":null,\"pid\":null,\"pname\":null,\"ifinit\":null,\"ifdel\":null,\"ifkey\":null,\"sort\":null,\"status\":null}', '2022-07-25 18:01:03')"
},
{ 
"Id":4940939,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":301,
"State":"Waiting for table level lock",
"Info":"delete from paishou where id=51242"
},
{ 
"Id":4940943,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":299,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=win3d1VEQU1B**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**jQsIm0iOj' where mmsUsername='pdd61079296496'"
},
{ 
"Id":4940944,
"User":"pddzs",
"Host":"localhost:46374",
"db":"pddzs",
"Command":"Execute",
"Time":299,
"State":"Waiting for table level lock",
"Info":"update `proxy` set `user_count` = 56, `shop_count` = '4050', `last_login_time` = '2022-07-25 18:01:06', `proxy`.`updated_at` = '2022-07-25 18:01:06' where `id` = 6073"
},
{ 
"Id":4940946,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":299,
"State":"Waiting for table level lock",
"Info":"update puser set ltime='2022-07-25 18:01:06',ver='1.2.1.2',pname='浩龙销量助手' where id=766"
},
{ 
"Id":4940948,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":299,
"State":"Waiting for table level lock",
"Info":"update pshop set access_token='**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**',refresh_token='**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**',expires_at='2022-08-14 13:47:25' where id=122038"
},
{ 
"Id":4940954,
"User":"pddzs",
"Host":"localhost:47124",
"db":"pddzs",
"Command":"Execute",
"Time":297,
"State":"Waiting for table level lock",
"Info":"update `proxy` set `user_count` = 56, `shop_count` = '4050', `last_login_time` = '2022-07-25 18:01:08', `proxy`.`updated_at` = '2022-07-25 18:01:08' where `id` = 6073"
},
{ 
"Id":4940959,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":296,
"State":"Waiting for table level lock",
"Info":"insert into paishou (userid,paiuser,ptype) values (1574,'1594******','pdd')"
},
{ 
"Id":4940962,
"User":"pddzs",
"Host":"localhost:47552",
"db":"pddzs",
"Command":"Execute",
"Time":296,
"State":"Waiting for table level lock",
"Info":"update `proxy` set `user_count` = 56, `shop_count` = '4050', `last_login_time` = '2022-07-25 18:01:09', `proxy`.`updated_at` = '2022-07-25 18:01:09' where `id` = 6073"
},
{ 
"Id":4940965,
"User":"pddzs",
"Host":"localhost:47824",
"db":"pddzs",
"Command":"Execute",
"Time":295,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6104, '116.117.60.30', 'dl/users/data', 'POST', '{\"page\":\"1\",\"limit\":\"10\",\"total\":\"0\",\"user_type\":\"1\",\"user\":\"04760069\",\"pid\":null,\"pname\":null,\"ifinit\":null,\"ifdel\":null,\"ifkey\":null,\"sort\":null,\"status\":null}', '2022-07-25 18:01:10')"
},
{ 
"Id":4940968,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":294,
"State":"Waiting for table level lock",
"Info":"insert into paishou (userid,paiuser,ptype) values (8216,'17007355639','pdd')"
},
{ 
"Id":4940971,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":294,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=windo**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**ke Gecko) Chrome/31.0.1650.16 Safari/537.36PddWorkbench-Online PddBrowser pdd_windows_version/1.0.0',shop_token='eyJ0IjoiQUJWZkd2QWYyQVNqek00LzdoU2FsR3Y5UVRmYXRSWHBkZHJiTmlCbWluYUxGWm5FNlJTeGFsem02SHRCYWJOciIsInYiOjEsInMiOjQsIm0iOjc4OTQ4MDM1OSwidSI6OTUwNjQxNzZ9' where mmsUsername='pdd78948035998'"
},
{ 
"Id":4940975,
"User":"pddzs",
"Host":"localhost:48312",
"db":"pddzs",
"Command":"Execute",
"Time":293,
"State":"Waiting for table level lock",
"Info":"update `proxy` set `user_count` = 56, `shop_count` = '4050', `last_login_time` = '2022-07-25 18:01:12', `proxy`.`updated_at` = '2022-07-25 18:01:12' where `id` = 6073"
},
{ 
"Id":4940976,
"User":"pddzs",
"Host":"localhost:48392",
"db":"pddzs",
"Command":"Execute",
"Time":293,
"State":"Waiting for table level lock",
"Info":"update `proxy` set `user_count` = 56, `shop_count` = '4050', `last_login_time` = '2022-07-25 18:01:12', `proxy`.`updated_at` = '2022-07-25 18:01:12' where `id` = 6073"
},
{ 
"Id":4940983,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":291,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=windows_1-CV7YaVpPRjgQPV**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**ri/537.36PddWorkbenchngyb0ljbEtmOExTcDJqSV5fQ' where mmsUsername='pdd74800893377'"
},
{ 
"Id":4940984,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":290,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**/31.0.1650.16 Safari/537.36PddWorkbench-Online PddBrowser pdd_windows_version/1.0.0',shop_token='eyJ0IjoiemFJRkNXcFo0aE8zVkdQU0Q1SWIxOGdGYno2S2ZSczlqYzlJdzVWUkVHdVhIYXZkOUVCRllQbXp4R2s1Q2t4ZyIsInYiOjEsInMiOjQsIm0iOjM2NDM2MTE4MywidSI6MTEyMDA0NTY4fQ' where mmsUsername='pdd36436118370'"
},
{ 
"Id":4940985,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":290,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='webp=true;api_uid=CiT4XGLeadScdABfPshNAg==;_nano_fp=XpEyl0mYXqPbXpXjXT_v15p3GWD5bgzTCnJsO2Dl;_bee=qTySnWAuRB4EyF4mlAkFGW57FMtjDBmF;_f77=693548ae-0b8b-4bb1-8f6e-0b35db37c16b;_a42=6e8644bc-c63a-445b-87e6-d1d7d0d31c30;rckk=qTySnWAuRB4EyF4mlAkFGW57FMtjDBmF;ru1k=693548ae-0b8b-4bb1-8f6e-0b35db37c16b;ru2k=6e8644bc-c63a-445b-87e6-d1d7d0d31c30;PASS_ID=1-YTqxUquDtffINrYTViOExfhP+ZLynqpzzFitcsduDGXSP91Q1yHMC18zR5HyYWQm5KIkSqIaG5WlEE0UDs3Jcg_318588314_117463176;JSESSIONID=47B1208E97CB538444170474071AE5FD;',jcookie='[{"Name":"webp","Value":"true","Domain":"mms.pinduoduo.com","Path":"/login","Secure":false,"HttpOnly":false,"Expires":"2022-08-24T10:01:32","Creation":"2022-07-25T10:01:32.426","LastAccess":"2022-07-25T10:01:32.426","SameSite":0,"Priority":0},{"Name":"api_uid","Value":"CiT4XGLeadScdABfPshNAg==","Domain":".pinduoduo.com","Path":"/","Secure":false,"HttpOnly":false,"Expires":"2037-12-31T23:56:33.235","Creation":"2022-07-25T10:01:30.235","LastAccess":"2022-07-25T10:01:30.235","SameSite":0,"Priority":0},{"Name":"_nano_fp","Value":"XpEyl0mYXqPbXpXjXT_v15p3GWD5bgzTCnJsO2Dl","Domain":"mms.pinduoduo.com","Path":"/","Secure":false,"HttpOnly":false,"Expires":"2049-12-09T10:01:31","Creation":"2022-07-25T10:01:31.761","LastAccess":"2022-07-25T10:01:31.761","SameSite":0,"Priority":0},{"Name":"_bee","Value":"qTySnWAuRB4EyF4mlAkFGW57FMtjDBmF","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:42.012","Creation":"2022-07-25T10:01:34.629","LastAccess":"2022-07-25T10:01:42.012","SameSite":1,"Priority":0},{"Name":"_f77","Value":"693548ae-0b8b-4bb1-8f6e-0b35db37c16b","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:42.012","Creation":"2022-07-25T10:01:34.629","LastAccess":"2022-07-25T10:01:42.012","SameSite":1,"Priority":0},{"Name":"_a42","Value":"6e8644bc-c63a-445b-87e6-d1d7d0d31c30","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:42.012","Creation":"2022-07-25T10:01:34.629","LastAccess":"2022-07-25T10:01:42.012","SameSite":1,"Priority":0},{"Name":"rckk","Value":"qTySnWAuRB4EyF4mlAkFGW57FMtjDBmF","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:42.012","Creation":"2022-07-25T10:01:34.629","LastAccess":"2022-07-25T10:01:42.012","SameSite":0,"Priority":0},{"Name":"ru1k","Value":"693548ae-0b8b-4bb1-8f6e-0b35db37c16b","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:42.012","Creation":"2022-07-25T10:01:34.629","LastAccess":"2022-07-25T10:01:42.012","SameSite":0,"Priority":0},{"Name":"ru2k","Value":"6e8644bc-c63a-445b-87e6-d1d7d0d31c30","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:42.012","Creation":"2022-07-25T10:01:34.629","LastAccess":"2022-07-25T10:01:42.012","SameSite":0,"Priority":0},{"Name":"PASS_ID","Value":"1-YTqxUquDtffINrYTViOExfhP+ZLynqpzzFitcsduDGXSP91Q1yHMC18zR5HyYWQm5KIkSqIaG5WlEE0UDs3Jcg_318588314_117463176","Domain":"mms.pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2022-08-04T10:01:52.372","Creation":"2022-07-25T10:01:52.372","LastAccess":"2022-07-25T10:01:52.372","SameSite":0,"Priority":0},{"Name":"JSESSIONID","Value":"47B1208E97CB538444170474071AE5FD","Domain":"mms.pinduoduo.com","Path":"/","Secure":false,"HttpOnly":true,"Expires":null,"Creation":"2022-07-25T10:01:52.452","LastAccess":"2022-07-25T10:01:52.452","SameSite":0,"Priority":0}]' where id=116465"
},
{ 
"Id":4940997,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":287,
"State":"Waiting for table level lock",
"Info":"update puser set ltime='2022-07-25 18:01:18',ver='1.2.1.0',pname='浩龙销量助手' where id=762"
},
{ 
"Id":4940999,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":287,
"State":"Waiting for table level lock",
"Info":"update pshop set access_token='a773e76171e74386832d71a315de4af144469c34',refresh_token='dfc195cc7c2445ff97a4d7ae34b9786248aef2d9',expires_at='2022-08-06 13:54:15' where id=119671"
},
{ 
"Id":4941003,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":286,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=windows_1-**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**ser pdd_windows_version/1.0.0',shop_token='eyJ0IjoiMUxkTlJLSTFxWERlV1QvTTN2dzNJOTVLQ3BtbHJRRTJVNVFFaDREcityeUkwV09yb2JxSFdUampBY3VoZFpjMyIsInYiOjEsInMiOjQsIm0iOjE5OTYyNDk3MSwidSI6MTA2NTEzMzU2fQ' where mmsUsername='pdd19962497153'"
},
{ 
"Id":4941010,
"User":"pddzs",
"Host":"localhost:50154",
"db":"pddzs",
"Command":"Execute",
"Time":284,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6104, '220.195.80.80', 'dl/users/data', 'POST', '{\"page\":\"1\",\"limit\":\"10\",\"total\":\"0\",\"user_type\":\"1\",\"user\":\"04760069\",\"pid\":null,\"pname\":null,\"ifinit\":null,\"ifdel\":null,\"ifkey\":null,\"sort\":null,\"status\":null}', '2022-07-25 18:01:21')"
},
{ 
"Id":4941012,
"User":"pddzs",
"Host":"localhost:50322",
"db":"pddzs",
"Command":"Execute",
"Time":283,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6104, '220.195.80.80', 'dl/users/data', 'POST', '{\"page\":\"1\",\"limit\":\"10\",\"total\":\"0\",\"user_type\":\"1\",\"user\":\"04760069\",\"pid\":null,\"pname\":null,\"ifinit\":null,\"ifdel\":null,\"ifkey\":null,\"sort\":null,\"status\":null}', '2022-07-25 18:01:22')"
},
{ 
"Id":4941013,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":283,
"State":"Waiting for table level lock",
"Info":"update puser set ltime='2022-07-25 18:01:22',ver='1.2.1.2',pname='浩龙销量助手' where id=1801"
},
{ 
"Id":4941014,
"User":"pddzs",
"Host":"localhost:50426",
"db":"pddzs",
"Command":"Execute",
"Time":283,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6104, '220.195.80.80', 'dl/users/data', 'POST', '{\"page\":\"1\",\"limit\":\"10\",\"total\":\"0\",\"user_type\":\"1\",\"user\":\"04760069\",\"pid\":null,\"pname\":null,\"ifinit\":null,\"ifdel\":null,\"ifkey\":null,\"sort\":null,\"status\":null}', '2022-07-25 18:01:22')"
},
{ 
"Id":4941016,
"User":"pddzs",
"Host":"localhost:50508",
"db":"pddzs",
"Command":"Execute",
"Time":282,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6104, '220.195.80.80', 'dl/users/data', 'POST', '{\"page\":\"1\",\"limit\":\"10\",\"total\":\"0\",\"user_type\":\"1\",\"user\":\"04760069\",\"pid\":null,\"pname\":null,\"ifinit\":null,\"ifdel\":null,\"ifkey\":null,\"sort\":null,\"status\":null}', '2022-07-25 18:01:23')"
},
{ 
"Id":4941018,
"User":"pddzs",
"Host":"localhost:50568",
"db":"pddzs",
"Command":"Execute",
"Time":282,
"State":"Waiting for table level lock",
"Info":"insert into `proxy_action` (`proxy_id`, `client_ip`, `url`, `method`, `vars`, `created_at`) values (6019, '171.43.225.202', 'dl/users/8533/rechange', 'POST', '{\"id\":\"8533\",\"username\":\"15191580850\",\"gold\":\"500\",\"snum\":null}', '2022-07-25 18:01:23')"
},
{ 
"Id":4941024,
"User":"pddzs",
"Host":"localhost:50954",
"db":"pddzs",
"Command":"Execute",
"Time":280,
"State":"Waiting for table level lock",
"Info":"update `proxy` set `user_count` = 56, `shop_count` = '4050', `last_login_time` = '2022-07-25 18:01:25', `proxy`.`updated_at` = '2022-07-25 18:01:25' where `id` = 6073"
},
{ 
"Id":4941025,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":279,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='webp=true;api_uid=CihF52Leadp2bQBYSuBGAg==;_nano_fp=XpEyl0mYXqPylpTol9_Pe1MWesOB~3QHKzc5uE2q;_bee=XSzTkud1r7xfgVUlUlCqhrdvrkOQVVVX;_f77=c96a6727-fbe7-48ae-9fbf-b9c10e6b698f;_a42=5afcb4e8-9a2c-4dcc-ac2a-c212e3e0a3bf;rckk=XSzTkud1r7xfgVUlUlCqhrdvrkOQVVVX;ru1k=c96a6727-fbe7-48ae-9fbf-b9c10e6b698f;ru2k=5afcb4e8-9a2c-4dcc-ac2a-c212e3e0a3bf;PASS_ID=1-g1LgrnOHSS3dhi2Gf7wNb7aVySnAybU1Sr7O+x+gM8vdcBD+Ho67HjENhpv+NHt7wRvO5gOL4F+yQzWDfhdGkQ_624017211_118042083;JSESSIONID=30E8CC800AFD7C118358F3A0B3305770;',jcookie='[{"Name":"webp","Value":"true","Domain":"mms.pinduoduo.com","Path":"/login","Secure":false,"HttpOnly":false,"Expires":"2022-08-24T10:01:00","Creation":"2022-07-25T10:01:00.464","LastAccess":"2022-07-25T10:01:00.464","SameSite":0,"Priority":0},{"Name":"api_uid","Value":"CihF52Leadp2bQBYSuBGAg==","Domain":".pinduoduo.com","Path":"/","Secure":false,"HttpOnly":false,"Expires":"2037-12-31T23:55:56.702","Creation":"2022-07-25T10:00:59.702","LastAccess":"2022-07-25T10:00:59.702","SameSite":0,"Priority":0},{"Name":"_nano_fp","Value":"XpEyl0mYXqPylpTol9_Pe1MWesOB~3QHKzc5uE2q","Domain":"mms.pinduoduo.com","Path":"/","Secure":false,"HttpOnly":false,"Expires":"2049-12-09T10:00:59","Creation":"2022-07-25T10:00:59.952","LastAccess":"2022-07-25T10:00:59.952","SameSite":0,"Priority":0},{"Name":"_bee","Value":"XSzTkud1r7xfgVUlUlCqhrdvrkOQVVVX","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:27.266","Creation":"2022-07-25T10:01:02.236","LastAccess":"2022-07-25T10:01:27.266","SameSite":1,"Priority":0},{"Name":"_f77","Value":"c96a6727-fbe7-48ae-9fbf-b9c10e6b698f","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:27.266","Creation":"2022-07-25T10:01:02.236","LastAccess":"2022-07-25T10:01:27.266","SameSite":1,"Priority":0},{"Name":"_a42","Value":"5afcb4e8-9a2c-4dcc-ac2a-c212e3e0a3bf","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:27.266","Creation":"2022-07-25T10:01:02.236","LastAccess":"2022-07-25T10:01:27.266","SameSite":1,"Priority":0},{"Name":"rckk","Value":"XSzTkud1r7xfgVUlUlCqhrdvrkOQVVVX","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:27.266","Creation":"2022-07-25T10:01:02.236","LastAccess":"2022-07-25T10:01:27.266","SameSite":0,"Priority":0},{"Name":"ru1k","Value":"c96a6727-fbe7-48ae-9fbf-b9c10e6b698f","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:27.266","Creation":"2022-07-25T10:01:02.236","LastAccess":"2022-07-25T10:01:27.266","SameSite":0,"Priority":0},{"Name":"ru2k","Value":"5afcb4e8-9a2c-4dcc-ac2a-c212e3e0a3bf","Domain":".pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2023-07-25T10:01:27.266","Creation":"2022-07-25T10:01:02.236","LastAccess":"2022-07-25T10:01:27.266","SameSite":0,"Priority":0},{"Name":"PASS_ID","Value":"1-g1LgrnOHSS3dhi2Gf7wNb7aVySnAybU1Sr7O+x+gM8vdcBD+Ho67HjENhpv+NHt7wRvO5gOL4F+yQzWDfhdGkQ_624017211_118042083","Domain":"mms.pinduoduo.com","Path":"/","Secure":true,"HttpOnly":true,"Expires":"2022-08-04T10:01:26.927","Creation":"2022-07-25T10:01:26.927","LastAccess":"2022-07-25T10:01:26.927","SameSite":0,"Priority":0},{"Name":"JSESSIONID","Value":"30E8CC800AFD7C118358F3A0B3305770","Domain":"mms.pinduoduo.com","Path":"/","Secure":false,"HttpOnly":true,"Expires":null,"Creation":"2022-07-25T10:01:27.016","LastAccess":"2022-07-25T10:01:27.016","SameSite":0,"Priority":0}]' where id=121006"
},
{ 
"Id":4941032,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":278,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=windows_1-2hyAiP+MRYooA4k**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**hrome/31.0.1650.16 Safari/537.36PddWorkbench-Online PddBrowser pdd_windows_version/1.0.0',shop_token='eyJ0IjoiQUZPZnNZSHljMC9ELzl3ZDVMOENsRUcrSitNNjdCMEtEWS91UXRqMjdMemd2K2hlMlNqME1XV1ZmaERYVHpSRyIsInYiOjEsInMiOjQsIm0iOjY1ODUzMDIwNywidSI6MTA3MTk3MjE4fQ' where mmsUsername='pdd65853020796'"
},
{ 
"Id":4941040,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":277,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=windows_1-+uNA+4BGcK3Yv0RJMuN/q**不予展示**不予展示**不予展示****不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**37.36 (KHTML, like Gecko) Cline PddBrowser pdd_windows_version/1.0.0',shop_token='eyJ0IjoiZWk5M09ST0JaTlI1Ylh3RTFoSVh1aWZ2ODFPN3hWaE9PcHpvTllTOWdtOWdFckVRY0dGY1hINGVydlFiYnQ3ZCIsInYiOjEsInMiOjQsIm0iOjE0ODY0MDE5NiwidSI6MTExNjY1ODA2fQ' where mmsUsername='pdd14864019613'"
},
{ 
"Id":4941042,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":277,
"State":"Waiting for table level lock",
"Info":"update pshop set cookie='PASS_ID=windows**不予展示**不予展示**不予展示**不予展示**不予展示**不予展示**/31.0.1650.16 SafUbmpSWM0fQ' where mmsUsername='pdd68619417681'"
},
{ 
"Id":4941044,
"User":"pddzs",
"Host":"localhost",
"db":"pddzs",
"Command":"Query",
"Time":277,
"State":"Waiting for table level lock",
"Info":"update puser set ltime='2022-07-25 18:01:28',ver='1.2.1.0',pname='浩龙销量助手' where id=762"
}
]
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 21

一般而言就是慢,耗时SQL引起的,复杂耗时的SQL会形成对其他的SQL阻塞,这就需要根据业务去排查SQL了。排查时比如说阻塞的SQL都涉及到哪个表,涉及这些表的SQL是否都没问题 有没有join等等,测试环境数据量和正式差不多时,直接改mysql的超时时间,直接定位SQL :joy: 个人见解

3周前 评论
╰ゝSakura

你这条sql语句是不可能引起这个问题的。这个是由于你的update语句导致了锁表,

  1. 你看下哪些语句报Waiting for table level lock。
  2. 看下这些语句中涉及的表有哪些,找出所有语句中都有的那张表
  3. 看下步骤2中的表,有哪些update语句
  4. 找出那条命中范围多(比如没有where条件或者符合where条件很多的数据)update语句

采纳答案,有疑问可帮忙解答

3周前 评论
没有哆啦A梦的大雄 (楼主) 3周前
没有哆啦A梦的大雄 (楼主) 3周前
没有哆啦A梦的大雄 (楼主) 3周前
╰ゝSakura (作者) 3周前

不应该是这条select引起的,因为它没有加锁

3周前 评论
╰ゝSakura 3周前
勇敢的心 (作者) 3周前
╰ゝSakura 3周前

看一下这条SQL走索引了吗?如果没有走,那就针对这条SQL加组合索引

explain update `yzshop` set `upnum` = 876300 where `shopid` = 105475339 and `upnum` < 876300
3周前 评论
没有哆啦A梦的大雄 (楼主) 3周前
没有哆啦A梦的大雄 (楼主) 3周前
没有哆啦A梦的大雄 (楼主) 3周前
勇敢的心 (作者) 3周前

@╰ゝSakura

CREATE TABLE `pshop` (
  `Id` int(100) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
  `groupid` int(11) NOT NULL DEFAULT '0' COMMENT '分组ID',
  `mmsUsername` varchar(255) NOT NULL DEFAULT '' COMMENT '店铺用户名',
  `mallId` int(11) NOT NULL DEFAULT '0' COMMENT '店铺ID',
  `mallName` varchar(255) NOT NULL DEFAULT '' COMMENT '店铺名称',
  `merchantType` varchar(255) DEFAULT '0' COMMENT '商户类型 1:个人 2:企业 3:旗舰店 4:专卖店 5:专营店 6:普通店',
  `cookie` text COMMENT 'cookie',
  `jcookie` text COMMENT 'jcookie',
  `ua` varchar(255) DEFAULT NULL COMMENT 'ua信息',
  `access_token` varchar(255) DEFAULT '' COMMENT 'token值',
  `refresh_token` varchar(255) DEFAULT '' COMMENT 'refresh_token',
  `shop_token` varchar(255) DEFAULT NULL COMMENT '店铺工作台token',
  `islock` int(11) NOT NULL DEFAULT '0' COMMENT '店铺是否开启',
  `remarks` varchar(255) DEFAULT NULL COMMENT '店铺备注',
  `expires_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT '授权到期日期',
  `ltime` datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '在线时间',
  `ctime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `utime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '到期时间',
  `upnum` int(11) DEFAULT '0' COMMENT '当日上传数量',
  `pid` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '直属代理ID',
  `pid2` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '顶级代理ID',
  `apitype` int(11) NOT NULL DEFAULT '1' COMMENT 'api类型,apitype的id',
  `dd_usn` int(10) NOT NULL DEFAULT '0',
  `sd_usn` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id`),
  UNIQUE KEY `mmsUsername` (`mmsUsername`) USING BTREE,
  UNIQUE KEY `mallId` (`mallId`) USING BTREE,
  KEY `apitype` (`apitype`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=126272 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='店铺列表'
3周前 评论
╰ゝSakura 3周前
╰ゝSakura 3周前

AUTO_INCREMENT=126272

这么多的店,你真的是拼多多的?

3周前 评论
没有哆啦A梦的大雄 (楼主) 3周前

是用RDS还是自建的mysql

3周前 评论

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!