Preconditions (*)
Magento 2.2.0 - 2.3.5 - 2.3.5-p1 - 2.3.5-p2 - 2.4
nginx, Mysql, PHP7.2, varnish, redis
Server Intel Xeon E5-2630 V4
CPU: 10 Cores @ 2.2 GHz
1TB SSD (RAID)
64GB RAM
Steps to reproduce (*)
The sales_order_item table got more than 2Gb and my site had about 4-500k orders
Customer purchase use credit card or PayPal
This happens with almost payment gateway(Braintree, Authorize.net, PayPal ...)
Expected result (*)
Order have to complete and go to the success page
Order captured in Payment Gateway
Order saved in database with order detail
Actual result (*)
Order captured in Payment Gateway and returned all information to Magento site
Order didn't save in Database
Log:
main.CRITICAL: SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO sales_order_item (order_id, quote_item_id, store_id, product_id, product_type, product_options, weight, is_virtual, sku, name, description, applied_rule_ids, additional_data, is_qty_decimal, qty_backordered, qty_invoiced, qty_ordered, base_cost, price, base_price, original_price, base_original_price, tax_percent, tax_amount, base_tax_amount, tax_invoiced, base_tax_invoiced, discount_percent, discount_amount, base_discount_amount, discount_invoiced, base_discount_invoiced, row_total, base_row_total, row_invoiced, base_row_invoiced, row_weight, base_tax_before_discount, tax_before_discount, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax, discount_tax_compensation_amount, base_discount_tax_compensation_amount, discount_tax_compensation_invoiced, base_discount_tax_compensation_invoiced, free_shipping, gift_message_id, gift_message_available, weee_tax_applied, weee_tax_applied_amount, weee_tax_applied_row_amount, weee_tax_disposition, weee_tax_row_disposition, base_weee_tax_applied_amount, base_weee_tax_applied_row_amnt, base_weee_tax_disposition, base_weee_tax_row_disposition) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {"exception":"[object] (Magento\\Framework\\DB\\Adapter\\DeadlockException(code: 1213): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO sales_order_item (order_id, quote_item_id, store_id, product_id, product_type, product_options, weight, is_virtual, sku, name, description, applied_rule_ids, additional_data, is_qty_decimal, qty_backordered, qty_invoiced, qty_ordered, base_cost, price, base_price, original_price, base_original_price, tax_percent, tax_amount, base_tax_amount, tax_invoiced, base_tax_invoiced, discount_percent, discount_amount, base_discount_amount, discount_invoiced, base_discount_invoiced, row_total, base_row_total, row_invoiced, base_row_invoiced, row_weight, base_tax_before_discount, tax_before_discount, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax, discount_tax_compensation_amount, base_discount_tax_compensation_amount, discount_tax_compensation_invoiced, base_discount_tax_compensation_invoiced, free_shipping, gift_message_id, gift_message_available, weee_tax_applied, weee_tax_applied_amount, weee_tax_applied_row_amount, weee_tax_disposition, weee_tax_row_disposition, base_weee_tax_applied_amount, base_weee_tax_applied_row_amnt, base_weee_tax_disposition, base_weee_tax_row_disposition) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) at /home/site/html/vendor/magento/framework/DB/Adapter/Pdo/Mysql.php:585, Zend_Db_Statement_Exception(code: 40001): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO sales_order_item (order_id, quote_item_id, store_id, product_id, product_type, product_options, weight, is_virtual, sku, name, description, applied_rule_ids, additional_data, is_qty_decimal, qty_backordered, qty_invoiced, qty_ordered, base_cost, price, base_price, original_price, base_original_price, tax_percent, tax_amount, base_tax_amount, tax_invoiced, base_tax_invoiced, discount_percent, discount_amount, base_discount_amount, discount_invoiced, base_discount_invoiced, row_total, base_row_total, row_invoiced, base_row_invoiced, row_weight, base_tax_before_discount, tax_before_discount, price_incl_tax, base_price_incl_tax, row_total_incl_tax, base_row_total_incl_tax, discount_tax_compensation_amount, base_discount_tax_compensation_amount, discount_tax_compensation_invoiced, base_discount_tax_compensation_invoiced, free_shipping, gift_message_id, gift_message_available, weee_tax_applied, weee_tax_applied_amount, weee_tax_applied_row_amount, weee_tax_disposition, weee_tax_row_disposition, base_weee_tax_applied_amount, base_weee_tax_applied_row_amnt, base_weee_tax_disposition, base_weee_tax_row_disposition) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) at /home/site/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:110, PDOException(code: 40001): SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction at /home/site/html/vendor/magento/framework/DB/Statement/Pdo/Mysql.php:91)"} []
This happens randomly and not easy to reproduce my site got once a week or twice a week.
-------
Migrate Magento 1 to 2 with low cost.
