In the end, I introduced a new lock mode LOCK_AUTO_INC_X that has a statement scope like LOCK_AUTO_INC but will conflict with any lock. This lock will be acquired when statement-level replication needs to reserve AUTO_INCREMENT values and we are running in the (now default) innodb_autoinc_lock_mode=2. This lock will be released at the end of the statement, just like LOCK_AUTO_INC always was.

When we remove the configuration parameter innodb_autoinc_lock_mode in a later major release, we would also remove LOCK_AUTO_INC but not the newer LOCK_AUTO_INC_X. Both could be removed if binlog_format=STATEMENT was extended to include the allocated AUTO_INCREMENT values.

I ran a simple 30-second benchmark (oltp_insert) with 64 threads. The check for statement-level binlogging is costing some performance, so we’d better cache it in the transaction object:

diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc

index fa8684ec427..3d27ef2f80d 100644

--- a/storage/innobase/handler/ha_innodb.cc

+++ b/storage/innobase/handler/ha_innodb.cc

@@ -7619,9 +7619,11 @@ ha_innobase::innobase_lock_autoinc()

   switch (innobase_autoinc_lock_mode) {

   default:

+#if 0

     if (UNIV_UNLIKELY(thd_rpl_stmt_based(m_user_thd)))

       if (dberr_t error= row_lock_table_autoinc(m_prebuilt, true))

         DBUG_RETURN(error);

+#endif

     break;

   case AUTOINC_NEW_STYLE_LOCKING:

Side note: with --log-bin the throughput falls to about half, and that is with the unsafe default sync_binlog=0 (MDEV-16589).

I tried one more tweak: Enclose all relevant code in #ifndef NO_AUTOINC_LOCKS so that it can be easily disabled by adding #define NO_AUTOINC_LOCKS to storage/innobase/include/univ.i. There was no noticeable performance difference in my very limited benchmark.

The reason why I conducted the experiment was that I was curious to see if this proposed change is too little to be useful, and whether we should rather extend binlog_format=STATEMENT so that the auto-increment locks can be removed altogether.

I think that we will need some more broadband performance testing by axel before making any conclusion.

Looks good to me, but Elkin reviewed MDEV-19577, what can cause MDEV-19577 changes and the necessity to rebase this issue in 10.9.

I merged MDEV-19577 to 10.9 and essentially reverted it in this task, because all innodb_autoinc_lock_mode values would be safe for statement-level replication.

Performance tests showed no impact from MDEV-27844. Not even with all autoincrement locks disabled (#define NO_AUTOINC_LOCKS in storage/innobase/include/univ.i)

See attached MDEV-27844-durable.pdf and MDEV-27844-non-durable.pdf

Thank you, axel! I had 2 reasons for filing this task:

1. Potential performance improvement.
2. Eventually removing a useless configuration parameter.

Testing suggests that there will be no significant performance benefit, at least when not using replication. The change that was tested would complicate the InnoDB code even more, by introducing another type of an ‘exclusive’ auto-increment lock, which would eventually replace the old ‘shared’ type of auto-increment lock.

There may exist users who expect that the sequences be allocated contiguously: https://stackoverflow.com/questions/55007957/am-i-guaranteed-to-get-consecutive-ids-with-a-single-insert-statement-in-mysql

If we care about such users, we should retain auto-increment locks in some form, in a way that would cover all storage engines.

Because row-level replication does not guarantee that the auto-increment values be allocated contiguously, we might as well extend the binlog_format=STATEMENT with explicit ranges of the allocated auto-increment values, as was previously discussed in MDEV-19577.

Either way, all code to deal with innodb_autoinc_lock_mode should be removed.

0001-Add-ifndef-NO_AUTOINC_LOCKS.patch identifies the InnoDB code that deals with auto-increment locks.