Getting deadlocks very frequently in Mysql insert query
0
votes
1
answer
152
views
=2023-09-15 15:16:35 0x1530b7bc17002023-09-15 15:16:35 0x1530b7bc1700
*** (1) TRANSACTION:
TRANSACTION 55060584750, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 10640581, OS thread handle 23400148567808, query id 455015502 10.1.13.206 admin update
INSERT IGNORE INTO product_config
(merchant_id
,config_path
,value
) VALUES ('58541','product_access_token','456670849.oo0Dm0YB-oV73AqMvcEf6Ysm3'),('58541','product_refresh_token','456670849.jxeCmCd8Sgvit7QlZLBTmqBjLO4dW-XsYv'),('58541','product_access_token_secret','456670849.jxeCmCd8SgvxSJS4-QlZLBTmqBjLO4dW-XsYv'),('58541','token_expire_time',1694790995) ON DUPLICATE KEY UPDATE value
= VALUES(value
)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3075 page no 6615 n bits 0 index PRIMARY of table marketplace_integration
.product_config
trx id 55060584750 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len=8; bufptr=0x153946e98070; hex= 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 55060584749, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 7 row lock(s), undo log entries 1
MySQL thread id 10640966, OS thread handle 23400144643840, query id 455015500 10.1.10.11 admin update
INSERT IGNORE INTO product_config
(merchant_id
,config_path
,value
) VALUES ('112043','product_access_token','290096243.BFg3suqIMxIpb5u18AnXG41piKXKxJ7ZzHSXMBflaN8iRqER4-W7WUf-jr'),('112043','product_refresh_token','290096243.LPs2VNdIuI6xniq71DsMG5xXFXPnv8Y'),('112043','product_access_token_secret','290096243.LPs2VNdIuxXFXPnv8Y'),('112043','token_expire_time',1694790995) ON DUPLICATE KEY UPDATE value
= VALUES(value
)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3075 page no 6615 n bits 0 index PRIMARY of table marketplace_integration
.product_config
trx id 55060584749 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len=8; bufptr=0x153946e98070; hex= 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3075 page no 6615 n bits 0 index PRIMARY of table marketplace_integration
.product_config
trx id 55060584749 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len=8; bufptr=0x153946e98070; hex= 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (1)
MySQL version 5.7
Table structure-
CREATE TABLE product_config
(
id
int(11) NOT NULL AUTO_INCREMENT,
merchant_id
int(11) DEFAULT NULL,
config_path
varchar(255) DEFAULT NULL,
value
text,
updated_at_new
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
created_at_new
datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id
),
UNIQUE KEY post_product_config_idx
(merchant_id
,config_path
),
KEY merchant_id
(merchant_id
),
CONSTRAINT fk_product_config_merchant_id
FOREIGN KEY (merchant_id
) REFERENCES product_shop_details
(merchant_id
) ON DELETE CASCADE ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=1168788710 DEFAULT CHARSET=utf8
I am getting four deadlocks in an hour after the reboot of the MySQL server.
Asked by Paras Sahu
(11 rep)
Sep 15, 2023, 04:23 PM
Last activity: Jul 20, 2025, 03:03 PM
Last activity: Jul 20, 2025, 03:03 PM