Sorry, it is not that simple. Acquiring a stronger index latch does not help this deadlock. The INSERT operation is actually holding an index latch in a mode that would conflict with the statistics operation, if the statistics operation were holding a latch on the same index.

The statistics can be updated for different indexes in the same tablespace than the one that the INSERT is operating on. To prevent this hang, one possible fix could be that dict_stats_update_transient_for_index() and possibly other functions (dict_index_t::clear(), btr_get_size_and_reserved(), dict_stats_save_defrag_stats(), dict_stats_analyze_index()) would acquire a latch on every index of the table, like row_quiesce_set_state() does it, and only then proceeds to acquire a fil_space_t::latch. This approach would still fail to prevent a hang if a table is stored in the InnoDB system tablespace.

The relevant latching order here should be as follows:

1. Acquire dict_index_t::lock in non-shared mode.
2. Acquire the index root page latch in non-shared mode.
3. Possibly acquire further index page latches in accordance with the WL#6326 rules explained in MDEV-29835.
4. Acquire the tablespace latch.
5. Acquire latches on the allocation data structures. These are protected by the tablespace latch.
6. Possibly allocate and write some pages.

Currently, dict_stats_update_transient_for_index() and possibly other functions fail to acquire the index root page latch before the tablespace latch. Swapping the order of acquisition ought to fix this. In the core dump that I analyzed, dict_stats_update_transient_for_index() was holding a tablespace latch and waiting for the index root page latch.

It turns out that dict_stats_update_transient_for_index() and other code that I examined is already following the correct latching order as described above. What looks incorrect is that we are only acquiring a shared tablespace latch. This will wrongly allow similar code to be executed for other indexes in the same tablespace. In the core dump of a hang that I analyzed, we have a wait for some metadata allocation page inside fseg_n_reserved_pages(). If all threads that access a tablespace allocation metadata pages are holding a non-shared latch on the tablespace, they would conflict with each other at a higher level and these deadlocks would be impossible.

Ironically, this hang was introduced with the fix of MDEV-29883. To fix that bug, we should only have introduced some non-shared dict_index_t::lock acquisition. We should not have downgraded some exclusive fil_space_t::latch acquisition to a shared one in the following functions:

• btr_get_size_and_reserved()
• dict_stats_update_transient_for_index()
• dict_stats_analyze_index()

After this fix, the only invoker of mtr_t::s_lock_space() will be the function fseg_page_is_allocated() (renamed from fseg_page_is_free() in MDEV-13542). On the surface, that mini-transaction should be fine, since it is only holding a shared fil_space_t::latch and a shared latch on an allocation bitmap page (n·innodb_page_size). But, if any caller of that function is holding more latches (for example, CHECK TABLE without QUICK is, in btr_validate_level()), we are prone to a deadlock. This possibility of a deadlock was introduced on the merge of MDEV-24569 to 10.6. We must replace mtr_t::s_lock_space() with mtr_t::x_lock_space().