Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.10
-
None
-
None
Description
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.
|
Attachments
Issue Links
- is part of
-
MDEV-4784 merge test cases from 5.6
- Stalled