Mariadb – Deadlocks appeared after moving to MariaDB10.2 from MySQL5.5

deadlockmariadbmariadb-10.1

After moving to mariadb-server-10.2 from mysql5.5 I am facing deadlock issues. Please see the output of 'show full processlist'. Can someone give some suggestion?

| 162506 | catalog     | mymagentositexxxxxx.com:34163 | catalog | Query   | 1016 | update                       | INSERT INTO `sales_flat_quote_item` (`quote_id`, `created_at`, `updated_at`, `product_id`, `store_id`, `is_virtual`, `sku`, `name`, `is_qty_decimal`, `weight`, `qty`, `product_type`, `base_cost`) VALUES ('6252710', '2017-09-08 07:38:14', '2017-09-08 07:38:14', '26601', '1', '1', 'c08236dbaf', 'Informaatika 8.c 2017/2018', '0', NULL, '1', 'virtual', NULL)                                                     |    0.000 |
| 162679 | catalog     | mymagentositexxxxxx.com:34380 | catalog | Query   | 1018 | Waiting for table level lock | DELETE FROM `catalogsearch_fulltext` WHERE (store_id=1) AND (product_id IN ('26601'))                                                                                                                                                                                                                                                                                                                                    |    0.000 |
| 162701 | catalog     | mymagentositexxxxxx.com:34410 | catalog | Query   | 1021 | Sending data                 | INSERT INTO `catalogsearch_result` SELECT 6656 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%informaatika%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)                                                                          |    0.000 |
| 162742 | catalog     | mymagentositexxxxxx.com:34456 | catalog | Query   | 1014 | Waiting for table level lock | INSERT INTO `catalogsearch_result` SELECT 11474 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%Informaatika%' OR `s`.`data_index` LIKE '%8.c%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)                                        |    0.000 |
| 162892 | catalog     | mymagentositexxxxxx.com:34628 | catalog | Query   |  976 | update                       | INSERT INTO `report_viewed_product_index` (`visitor_id`,`customer_id`,`product_id`,`store_id`,`added_at`) VALUES ('8391969', NULL, '26601', '1', '2017-09-08 07:38:54') ON DUPLICATE KEY UPDATE visitor_id = VALUES(`visitor_id`), customer_id = VALUES(`customer_id`), product_id = VALUES(`product_id`), store_id = VALUES(`store_id`), added_at = VALUES(`added_at`)                                                  |    0.000 |
| 163286 | catalog     | mymagentositexxxxxx.com:35318 | catalog | Query   |  895 | Waiting for table level lock | INSERT INTO `catalogsearch_result` SELECT 33241 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%Informaatika%' OR `s`.`data_index` LIKE '%8.c%' OR `s`.`data_index` LIKE '%2017/2018%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`) |    0.000 |
| 163303 | catalog     | mymagentositexxxxxx.com:35337 | catalog | Query   |  891 | Waiting for table level lock | INSERT INTO `catalogsearch_result` SELECT 11474 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%informaatika%' OR `s`.`data_index` LIKE '%8.c%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)                                        |    0.000 |
| 163783 | catalog     | mymagentositexxxxxx.com:35890 | catalog | Query   |  811 | Waiting for table level lock | INSERT INTO `catalogsearch_result` SELECT 6656 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%informaatika%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)                                                                          |    0.000 |
| 163825 | catalog     | mymagentositexxxxxx.com:35952 | catalog | Query   |  804 | Waiting for table level lock | INSERT INTO `catalogsearch_result` SELECT 33241 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%informaatika%' OR `s`.`data_index` LIKE '%8.c%' OR `s`.`data_index` LIKE '%2017/2018%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`) |    0.000 |
| 164314 | catalog     | mymagentositexxxxxx.com:36583 | catalog | Query   |  721 | Waiting for table level lock | INSERT INTO `catalogsearch_result` SELECT 11474 AS `query_id`, `s`.`product_id`, 0 AS `relevance` FROM `catalogsearch_fulltext` AS `s`
 INNER JOIN `catalog_product_entity` AS `e` ON e.entity_id = s.product_id WHERE (s.store_id = 1) AND ((`s`.`data_index` LIKE '%Informaatika%' OR `s`.`data_index` LIKE '%8.c%')) ON DUPLICATE KEY UPDATE `relevance` = VALUES(`relevance`)                                        |    0.000 |
| 165111 | catalog     | mymagentositexxxxxx.com:37633 | catalog | Sleep   |  557 |                              | NULL                                                                                                                                                                                                                                                                                                                                                                                                                     |    0.000 |

The full output of 'show processlist' command is at here.

catalog_sarch_fulltext uses MyISAM engine and all other tables using InnoDB engine. The creation SQL of table's are as below.

MariaDB [catalog]> SHOW CREATE TABLE catalogsearch_fulltext;
+------------------------+------------------------------------------------------
| catalogsearch_fulltext | CREATE TABLE `catalogsearch_fulltext` (
  `product_id` int(10) unsigned NOT NULL COMMENT 'Product ID',
  `store_id` smallint(5) unsigned NOT NULL COMMENT 'Store ID',
  `data_index` longtext DEFAULT NULL COMMENT 'Data index',
  `fulltext_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
  PRIMARY KEY (`fulltext_id`),
  UNIQUE KEY `UNQ_CATALOGSEARCH_FULLTEXT_PRODUCT_ID_STORE_ID` (`product_id`,`store_id`),
  FULLTEXT KEY `FTI_CATALOGSEARCH_FULLTEXT_DATA_INDEX` (`data_index`)
) ENGINE=MyISAM AUTO_INCREMENT=912741 DEFAULT CHARSET=utf8 COMMENT='Catalog search result table' |
+------------------------+------------------------------------------------------
1 row in set (0.00 sec)

MariaDB [catalog]> SHOW CREATE TABLE catalogsearch_result;

| Table                | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            |

| catalogsearch_result | CREATE TABLE `catalogsearch_result` (
  `query_id` int(10) unsigned NOT NULL COMMENT 'Query ID',
  `product_id` int(10) unsigned NOT NULL COMMENT 'Product ID',
  `relevance` decimal(20,4) NOT NULL DEFAULT 0.0000 COMMENT 'Relevance',
  PRIMARY KEY (`query_id`,`product_id`),
  KEY `IDX_CATALOGSEARCH_RESULT_QUERY_ID` (`query_id`),
  KEY `IDX_CATALOGSEARCH_RESULT_PRODUCT_ID` (`product_id`),
  CONSTRAINT `FK_CATALOGSEARCH_RESULT_QUERY_ID_CATALOGSEARCH_QUERY_QUERY_ID` FOREIGN KEY (`query_id`) REFERENCES `catalogsearch_query` (`query_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `FK_CATSRCH_RESULT_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Catalog search result table' |

1 row in set (0.00 sec)

Does setting some timeout values remove the deadlocks? Please suggest.
-Thanks

Best Answer

If catalogsearch_fulltext is designed to use a FULLTEXT index, then you should be using MATCH, not LIKE.

If you do need LIKE, then there are two strikes against efficiency: OR and leading wildcard.

What is the idea behind doing a search in ...fulltext, then INSERTing INTO ...result. Shouldn't you simply return the results to the user? Or is this some dubious optimization of the 3rd party software that generated the query?

InnoDB has supported FULLTEXT since MariaDB 10.0.6; you should get away from MyISAM. Note "Waiting for table level lock", which is what the queries is stuck on. That comes from MyISAM, not InnoDB.

(No, I can't explain why you were not seeing these problems in MariaDB 5.5; perhaps there are other things that accidentally changed when you upgraded to 10.2.)

Related Solutions

Sql-server – Should an INSERT cause an exclusive lock on a foreign key

Here's my first recommendation to remove the deadlock (and this is a very common cause with the same resolution every time). Your RoutePlan_Save procedure currently has this logic:

IF EXISTS (SELECT ... WHERE key = @key)
  UPDATE ... WHERE key = @key;
ELSE
  INSERT(key) VALUES(@key);

So you actually have two queries here that require locks on the table, regardless of whether you are ultimately going to perform an insert or update. Stop doing that! What is the point of checking if a row exists, and then in a separate query, firing an update statement that again has to check if the row exists? This requires more resources, not less, than just trying to update the row not knowing whether it exists. So do this instead:

BEGIN TRANSACTION;
UPDATE ... WHERE key = @key;
IF @@ROWCOUNT = 0
BEGIN
  INSERT(key) VALUES(@key);
END
COMMIT TRANSACTION;

You can also isolate the other read-only processes by using a different isolation level that doesn't require them to wait for write operations to complete. NOLOCK / READ UNCOMMITTED is a popular one, but READ COMMITTED SNAPSHOT - assuming you can take the hit on tempdb - is way better IMHO. There are other alternatives, too. I happened to blog about this just yesterday:

Bad habits : Putting NOLOCK everywhere

Postgresql – Simultaneous calls to the same function: how are deadlocks occurring

I posted this to pgsql-bugs and the reply there from Tom Lane indicates this is a lock escalation issue, disguised by the mechanics of the way SQL language functions are processed. Essentially, the lock generated by the insert is obtained before the exclusive lock on the table:

I believe the issue with this is that a SQL function will do parsing (and maybe planning too; don't feel like checking the code right now) for the entire function body at once. This means that due to the INSERT command you acquire RowExclusiveLock on the "test" table during function body parsing, before the LOCK command actually executes. So the LOCK represents a lock escalation attempt, and deadlocks are to be expected.

This coding technique would be safe in plpgsql, but not in a SQL-language function.

There have been discussions of reimplementing SQL-language functions so that parsing occurs one statement at a time, but don't hold your breath about something happening in that direction; it doesn't seem to be a high priority concern for anybody.

regards, tom lane

This also explains why locking the table outside the function in a wrapping plpgsql block (as suggested by @ypercube) prevents the deadlocks.

Best Answer

Related Solutions

Sql-server – Should an INSERT cause an exclusive lock on a foreign key

Postgresql – Simultaneous calls to the same function: how are deadlocks occurring

Related Question