MySQL InnoDB: Can one make large inserts from queries run with no rollback penalty

Question

We've ran into some issues where a DBA or a developer will accidentally do something like

insert into `newtable`
select * from `verylargetable`

10 minutes (or maybe an hour!) into running this query, we're getting alarms that there is a process running for much to long. The problem is that the rollback process is just as bad as the original problem.

These are typically for analytics workloads where it would be better to just cancel the query. Even if a partial number of rows are inserted, that's better than having to wait 20 minutes or longer for the query to rollback.

In InnoDB is there a way to auto-commit every row, or every X rows as large inserts from queries are done, or just tell MySQL that if things are canceled not to roll everything back? I know it's not what InnoDB was designed for, but with MyISAM essentially deprecated, it's what we've got.

Answer 1

There is no way to make an individual SQL statement, even INSERT...SELECT commit incrementally during execution. InnoDB treats each statement as atomic.

If the problem is that you want to prevent accidentally copying large quantities of rows with INSERT...SELECT, then you can set safe_updates and max_join_size to some limited value.

For example, I set it to 100 (the default is 18446744073709551615). Then I tried to copy the rows from a test table to itself (the table has a few thousand rows).

% mysql --safe-updates --max-join-size=100

mysql> insert into t (t) select t from t;
ERROR 1104 (42000): The SELECT would examine more than MAX_JOIN_SIZE rows; 
check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# 
if the SELECT is okay

Read https://dev.mysql.com/doc/refman/8.0/en/mysql-tips.html#safe-updates for more details on safe updates mode.