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 questionsThe 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.
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.
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
Keep practicing
Take the free 1Z0-908 practice test
Ten exam-style questions with answers and explanations, plus the exam facts and study guides.
More questions
Other 1Z0-908 practice questions
- Question 2You must run multiple instances of MySQL Server on a single host. Which three methods are…
- Question 3Which two tools are available to monitor the global status of InnoDB locking? (Choose two…
- Question 4Which two MySQL Server accounts are locked by default? (Choose two.)
- Question 5All MySQL Server instances belonging to InnoDB Cluster have SSL configured and enabled. Y…
- Question 8Examine this command, which executes successfully: $ mysqlrouter --bootstrap user@hostnam…
- Question 9Examine this command, which executes successfully: shell> mysqldump --master-data=2 --sin…
