duplicate key update | ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction

[thenaazimkhan]

I am getting duplicate key updates when trying to save data from strapi admin but when I try the same query from tableplus mysql client, it is working fine.

I am surprised where is the duplicate data in that query.

attaching a screenshot for detail.

insert into `components_elements_pricing_plans_components` (`component_id`, `component_type`, `entity_id`, `field`, `order`) values (15, 'elements.pricing-feature', 3, 'features', 1), (16, 'elements.pricing-feature', 3, 'features', 2), (17, 'elements.pricing-feature', 3, 'features', 3), (18, 'elements.pricing-feature', 3, 'features', 4), (20, 'elements.pricing-feature', 3, 'features', 5), (19, 'elements.pricing-feature', 3, 'features', 6), (21, 'elements.pricing-feature', 3, 'features', 7) on duplicate key update `order` = values(`order`) - ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
    at Query.Sequence._packetToError (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/sequences/Sequence.js:47:14)
    at Query.ErrorPacket (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/sequences/Query.js:79:18)
    at Protocol._parsePacket (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/Protocol.js:291:23)
    at Parser._parsePacket (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/Parser.js:433:10)
    at Parser.write (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/Parser.js:43:10)
    at Protocol.write (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/Protocol.js:38:16)
    at Socket.<anonymous> (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/Connection.js:88:28)
    at Socket.<anonymous> (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/Connection.js:526:10)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    --------------------
    at Protocol._enqueue (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/protocol/Protocol.js:144:48)
    at Connection.query (/Users/naazimkhan/Workspace/th-cms/node_modules/mysql/lib/Connection.js:198:25)
    at /Users/naazimkhan/Workspace/th-cms/node_modules/knex/lib/dialects/mysql/index.js:132:18
    at new Promise (<anonymous>)
    at Client_MySQL._query (/Users/naazimkhan/Workspace/th-cms/node_modules/knex/lib/dialects/mysql/index.js:126:12)
    at executeQuery (/Users/naazimkhan/Workspace/th-cms/node_modules/knex/lib/execution/internal/query-executioner.js:37:17)
    at Client_MySQL.query (/Users/naazimkhan/Workspace/th-cms/node_modules/knex/lib/client.js:146:12)
    at /Users/naazimkhan/Workspace/th-cms/node_modules/knex/lib/execution/transaction.js:363:24
    at new Promise (<anonymous>)
    at Client_MySQL.trxClient.query (/Users/naazimkhan/Workspace/th-cms/node_modules/knex/lib/execution/transaction.js:358:12)

attaching formated sql:-

INSERT INTO `components_elements_pricing_plans_components` 
	(`component_id`, `component_type`, `entity_id`, `field`, `order`) values
	(15, 'elements.pricing-feature', 3, 'features', 1), 
	(16, 'elements.pricing-feature', 3, 'features', 2), 
	(17, 'elements.pricing-feature', 3, 'features', 3), 
	(18, 'elements.pricing-feature', 3, 'features', 4), 
	(20, 'elements.pricing-feature', 3, 'features', 5), 
	(19, 'elements.pricing-feature', 3, 'features', 6), 
	(21, 'elements.pricing-feature', 3, 'features', 7);

here is the UNIQUE KEY of the table (component_id,component_type,entity_id,field)

here is the table schema of the table

CREATE TABLE `components_elements_pricing_plans_components` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `entity_id` int unsigned DEFAULT NULL,
  `component_id` int unsigned DEFAULT NULL,
  `component_type` varchar(255) DEFAULT NULL,
  `field` varchar(255) DEFAULT NULL,
  `order` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `components_elements_pricing_plans_unique` (`entity_id`,`component_id`,`field`,`component_type`),
  KEY `components_elements_pricing_plans_field_index` (`field`),
  KEY `components_elements_pricing_plans_component_type_index` (`component_type`),
  KEY `components_elements_pricing_plans_entity_fk` (`entity_id`),
  CONSTRAINT `components_elements_pricing_plans_entity_fk` FOREIGN KEY (`entity_id`) REFERENCES `components_elements_pricing_plans` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=491 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

[petersg83]

It will probably be fixed in the next release by #15197
If you try on this branch, do you still have the issue?

[derrickmehaffy]

Manually reassigning to the content squad since this relates to the new reordering feature

[dottodot]

I'm seeing a similar issue

insert into `components_customiser_custom_parts_model_parts_links` (`custom_parts_id`, `model_part_id`, `model_part_order`) values (15, '114', 1), (15, '119', 2) on duplicate key update `model_part_order` = values(`model_part_order`) - ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction
Error: ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction

[Santiago-j-s]

I'm seeing the same issue with version 4.5.5

error: insert ignore into components_component_base_contents_components
(component_id, component_type, entity_id, field)
values (36, 'atomic.responsive-text', 10, 'subtitle')

ER_LOCK_DEADLOCK: Deadlock found when trying to get lock; try restarting transaction.

Database version:

mysql  Ver 15.1 Distrib 10.8.3-MariaDB, for debian-linux-gnu (aarch64) using  EditLine wrapper