List:Commits« Previous MessageNext Message »
From:Gopal Shankar Date:July 20 2012 7:17am
Subject:bzr push into mysql-trunk branch (gopal.shankar:4095 to 4096) Bug#13036505
View as plain text  
 4096 Gopal Shankar	2012-07-20 [merge]
      Null merge from 5.6 for Bug#13036505

 4095 Gopal Shankar	2012-07-20
      Bug#13036505 62540: TABLE LOCKS WITHIN STORED FUNCTIONS ARE BACK IN
                          5.5 WITH MIXED AND ROW BI.
      
      Problem:-
      -------
      SELECT/SET/DO statements that used stored functions but didn't change
      any data acquired too strong locks on tables used (i.e. read) in these
      functions if binary logging was on and used statement or mixed mode.
      For MyISAM tables this resulted in that concurrent insert to such
      tables were blocked while such a statement was running. For InnoDB
      tables such statements were using locking reads (and as result blocked
      any concurrent changes and SELECT ... FOR UPDATE statements) instead
      of using snapshot isolation.
      
      Analysis:
      --------
      Due to a statement-based replication limitation, statements such as
      INSERT INTO .. SELECT FROM .. and CREATE TABLE .. SELECT FROM need
      to grab a TL_READ_NO_INSERT lock on the source table in order to
      prevent the replication of a concurrent statement that modifies the
      source table. If such a statement gets applied on the slave before
      the INSERT .. SELECT statement finishes, data on the master could
      differ from data on the slave and end-up with a discrepancy between
      the binary log and table state.
      
      This also applies to SELECT/SET/DO statements which use stored
      functions. Calls to such functions are going to be logged as a
      whole and thus should be serialized against concurrent changes
      to tables used by those functions. The current implementation
      does not check if functions only read data and won't be written
      into binary log as result. Currently we use TL_READ_NO_INSERT lock
      for all tables used by stored functions called from SELECTs if
      binary logging is on and uses statement or mixed mode.
      
      Note that even though InnoDB engine does its own locking it still
      relies on thr_lock.c locks set by SQL-layer to infer type of
      row-locks to acquire when reading data. Since TL_READ_NO_INSERT
      is translated to locking reads in InnoDB the above means that
      SELECT/SET/DO that uses stored functions will do locking reads
      on tables used by routines.
      
      We can use weaker type of lock TL_READ, which will allow concurrent
      inserts, if a statement only reads data (since such a statement
      won't get into binary log anyway). For InnoDB this lock will be
      translated to non-locking, snapshot reads (unless in serializable
      mode).
      
      Fix:-
      ---
      If we know that all stored routines which are going to be called by
      statement do not change tables we can choose weaker TL_READ lock for
      tables used by it.
      
      If some substatement which is added to sp_head modifies data, then
      we set this flag. This flag is used in read_lock_type_for_table()
      to choose weaker TL_READ lock for tables.
      
      Currently open_tables() processes a loop, where it initially
      processes known tables and routines, and further in each iteration
      it processes new tables and routines eventually found. Currently
      the lock upgrade happens within the loop where complete
      table/routine list for statement is not known, within which
      we cannot decide if lock upgrade is needed or to be ignored.
      This fix handles lock upgrade upon loop completion. When the
      complete list of tables get prepared, we check if there were
      any SF which write data, if not, we ignore upgrading lock.
      
      Test case:-
      --------
      main.lock_sync and main.innodb_mysql_lock2 test most of above
      mentioned scenario's. These tests have been updated according
      to new behavior. A new test case has been added to test
      SF which modifies temporary table.

    modified:
      mysql-test/r/innodb_mysql_lock2.result
      mysql-test/r/lock_sync.result
      mysql-test/t/innodb_mysql_lock2.test
      mysql-test/t/lock_sync.test
      sql/sp_head.cc
      sql/sp_head.h
      sql/sql_base.cc
      sql/sql_base.h
      sql/sql_update.cc
No bundle (reason: useless for push emails).
Thread
bzr push into mysql-trunk branch (gopal.shankar:4095 to 4096) Bug#13036505Gopal Shankar20 Jul