Query hangs in preparing state and blocks others in waiting for table flush In a webapp I'm working on, there's an UPDATE that's slowing / blocking out several other queries:

UPDATE products
SET visible = 1
WHERE merchant_id = in_merchant_id
    AND source_code IN (
        SELECT source_code
        FROM feeds_processing_data
        WHERE processing_id = in_processing_id
        )


It basically updates a specific "window" on the products table, turning visibiliy on or off based on some other info. That table is central to the webapp, searches from the frontend are run on in (either via FULLTEXT or by code lookups), and background processes update its contents from time to time.
I usually approach these issues by checking the query plan and verifying that proper indexes are there.

What puzzles me, though, is that listing sessions from information_schema.PROCESSLIST shows that the above query sits in the preparing state, while the other blocked queries are Waiting for table flush, something I have little to no knowledge about.

This is one of those blocked queries:

SELECT id,
    [...]
FROM (
    SELECT (MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE)) AS rank,
        p.*
    FROM products p
    WHERE visible = 1
        AND MATCH(NAME, description, manufacturer_name) AGAINST('+crema +mani' IN BOOLEAN MODE) LIMIT 400
    ) ranked
ORDER BY final_price


At a first glance, I see no problems with that UPDATE, this is its EXPLAIN plan:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY products    range   uk_merchant_id_source_code  uk_merchant_id_source_code  4   const   3861    Using where
2   DEPENDENT SUBQUERY  feeds_processing_data   ref fk_processing,source_code   fk_processing   4   const   2   Using where


Indexes on the involved tables follow.

SHOW INDEXES FROM products:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
products    0   PRIMARY 1   id  A   409324  NULL    NULL        BTREE       
products    0   uk_merchant_id_source_code  1   merchant_id A   126 NULL    NULL        BTREE       
products    0   uk_merchant_id_source_code  2   source_code A   409324  NULL    NULL        BTREE       
products    1   visible 1   visible A   2   NULL    NULL        BTREE       
products    1   gtin    1   gtin    A   409324  NULL    NULL    YES BTREE       
products    1   industry_code   1   industry_code   A   409324  NULL    NULL    YES BTREE       
products    1   name_description_manufacturer_name  1   name    NULL    409324  NULL    NULL    YES FULLTEXT        
products    1   name_description_manufacturer_name  2   description NULL    409324  NULL    NULL    YES FULLTEXT        
products    1   name_description_manufacturer_name  3   manufacturer_name   NULL    409324  NULL    NULL    YES FULLTEXT        


SHOW INDEXES FROM feeds_processing_data:

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
feeds_processing_data   0   PRIMARY 1   id  A   10336188    NULL    NULL        BTREE       
feeds_processing_data   1   fk_processing   1   processing_id   A   2981    NULL    NULL        BTREE       
feeds_processing_data   1   source_code 1   source_code A   136002  NULL    NULL    YES BTREE       
feeds_processing_data   1   rejected    1   rejected    A   1   NULL    NULL        BTREE       


The first question I can't address is: why is it taking so much to complete the prepare phase?
Then, how could I further inspect the issue? What informations am I missing here?
As @jkavalik wrote you're executing the update with a dependent subquery which effectively runs it 3861 times making it highly ineffective even if the query itself runs fast.

An updated version of the query could be:

UPDATE products
JOIN feeds_processing_data ON 
   (product.source_code = feeds_processing_data.source_code 
    AND ?.processing_id = ?.in_processing_id)
SET products.visible = 1
WHERE merchant_id = in_merchant_id


You didn't attach the table schemas so I just assume this would work. You might need to fully qualify the field names (using tablename.fieldname). Also feeds_processing_data could benefit from a composite index covering the involved fields.