MySQL Bug#13036505 62540: TABLE LOCKS WITHIN STORED FUNCTIONS ARE BACK IN 5.5 WITH MIXED AND ROW BI.

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.