Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6050

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

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.0.10
    • Fix Version/s: 10.0.13
    • Component/s: None
    • Labels:
      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

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              svoj Sergey Vojtovich
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: