If your Magento 2 deployment or cron just failed with Could not acquire lock for index: catalog_category_product, the short version is this: a previous reindex still holds the lock, or was killed while holding it. On large catalogs the reason it was killed is almost always the same - core's category product indexer accumulated so many row locks that MySQL terminated the transaction. This article covers how to unstick it right now, why it returns, and what actually fixes it.
What the error actually means
Magento takes a lock before an indexer runs so two processes cannot rebuild the same index at once. The error appears when that lock cannot be taken. There are three realistic causes:
| Cause | What you will see | Reality |
|---|---|---|
| A reindex is genuinely still running | indexer:status shows "Processing", the process exists | Normal - wait for it |
| A previous run was killed mid-flight | "Processing" with no running process | Stale lock, needs a reset |
| MySQL killed the transaction | "Processing" or "Suspended", errors in the MySQL log | The common case on big catalogs |
The third case is the one that brings people here. It is not a Magento bug in the "someone forgot a semicolon" sense - it is a scaling limit in how the indexer queries data.
Fix it right now
First, see the actual state of every indexer:
bin/magento indexer:status
If catalog_category_product is stuck and no reindex process is running, clear it:
bin/magento indexer:reset catalog_category_product catalog_product_category
Reset both - they are two sides of the same index and leaving one behind causes confusing partial results. Then reindex, ideally in a quiet window rather than mid-traffic:
bin/magento indexer:reindex catalog_category_product
If it fails again in the same place, do not simply retry in a loop. Retrying a reindex that MySQL is killing puts the database under sustained load for no benefit, and on a busy store that makes things worse. Move to the next section instead.
Two settings worth confirming while you are here:
- Indexer mode -
bin/magento indexer:show-modeshould report "Update by Schedule" for catalog indexers. "Update on Save" triggers a reindex on every catalog write, which multiplies your chances of collisions. - Cron health - if cron is not running,
mviewnever processes the change log, the backlog grows, and the next full reindex has far more work to do.
Why it keeps coming back
Resetting the lock clears the symptom. If the error returns after a day, a deploy, or the next big catalog import, the cause is structural.
Magento's stock catalog_category_product indexer runs a single INSERT...SELECT with 13 JOINs. Four of them hit catalog_product_entity_int alone, resolving status and visibility at both default and store-view scope. Then the WHERE clause wraps three EAV columns in IFNULL(store_value, default_value).
That last detail is the important one. Wrapping a column in a function makes the condition non-sargable - MySQL cannot use an index for it. The optimizer falls back to a nested-loop join across a very large intermediate result, and the work grows far faster than your catalog does. This is why stores tend to fall off a cliff rather than degrade smoothly: everything is fine at 80k products and impossible at 300k.
The measurable consequences on a real 447k-product store:
- 1 056 250 row locks held simultaneously, still growing when it was killed
- 7 of 19 tables in use locked at the same time
- A full reindex that hung for over 24 hours before being terminated
At that point MySQL kills the transaction, the lock record is left behind, and you get the error you searched for.
The permanent fix
The query is the problem, so the fix is to stop asking MySQL to resolve EAV attributes inside the indexing join. Resolve them once, up front, into flat tables - then let core's indexer run unchanged against those.
That is exactly what I built SimpleMage Category/Product Indexer to do. It is free, MIT licensed, and installs with one composer command. It replaces three core action classes via DI preferences, materialises product and category EAV data into snapshot tables in chunks, and rewrites the Select objects to read from them - taking the main INSERT...SELECT from 13 JOINs down to 2.
Measured on copies of real production databases, with output verified byte-for-byte identical to core:
| Catalog | Core Magento | With the module |
|---|---|---|
| 111k products, 700+ categories | 124.4 s | 16.2 s (7.7× faster) |
| 447k products, 3 store views | hangs (> 24 h, killed) | 2 min 41 s |
| 503k products, 4 store views | 42 min 8 s | 16 min 21 s (2.6× faster) |
Row locks drop from over a million to a few thousand, which is what stops MySQL killing the transaction in the first place. Because the module changes how data is read and still hands the actual write to core's parent::execute(), the resulting index is identical - verified with full-content MD5 snapshots across every row, column and store view.
composer require simplemage/module-category-product-indexer
bin/magento module:enable SimpleMage_CategoryProductIndexer
bin/magento setup:upgrade
bin/magento setup:di:compile
bin/magento cache:flush
If it is not for you, bin/magento module:disable reverts to core immediately - no core tables are modified, so there is nothing to migrate back.
When it is not just the indexer
A stuck indexer is often the most visible symptom rather than the whole problem. If your store is also slow to load, slow to deploy, or slow to change, the same underlying causes are usually at work - EAV-heavy queries, module bloat, an upgrade deferred one release too long, or hosting that was sized for a smaller catalog. Fixing one bottleneck simply reveals the next.
If you want to know which ones actually apply to your store, measured rather than guessed, that is what a Magento performance audit is for - you get every finding rated by severity, with an effort estimate and a prioritised order of work. Related reading: Why is my Magento store slow? covers the eight causes I see most.
