Analyzing InnoDB Semaphore Crashes and Lock Contention in MySQL 5.5

MySQL's background thread srv_error_monitor_thread detects a latch lock blocked for more than 600 seconds; after ten consecutive checks it forces a panic to avoid a hung server, producing the message "Semaphore wait has lasted > 600 seconds. We intentionally crash the server because it appears to be hung."

The log shows repeated waits on the data dictionary lock at dict0dict.c line 305 (over 900 s) held by thread ID 139998697924352 (hex 0x7f53fca8a700). Another critical lock appears in btr0sea.c line 1134, also held by the same thread, related to the Adaptive Hash Index (AHI).

These locks occur inside the functions dict_table_stats_lock (in dict0dict.c) and btr_search_drop_page_hash_index (in btr0sea.c). Enabling innodb_table_monitor or innodb_stats_on_metadata causes queries on data‑dictionary tables to acquire exclusive X‑locks on these structures, which can block many business operations.

The AHI, used to speed up index page lookups, is protected by the global latch btr_search_latch. In MySQL 5.5 this latch can become a performance hotspot, especially when the InnoDB buffer pool is full and pages are evicted, triggering additional AHI lock contention and ultimately a semaphore timeout crash.

To aid DBAs, the author provides a small program mysqldba. Its usage examples are:

hongbin@MBP ~> mysqldba doctor -f /Users/hongbin/workbench/mysqld_safe.log

and hongbin@MBP ~> mysqldba -uxxx -pxxx doctor -w The tool monitors MySQL error logs for patterns like "a writer (thread id ...) has reserved it in mode" and automatically captures the corresponding transaction information from the process list, helping to correlate semaphore crashes with the responsible threads and queries.

Overall, the analysis demonstrates how lock contention in InnoDB’s dictionary and AHI structures, combined with insufficient buffer pool space, can lead to severe performance degradation and server crashes, and offers a practical utility for post‑mortem and real‑time diagnosis.