Status: Confirmed (View Workflow)
Affects Version/s: 10.1.21, 5.5, 10.0, 10.1, 10.2, 10.3, 10.4, 10.1.34
Environment:for example Linux version 2.6.32-696.10.1.el6.x86_64 (firstname.lastname@example.org) (gcc version 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC) ) #1 SMP Tue Aug 22 18:51:35 UTC 2017
Transactions started while the database is waiting to finish a "FLUSH TABLES WITH READ LOCK" are still blocked, when the FLUSH command hits a timeout. In our environment this happens as follows:
select * from a; # this is a very long running statement, 1800 seconds for example
SET lock_wait_timeout = 10;
flush tables with read lock; # this blocks due to session 1
select * from a where a.x=1; # this is executed, while session 2 is waiting in the flush tables
After 10 seconds the session 2 gets a timeout. We then close the connection. But now session 3 is still blocked. The list of running statements shows, that it is "waiting for table flush". Once session 1 returns, session 3 continues. This is the behavior we see with version 10.1.34.
In our live environment we see a slightly different behavior of session 3: Instead of waiting for session 1 to finish, it continues after exactly 600 seconds (our slow query log is full with such statements started shortly after we started "flush tables with read lock"). In our live system we are using version 10.1.21.
In the slow query log we see one more interesting aspect. The relevant line reads:
# Query_time: 601.052932 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0
It claims that the lock time was 0, so what did the query do all those 600 seconds?
While our production tables have lots of indices we were able to reproduce it with a newly created table:
create table a select id from b
So we just had a table with lots of integers without indices. Still the same behavior.
Is there any way how we can tell session 3 to continue?