DELETE with auto commit

DELETE from very_big_table where "delete million rows"
causes the following issue within a replication setup:

• Delete is run on master (takes one hour)
• deleted rows are sent to slave (15 min)
• Delete is run on slave (takes 45 min)

The delete will not affect the master much as it will be run in parallel with other queries.
However the slave will stall for 1 hour and replication will be be delayed.

This is a common problem when deleting old rows from tables as part of data cleanups.

The suggested solution is to allow deletes to do auto commit for parts of the delete:

DELETE from very_big_table where ... AUTO COMMIT EVERY 1000 ROWS

This will allow the salve to run the delete in parallel as the master. The disadvantage is that if the statement fails in the middle, then what is already commit will continue to be deleted.

From the user point of view this should work as an optimized version if:

repeat as long as some rows where deleted
DELETE from big_table WHERE ... LIMIT 1000

The new solution is superior to above pseudo code as we don't have to start searching for rows to delete from the start of the table for each delete block.

Things to do in the server:

• Add the new syntax
• Update delete loop in sql_delete.cc that when the limit is reached, we commit the current deleted rows (but keep the cursor and mvcc visibility), write the commit to the binary log and flush it and start a new binlog statement
• Enhance the storage engine interface with 'commit but keep cursor and mvcc visibility).
• This command should only have an effect in auto_commit mode and if the engine support "commit but keep cursor". If this is not true, then it should work as if "AUTO COMMIT EVERY X ROWS" would not have been used.