Deadlock for 2 simple insert queries on shipperhq_quote_package_items table

[klakovsky]

We faced to deadlocks with simple INSERT on table shipperhq_quote_package_items.

Info from "SHOW ENGINE INNODB STATUS".

------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-07-13 08:28:54 2b60f9d44700
*** (1) TRANSACTION:
TRANSACTION 1540603346, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 7 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 4
MySQL thread id 13015382, OS thread handle 0x2b60d9fb7700, query id 2638832347 10.0.1.87 magedb update
INSERT INTO `shipperhq_quote_package_items` (`package_id`, `sku`, `weight_packed`, `qty_packed`) VALUES ('636413', '615755', '1', '1')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25870 page no 1535 n bits 128 index `FK_E889295880F829D5ADA7C3C4604ECF61` of table `magento`.`shipperhq_quote_package_items` trx id 1540603346 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION:
TRANSACTION 1540603347, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 2
MySQL thread id 13015383, OS thread handle 0x2b60f9d44700, query id 2638832348 10.0.1.69 magedb update
INSERT INTO `shipperhq_quote_package_items` (`package_id`, `sku`, `weight_packed`, `qty_packed`) VALUES ('636414', '901268', '1', '1')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 25870 page no 1535 n bits 128 index `FK_E889295880F829D5ADA7C3C4604ECF61` of table `magento`.`shipperhq_quote_package_items` trx id 1540603347 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 25870 page no 1535 n bits 128 index `FK_E889295880F829D5ADA7C3C4604ECF61` of table `magento`.`shipperhq_quote_package_items` trx id 1540603347 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

*** WE ROLL BACK TRANSACTION (2)

mysql> show create table shipperhq_quote_package_items \G
*************************** 1. row ***************************
       Table: shipperhq_quote_package_items
Create Table: CREATE TABLE `shipperhq_quote_package_items` (
  `package_id` int(10) unsigned NOT NULL DEFAULT '0',
  `sku` text NOT NULL COMMENT 'SKU',
  `qty_packed` float DEFAULT NULL COMMENT 'Qty packed',
  `weight_packed` float DEFAULT NULL COMMENT 'Weight packed',
  KEY `FK_E889295880F829D5ADA7C3C4604ECF61` (`package_id`),
  KEY `SHIPPERHQ_QUOTE_PACKAGE_ITEMS_PACKAGE_ID` (`package_id`),
  CONSTRAINT `FK_E889295880F829D5ADA7C3C4604ECF61` FOREIGN KEY (`package_id`) REFERENCES `shipperhq_quote_packages` (`package_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='ShipperHQ Quote Address Package Items Information'
1 row in set (0.00 sec)

Link for same problem with deadlocks for 2 simple insert on one talbe.

We use last vesrion your extension.

[sshymko]

We experience the issue and it seems to be affecting the checkout.
Customers report the "checkout is freezing" and they're unable to complete it.
We've recorded ~2000 deadlocks on 3 web-nodes today so far.
Please help.

[wsajason]

If you email support@shipperhq.com with the steps to reproduce and include details of the environment (Magento 2 version, PHP version and MySQL version) and registered domain name we will get this tested out and raised with our developers accordingly.

[sshymko]

@wsajason
I did email both ShipperHQ Support and Enterprise Support.
Could you please reopen the issue as it's not resolved at the moment.
Either disable the issue reporting for this project on GitHub or use it appropriately.

[sshymko]

Reproduced on:
Magento 2.1.4
magento/product-community-edition 2.1.4
shipperhq/library-shipper 20.3.1
shipperhq/library-ws 20.1.0
shipperhq/module-common 20.3.1
shipperhq/module-logger 20.0.5
shipperhq/module-shipper 20.5.5

[wsajason]

@sshymko We will follow up accordingly via email.

[klakovsky]

Hi there,
we localized the problem.

While saving the model \ShipperHQ\Shipper\Model\Quote\Packages->save(),
according the customization \ShipperHQ\Shipper\Model\ResourceModel\Quote\Packages->_afterSave() MySQL are executed next transaction:

START TRANSACTION;

INSERT INTO `shipperhq_quote_packages` (`quote_address_id`, `carrier_group_id`, `carrier_code`, `package_name`, `length`, `width`, `height`, `weight`, `declared_value`, `surcharge_price`) VALUES ('4646569', '9551', 'blabla', 'blabla', '0', '0', '0', '1', '0', '0');
DELETE FROM `shipperhq_quote_package_items` WHERE (package_id = '3674078');
INSERT INTO `shipperhq_quote_package_items` (`package_id`, `sku`, `weight_packed`, `qty_packed`) VALUES ('3674078', '616457', '1', '1');
INSERT INTO `shipperhq_quote_package_items` (`package_id`, `sku`, `weight_packed`, `qty_packed`) VALUES ('3674078', '616459', '1', '1');

COMMIT;

If two different process will save these objects on same time we got deadlock, it is happen because after insert and delete MySQL locks for both transactions foreign key package_id.

This is script to reproduce deadlocks:

Session 1:
mysql> START TRANSACTION;                                                                                                                         
mysql> INSERT INTO `shipperhq_quote_packages` (`quote_address_id`, `carrier_group_id`, `carrier_code`, `package_name`, `length`, `width`, `height`, `weight`, `declared_value`, `surcharge_price`) VALUES ('4646569', '9551', 'blabla', 'blabla', '0', '0', '0', '1', '0', '0');
mysql> SELECT LAST_INSERT_ID() \G
*************************** 1. row ***************************
LAST_INSERT_ID(): 4048513
mysql> delete from shipperhq_quote_package_items where package_id = 4048513;
mysql> insert into shipperhq_quote_package_items (`package_id`, `sku`, `weight_packed`, `qty_packed`) values (4048513, 'hoho', 1, 1);

Session 2:
mysql> START TRANSACTION;                                                                                                                         
mysql> INSERT INTO `shipperhq_quote_packages` (`quote_address_id`, `carrier_group_id`, `carrier_code`, `package_name`, `length`, `width`, `height`, `weight`, `declared_value`, `surcharge_price`) VALUES ('4646569', '9551', 'blabla', 'blabla', '0', '0', '0', '1', '0', '0');
mysql> SELECT LAST_INSERT_ID() \G
*************************** 1. row ***************************
LAST_INSERT_ID(): 4048514
mysql> delete from shipperhq_quote_package_items where package_id = 4048514;
mysql> insert into shipperhq_quote_package_items (`package_id`, `sku`, `weight_packed`, `qty_packed`) values (4048514, 'hoho', 1, 1);

Session 1:
mysql> insert into shipperhq_quote_package_items (`package_id`, `sku`, `weight_packed`, `qty_packed`) values (4048513, 'hoho', 1, 1);

now session 2 got deadlock.

[wsajosh]

We've just released a update which I believe should alleviate the issue. Please update and retest. Let us know if you still see issues, I'll leave this issue open until you confirm the update resolves it for you.

[birchestx]

Thanks for this, I've escalated this up and will get back to you by tomorrow morning. Appreciate your input