MySQL 8.0 Database Administrator — Question 104

t is a non-empty InnoDB table.
Examine these statements, which are executed in one session:
BEGIN;
SELECT * FROM t FOR UPDATE;
Which is true?

Answer options

• A. If OPTIMIZE TABLE; is invoked, it will create a table lock on t and force a transaction rollback.
• B. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.
• C. mysqlcheck --analyze --all-databases will execute normally on all tables and return a report.
• D. If ANALYZE TABLE; is invoked from the same session, it hangs until the transaction is committed or rolled back.

Correct answer: C

Explanation

The correct answer is C because mysqlcheck --analyze --all-databases operates outside of transaction locks and will analyze all tables without issues. Option A is incorrect since OPTIMIZE TABLE; would not cause a rollback on an active transaction; Option B is wrong because OPTIMIZE LOCAL TABLE t; does not interfere with the transaction; and Option D is incorrect because ANALYZE TABLE; from the same session would indeed wait until the transaction is resolved.