• Dmitry Lenev's avatar
    Fix for bug #12641342 - "61401: UPDATE PERFORMANCE DEGRADES · 5b225518
    Dmitry Lenev authored
    GRADUALLY IF A TRIGGER EXISTS".
    
    This bug manifested itself in two ways:
    
    - Firstly execution of any data-changing statement which
      required prelocking (i.e. involved stored function or
      trigger) as part of transaction slowed down a bit all
      subsequent statements in this transaction. So performance
      in transaction which periodically involved such statements
      gradually degraded over time.
    - Secondly execution of any data-changing statement which
      required prelocking as part of transaction prevented
      concurrent FLUSH TABLES WITH READ LOCK from proceeding
      until the end of transaction instead of end of particular
      statement.
      
    The problem was caused by incorrect handling of metadata lock
    used in FTWRL implementation for statements requiring prelocked 
    mode. 
    Each statement which changes data acquires global IX lock
    with STATEMENT duration. This lock is supposed to block 
    concurrent FTWRL from proceeding until the statement ends.
    
    When entering prelocked mode, durations of all metadata locks
    acquired so far were changed to EXPLICIT, to prevent 
    substatements from releasing these locks. When prelocked mode
    was left, durations of metadata locks were changed to
    TRANSACTIONAL (with a few exceptions) so they can be properly
    released at the end of transaction. 
    Unfortunately, this meant that the global IX lock blocking
    FTWRL with STATEMENT duration was moved to TRANSACTIONAL
    duration after execution of statement requiring prelocking.
    
    Since each subsequent statement that required prelocking and
    tried to acquire global IX lock with STATEMENT duration got
    a new instance of MDL_ticket, which was later moved to
    TRANSACTIONAL duration, this led to unwarranted growth of
    number of tickets with TRANSACITONAL duration in this
    connection's MDL_context. As result searching for other
    tickets in it became slow and acquisition of other metadata
    locks by this transaction started to hog CPU.
    
    Moreover, this also meant that after execution of statement
    requiring prelocking concurrent FTWRL was blocked
    until the end of transaction instead of end of statement.
    
    This patch solves this problem by not moving locks to EXPLICIT
    duration when thread enters prelocked mode (unless it is a real 
    LOCK TABLES mode). This step turned out to be not really 
    necessary as substatements don't try to release metadata locks.
    Consequently, the global IX lock blocking FTWRL keeps its
    STATEMENT duration and is properly released at the end of
    statement and the above issue goes away.
    
    mysql-test/r/flush.result:
      Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
      DEGRADES GRADUALLY IF A TRIGGER EXISTS".
    mysql-test/t/flush.test:
      Added test for bug #12641342 - "61401: UPDATE PERFORMANCE
      DEGRADES GRADUALLY IF A TRIGGER EXISTS".
    sql/mdl.h:
      Added comment describing various types of metadata lock
      duration.
    sql/sql_class.cc:
      Since we no longer change duration of metadata locks to EXPLICIT
      when entering prelocked mode (unless it is a real LOCK TABLES)
      there is no need to restore proper duration of the locks when
      leaving prelocked mode.
    sql/sql_class.h:
      Do not change duration of metadata locks to EXPLICIT when
      entering prelocking mode (unless it is a real LOCK TABLES).
      This allows to avoid problems with restoring correct duration
      when leaving this mode. It is possible to do this as
      substatements won't release metadata locks in any case.
    sql/sql_parse.cc:
      Added assert checking that we won't release metadata locks
      when in substatement.
    5b225518
sql_parse.cc 224 KB