0
=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.

5
  • Deadlocks are related to how your application accesses the database. Without knowing the application, it is not possible to advise how to resolve the root cause of deadlocks. Sep 16, 2023 at 9:29
  • is there something wrong with the query or the table structure and indexing that are causing deadlocks? because 4 days ago server was rebooted and after that deadlock on this query start occurring before that on this query I have never received deadlocks
    – Paras Sahu
    Sep 18, 2023 at 7:56
  • @MikkoOhtamaa kindly suggest the points i need to check in my database i am getting deadlocks very frequently after server restart before that i haven't received deadlock on the query mentioned in the innodb report. my innodb_lock_wait_timeout is 120 secs
    – Paras Sahu
    Sep 20, 2023 at 11:58
  • Unfortunately I can only advise on technical questions, not to do your work for you. Please contact the application vendor support why their software is causing deadlocks. Sep 20, 2023 at 12:03
  • Did you delete some merchants with lots of rows? (ON DELETE CASCADE)
    – Rick James
    Sep 20, 2023 at 22:44

1 Answer 1

0
  • KEY merchant_id (merchant_id), can be dropped since the UNIQUE can handle any needs for that index. (This is likely to decrease the I/O.)
  • Presumably merchant_id and config_path could actually be NOT NULL? (This won't fix anything, but it is necessary for my next suggestion.)
  • It seems that id could be removed and the UNIQUE be promoted to PRIMARY KEY. This would, for example, keep the two INSERTs listed away from each other. Currently they are probably trying to insert onto the end of the table (for new id values) while reaching back into the table to check for dups on the UNIQUE key. (Cutting back on I/O may help avoid deadlocks.)
  • If there were other queries running, they may have impacted the deadlock without showing up in the STATUS. Were other processes running?
  • Live with deadlocks -- when you get one , rerun the query; it will almost always run quietly on the second try. Are you still getting 4 deadlocks per hour? Was there a flurry of pent up Inserts after the reboot? How many INSERTs per hour now?
  • Consider getting rid of the ON CASCADE DELETE, perhaps such was running through lots and lots of rows. Note that my PK recommendation would cluster all the rows for a given merchant_id together, thereby minimizing I/O.
  • What is the value of innodb_buffer_pool_size? How much RAM do you have? How big (in GB) is product_config? (These may lead to another recommendation.)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.