MySQL Bugs: #52117: Pending FLUSH TALBES <list> aborts transactions unnecessarily.

Bug #52117	Pending FLUSH TALBES <list> aborts transactions unnecessarily.
Submitted:	16 Mar 2010 21:27	Modified:	8 Sep 2010 2:08
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S3 (Non-critical)
Version:	5.5.3-m3	OS:	Any
Assigned to:	Konstantin Osipov	CPU Architecture:	Any

Description:
Pending FLUSH TABLES <list> aborts transactions unnecessarily.
The test case is engine-independent.

How to repeat:
--disable_warnings
drop table if exists t1;
--enable_warnings
connect (con1,localhost,root,,);
connect (con2,localhost,root,,);
--echo # --> conection default
connection default;

create table t1 (a int);
begin;
select * from t1;
--echo # --> connection con1
connection con1;
--echo # 
--echo # Issue a LOCK TABLE t1 READ. We could use HANDLER t1 OPEN
--echo # or a long-running select -- anything that
--echo # prevents FLUSH TABLE t1 from immediate completion would do.
--echo #
lock table t1 read;
--echo # --> connection con2
connection con2;
--echo #
--echo # FLUSH TABLE expels the table definition from the cache.
--echo # If there is a new table definition on disk, the started
--echo # transaction may subsequently read it, and thus have
--echo # its repeatable read isolation broken. This, subsequently,
--echo # may lead to broken statement-based replication.
--echo # FLUSH TABLE, therefore, must wait for open transactions to 
--echo # complete.
--echo # Sending 'flush table t1'...
send flush table t1;
--echo # --> connection default
connection default;
--echo # Let flush tables sync in.
let $wait_condition=
  select count(*) = 1 from information_schema.processlist
  where state = "Flushing tables"
  and info = "flush table t1";
--source include/wait_condition.inc
--echo # Bug! select * from t1 should be able to continue.
--error ER_LOCK_DEADLOCK
select * from t1;
--echo # --> connection con1 
connection con1;
unlock tables;
--echo # --> connection con2 
connection con2;
--echo # Sic: can reap 'flush table t1' even though the transaction
--echo # is not yet finished.
--echo # Reaping 'flush table t1'...
reap;

--echo # Cleanup

--echo # --> connection con1
connection con1;
disconnect con1;
--source include/wait_until_disconnected.inc
--echo # --> connection con2
connection con2;
disconnect con2;
--source include/wait_until_disconnected.inc
--echo # --> connection default
connection default;
drop table t1;

Suggested fix:
FLUSH TABLE <list>, like FLUSH TABLE <list> WITH READ LOCK, should fist take
exclusive metadata locks on the table, and thus wait for started transactions to complete.

This bug needs to be fixed in order to implement the cached object hook 
optimization.

Thank you for the bug report.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103640

2997 Konstantin Osipov	2010-03-18
      A fix and a test case for Bug#52117 "Pending FLUSH TALBES <list> 
      aborts transactions unnecessarily".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/103716

2997 Konstantin Osipov	2010-03-18
      A fix and a test case for Bug#52117 "Pending FLUSH TALBES <list> 
      aborts transactions unnecessarily".
      
      Fix a crash in FLUSH TABLES <list> WITH READ LOCK
      when the connection issuing the flush had open SQL HANDLERs.
     @ mysql-test/r/debug_sync.result
        FLUSH TABLES <list> never hits after_flush_unlock sync point.
     @ mysql-test/r/flush.result
        Add a test case for Bug#52117.
     @ mysql-test/r/lock_multi.result
        FLUSH TALBE <list> now waits for transactions to complete.
     @ mysql-test/t/debug_sync.test
        FLUSH TABLE <list> no longer hits after_flush_unlock debug
        sync point.
     @ mysql-test/t/flush.test
        Add a test case for Bug#52117.
     @ mysql-test/t/lock_multi.test
        FLUSH TABLE <list> now waits for transactions.
     @ sql/mysql_priv.h
        - reload_acl_and_cache() no longer accepts a table list.
        - add declaraction for mysql_ha_flush_tables() function,
        that closes open HANDLER objects, if there are any, for a given
        list of tables.
        - rename flush_tables() to flush_unused_tables().
     @ sql/mysqld.cc
        - reload_acl_and_cache() no longer accepts TABLE_LIST.
     @ sql/sql_base.cc
        Rename flush_tables() to flush_unused_tables() to avoid
        confusion with FLUSH TABLES statement implementation.
     @ sql/sql_handler.cc
        Implement mysql_ha_flush_tables() function.
     @ sql/sql_manager.cc
        Rename flush_tables() to flush_unused_tables().
     @ sql/sql_parse.cc
        Implement a new version of FLUSH TABLES <list> that is
        transaction-aware.
     @ sql/sql_show.cc
        An unrelated fix: make sure we take SHARED locks in
        mysqld_list_fields() (COM_FIELD_LIST implementation).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/115687

3107 Konstantin Osipov	2010-08-13
      Bug#52117 "Pending FLUSH TALBES <list> aborts transactions unnecessarily"
      The bug was fixed by the patch for Bug 52044.
      Add a test case.
     @ mysql-test/r/flush.result
        Update results (Bug#52117)
     @ mysql-test/t/flush.test
        Add a test case for Bug#52117 "Pending FLUSH TALBES <list> aborts
        transactions unnecessarily"

Queued the test case into 5.5-runtime.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/116013

3108 Konstantin Osipov	2010-08-18
      A follow-up for the patch for Bug#52117: split and reorganize 
      reload_acl_from_cache() and close_cached_tables().
      
      Move the logic responsible for table flush from
      reload_acl_and_cache().
      Reorganize code of SQLCOM_FLUSH.
      Add comments.
     @ sql/mysqld.cc
        Call close_cached_tables() explicitly now,
        when reload_acl_and_cache() no longer calls it.
     @ sql/sql_parse.cc
        Reorganize execution of COM_REFRESH and SQLCOM_FLUSH.
        Changes in behavior: we no longer flush the
        database options cache or query cache
        in FLUSH TABLES WITH READ LOCK. This was
        not documented, and is apparently a bug
        or an omission. 
        FLUSH TABLES continues to flush the query
        cache, as documented, and the database options
        cache.
     @ sql/sql_reload.cc
        FLUSH TABLES WITH GLOBAL READ LOCK used to also flush the
        query cache. This not documented, and seems to have been a bug, 
        thus is no longer done.
     @ sql/sql_reload.h
        Add declarations for new functions.

Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)

Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)

Noted in 5.5.6, 5.6.1 changelogs.

A pending FLUSH TABLES tbl_list WITH READ LOCK statement
unnecessarily aborted transactions.