Re: Deadlock issue and table lock frequently

[Devrishi Shandilya]

Hi Rick,

Thanks for update.
I am trying to reproduce this deadlock on my system, pl suggest.

According to show engine innodb status, both transactions have S and X lock on a single table primary index eav_attribute, how can this possible, because both transactions are happening in separate tables, i.e. catalog_product_entity_varchar and eav_attribute. and transaction 1 is insert, that does not update any record in eav_attribute because fK is on on update and on delete cascase. Pl help me to get understand this situation, how the deadlock occoured in system because of these transactions.

Here is show engine innodb status and create table info for your references ;
---------------------------------------------------------------------------------------------------------
LATEST DETECTED DEADLOCK
------------------------
2015-08-20 11:18:53 7fd81486d700
*** (1) TRANSACTION:
TRANSACTION 31635498615, 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 2603578, OS thread handle 0x7fd814a26700, query id 627581613 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', '942658', 'T14HAV11807'), ('4', '85', '0', '942658', '/T/r/Translusent-Distribution-Boards_3.jpg'), ('4', '86', '0', '942658', '/T/r/Translusent-Distribution-Boards_3.jpg'), ('4', '87', '0', '942658', '/T/r/Translusent-Distribution-Boards_3.jpg'), ('4', '71', '0', '942658', 'Havells DD Transparent Designer Distribution Board, Number Of Ways: 16, DHDPSHODRT16'), ('4', '525', '0', '942658', 'Havells DD Transparent Designer Distribution Board, Number Of Ways: 16, DHDPSHODRT16'), ('4', '511', '0', '942658', 'Havells DD Transparent Designer Distribution Board'), ('4', '119', '0', '942658', '4'), ('4', '118', '0', '942658', '2'), ('4', '109', '0', '942658', 'container2'), ('4', '271', '0', '942658', '1.5'), ('4', '564', '0', '942658', '150 x 150 x 100'), ('4', '506', '0', '94265
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1010 page no 6 n bits 224 index `PRIMARY` of table `magento`.`eav_attribute` trx id 31635498615 lock mode S locks rec but not gap waiting
Record lock, heap no 72 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 0047; asc G;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d26f0; asc & ;;
3: len 2; hex 0004; asc ;;
4: len 4; hex 6e616d65; asc name;;
5: SQL NULL;
6: SQL NULL;
7: len 7; hex 76617263686172; asc varchar;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 74657874; asc text;;
11: len 4; hex 4e616d65; asc Name;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0001; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

*** (2) TRANSACTION:
TRANSACTION 31635498620, ACTIVE 1 sec starting index read, thread declared inside InnoDB 5000
mysql tables in use 1, locked 1
43 lock struct(s), heap size 6544, 738 row lock(s), undo log entries 234
MySQL thread id 2603889, OS thread handle 0x7fd81486d700, query id 627583714 172.16.50.116 tolexo updating
UPDATE `eav_attribute` SET `entity_type_id` = '4' WHERE (attribute_id='85')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1010 page no 6 n bits 224 index `PRIMARY` of table `magento`.`eav_attribute` trx id 31635498620 lock_mode X locks rec but not gap
Record lock, heap no 72 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 0047; asc G;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d26f0; asc & ;;
3: len 2; hex 0004; asc ;;
4: len 4; hex 6e616d65; asc name;;
5: SQL NULL;
6: SQL NULL;
7: len 7; hex 76617263686172; asc varchar;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 74657874; asc text;;
11: len 4; hex 4e616d65; asc Name;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0001; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

Record lock, heap no 73 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 0048; asc H;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d2797; asc ' ;;
3: len 2; hex 0004; asc ;;
4: len 11; hex 6465736372697074696f6e; asc description;;
5: SQL NULL;
6: SQL NULL;
7: len 4; hex 74657874; asc text;;
8: SQL NULL;
9: SQL NULL;
10: len 8; hex 7465787461726561; asc textarea;;
11: len 11; hex 4465736372697074696f6e; asc Description;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0001; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

Record lock, heap no 74 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 0049; asc I;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d2816; asc ( ;;
3: len 2; hex 0004; asc ;;
4: len 17; hex 73686f72745f6465736372697074696f6e; asc short_description;;
5: SQL NULL;
6: SQL NULL;
7: len 4; hex 74657874; asc text;;
8: SQL NULL;
9: SQL NULL;
10: len 8; hex 7465787461726561; asc textarea;;
11: len 17; hex 53686f7274204465736372697074696f6e; asc Short Description;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0001; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

Record lock, heap no 75 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 004a; asc J;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d2895; asc ( ;;
3: len 2; hex 0004; asc ;;
4: len 3; hex 736b75; asc sku;;
5: SQL NULL;
6: len 30; hex 636174616c6f672f70726f647563745f6174747269627574655f6261636b; asc catalog/product_attribute_back; (total 37 bytes);
7: len 6; hex 737461746963; asc static;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 74657874; asc text;;
11: len 3; hex 534b55; asc SKU;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0001; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0001; asc ;;
18: SQL NULL;

Record lock, heap no 76 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 004b; asc K;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d3077; asc 0w;;
3: len 2; hex 0004; asc ;;
4: len 5; hex 7072696365; asc price;;
5: SQL NULL;
6: len 30; hex 636174616c6f672f70726f647563745f6174747269627574655f6261636b; asc catalog/product_attribute_back; (total 39 bytes);
7: len 7; hex 646563696d616c; asc decimal;;
8: SQL NULL;
9: SQL NULL;
10: len 5; hex 7072696365; asc price;;
11: len 5; hex 5072696365; asc Price;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0001; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

Record lock, heap no 77 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 004c; asc L;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d30f9; asc 0 ;;
3: len 2; hex 0004; asc ;;
4: len 13; hex 7370656369616c5f7072696365; asc special_price;;
5: SQL NULL;
6: len 30; hex 636174616c6f672f70726f647563745f6174747269627574655f6261636b; asc catalog/product_attribute_back; (total 39 bytes);
7: len 7; hex 646563696d616c; asc decimal;;
8: SQL NULL;
9: SQL NULL;
10: len 5; hex 7072696365; asc price;;
11: len 13; hex 5370656369616c205072696365; asc Special Price;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0000; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

Record lock, heap no 78 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 004d; asc M;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d317b; asc 1{;;
3: len 2; hex 0004; asc ;;
4: len 17; hex 7370656369616c5f66726f6d5f64617465; asc special_from_date;;
5: SQL NULL;
6: len 30; hex 636174616c6f672f70726f647563745f6174747269627574655f6261636b; asc catalog/product_attribute_back; (total 43 bytes);
7: len 8; hex 6461746574696d65; asc datetime;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 64617465; asc date;;
11: len 23; hex 5370656369616c2050726963652046726f6d2044617465; asc Special Price From Date;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0000; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

Record lock, heap no 79 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 004e; asc N;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d31fd; asc 1 ;;
3: len 2; hex 0004; asc ;;
4: len 15; hex 7370656369616c5f746f5f64617465; asc special_to_date;;
5: SQL NULL;
6: len 30; hex 6561762f656e746974795f6174747269627574655f6261636b656e645f64; asc eav/entity_attribute_backend_d; (total 37 bytes);
7: len 8; hex 6461746574696d65; asc datetime;;
8: SQL NULL;
9: SQL NULL;
10: len 4; hex 64617465; asc date;;
11: len 21; hex 5370656369616c20507269636520546f2044617465; asc Special Price To Date;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0000; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1010 page no 7 n bits 192 index `PRIMARY` of table `magento`.`eav_attribute` trx id 31635498620 lock_mode X locks rec but not gap waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 19; compact format; info bits 0
0: len 2; hex 0055; asc U;;
1: len 6; hex 00030b06ef9d; asc ;;
2: len 7; hex 1e0003801d397b; asc 9{;;
3: len 2; hex 0004; asc ;;
4: len 5; hex 696d616765; asc image;;
5: SQL NULL;
6: SQL NULL;
7: len 7; hex 76617263686172; asc varchar;;
8: SQL NULL;
9: len 30; hex 636174616c6f672f70726f647563745f6174747269627574655f66726f6e; asc catalog/product_attribute_fron; (total 40 bytes);
10: len 11; hex 6d656469615f696d616765; asc media_image;;
11: len 10; hex 4261736520496d616765; asc Base Image;;
12: SQL NULL;
13: SQL NULL;
14: len 2; hex 0000; asc ;;
15: len 2; hex 0000; asc ;;
16: SQL NULL;
17: len 2; hex 0000; asc ;;
18: SQL NULL;

*** 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=184187186 DEFAULT CHARSET=utf8 COMMENT='Catalog Product Varchar Attribute Backend Table'

-----------------------------------------------------------------------------

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=1958 DEFAULT CHARSET=utf8 COMMENT='Eav Attribute'