Prepare and test your skills

Prepare and test your skills

Free sample question with answers and detailed explanation below. The Professional Cloud Database Engineer practice bank has an additional 769 practice questions.Try 10 free realistic questions: instant score, no credit card, and no account required.
Your team manages a Cloud SQL for MySQL instance that supports both high-volume transactional writes and long-running reporting queries. During peak traffic, query latency spikes and CPU utilization approaches 95%. Your initial investigation reveals the following symptoms:
SHOW FULL PROCESSLIST shows dozens of write threads stuck in the Updating state alongside several long-running SELECT queries.Which diagnostic query and isolation level change should you implement?
MySQL performance_schema collects low-level server execution instrumentation, and its events_statements_history consumer stores recent SQL statements executed by each thread. The adaptive hash index (btr0sea.c) is an internal InnoDB optimization that builds in-memory hash lookups for frequently accessed B-tree index pages.
performance_schema requires a database instance restart, which interrupts production workloads and clears current transient lock states. Even once enabled, events_statements_history only keeps the last 10 SQL statements per thread to trace application code history—it does not directly solve undo log retention or replace innodb_lock_waits for mapping active blocker-to-waiter chains.btr0sea.c under heavy concurrent index lookups, but it has no effect on SQL row-level lock contention or the undo history list kept open by REPEATABLE READ snapshots.performance_schema and performance_schema_events_statements_history_size are not dynamic parameters.btr0sea.c was not actually the bottleneck.You needed to trace the historical SQL statement sequence leading up to a long-running idle transaction after an instance restart, or if SHOW ENGINE INNODB STATUS revealed frequent semaphore waits on btr0sea.c.
Always match the diagnostic tool to the contention layer: use information_schema.innodb_lock_waits for row-lock contention and READ COMMITTED for undo log purging bottlenecks.
The SEMAPHORES section of SHOW ENGINE INNODB STATUS reports internal engine synchronization primitives—specifically mutexes and read/write latches protecting in-memory structures such as the buffer pool and data dictionary. Meanwhile, SERIALIZABLE is the strictest SQL transaction isolation level, designed to prevent all concurrency anomalies including phantom reads.
SEMAPHORES section tracks low-level internal thread contention (such as btr0sea.c or dict0dict.cc latches), not SQL-level row lock waits between application sessions. Row-level transaction details reside in the TRANSACTIONS section or information_schema.innodb_lock_waits.SELECT queries to SERIALIZABLE converts standard non-locking snapshot reads into locking reads (SELECT ... LOCK IN SHARE MODE). Instead of freeing undo logs to purge, reporting queries would actively acquire shared locks on every scanned row, severely blocking concurrent UPDATE and DELETE operations.SHOW ENGINE INNODB STATUS caps output size, potentially hiding active transactions when hundreds of connections exist.SERIALIZABLE sacrifices read-write concurrency for strict serial execution.You were diagnosing internal InnoDB engine bottlenecks caused by adaptive hash index latch contention (btr0sea.c) alongside a requirement to prevent write-skew or phantom reads at all costs.
To lower undo history list growth in MySQL, you must relax snapshot retention from REPEATABLE READ to READ COMMITTED, not tighten isolation to SERIALIZABLE.
Combining information_schema.innodb_lock_waits with information_schema.innodb_trx provides real-time visibility into InnoDB row-level lock contention by linking blocked transactions to the exact transactions holding the locks. Pairing this with the READ COMMITTED transaction isolation level alters how InnoDB manages consistent read snapshots and undo log purging for reporting queries.
innodb_lock_waits (blocking_trx_id and requesting_trx_id) with innodb_trx surfaces each blocking transaction's ID, state, start time, lock wait duration, active SQL statement (trx_query), and count of blocked transactions. This immediately pinpoints which write sessions are causing the chained Updating backlog.REPEATABLE READ isolation level, a long-running SELECT maintains a single snapshot from its start time, blocking InnoDB from purging undo log records modified since that snapshot began. Switching reporting sessions to READ COMMITTED generates a fresh snapshot for each statement instead of holding one for the entire transaction, allowing the purge thread to clean up undo logs continuously.information_schema tables works immediately on live Cloud SQL for MySQL instances without restarting the server.trx_rows_locked).READ COMMITTED isolation level can be applied globally, per session, or for a single transaction without altering application write semantics.Unlike aggregate thread counters or internal latch diagnostics, joining innodb_lock_waits with innodb_trx directly exposes SQL-level blocking relationships. Likewise, shifting long-running read sessions to READ COMMITTED directly eliminates snapshot-induced purge delays without requiring stricter locking modes that would further reduce system concurrency.
The SHOW STATUS LIKE 'Threads_%' command displays global server status counters, including Threads_connected (total open client connections) and Threads_running (threads not currently sleeping). REPEATABLE READ is InnoDB's default transaction isolation level, ensuring that all reads within a transaction observe a consistent point-in-time snapshot.
Threads_running only provides an aggregate integer showing how many threads are active at that moment. While a spike in Threads_running often correlates with lock contention and CPU saturation, the counter cannot identify which specific trx_id is holding locks or which queries are blocked.REPEATABLE READ forces InnoDB to preserve every undo log record modified since the oldest active SELECT began. Even if queries execute in smaller batches within the same REPEATABLE READ transaction, the read view remains pinned until commit or rollback.Threads_% variables help confirm concurrency spikes but require SHOW FULL PROCESSLIST or innodb_trx for root-cause inspection.REPEATABLE READ, InnoDB traverses undo version chains to reconstruct older row versions, slowing queries as the history list grows.Threads_connected includes idle sessions that consume memory without actively running queries.You were establishing baseline concurrency metrics to verify whether active thread counts were rising alongside CPU utilization, and your reporting workload strictly required transaction-wide point-in-time snapshot consistency.
Checking Threads_running is an initial triage step rather than a blocking-session diagnostic, and resolving undo purge delays requires switching read sessions from REPEATABLE READ to READ COMMITTED.
Answer them yourself and get your score the moment you finish. No account needed.
Full access includes
Keep the momentum going with these hand-picked practice scenarios
Last updated Validated