Case study: the indexer deadlocked at peak traffic

Field notes — a real production bug, project name withheld. Symptom → investigation → fix → lesson.

The symptom

The store ran fine all week and fell over every evening between 19:00 and 21:00, right at the top of the dinner-browsing peak. Checkout slowed to a crawl, the cart page sometimes took twenty seconds to render, and the admin grid for orders timed out. In the logs the same two lines repeated hundreds of times:

SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock;
try restarting transaction, query was: INSERT INTO `catalogsearch_fulltext_scope1`
SELECT ... FROM `catalog_product_index_price_temp`

At the same time the cron log showed indexer_update_all_views running for 40 minutes and never finishing, and a second indexer:reindex process starting on top of it from a different cron entry. The on-call engineer restarted MySQL the first night and the problem went away for an hour, then came back worse. The second night they threw more PHP-FPM workers at it and response times got worse still, because every new worker just queued another locked query behind the same rows.

What made it confusing was that nothing had been deployed. No new module, no price rule change, no import. Traffic was only about 30 percent above the weekly average, well within what the servers had handled during the last sale. The deadlock errors pointed at catalog search, but the business symptom was checkout slowness, and those two systems should barely touch each other. That mismatch was the first clue that the real problem was a lock both paths needed, not either query alone.

Investigation

The investigation started with the cron schedule, because two overlapping indexer runs was already suspicious. The crontab had the stock Magento cron plus a hosting-panel "optimization" entry added months earlier:

* * * * * /usr/bin/php /var/www/html/bin/magento cron:run
*/5 * * * * /usr/bin/php /var/www/html/bin/magento indexer:reindex catalogsearch_fulltext
*/5 * * * * /usr/bin/php /var/www/html/bin/magento indexer:reindex catalog_product_price

So every five minutes, wall-clock indexers fired regardless of what the scheduled indexer_update_all_views job was already doing. During the day the catalog barely changed, each run finished in seconds, and nobody noticed. In the evening, merchandisers updated prices and stock for the next-day promo while shoppers placed orders, so the changelog tables filled up and each run took minutes instead of seconds. The next five-minute tick then started a second full reindex on the same tables.

The deadlock proof came from InnoDB itself:

SHOW ENGINE INNODB STATUS\G

Under LATEST DETECTED DEADLOCK the output showed two transactions in a classic cycle. Transaction 1 held an exclusive next-key lock on catalog_product_index_price_idx while trying to acquire an insert intention lock on catalogsearch_fulltext_scope1. Transaction 2 held the reverse: an exclusive lock on the fulltext scope table while inserting into the price index temp table. Both indexers materialize their results by selecting from shared price and stock sources into per-index tables, so when they ran concurrently they locked the same source rows in opposite order. Neither could proceed, InnoDB killed one, Magento retried it, and the retry deadlocked again against the still-running sibling.

Three more findings filled out the picture. First, all indexers were in realtime mode (indexer:show-mode confirmed it), meaning every product save during the evening merchandising window triggered synchronous partial reindexing inside the admin request, adding a third writer to the same tables. Second, the price indexer dimension mode was set to website-level reindexing per customer group, so one price change fanned out into dozens of per-group writes instead of one. Third, the MySQL innodb_lock_wait_timeout was at the default 50 seconds and wsrep certification was enabled on a Galera cluster, which turned every local lock wait into a cluster-wide certification conflict under write pressure. The SHOW PROCESSLIST snapshot during the incident showed 60+ queries in updating state, almost all of them indexer temp-table inserts, with frontend checkout SELECTs stuck behind them in Waiting for table metadata lock.

The checkout connection was the temp tables. Magento builds the new price index into *_temp tables under a metadata lock, then swaps them with RENAME. While the swap is pending, any query touching the live price index stalls, and the cart price resolver touches it on every totals collection. So a search-index deadlock froze checkout pricing lookups even though checkout never writes to the search tables. That explained the symptom mismatch.

Fix

The fix had four parts, applied in order, each one verified before the next.

First, kill the duplicate schedulers. The two indexer:reindex crontab lines were deleted outright. All indexing now goes through the single cron:run path with indexer_update_all_views, which uses the mview changelog and processes only changed entities. Overlapping runs became impossible because the scheduled job takes a lock in the cron_schedule table.

Second, move every indexer to schedule mode and stagger the changelog consumers:

bin/magento indexer:set-mode schedule \
  catalogsearch_fulltext catalog_product_price cataloginventory_stock inventory
bin/magento config:set dev/grid/async_indexing 1

Merchandiser saves now write only to *_cl changelog tables (milliseconds) and return. The actual index work happens in cron, batched. The changelog batch size was raised so each pass drains the queue instead of dribbling:

bin/magento config:set indexer/batch_size/catalogsearch_fulltext/partial_reindex 5000

Third, shrink the write fan-out. The price indexer was switched to a single batch per website instead of per customer group where the business allowed it, cutting the rows written per price change by roughly a factor of six on this catalog. The nightly full reindex was moved to 03:00 with a guard that skips it when a partial run is already in working state, so the evening peak never sees a full-table rebuild.

Fourth, tune the database for the remaining contention. The Galera node handling the storefront was given innodb_lock_wait_timeout = 10 so a stuck indexer fails fast instead of holding metadata locks for almost a minute, and the indexer cron got --retry 3 semantics through the standard cron_schedule retry column rather than blind wall-clock restarts. Deadlock error rate went from hundreds per evening to zero, and the indexer_update_all_views runtime dropped from 40+ minutes to under 4 minutes at peak.

Verification was direct: SHOW ENGINE INNODB STATUS clean for seven consecutive evenings, cron_schedule showing single non-overlapping indexer entries, and checkout p95 back under 2 seconds during the same traffic window that had previously collapsed.

Lesson

Two schedulers for the same job is a deadlock waiting for traffic. Wall-clock indexer:reindex on a timer plus the built-in changelog consumer will eventually overlap, and when they do they lock the same source tables in opposite order. Run exactly one indexing path, keep it in schedule mode on any catalog with evening merchandising activity, and never let a full reindex anywhere near peak hours. When checkout slows but the errors name search, look at the temp-table swap: the metadata lock on the price index is the shared choke point. SHOW ENGINE INNODB STATUS and SHOW PROCESSLIST during the incident are worth more than a week of log archaeology afterwards.

This case illustrates the profiling workflow in /blog/magento2-performance-profiling.