Deadlocks found when indexing search api db index

Open on Drupal.org →

Created on 17 December 2022, over 2 years ago

Updated 13 May 2023, almost 2 years ago

Problem/Motivation

When re-indexing the search api database indes, deadlocks are found (see below). Indexing continues until finished. But this leaves the site with error 500. This issue existed in D7 ( #2257245: Deadlock possible with index immedietatly and content creation at same time → ).

> [warning] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM "search_api_db_default_max_date"
> WHERE "item_id" = :db_condition_placeholder_0; Array
> (
> [:db_condition_placeholder_0] => entity:node/139:nl
> )
>
> [notice] Successfully indexed 699 items on Default.
> [warning] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: DELETE FROM "search_api_db_default_node_grants"
> WHERE "item_id" = :db_condition_placeholder_0; Array
> (
> [:db_condition_placeholder_0] => entity:node/1470:nl
> )
>
> [notice] Successfully indexed 748 items on Default.
> [warning] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO "search_api_db_default_node_grants" ("item_id", "value") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1), (:db_insert_placeholder_2, :db_insert_placeholder_3), (:db_insert_placeholder_4, :db_insert_placeholder_5), (:db_insert_placeholder_6, :db_insert_placeholder_7); Array
> (
> [:db_insert_placeholder_0] => entity:node/1497:nl
> [:db_insert_placeholder_1] => node_access_all:0
> [:db_insert_placeholder_2] => entity:node/1497:nl
> [:db_insert_placeholder_3] => node_access_view_unpublished_content:1
> [:db_insert_placeholder_4] => entity:node/1497:nl
> [:db_insert_placeholder_5] => node_access_view_unpublished_news_item_content:1
> [:db_insert_placeholder_6] => entity:node/1497:nl
> [:db_insert_placeholder_7] => node_access_view_unpublished_author:323
> )
>
> [warning] SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction: INSERT INTO "search_api_db_default_node_grants" ("item_id", "value") VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1), (:db_insert_placeholder_2, :db_insert_placeholder_3), (:db_insert_placeholder_4, :db_insert_placeholder_5), (:db_insert_placeholder_6, :db_insert_placeholder_7); Array
> (
> [:db_insert_placeholder_0] => entity:node/1498:nl
> [:db_insert_placeholder_1] => node_access_all:0
> [:db_insert_placeholder_2] => entity:node/1498:nl
> [:db_insert_placeholder_3] => node_access_view_unpublished_content:1
> [:db_insert_placeholder_4] => entity:node/1498:nl
> [:db_insert_placeholder_5] => node_access_view_unpublished_news_item_content:1
> [:db_insert_placeholder_6] => entity:node/1498:nl
> [:db_insert_placeholder_7] => node_access_view_unpublished_author:323
> )

This is the description from the D7 issue:

Similar to the deadlock found #2252871: Deadlock Fix: Only update workbench_moderation_node_history in workbench_moderation_moderate on node update here, search api's DELETE on empty rows can cause a deadlock due to gap lock issues (see blog post linked in that issue).

Potential solutions
Query the database and don't delete when no rows for that item id
Switch transaction level to READ COMMIT for these queries (http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html, not sure how that'd be done in drupal)
Switch overall drupal to READ-COMMIT, which is being worked on #1650930: Use READ COMMITTED by default for MySQL transactions here
Switch mysql to use READ-COMMIT by default (outside of search api and drupal)

Steps to reproduce

I don't know how to reproduce this issue.

Proposed resolution

Use the patch from the D7 issue and apply it for D8+ version of the module.

Remaining tasks

The D7 patch was modified for D8+ and successfully applied to D9. It resolved the deadlock issue. I will upload the patch in the next comment.

🐛 Bug report

Status

Needs review

Version

1.28

Component

Database backend

Created by

🇳🇱Netherlands thomasdegraaff

Live updates comments and jobs are added and updated live.

Incomplete comments

Sign in to follow issues

Merge Requests

!209Deadlocks found when indexing search api db index
Open
🇪🇸Spain keopx
updated 3 months ago

Comments & Activities

Not all content is available!

It's likely this issue predates Contrib.social: some issue and comment data are missing.

Open in Jenkins → Open on Drupal.org →
Core: 9.5.x + Environment: PHP 8.1 & sqlite-3.27
last update almost 2 years ago
534 pass
Comment almost 2 years ago →
🇦🇹Austria drunken monkey Vienna, Austria
Thanks a lot for your investigation and already providing a patch! And sorry it took me a while to get back to you.

Attached is a revised patch from me. Please test and/or review it and tell me if it still works for you.
As this problem isn’t really reproducible, as far as I can tell, there is also no test coverage we can, or need to, add.
Comment almost 2 years ago →
🇦🇹Austria drunken monkey Vienna, Austria
Would be great if you could give my patch a try and see if the issue remains solved.
First commit to issue fork.
Merge request !209Issue #3327803 by drunken monkey, thomasdegraaff, keopx: Deadlocks found when... → (Open) created by keopx
Pipeline finished with Success
3 months ago
Total: 387s
#391885
Comment 3 months ago →
🇪🇸Spain keopx Basque Country
Rerolled

contrib.social Blog FAQ Discussions

Production build 0.71.5 2024