Skip to content
  • Libing Song's avatar
    61e2172a
    BUG#20136704 --SLAVE-PRESERVE-COMMIT-ORDER CAUSES SLAVE TO DEADLOCK AND · 61e2172a
    Libing Song authored
                 BREAK FOR SOME QUERIE
    
    A corner case caused slave hang when slave_preserve_commit_order is ON.
    
    ANALYSIS
    ========
    CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
    INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
    
    INSERT INTO t1 VALUES(7, NULL);
    DELETE FROM t1 WHERE c2 <= 3;
    
    On master, the INSERT statement acquires its row lock before DELETE STATEMENT.
    Since the INSERT doesn't block the DELETE, Both statements may have same
    commit parent which mean they can be applied parallel on slave.
    
    On slave, they will be applied parallel if MTS is ON. There is a chance that
    the DELETE acquires its lock before the INSERT. The DELETE holds a gap lock
    on INDEX(c2) which blocks the INSERT statement. The INSERT cannot be applied
    unless DELETE is committed or rolled back. And meanwhile the DELETE is waiting
    for the INSERT to commit since slave_preserve_commit_order is ON. That is
    a deadlock and hangs the slave. Here we call the deadlock as a order commit
    deadlock.
    
    FIX
    ===
    A deadlock checking mechanism is introduced. Every time when a transaction
    needs to wait for another transaction to release a row lock, innodb will
    call a slave function to check if there is an order commit deadlock. If
    it founds an order commit deadlock, It will set a deadlock flag to the
    slave worker which is holding the row lock. Thereafter, the worker will
    roll its transaction back and retry it again.
    61e2172a
    BUG#20136704 --SLAVE-PRESERVE-COMMIT-ORDER CAUSES SLAVE TO DEADLOCK AND
    Libing Song authored
                 BREAK FOR SOME QUERIE
    
    A corner case caused slave hang when slave_preserve_commit_order is ON.
    
    ANALYSIS
    ========
    CREATE TABLE t1(c1 INT PRIMARY KEY, c2 INT, INDEX(c2)) ENGINE = InnoDB;
    INSERT INTO t1 VALUES(1, NULL),(2, 2), (3, NULL), (4, 4), (5, NULL), (6, 6);
    
    INSERT INTO t1 VALUES(7, NULL);
    DELETE FROM t1 WHERE c2 <= 3;
    
    On master, the INSERT statement acquires its row lock before DELETE STATEMENT.
    Since the INSERT doesn't block the DELETE, Both statements may have same
    commit parent which mean they can be applied parallel on slave.
    
    On slave, they will be applied parallel if MTS is ON. There is a chance that
    the DELETE acquires its lock before the INSERT. The DELETE holds a gap lock
    on INDEX(c2) which blocks the INSERT statement. The INSERT cannot be applied
    unless DELETE is committed or rolled back. And meanwhile the DELETE is waiting
    for the INSERT to commit since slave_preserve_commit_order is ON. That is
    a deadlock and hangs the slave. Here we call the deadlock as a order commit
    deadlock.
    
    FIX
    ===
    A deadlock checking mechanism is introduced. Every time when a transaction
    needs to wait for another transaction to release a row lock, innodb will
    call a slave function to check if there is an order commit deadlock. If
    it founds an order commit deadlock, It will set a deadlock flag to the
    slave worker which is holding the row lock. Thereafter, the worker will
    roll its transaction back and retry it again.
Loading