Bug #54584 query cache can lock DML threads if a reader is slow

• Submitted: 17 Jun 2010 14:06
• Modified: 7 Jul 2017 9:44
• Reporter: Axel Schwenke
• Status: Won't fix
• Category: MySQL Server: Query Cache
• Severity: S1 (Critical)
• Version: Any
• OS: Any
• CPU Architecture: Any

[17 Jun 2010 14:06] Axel Schwenke

Description:
Query_cache::send_result_to_client and Query_cache::invalidate_query_block_list compete for the lock on query_block. That effectively makes reading from the query cache and invalidating cache entries mutually exclusive.

If a read from cache is delayed, i.e. by a client loosing network connection, the read lock on the query_block will be held for some time (net_write_timeout will eventually kick in) and all DML operations for tables associated with the cached query will have to wait when they try to invalidate query cache entries.

The locked DML operations can cause a cascade of locks (i.e. via the auto-inc lock) and finally stop all server activity.

How to repeat:
see private comment

Suggested fix:
Invalidation of query cache entries should use a lock free approach. I.e. by marking invalidate entries and collecting them later.

Possible workaround: set a low query_cache_limit and high tcp send buffers. If a complete result fits into send buffers then this problem disappears.

[7 Jul 2017 9:44] Erlend Dahl

MySQL will no longer invest in the query cache, see:

http://mysqlserverteam.com/mysql-8-0-retiring-support-for-the-query-cache/