0

I noticed MySQL deadlock errors in my application logs and for investigation, I queried InnoDB status and got the below mentioned Latest detected deadlock

*** (1) TRANSACTION:
TRANSACTION 19959057532, ACTIVE 0 sec fetching rows
mysql tables in use 3, locked 3
LOCK WAIT 112 lock struct(s), heap size 13864, 1320 row lock(s)
MySQL thread id 39377662, OS thread handle 0x2ad01bbd5700, query id 1863218139 ec2-13-229-237-243.ap-southeast-1.compute.amazonaws.com 13.229.237.243 prod_zefo_rw Sending data
SELECT `sale_products`.`id`, `sale_products`.`created_at`, `sale_products`.`updated_at`, `sale_products`.`product_id`, `sale_products`.`model_id`, `sale_products`.`estimated_original_price`, `sale_products`.`middle_price`, `sale_products`.`sale_price`, `sale_products`.`sale_id` FROM `sale_products` WHERE (`sale_products`.`sale_id` = 4530 AND `sale_products`.`product_id` = '208422') FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6467 page no 7773 n bits 248 index `PRIMARY` of table `sale`.`sale_products` trx id 19959057532 lock_mode X locks rec but not gap waiting


*** (2) TRANSACTION:
TRANSACTION 19959057530, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
37 lock struct(s), heap size 6544, 1114 row lock(s), undo log entries 1
MySQL thread id 39377661, OS thread handle 0x2acd1ac9d700, query id 1863218141 ec2-54-255-206-98.ap-southeast-1.compute.amazonaws.com 54.255.206.98 prod_zefo_rw update
INSERT INTO `sale_products` (`created_at`, `updated_at`, `product_id`, `model_id`, `estimated_original_price`, `middle_price`, `sale_price`, `sale_id`) VALUES ('2019-01-09 23:02:56.787354', '2019-01-09 23:02:56.787388', '231112', 'sony-32-inch-hd-ready-led-tv_gently-used', 28900, 11999, 10799, 4529)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6467 page no 7773 n bits 248 index `PRIMARY` of table `sale`.`sale_products` trx id 19959057530 lock_mode X locks rec but not gap
Record lock, heap no 5 PHYSICAL RECORD: n_fields 11; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6467 page no 6936 n bits 1224 index `sale_products_sale_id_f698c377_fk_sales_id1` of table `sale`.`sale_products` trx id 19959057530 lock_mode X locks gap before rec insert intention waiting

I can see that transaction 2 is holding an exclusive record lock on page 7773 for which transaction 1 is waiting but there's no mention of transaction 1 holding any lock so how come is it a deadlock? MySQL version - 5.6

Show create table sale_products:

CREATE TABLE `sale_products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_at` datetime(6) NOT NULL,
  `updated_at` datetime(6) NOT NULL,
  `product_id` varchar(100) NOT NULL,
  `estimated_original_price` int(11) NOT NULL,
  `middle_price` int(11) DEFAULT NULL,
  `sale_price` int(11) NOT NULL,
  `sale_id` int(11) NOT NULL,
  `model_id` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `sale_products_sale_id_f698c377_fk_sales_id1` (`sale_id`),
  KEY `product_id_index` (`product_id`),
  CONSTRAINT `sale_products_sale_id_f698c377_fk_sales_id1` FOREIGN KEY (`sale_id`) REFERENCES `sales` (`id`)
)
  • What are the indexes on sale_products (SHOW CREATE TABLE sale_products)? – danblack Jan 10 at 23:52
  • 1) Auto incremented primary key id 2) product_id 3) Foreign key sale_id – Ishank Gulati Jan 11 at 13:00
  • Please provide SHOW CREATE TABLE sale_products. Also, explain what else in inside each transaction. Perhaps other rows or other tables are involved. – Rick James Jan 12 at 1:52
  • Added show create table output – Ishank Gulati Jan 13 at 9:24

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.