• Konstantin Osipov's avatar
    Backport of revno ## 2617.31.1, 2617.31.3, 2617.31.4, 2617.31.5, · 709f8b5b
    Konstantin Osipov authored
    2617.31.12, 2617.31.15, 2617.31.15, 2617.31.16, 2617.43.1
    - initial changeset that introduced the fix for 
    Bug#989 and follow up fixes for all test suite failures
    introduced in the initial changeset. 
    ------------------------------------------------------------
    revno: 2617.31.1
    committer: Davi Arnaut <Davi.Arnaut@Sun.COM>
    branch nick: 4284-6.0
    timestamp: Fri 2009-03-06 19:17:00 -0300
    message:
    Bug#989: If DROP TABLE while there's an active transaction, wrong binlog order
    WL#4284: Transactional DDL locking
    
    Currently the MySQL server does not keep metadata locks on
    schema objects for the duration of a transaction, thus failing
    to guarantee the integrity of the schema objects being used
    during the transaction and to protect then from concurrent
    DDL operations. This also poses a problem for replication as
    a DDL operation might be replicated even thought there are
    active transactions using the object being modified.
    
    The solution is to defer the release of metadata locks until
    a active transaction is either committed or rolled back. This
    prevents other statements from modifying the table for the
    entire duration of the transaction. This provides commitment
    ordering for guaranteeing serializability across multiple
    transactions.
    
    - Incompatible change:
    
    If MySQL's metadata locking system encounters a lock conflict,
    the usual schema is to use the try and back-off technique to
    avoid deadlocks -- this schema consists in releasing all locks
    and trying to acquire them all in one go.
    
    But in a transactional context this algorithm can't be utilized
    as its not possible to release locks acquired during the course
    of the transaction without breaking the transaction commitments.
    To avoid deadlocks in this case, the ER_LOCK_DEADLOCK will be
    returned if a lock conflict is encountered during a transaction.
    
    Let's consider an example:
    
    A transaction has two statements that modify table t1, then table
    t2, and then commits. The first statement of the transaction will
    acquire a shared metadata lock on table t1, and it will be kept
    utill COMMIT to ensure serializability.
    
    At the moment when the second statement attempts to acquire a
    shared metadata lock on t2, a concurrent ALTER or DROP statement
    might have locked t2 exclusively. The prescription of the current
    locking protocol is that the acquirer of the shared lock backs off
    -- gives up all his current locks and retries. This implies that
    the entire multi-statement transaction has to be rolled back.
    
    - Incompatible change:
    
    FLUSH commands such as FLUSH PRIVILEGES and FLUSH TABLES WITH READ
    LOCK won't cause locked tables to be implicitly unlocked anymore.
    
    
    mysql-test/extra/binlog_tests/drop_table.test:
      Add test case for Bug#989.
    mysql-test/extra/binlog_tests/mix_innodb_myisam_binlog.test:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction.
    mysql-test/include/mix1.inc:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction.
    mysql-test/include/mix2.inc:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction.
    mysql-test/r/flush_block_commit.result:
      Update test case result (WL#4284).
    mysql-test/r/flush_block_commit_notembedded.result:
      Update test case result (WL#4284).
    mysql-test/r/innodb.result:
      Update test case result (WL#4284).
    mysql-test/r/innodb_mysql.result:
      Update test case result (WL#4284).
    mysql-test/r/lock.result:
      Add test case result for an effect of WL#4284/Bug#989
      (all locks should be released when a connection terminates).
    mysql-test/r/mix2_myisam.result:
      Update test case result (effects of WL#4284/Bug#989).
    mysql-test/r/not_embedded_server.result:
      Update test case result (effects of WL#4284/Bug#989).
      Add a test case for interaction of WL#4284 and FLUSH PRIVILEGES.
    mysql-test/r/partition_innodb_semi_consistent.result:
      Update test case result (effects of WL#4284/Bug#989).
    mysql-test/r/partition_sync.result:
      Temporarily disable the test case for Bug#43867,
      which will be fixed by a subsequent backport.
    mysql-test/r/ps.result:
      Add a test case for effect of PREPARE on transactional
      locks: we take a savepoint at beginning of PREAPRE
      and release it at the end. Thus PREPARE does not 
      accumulate metadata locks (Bug#989/WL#4284).
    mysql-test/r/read_only_innodb.result:
      Update test case result (effects of WL#4284/Bug#989).
    mysql-test/suite/binlog/r/binlog_row_drop_tbl.result:
      Add a test case result (WL#4284/Bug#989).
    mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result:
      Update test case result (effects of WL#4284/Bug#989).
    mysql-test/suite/binlog/r/binlog_stm_drop_tbl.result:
      Add a test case result (WL#4284/Bug#989).
    mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result:
      Update test case result (effects of WL#4284/Bug#989).
    mysql-test/suite/binlog/r/binlog_unsafe.result:
      A side effect of Bug#989 -- slightly different table map ids.
    mysql-test/suite/binlog/t/binlog_row_drop_tbl.test:
      Add a test case for WL#4284/Bug#989.
    mysql-test/suite/binlog/t/binlog_stm_drop_tbl.test:
      Add a test case for WL#4284/Bug#989.
    mysql-test/suite/binlog/t/binlog_stm_row.test:
      Update to the new state name. This
      is actually a follow up to another patch for WL#4284, 
      that changes Locked thread state to Table lock.
    mysql-test/suite/ndb/r/ndb_index_ordered.result:
      Remove result for disabled part of the test case.
    mysql-test/suite/ndb/t/disabled.def:
      Temporarily disable a test case (Bug#45621).
    mysql-test/suite/ndb/t/ndb_index_ordered.test:
      Disable a part of a test case (needs update to
      reflect semantics of Bug#989).
    mysql-test/suite/rpl/t/disabled.def:
      Disable tests made meaningless by transactional metadata
      locking.
    mysql-test/suite/sys_vars/r/autocommit_func.result:
      Add a commit (Bug#989).
    mysql-test/suite/sys_vars/t/autocommit_func.test:
      Add a commit (Bug#989).
    mysql-test/t/flush_block_commit.test:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction.
    mysql-test/t/flush_block_commit_notembedded.test:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction.
      Add a test case for transaction-scope locks and the global
      read lock (Bug#989/WL#4284).
    mysql-test/t/innodb.test:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction
      (effects of Bug#989/WL#4284).
    mysql-test/t/lock.test:
      Add a test case for Bug#989/WL#4284.
    mysql-test/t/not_embedded_server.test:
      Add a test case for Bug#989/WL#4284.
    mysql-test/t/partition_innodb_semi_consistent.test:
      Replace TRUNCATE with DELETE, to not issue
      an implicit commit of a transaction, and not depend
      on metadata locks.
    mysql-test/t/partition_sync.test:
      Temporarily disable the test case for Bug#43867,
      which needs a fix to be backported from 6.0.
    mysql-test/t/ps.test:
      Add a test case for semantics of PREPARE and transaction-scope
      locks: metadata locks on tables used in PREPARE are enclosed into a
      temporary savepoint, taken at the beginning of PREPARE,
      and released at the end. Thus PREPARE does not effect
      what locks a transaction owns.
    mysql-test/t/read_only_innodb.test:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction 
      (Bug#989/WL#4284).
      
      Wait for the read_only statement to actually flush tables before
      sending other concurrent statements that depend on its state.
    mysql-test/t/xa.test:
      Fix test case to reflect the fact that transactions now hold
      metadata locks for the duration of a transaction 
      (Bug#989/WL#4284).
    sql/ha_ndbcluster_binlog.cc:
      Backport bits of changes of ha_ndbcluster_binlog.cc
      from 6.0, to fix the failing binlog test suite with
      WL#4284. WL#4284 implementation does not work
      with 5.1 implementation of ndbcluster binlog index.
    sql/log_event.cc:
      Release metadata locks after issuing a commit.
    sql/mdl.cc:
      Style changes (WL#4284).
    sql/mysql_priv.h:
      Rename parameter to match the name used in the definition (WL#4284).
    sql/rpl_injector.cc:
      Release metadata locks on commit (WL#4284).
    sql/rpl_rli.cc:
      Remove assert made meaningless, metadata locks are released
      at the end of the transaction.
    sql/set_var.cc:
      Close tables and release locks if autocommit mode is set.
    sql/slave.cc:
      Release metadata locks after a rollback.
    sql/sql_acl.cc:
      Don't implicitly unlock locked tables. Issue a implicit commit
      at the end and unlock tables.
    sql/sql_base.cc:
      Defer the release of metadata locks when closing tables
      if not required to.
      Issue a deadlock error if the locking protocol requires
      that a transaction re-acquire its locks.
      
      Release metadata locks when closing tables for reopen.
    sql/sql_class.cc:
      Release metadata locks if the thread is killed.
    sql/sql_parse.cc:
      Release metadata locks after implicitly committing a active
      transaction, or after explicit commits or rollbacks.
    sql/sql_plugin.cc:
      
      Allocate MDL request on the stack as the use of the table
      is contained within the function. It will be removed from
      the context once close_thread_tables is called at the end
      of the function.
    sql/sql_prepare.cc:
      The problem is that the prepare phase of the CREATE TABLE
      statement takes a exclusive metadata lock lock and this can
      cause a self-deadlock the thread already holds a shared lock
      on the table being that should be created.
      
      The solution is to make the prepare phase take a shared
      metadata lock when preparing a CREATE TABLE statement. The
      execution of the statement will still acquire a exclusive
      lock, but won't cause any problem as it issues a implicit
      commit.
      
      After some discussions with stakeholders it has been decided that
      metadata locks acquired during a PREPARE statement must be released
      once the statement is prepared even if it is prepared within a multi
      statement transaction.
    sql/sql_servers.cc:
      Don't implicitly unlock locked tables. Issue a implicit commit
      at the end and unlock tables.
    sql/sql_table.cc:
      Close table and release metadata locks after a admin operation.
    sql/table.h:
      The problem is that the prepare phase of the CREATE TABLE
      statement takes a exclusive metadata lock lock and this can
      cause a self-deadlock the thread already holds a shared lock
      on the table being that should be created.
      
      The solution is to make the prepare phase take a shared
      metadata lock when preparing a CREATE TABLE statement. The
      execution of the statement will still acquire a exclusive
      lock, but won't cause any problem as it issues a implicit
      commit.
    sql/transaction.cc:
      Release metadata locks after the implicitly committed due
      to a new transaction being started. Also, release metadata
      locks acquired after a savepoint if the transaction is rolled
      back to the save point.
      
      The problem is that in some cases transaction-long metadata locks
      could be released before the transaction was committed. This could
      happen when a active transaction was ended by a "START TRANSACTION"
      or "BEGIN" statement, in which case the metadata locks would be
      released before the actual commit of the active transaction.
      
      The solution is to defer the release of metadata locks to after the
      transaction has been implicitly committed. No test case is provided
      as the effort to provide one is too disproportional to the size of
      the fix.
    709f8b5b
partition_innodb_semi_consistent.test 4.06 KB