MySQL Forums
Forum List  »  Performance

Deadlock issue and table lock frequently
Posted by: Devrishi Shandilya
Date: August 07, 2015 02:57AM

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 | | |
+---------------+------------+-------------------------------------------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Deadlock issue and table lock frequently
2162
August 07, 2015 02:57AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.