• Dmitry Lenev's avatar
    Implement new type-of-operation-aware metadata locks. · afd15c43
    Dmitry Lenev authored
    Add a wait-for graph based deadlock detector to the
    MDL subsystem.
    
    Fixes bug #46272 "MySQL 5.4.4, new MDL: unnecessary deadlock" and
    bug #37346 "innodb does not detect deadlock between update and
    alter table".
    
    The first bug manifested itself as an unwarranted abort of a
    transaction with ER_LOCK_DEADLOCK error by a concurrent ALTER
    statement, when this transaction tried to repeat use of a
    table, which it has already used in a similar fashion before
    ALTER started.
    
    The second bug showed up as a deadlock between table-level
    locks and InnoDB row locks, which was "detected" only after
    innodb_lock_wait_timeout timeout.
    
    A transaction would start using the table and modify a few
    rows.
    Then ALTER TABLE would come in, and start copying rows
    into a temporary table. Eventually it would stumble on
    the modified records and get blocked on a row lock.
    The first transaction would try to do more updates, and get
    blocked on thr_lock.c lock.
    This situation of circular wait would only get resolved
    by a timeout.
    
    Both these bugs stemmed from inadequate solutions to the
    problem of deadlocks occurring between different
    locking subsystems.
    
    In the first case we tried to avoid deadlocks between metadata
    locking and table-level locking subsystems, when upgrading shared
    metadata lock to exclusive one.
    Transactions holding the shared lock on the table and waiting for
    some table-level lock used to be aborted too aggressively.
    
    We also allowed ALTER TABLE to start in presence of transactions
    that modify the subject table. ALTER TABLE acquires
    TL_WRITE_ALLOW_READ lock at start, and that block all writes
    against the table (naturally, we don't want any writes to be lost
    when switching the old and the new table). TL_WRITE_ALLOW_READ
    lock, in turn, would block the started transaction on thr_lock.c
    lock, should they do more updates. This, again, lead to the need
    to abort such transactions.
    
    The second bug occurred simply because we didn't have any
    mechanism to detect deadlocks between the table-level locks
    in thr_lock.c and row-level locks in InnoDB, other than
    innodb_lock_wait_timeout.
    
    This patch solves both these problems by moving lock conflicts
    which are causing these deadlocks into the metadata locking
    subsystem, thus making it possible to avoid or detect such
    deadlocks inside MDL.
    
    To do this we introduce new type-of-operation-aware metadata
    locks, which allow MDL subsystem to know not only the fact that
    transaction has used or is going to use some object but also what
    kind of operation it has carried out or going to carry out on the
    object.
    
    This, along with the addition of a special kind of upgradable
    metadata lock, allows ALTER TABLE to wait until all
    transactions which has updated the table to go away.
    This solves the second issue.
    Another special type of upgradable metadata lock is acquired
    by LOCK TABLE WRITE. This second lock type allows to solve the
    first issue, since abortion of table-level locks in event of
    DDL under LOCK TABLES becomes also unnecessary.
    
    Below follows the list of incompatible changes introduced by
    this patch:
    
    - From now on, ALTER TABLE and CREATE/DROP TRIGGER SQL (i.e. those
      statements that acquire TL_WRITE_ALLOW_READ lock)
      wait for all transactions which has *updated* the table to
      complete.
    
    - From now on, LOCK TABLES ... WRITE, REPAIR/OPTIMIZE TABLE
      (i.e. all statements which acquire TL_WRITE table-level lock) wait
      for all transaction which *updated or read* from the table
      to complete.
      As a consequence, innodb_table_locks=0 option no longer applies
      to LOCK TABLES ... WRITE.
    
    - DROP DATABASE, DROP TABLE, RENAME TABLE no longer abort
      statements or transactions which use tables being dropped or
      renamed, and instead wait for these transactions to complete.
    
    - Since LOCK TABLES WRITE now takes a special metadata lock,
      not compatible with with reads or writes against the subject table
      and transaction-wide, thr_lock.c deadlock avoidance algorithm
      that used to ensure absence of deadlocks between LOCK TABLES
      WRITE and other statements is no longer sufficient, even for
      MyISAM. The wait-for graph based deadlock detector of MDL
      subsystem may sometimes be necessary and is involved. This may
      lead to ER_LOCK_DEADLOCK error produced for multi-statement
      transactions even if these only use MyISAM:
    
      session 1:         session 2:
      begin;
    
      update t1 ...      lock table t2 write, t1 write;
                         -- gets a lock on t2, blocks on t1
    
      update t2 ...
      (ER_LOCK_DEADLOCK)
    
    - Finally,  support of LOW_PRIORITY option for LOCK TABLES ... WRITE
      was abandoned.
      LOCK TABLE ... LOW_PRIORITY WRITE from now on has the same
      priority as the usual LOCK TABLE ... WRITE.
      SELECT HIGH PRIORITY no longer trumps LOCK TABLE ... WRITE  in
      the wait queue.
    
    - We do not take upgradable metadata locks on implicitly
      locked tables. So if one has, say, a view v1 that uses
      table t1, and issues:
      LOCK TABLE v1 WRITE;
      FLUSH TABLE t1; -- (or just 'FLUSH TABLES'),
      an error is produced.
      In order to be able to perform DDL on a table under LOCK TABLES,
      the table must be locked explicitly in the LOCK TABLES list.
    afd15c43
debug_sync.result 11.8 KB
SET DEBUG_SYNC= 'RESET';
DROP TABLE IF EXISTS t1;
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: ''
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6           HIT_LIMIT 3';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2           EXECUTE 2 HIT_LIMIT 3';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2           EXECUTE 2';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2                     HIT_LIMIT 3';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2';
SET DEBUG_SYNC='p0 SIGNAL s1                       EXECUTE 2 HIT_LIMIT 3';
SET DEBUG_SYNC='p0 SIGNAL s1                       EXECUTE 2';
SET DEBUG_SYNC='p0 SIGNAL s1                                 HIT_LIMIT 3';
SET DEBUG_SYNC='p0 SIGNAL s1';
SET DEBUG_SYNC='p0           WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3';
SET DEBUG_SYNC='p0           WAIT_FOR s2 TIMEOUT 6 EXECUTE 2';
SET DEBUG_SYNC='p0           WAIT_FOR s2 TIMEOUT 6           HIT_LIMIT 3';
SET DEBUG_SYNC='p0           WAIT_FOR s2 TIMEOUT 6';
SET DEBUG_SYNC='p0           WAIT_FOR s2           EXECUTE 2 HIT_LIMIT 3';
SET DEBUG_SYNC='p0           WAIT_FOR s2           EXECUTE 2';
SET DEBUG_SYNC='p0           WAIT_FOR s2                     HIT_LIMIT 3';
SET DEBUG_SYNC='p0           WAIT_FOR s2';
SET DEBUG_SYNC='p0                                           HIT_LIMIT 3';
SET DEBUG_SYNC='p0 CLEAR';
SET DEBUG_SYNC='p0 TEST';
SET DEBUG_SYNC='RESET';
set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2 hit_limit 3';
set debug_sync='p0 signal s1 wait_for s2 timeout 6 execute 2';
set debug_sync='p0 signal s1 wait_for s2 timeout 6           hit_limit 3';
set debug_sync='p0 signal s1 wait_for s2 timeout 6';
set debug_sync='p0 signal s1 wait_for s2           execute 2 hit_limit 3';
set debug_sync='p0 signal s1 wait_for s2           execute 2';
set debug_sync='p0 signal s1 wait_for s2                     hit_limit 3';
set debug_sync='p0 signal s1 wait_for s2';
set debug_sync='p0 signal s1                       execute 2 hit_limit 3';
set debug_sync='p0 signal s1                       execute 2';
set debug_sync='p0 signal s1                                 hit_limit 3';
set debug_sync='p0 signal s1';
set debug_sync='p0           wait_for s2 timeout 6 execute 2 hit_limit 3';
set debug_sync='p0           wait_for s2 timeout 6 execute 2';
set debug_sync='p0           wait_for s2 timeout 6           hit_limit 3';
set debug_sync='p0           wait_for s2 timeout 6';
set debug_sync='p0           wait_for s2           execute 2 hit_limit 3';
set debug_sync='p0           wait_for s2           execute 2';
set debug_sync='p0           wait_for s2                     hit_limit 3';
set debug_sync='p0           wait_for s2';
set debug_sync='p0                                           hit_limit 3';
set debug_sync='p0 clear';
set debug_sync='p0 test';
set debug_sync='reset';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6
                EXECUTE 2 HIT_LIMIT 3';
SET DEBUG_SYNC='   p0 SIGNAL s1 WAIT_FOR s2';
SET DEBUG_SYNC='p0    SIGNAL    s1    WAIT_FOR    s2';
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2   ';
SET DEBUG_SYNC='   p0 SIGNAL s1 WAIT_FOR s2   ';
SET DEBUG_SYNC='   p0    SIGNAL    s1    WAIT_FOR    s2   ';
SET DEBUG_SYNC='';
ERROR 42000: Missing synchronization point name
SET DEBUG_SYNC=' ';
ERROR 42000: Missing synchronization point name
SET DEBUG_SYNC='p0';
ERROR 42000: Missing action after synchronization point name 'p0'
SET DEBUG_SYNC='p0                                          EXECUTE 2';
ERROR 42000: Missing action before EXECUTE
SET DEBUG_SYNC='p0                                TIMEOUT 6 EXECUTE 2';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0                                TIMEOUT 6';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1';
ERROR 42000: Illegal or out of order stuff: 'SIGNAL'
SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1           EXECUTE 2';
ERROR 42000: Illegal or out of order stuff: 'SIGNAL'
SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6 EXECUTE 2';
ERROR 42000: Illegal or out of order stuff: 'SIGNAL'
SET DEBUG_SYNC='p0 WAIT_FOR s2 SIGNAL s1 TIMEOUT 6';
ERROR 42000: Illegal or out of order stuff: 'SIGNAL'
SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1 EXECUTE 2';
ERROR 42000: Illegal or out of order stuff: 'SIGNAL'
SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 SIGNAL s1';
ERROR 42000: Illegal or out of order stuff: 'SIGNAL'
SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2 EXECUTE 2';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0 TIMEOUT 6 WAIT_FOR s2';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0                  SIGNAL s1 TIMEOUT 6 EXECUTE 2';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0                  SIGNAL s1 TIMEOUT 6';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0 EXECUTE 2 SIGNAL s1 TIMEOUT 6';
ERROR 42000: Missing action before EXECUTE
SET DEBUG_SYNC='p0 TIMEOUT 6 SIGNAL s1';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUT'
SET DEBUG_SYNC='p0 EXECUTE 2 TIMEOUT 6 SIGNAL s1';
ERROR 42000: Missing action before EXECUTE
SET DEBUG_SYNC='p0 CLEAR HIT_LIMIT 3';
ERROR 42000: Nothing must follow action CLEAR
SET DEBUG_SYNC='CLEAR';
ERROR 42000: Missing action after synchronization point name 'CLEAR'
SET DEBUG_SYNC='p0 CLEAR p0';
ERROR 42000: Nothing must follow action CLEAR
SET DEBUG_SYNC='TEST';
ERROR 42000: Missing action after synchronization point name 'TEST'
SET DEBUG_SYNC='p0 TEST p0';
ERROR 42000: Nothing must follow action TEST
SET DEBUG_SYNC='p0 RESET';
ERROR 42000: Illegal or out of order stuff: 'RESET'
SET DEBUG_SYNC='RESET p0';
ERROR 42000: Illegal or out of order stuff: 'p0'
SET DEBUG_SYNC='p0 RESET p0';
ERROR 42000: Illegal or out of order stuff: 'RESET'
SET DEBUG_SYNC='p0 SIGNAL ';
ERROR 42000: Missing signal name after action SIGNAL
SET DEBUG_SYNC='p0 WAIT_FOR ';
ERROR 42000: Missing signal name after action WAIT_FOR
SET DEBUG_SYNC='p0 SIGNAL s1 EXECUTE ';
ERROR 42000: Missing valid number after EXECUTE
SET DEBUG_SYNCx='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3';
ERROR HY000: Unknown system variable 'DEBUG_SYNCx'
SET DEBUG_SYNC='p0 SIGNAx s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3';
ERROR 42000: Illegal or out of order stuff: 'SIGNAx'
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOx s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIT 3';
ERROR 42000: Illegal or out of order stuff: 'WAIT_FOx'
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUx 0 EXECUTE 2 HIT_LIMIT 3';
ERROR 42000: Illegal or out of order stuff: 'TIMEOUx'
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTx 2 HIT_LIMIT 3';
ERROR 42000: Illegal or out of order stuff: 'EXECUTx'
SET DEBUG_SYNC='p0 SIGNAL s1 WAIT_FOR s2 TIMEOUT 6 EXECUTE 2 HIT_LIMIx 3';
ERROR 42000: Illegal or out of order stuff: 'HIT_LIMIx'
SET DEBUG_SYNC='p0 CLEARx';
ERROR 42000: Illegal or out of order stuff: 'CLEARx'
SET DEBUG_SYNC='p0 TESTx';
ERROR 42000: Illegal or out of order stuff: 'TESTx'
SET DEBUG_SYNC='RESETx';
ERROR 42000: Missing action after synchronization point name 'RESETx'
SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 0x6 EXECUTE 2 HIT_LIMIT 3';
ERROR 42000: Missing valid number after TIMEOUT
SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 6 EXECUTE 0x2 HIT_LIMIT 3';
ERROR 42000: Missing valid number after EXECUTE
SET DEBUG_SYNC='p0 WAIT_FOR s2 TIMEOUT 7 EXECUTE 2 HIT_LIMIT 0x3';
ERROR 42000: Missing valid number after HIT_LIMIT
SET DEBUG_SYNC= 7;
ERROR 42000: Incorrect argument type to variable 'debug_sync'
SET GLOBAL DEBUG_SYNC= 'p0 CLEAR';
ERROR HY000: Variable 'debug_sync' is a SESSION variable and can't be used with SET GLOBAL
SET @myvar= 'now SIGNAL from_myvar';
SET DEBUG_SYNC= @myvar;
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 'from_myvar'
SET DEBUG_SYNC= LEFT('now SIGNAL from_function_cut_here', 24);
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 'from_function'
SET DEBUG_SYNC= 'now SIGNAL something';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 'something'
SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0';
Warnings:
Warning	####	debug sync point wait timed out
SET DEBUG_SYNC= 'now SIGNAL nothing';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 'nothing'
SET DEBUG_SYNC= 'now WAIT_FOR nothing TIMEOUT 0';
SET DEBUG_SYNC= 'now SIGNAL something EXECUTE 0';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 'nothing'
SET DEBUG_SYNC= 'now WAIT_FOR anotherthing TIMEOUT 0 EXECUTE 0';
SET DEBUG_SYNC= 'now HIT_LIMIT 1';
ERROR HY000: debug sync point hit limit reached
SET DEBUG_SYNC= 'RESET';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: ''
SET DEBUG_SYNC= 'p1abcd   SIGNAL s1 EXECUTE 2';
SET DEBUG_SYNC= 'p2abc    SIGNAL s2 EXECUTE 2';
SET DEBUG_SYNC= 'p9abcdef SIGNAL s9 EXECUTE 2';
SET DEBUG_SYNC= 'p4a      SIGNAL s4 EXECUTE 2';
SET DEBUG_SYNC= 'p5abcde  SIGNAL s5 EXECUTE 2';
SET DEBUG_SYNC= 'p6ab     SIGNAL s6 EXECUTE 2';
SET DEBUG_SYNC= 'p7       SIGNAL s7 EXECUTE 2';
SET DEBUG_SYNC= 'p8abcdef SIGNAL s8 EXECUTE 2';
SET DEBUG_SYNC= 'p3abcdef SIGNAL s3 EXECUTE 2';
SET DEBUG_SYNC= 'p4a      TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's4'
SET DEBUG_SYNC= 'p1abcd   TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's1'
SET DEBUG_SYNC= 'p7       TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's7'
SET DEBUG_SYNC= 'p9abcdef TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's9'
SET DEBUG_SYNC= 'p3abcdef TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's3'
SET DEBUG_SYNC= 'p1abcd   CLEAR';
SET DEBUG_SYNC= 'p2abc    CLEAR';
SET DEBUG_SYNC= 'p5abcde  CLEAR';
SET DEBUG_SYNC= 'p6ab     CLEAR';
SET DEBUG_SYNC= 'p8abcdef CLEAR';
SET DEBUG_SYNC= 'p9abcdef CLEAR';
SET DEBUG_SYNC= 'p3abcdef CLEAR';
SET DEBUG_SYNC= 'p4a      CLEAR';
SET DEBUG_SYNC= 'p7       CLEAR';
SET DEBUG_SYNC= 'p1abcd   TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's3'
SET DEBUG_SYNC= 'p7       TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's3'
SET DEBUG_SYNC= 'p9abcdef TEST';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: 's3'
SET DEBUG_SYNC= 'RESET';
SHOW VARIABLES LIKE 'DEBUG_SYNC';
Variable_name	Value
debug_sync	ON - current signal: ''
CREATE USER mysqltest_1@localhost;
GRANT SUPER ON *.* TO mysqltest_1@localhost;
connection con1, mysqltest_1
SET DEBUG_SYNC= 'RESET';
connection default
DROP USER mysqltest_1@localhost;
CREATE USER mysqltest_2@localhost;
GRANT ALL ON *.* TO mysqltest_2@localhost;
REVOKE SUPER ON *.* FROM mysqltest_2@localhost;
connection con1, mysqltest_2
SET DEBUG_SYNC= 'RESET';
ERROR 42000: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
connection default
DROP USER mysqltest_2@localhost;
SET DEBUG_SYNC= 'RESET';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 INT);
connection con1
SET DEBUG_SYNC= 'before_lock_tables_takes_lock
      SIGNAL opened WAIT_FOR flushed';
INSERT INTO t1 VALUES(1);
connection default
SET DEBUG_SYNC= 'now WAIT_FOR opened';
SET DEBUG_SYNC= 'after_flush_unlock SIGNAL flushed';
FLUSH TABLE t1;
connection con1
connection default
DROP TABLE t1;
SET DEBUG_SYNC= 'RESET';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (c1 INT);
LOCK TABLE t1 READ;
connection con1
SET DEBUG_SYNC= 'wait_for_lock SIGNAL locked EXECUTE 2';
INSERT INTO t1 VALUES (1);
connection default
SET DEBUG_SYNC= 'now WAIT_FOR locked';
UNLOCK TABLES;
connection con1
retrieve INSERT result.
connection default
DROP TABLE t1;
SET DEBUG_SYNC= 'RESET';