• Dmitry Lenev's avatar
    A 5.1-only version of fix for bug #46947 "Embedded SELECT · 78c6a8ca
    Dmitry Lenev authored
    without FOR UPDATE is causing a lock".
    
    SELECT statements with subqueries referencing InnoDB tables
    were acquiring shared locks on rows in these tables when they
    were executed in REPEATABLE-READ mode and with statement or
    mixed mode binary logging turned on.
    
    This was a regression which were introduced when fixing
    bug 39843.
    
    The problem was that for tables belonging to subqueries
    parser set TL_READ_DEFAULT as a lock type. In cases when
    statement/mixed binary logging at open_tables() time this
    type of lock was converted to TL_READ_NO_INSERT lock at
    open_tables() time and caused InnoDB engine to acquire
    shared locks on reads from these tables. Although in some
    cases such behavior was correct (e.g. for subqueries in
    DELETE) in case of SELECT it has caused unnecessary locking.
    
    This patch implements minimal version of the fix for the
    specific problem described in the bug-report which supposed
    to be not too risky for pushing into 5.1 tree.
    The 5.5 tree already contains a more appropriate solution
    which also addresses other related issues like bug 53921
    "Wrong locks for SELECTs used stored functions may lead
    to broken SBR".
    
    This patch tries to solve the problem by ensuring that
    TL_READ_DEFAULT lock which is set in the parser for
    tables participating in subqueries at open_tables()
    time is interpreted as TL_READ_NO_INSERT or TL_READ.
    TL_READ is used only if we know that this is a SELECT
    and that this particular table is not used by a stored
    function.
    
    Test coverage is added for both InnoDB and MyISAM.
    
    This patch introduces an "incompatible" change in locking
    scheme for subqueries used in SELECT ... FOR UPDATE and
    SELECT .. IN SHARE MODE.
    
    In 4.1 (as well as in 5.0 and 5.1 before fix for bug 39843)
    the server would use a snapshot InnoDB read for subqueries
    in SELECT FOR UPDATE and SELECT .. IN SHARE MODE statements,
    regardless of whether the binary log is on or off.
    
    If the user required a different type of read (i.e. locking
    read), he/she could request so explicitly by providing FOR
    UPDATE/IN SHARE MODE clause for each individual subquery.
    
    The patch for bug 39843 broke this behaviour (which was not
    documented or tested), and started to use locking reads for
    all subqueries in SELECT ... FOR UPDATE/IN SHARE MODE.
    This patch restores 4.1 behaviour.
    
    This patch should be mostly null-merged into 5.5 tree.
    78c6a8ca
check_no_row_lock.inc 1.76 KB