1Z0-908 Question #16: Real Exam Question with Answer & Explanation

Question

Options

• AIf OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback.
• BIf OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns
• Cmysqlcheck --analyze --all-databases will execute normally on all tables and return a report.
• DIf ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed

Explanation

Option B is correct because OPTIMIZE LOCAL TABLE t from another session targets a specific InnoDB table, and InnoDB internally remaps OPTIMIZE TABLE to an online ALTER TABLE ... FORCE operation. Online DDL in InnoDB allows this to run concurrently alongside the open transaction's row-level locks (not table-level locks), so it completes and returns normally. The LOCAL keyword only suppresses binary logging - it has no bearing on whether the statement blocks.

A is wrong on two counts: InnoDB's OPTIMIZE TABLE uses online DDL and does not acquire a traditional blocking table lock, and more critically, no maintenance statement in InnoDB forces a transaction rollback - the transaction would simply continue untouched.

C is wrong because mysqlcheck --analyze --all-databases includes system schemas (information_schema, performance_schema) where ANALYZE TABLE is not applicable or will produce errors, so it cannot execute cleanly across all tables as claimed.

D is wrong because ANALYZE TABLE issued from the same session does not hang - a session is never blocked by its own locks, and InnoDB's ANALYZE TABLE is non-blocking regardless.

Memory tip: Think "InnoDB = row locks, not table locks." Maintenance commands that would block on MyISAM (table-level locking) often run online on InnoDB. When an answer claims a maintenance statement causes a rollback, that's a MyISAM-era misconception - InnoDB doesn't work that way.

No community discussion yet for this question.