How to fix a database timeout when deleting a source item through the REST API?

Question

I have Magento 2.4.1 with MariaDB and a catalogue of 370,000 products. I’m trying to delete source items through the REST API. It used to work well in the past, but today I can’t get it to work. Even deleting a single source item results in an error.

Using the endpoint /V1/inventory/source-items-delete with PyMagento:

>>> client.delete_source_items([{'sku': 'mysku', 'source_code': 'mysource'}])
...
magento.exceptions.MagentoException: Internal Error. Details are available in Magento log file. Report ID: webapi-63ff1c22cac22

If I grep for this report ID in the log, I get:

Next Exception: Report ID: webapi-63ff1c22cac22; Message: SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded; try restarting transaction, query was: RENAME TABLE inventory_stock_2 TO inventory_stock_2_outdated,inventory_stock_2_replica TO inventory_stock_2,inventory_stock_2_outdated TO inventory_stock_2_replica in /home/bixoto_admin/ecom/vendor/magento/framework/Webapi/ErrorProcessor.php:208

Why does Magento try to rename an entire table just to delete a single source item?

Answer 1

Please try this SQL: https://stackoverflow.com/a/13405814/12004981

set innodb_lock_wait_timeout=120;

OR try this link: https://stackoverflow.com/a/6005564/12004981

SET GLOBAL innodb_lock_wait_timeout = 120; 

You can add value as you need.

Answer 2

Ran into a similar issue while updating stock trough API's. Had delete on stock items in a for loop, instead of batching them.

Batching the stock sources, and calling delete on the array of items, helped with this issue, and had much better performance, but was not completely a solution

Another option is to skip deleting the items all together, and just set their stock source qty to 0, and disable it.