The following patch has been used during some performance testing to remove the scalability bottleneck due to backup locks, so that we can better highlight scalability bottlenecks inside storage engines.

diff --git a/sql/handler.cc b/sql/handler.cc

index eb7b5b8..012ef20 100644

--- a/sql/handler.cc

+++ b/sql/handler.cc

@@ -1567,7 +1567,7 @@ int ha_commit_trans(THD *thd, bool all)

   DBUG_PRINT("info", ("is_real_trans: %d  rw_trans:  %d  rw_ha_count: %d",

                       is_real_trans, rw_trans, rw_ha_count));

-  if (rw_trans)

+  if (0 && rw_trans)

   {

     /*

       Acquire a metadata lock which will ensure that COMMIT is blocked

diff --git a/sql/sql_base.cc b/sql/sql_base.cc

index c41e08e..f9e3f34 100644

--- a/sql/sql_base.cc

+++ b/sql/sql_base.cc

@@ -2100,7 +2100,7 @@ bool open_table(THD *thd, TABLE_LIST *table_list, Open_table_context *ot_ctx)

   }

   if (!(flags & MYSQL_OPEN_HAS_MDL_LOCK) &&

-      table->s->table_category < TABLE_CATEGORY_INFORMATION)

+      table->s->table_category < TABLE_CATEGORY_INFORMATION && 0)

   {

     /*

       We are not under LOCK TABLES and going to acquire write-lock/

Possibly, this locking can be removed as part of implementing backup in the server process (MDEV-14992).

I do not think it is possible to remove the backup lock in ha_commit_trans.
This lock is essential to ensure that we can get a consistent backup, in any scenario (as far as I know).
As long we support external backup tools, we need to have it, even if we have in server backup.
It is also needed the lock to be able to create a consistent snapshot of the server.

The lock is also critically needed for binlogs.

It would be nice to be able to 'know in advance' that a backup will take place so that we could ignore all backup locks when there will not be any lock for existing transactions.
What could be possible be done is to have a flag that disables all the backup locks. When we do BACKUP STAGE START, we would then enable the lock and wait for all transactions that was started outside of the lock to complete before continue.

Disabling backup locks at rw_trans would make backups, both internal and externa impossible.
This required to get a constant backup point for all engines and replication.
It would also disable flush tables with read lock.

One possible solution is to not do any backup locks before 'backup start' and have 'backup start' inform all threads that a backup is about to start.
The there would be a wait until all current transactions has ended and all transactions are 'backup aware' and have started to take backup locks.

Rough estimate for getting this done is about one week.

Another option is to see if we can speed up the backup lock in ha_commit_trans() so that if there are no conflicting locks there would never be a thread context switch.
Maybe this could be done faster with two atomic increments when backup is not running

I’m not familiar with this code, so it could take me significantly more than a week to understand the logic deep enough in order to fix this.

I have a rough idea how we could minimize atomic writes or read-modify-writes of a shared memory location in the fast path. It would go something like this:

1. The BACKUP statement sets a global flag or switches function pointers to note that a critical phase of backup is about to start.
2. As a result of this, ha_commit_trans() and open_table() will start to register the THD as "backup aware" and acquire the locks as they would if the work-around patch from 2021-07-21 were not present.
3. The BACKUP statement will poll until all active THD objects are registered "backup aware".
4. Once the backup lock is released, the global atomic flag will be reset. As a result, open_table() and ha_commit_trans() will resume fast operation.

I am working on the following solution (which is very much like Marko's suggestion) to remove some of the issues with BACKUP STAGE BLOCK_COMMIT;

Adding before MDL_REQUEST_INIT() in ha_commit_trans()

If (!(thd->backup_state= backup_is_running))

else

and when releasing:

if (mdl_backup.ticket)

else if (thd->backup_state)

If we have the above, we could in BACKUP_STAGE_START do something like

backup_running= 1;
do

while (some_thd_has_backup_state_set())

The effect would be to replace the MDL_backup lock in ha_commit_trans() with two atomic increment and two thread-local memory assignments when backup is not running. The cost is a slightly slower start of the backup.

monty, my idea was that the "fast" code path (no backup is running) would not run any atomic read-modify-write operations. This idea would work if the logic that handles the BACKUP statement could somehow count all active THD objects and wait until the atomic counter has reached that value, instead of the counter being 0.

Pushed to bb-10.11-monty for testing

monty, I revised the logic a little and created https://github.com/MariaDB/server/pull/3966 for this, currently as a draft. It seems to me that the global atomic counter may be redundant. I am still reviewing the logic for potential race conditions.