I would propose new syntax to make the MDEV-515 ‘opt-in’ and also at the same time fix other limitations with it:

--echo # If any statement fails, the entire transaction will be rolled back.

START TRANSACTION ATOMIC;

This would have the following consequences:

1. The MDEV-515 optimization would only apply to operations between START TRANSACTION ATOMIC and COMMIT. More backward compatibility.
2. We would also make mysqldump emit this new syntax (version-conditionally) in order not to cause a regression for MDEV-515.
3. In a follow-up ticket, we could extend InnoDB so that multiple INSERT into the same initially empty table will be optimized.

MDEV-515 is very welcome . I agree the behaviour shown in this ticket is sub-optimal.
But we could make MDEV-515 trigger on LOAD DATA INFILE only , as no one would normally try to insert rows in a table where a LOAD DATA INFILE is happening . And if he does, then have him waiting.

I agree that my original proposed syntax is somewhat weird, because transactions are expected to be kind of atomic. Maybe we could make it explicitly refer to the fact that only full and no partial rollback is supported:

START TRANSACTION WITH ATOMIC ROLLBACK;

Like my original proposal, also this one would reuse existing reserved words.

What is the problem with adding a new dedicated word?
Here's my take:

START TRANSACTION FOR BULK LOAD;

For the purpose of this bug fix and extra optimizations aside, it would be enough to enable the new optimization only in auto-commit more or under LOCK TABLES.

The optimization is moved to MDEV-25036

The following patch would make this feature ‘opt-in’:

diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc

index 65cb8321500..b652aa187e8 100644

--- a/storage/innobase/row/row0ins.cc

+++ b/storage/innobase/row/row0ins.cc

@@ -2660,6 +2660,7 @@ row_ins_clust_index_entry_low(

 	if (!(flags & BTR_NO_UNDO_LOG_FLAG)

 	    && page_is_empty(block->frame)

 	    && !entry->is_metadata() && !trx->duplicates

+	    && !trx->check_unique_secondary && !trx->check_foreigns

 	    && !trx->ddl && !trx->internal

 	    && block->page.id().page_no() == index->page

 	    && !index->table->skip_alter_undo

That is, explicit

SET UNIQUE_CHECKS=0;

SET FOREIGN_KEY_CHECKS=0;

would be required to enable the table-level locking and undo logging of the INSERT statement. The settings are used by the scripts that are generated by mysqldump.

We could extend this approach further to allow subsequent INSERT statements in the same transaction to suppress row-level undo logging in the same transaction. That should make loads of mysqldump fast out of the box. I do not think that we really need to detect LOCK TABLES to enable this logic, because unique_checks=0 should never be set unless one really is sure that no duplicate keys are possible. (It allows the InnoDB change buffer to corrupt data when the change buffer merge of a duplicate key eventually occurs.)

I found out that the sysbench prepare scripts are usually running in autocommit mode without transaction boundaries. The following would enable the slightly faster insert (and remove the need of subsequent purge of INSERT history that was introduced in MDEV-12288):

--- /usr/share/sysbench/oltp_update_index.lua	2020-06-05 16:47:03.000000000 +0300

+++ oltp_update_index.lua	2021-03-12 14:51:26.974791244 +0200

@@ -22,7 +22,9 @@

 require("oltp_common")

 function prepare_statements()

+   con:query("SET autocommit=0, unique_checks=0, foreign_key_checks=0")

    prepare_index_updates()

+   con:query("SET autocommit=1, unique_checks=1, foreign_key_checks=1")

 end

 function event()