Sample Header Ad - 728x90

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