"What now?" issues every now and then with Magento are the norm, and I got this one a few days ago:
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when trying to get lock; try restarting transaction, query was: INSERT INTO `catalog_product_index_eav_temp` SELECT DISTINCT [...]Apparently the server went down for a second, or MySQL went down... or something happened and a reindex process was interrupted (in my case the one for the "Product EAV" index), and there was no way to make it work again.
Even the usual bin/magento indexer:reset and then a manual reindex with bin/magento indexer:reindex didn't do the trick and the only difference was that instead of the previous error I was getting the classic one.
Product EAV index is locked by another reindex process. Skipping.Lucky me, I find the following on Twitter.
Magento 2 cron uses `SELECT GET_LOCK()`, which sets a lock for the current DB session. If you lose the session, you loose control over the lock, leaving you unable to unlock it.
— willem wigman (@willemwigman) September 26, 2019
Solution: change the default lock-prefix in env.php.
(Or alternatively, use `provider => file`) pic.twitter.com/BBl9kBjgP2
Basically, when you have no way to "unlock" an index, and after you already tried the reset and manual reindex, just change that prefix in your app/etc/env.php file to something new, then reset and finally try to reindex again.
That alone should do the trick.
