High rate of index_lock_upgrades due to btr_cur_need_opposite_intention() mostly returning true

While testing MDEV-34431 using HammerDB TPROC-C (Delivery mix) on a 24 vCPU Intel system (oversubscribed with 88 threads) we observed a strong correlation between ORDER_LINE updates and frequent index lock upgrades.

As an experiment, commenting out the ORDER_LINE update in the Delivery stored procedure significantly reduced index lock upgrades and reduced rd_lock_spin sample percentage.

Counters were added around:
btr_cur_need_opposite_intention()
Entry to pessimistic_search_leaf()
mtr_t::index_lock_upgrade()

For both partitioned and unpartitioned schemas:

btr_cur_need_opposite_intention() returned true for nearly all calls.
The dominant reason was nosib=true.
pessimistic_search_leaf() calls closely tracked the number of true returns.
index_lock_upgrade() calls closely tracked pessimistic_search_leaf() calls.

Example (Partitioned)

[btr_cur_need_opposite_intention]

calls=14018262 true=14010891

zip=0 nosib=14010891 first=104879 last=121344 small=514079 rightmost=121344 reorg=0

[index_lock_upgrade]

calls=14018262 already_x=0 upgraded=14018262

pess@table=tpcc/order_line#P#p7 index=PRIMARY tree_height=3 height=2

pess@table=tpcc/orders index=PRIMARY tree_height=3 height=2

pess@table=tpcc/new_order index=PRIMARY tree_height=3 height=2

Example (Unpartitioned)

[btr_cur_need_opposite_intention]

calls=9786751 true=9780023

zip=0 nosib=9780023 first=1 last=2849015 small=471126 rightmost=2849015 reorg=0

[index_lock_upgrade]

calls=9786751 already_x=0 upgraded=9786751

pess@table=tpcc_nopart/order_line index=PRIMARY tree_height=4 height=3

pess@table=tpcc_nopart/orders index=PRIMARY tree_height=3 height=2

pess@table=tpcc_nopart/new_order index=PRIMARY tree_height=3 height=2

This suggests that the update path is almost always escalating to pessimistic search and upgrading the index latch early in the descent (height == tree_height - 1), e.g.:

calls=14018262 true=14010891 root=14018262 root_true=14010891 lvl0=0 lvlgt0=14018262

Diagnostic experiment

As a diagnostic experiment, btr_cur_need_opposite_intention() was modified to always return false.

Results:
pessimistic_search_leaf() and index_lock_upgrade() calls dropped from millions to tens.
Unpartitioned throughput improved by ~7.5%.
A small number of pessimistic_search_leaf() calls remained.

[btr_cur_need_opposite_intention]

calls=41255137 true=0

zip=0 nosib=0 first=0 last=0 small=0 rightmost=0 reorg=0

[index_lock_upgrade]

calls=30 already_x=0 upgraded=30

When btr_cur_need_opposite_intention() was modified to always return false, instance hangs were observed during schema builds loading the history table. After adding a primary key in our local test schema, the hang did not reproduce.