Deadlock issue and table lock frequently
Hi Friends,
I am facing deadlock issue frequeltly, here is the example. Apart from that table eav_attribute is locking frequeltly.Please suggest.
Here are the show create table and show indexes of both tables with deadlock message.....
Thanks,
Devrishi Shandilya
-------------------------------------------------------------------------------------------------------------------------------------
Fri Aug 07 2015 10:37:41
*** (1) TRANSACTION:
TRANSACTION 28805188366, ACTIVE 1 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 2936, 9 row lock(s), undo log entries 6
MySQL thread id 285873, OS thread handle 0x7f3992035700, query id 226584527 upload01 172.16.50.100 tolexo update
INSERT INTO `catalog_product_entity_varchar` (`entity_type_id`,`attribute_id`,`store_id`,`entity_id`,`value`) VALUES ('4', '141', '0', '889589', 'T04EVE792'), ('4', '85', '0', '889589', '/B/i/Bihexagon-Ring-Spanner-Deep-Offset-Pattern-Grip-Rite-Configuration_32.jpg'), ('4', '86', '0', '889589', '/B/i/Bihexagon-Ring-Spanner-Deep-Offset-Pattern-Grip-Rite-Configuration_32.jpg'), ('4', '87', '0', '889589', '/B/i/Bihexagon-Ring-Spanner-Deep-Offset-Pattern-Grip-Rite-Configuration_32.jpg'), ('4', '71', '0', '889589', 'Everest Bihexagon Ring Spanner Deep Offset Pattern Grip-Rite Configuration Set, 8 M'), ('4', '525', '0', '889589', 'Everest Bihexagon Ring Spanner Deep Offset Pattern Grip-Rite Configuration Set, 8 M'), ('4', '511', '0', '889589', 'Everest Bihexagon Ring Spanner Deep Offset Pattern Grip-Rite Configuration Set, 8 M'), ('4', '119', '0', '889589', '4'), ('4', '118', '0', '889589', '2'), ('4',
*** (2) TRANSACTION:
TRANSACTION 28805186831, ACTIVE 2 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
37 lock struct(s), heap size 6544, 720 row lock(s), undo log entries 228
MySQL thread id 287054, OS thread handle 0x7f3992314700, query id 226594511 web02 172.16.50.116 tolexo updating
UPDATE `eav_attribute` SET `entity_type_id` = '4' WHERE (attribute_id='85')
*** WE ROLL BACK TRANSACTION (1)
-------------------------------------------------------------------------------------------------------------------
CREATE TABLE `catalog_product_entity_varchar` (
`value_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Value ID',
`entity_type_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type ID',
`attribute_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Attribute ID',
`store_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Store ID',
`entity_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity ID',
`value` varchar(255) DEFAULT NULL COMMENT 'Value',
PRIMARY KEY (`value_id`),
UNIQUE KEY `UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID` (`entity_id`,`attribute_id`,`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID` (`attribute_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID` (`store_id`),
KEY `IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID` (`entity_id`),
KEY `IDX_catalog_product_entity_varchar_value` (`value`),
CONSTRAINT `FK_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID_CORE_STORE_STORE_ID` FOREIGN KEY (`store_id`) REFERENCES `core_store` (`store_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ATTR_ID_EAV_ATTR_ATTR_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `FK_CAT_PRD_ENTT_VCHR_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=181835771 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Varchar Attribute Backend Table'
show index from catalog_product_entity_varchar;
+--------------------------------+------------+-------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------------------------+------------+-------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| catalog_product_entity_varchar | 0 | PRIMARY | 1 | value_id | A | 19477437 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 0 | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 1 | entity_id | A | 1145731 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 0 | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 2 | attribute_id | A | 19477437 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 0 | UNQ_CAT_PRD_ENTT_VCHR_ENTT_ID_ATTR_ID_STORE_ID | 3 | store_id | A | 19477437 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 1 | IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ATTRIBUTE_ID | 1 | attribute_id | A | 27706 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 1 | IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_STORE_ID | 1 | store_id | A | 2 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 1 | IDX_CATALOG_PRODUCT_ENTITY_VARCHAR_ENTITY_ID | 1 | entity_id | A | 1082079 | NULL | NULL | | BTREE | | |
| catalog_product_entity_varchar | 1 | IDX_catalog_product_entity_varchar_value | 1 | value | A | 9738718 | NULL | NULL | YES | BTREE | | |
+--------------------------------+------------+-------------------------------------------------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
-------------------------------------------------------------------------------------------------------------------
CREATE TABLE `eav_attribute` (
`attribute_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Attribute Id',
`entity_type_id` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Entity Type Id',
`attribute_code` varchar(255) DEFAULT NULL COMMENT 'Attribute Code',
`attribute_model` varchar(255) DEFAULT NULL COMMENT 'Attribute Model',
`backend_model` varchar(255) DEFAULT NULL COMMENT 'Backend Model',
`backend_type` varchar(8) NOT NULL DEFAULT 'static' COMMENT 'Backend Type',
`backend_table` varchar(255) DEFAULT NULL COMMENT 'Backend Table',
`frontend_model` varchar(255) DEFAULT NULL COMMENT 'Frontend Model',
`frontend_input` varchar(50) DEFAULT NULL COMMENT 'Frontend Input',
`frontend_label` varchar(255) DEFAULT NULL COMMENT 'Frontend Label',
`frontend_class` varchar(255) DEFAULT NULL COMMENT 'Frontend Class',
`source_model` varchar(255) DEFAULT NULL COMMENT 'Source Model',
`is_required` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Defines Is Required',
`is_user_defined` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Defines Is User Defined',
`default_value` text COMMENT 'Default Value',
`is_unique` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Defines Is Unique',
`note` varchar(255) DEFAULT NULL COMMENT 'Note',
PRIMARY KEY (`attribute_id`),
UNIQUE KEY `UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE` (`entity_type_id`,`attribute_code`),
KEY `IDX_EAV_ATTRIBUTE_ENTITY_TYPE_ID` (`entity_type_id`),
CONSTRAINT `FK_EAV_ATTRIBUTE_ENTITY_TYPE_ID_EAV_ENTITY_TYPE_ENTITY_TYPE_ID` FOREIGN KEY (`entity_type_id`) REFERENCES `eav_entity_type` (`entity_type_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1717 DEFAULT CHARSET=utf8 COMMENT='Eav Attribute'
show index from eav_attribute\g;
+---------------+------------+-------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------+------------+-------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| eav_attribute | 0 | PRIMARY | 1 | attribute_id | A | 1565 | NULL | NULL | | BTREE | | |
| eav_attribute | 0 | UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE | 1 | entity_type_id | A | 10 | NULL | NULL | | BTREE | | |
| eav_attribute | 0 | UNQ_EAV_ATTRIBUTE_ENTITY_TYPE_ID_ATTRIBUTE_CODE | 2 | attribute_code | A | 1565 | NULL | NULL | YES | BTREE | | |
| eav_attribute | 1 | IDX_EAV_ATTRIBUTE_ENTITY_TYPE_ID | 1 | entity_type_id | A | 10 | NULL | NULL | | BTREE | | |
+---------------+------------+-------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+