buf_page_get_low() is unnecessarily acquiring exclusive latch on secondary index pages

With a simple Sysbench select_random_ranges workload on 1 table, 1 connection and 10000 rows, the following code is being exercised very frequently:

diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc

index 44b093e93ed..ab720dc128f 100644

--- a/storage/innobase/buf/buf0buf.cc

+++ b/storage/innobase/buf/buf0buf.cc

@@ -2849,6 +2849,7 @@ buf_page_get_low(

 	    && fil_page_get_type(block->page.frame) == FIL_PAGE_INDEX

 	    && page_is_leaf(block->page.frame)) {

 		block->page.lock.x_lock();

+		sql_print_warning("InnoDB: x_locked %p", block);

 		state = block->page.state();

 		ut_ad(state < buf_page_t::READ_FIX);

Here is the corresponding patch for the 10.5 branch:

diff --git a/storage/innobase/buf/buf0buf.cc b/storage/innobase/buf/buf0buf.cc

index f37a96d5a7c..7b8aa0934d2 100644

--- a/storage/innobase/buf/buf0buf.cc

+++ b/storage/innobase/buf/buf0buf.cc

@@ -3198,6 +3198,7 @@ buf_page_get_low(

 	    && fil_page_get_type(fix_block->frame) == FIL_PAGE_INDEX

 	    && page_is_leaf(fix_block->frame)) {

 		rw_lock_x_lock_inline(&fix_block->lock, 0, file, line);

+		sql_print_warning("InnoDB: x_locked %p", block);

 		if (fix_block->page.ibuf_exist) {

 			fix_block->page.ibuf_exist = false;

When using the oltp_read_only workload, the above would only be covered only during sysbench prepare.

The purpose of the above code is to apply buffered changes to a secondary index leaf page if such changes exist. This logic was originally implemented in MDEV-19514. Note: the change buffer was removed in MariaDB Server 11.0 in MDEV-29694, and therefore 11.x is not affected by this.

The problem that is exposed by the select_random_ranges workload is that even though a shared latch was requested, we are first acquiring an exclusive latch. This could be improved in a number of ways:

• If a buffer-fix is being requested (rw_latch=RW_NO_LATCH), skip this logic. This mode should not be used together with allow_ibuf_merge=true.
• Acquire the latch that was requested, and if necessary, temporarily upgrade it to exclusive so that the buffered changes can be merged. Upgrading a latch used to be deadlock-prone before the locks were refactored in MDEV-24142 (MariaDB Server 10.6). After the refinement in MDEV-34178, I think the needed upgrade step is trivial to implement.
• If ibuf.empty holds (the change buffer was found to be empty), it does not make any sense to try a change buffer merge.