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.
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.
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
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.
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.
No bundle (reason: useless for push emails).
|• bzr push into mysql-trunk branch (gopal.shankar:4095 to 4096) Bug#13036505||Gopal Shankar||20 Jul|