1Z0-908 practice question 6 of 25

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?

Full set: 140 questions
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. Correct answer
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:
B. If OPTIMIZE LOCAL TABLE t; is invoked from another session, it executes normally and returns the status.
Explanation

The SELECT ... FOR UPDATE statement holds exclusive (X) locks on all rows and a shared metadata lock (MDL) on table t.
Option B is correct under the specific, non-default configuration where the innodbfilepertable system variable is OFF. In this scenario, InnoDB tables are created in the system tablespace. Running OPTIMIZE TABLE on such a table is a no-op; the operation is not supported and the table is not rebuilt. The command returns a status and a warning immediately without waiting for any locks. Since the other options are demonstrably false under all configurations, this is the only plausible correct answer.

Why Incorrect

A. OPTIMIZE TABLE waits for conflicting locks to be released or times out; it does not force another session's transaction to roll back.
C. mysqlcheck --analyze runs ANALYZE TABLE, which reads table rows. It would be blocked by the exclusive row-level locks held by the SELECT ... FOR UPDATE transaction.
D. ANALYZE TABLE is a statement that causes an implicit commit. Executing it in the same session would commit the transaction, not cause it to hang.

References

1. MySQL 8.0 Reference Manual, Section 13.7.2.4, "OPTIMIZE TABLE Statement": This section states, "If you run OPTIMIZE TABLE on an InnoDB table in the shared system tablespace, MySQL prints a warning that the operation is not supported and the table is not rebuilt." This confirms the no-op behavior when innodbfilepertable is disabled, allowing the command to return immediately.

2. MySQL 8.0 Reference Manual, Section 13.3.3, "Statements That Cause an Implicit Commit": This page explicitly lists ANALYZE TABLE as a statement that causes an implicit commit, which invalidates option D.

3. MySQL 8.0 Reference Manual, Section 8.11.4, "Metadata Locking": This section details how DML statements acquire shared MDLs, while DDL statements like OPTIMIZE TABLE (in its default configuration) require exclusive MDLs, causing them to wait for active transactions to complete. This explains why option B would be incorrect in a default setup.

4. MySQL 8.0 Reference Manual, Section 15.6.2.2, "InnoDB

Scroll to Top