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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.