revno: 4053
committer: Gopal Shankar <gopal.shankar@oracle.com>
branch nick: sf_mysql-5.6
timestamp: Fri 2012-07-20 12:25:34 +0530
message:
  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.