use bulk insert optimization for multiple insert statements

After MDEV-515 InnoDB can handle bulk inserts better. But still that optimization only applies to the first INSERT into the empty table. It works well for INSERT...SELECT and LOAD DATA, but, for example, mysqldump generated dumps contain a series of INSERT statements, and that usage is still poorly optimized.

The problem here is that in the bulk insert mode InnoDB cannot rollback individual statements, all the work done in the bulk insert mode is atomic, as a whole.

There should either be some way to express it in SQL or make sure there will be no rollbacks.

Another complication is that in the bulk insert mode InnoDB locks the whole table, not individual rows.

A typical sql dump looks like

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;

LOCK TABLES `t1` WRITE;

/*!40000 ALTER TABLE `t1` DISABLE KEYS */;

INSERT INTO `t1` VALUES (...), (...), ...

/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

UNLOCK TABLES;

/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;

It seems that with UNIQUE_CHECKS=0 and FOREIGN_KEY_CHECKS=0 there should be no rollbacks. But with CHECK constraints there still should be? And as inserts are done under LOCK TABLES, InnoDB fine grained row level locking is wasted here. So, it seems to be possible to get MDEV-515 to work with no syntax extensions in the specific case of mysqldump output.

To make it work generally we'll need a new syntax that will mean "this block of work can only be rolled back as a whole"

A standard approach would be something like

BEGIN ATOMIC;

DECLARE UNDO HANDLER FOR SQLEXCEPTION RESIGNAL;

which means pretty much exactly what we want to do. But the syntax is, perhaps, too awkward?

Non-standard suggestions, copied from MDEV-24818:

START TRANSACTION ATOMIC;

--

START TRANSACTION WITH ATOMIC ROLLBACK;

--

START TRANSACTION FOR BULK LOAD;