InnoDB: Any way to kill an update and prevent rollback?

In case I ran a very long update (which has millions of records to update and is going to take several hours), I was wondering if there is anyway to kill the update without开发者_运维知识库 having InnoDB rollback the changes.

I would like the records that were already updated to stay as they are (and the table locks released ASAP), meaning to continue the update later when I have time for it.

This is similar to what MyISAM would do when killing an update.

If you mean a single UPDATE statement, I may be wrong but I doubt that's possible. However, you can always split your query into smaller sets. Rather than:

UPDATE foo SET bar=gee

... use:

UPDATE foo SET bar=gee WHERE id BETWEEN 1 AND 100;
UPDATE foo SET bar=gee WHERE id BETWEEN 101 AND 200;
UPDATE foo SET bar=gee WHERE id BETWEEN 201 AND 300;
...

This can be automated in a number of ways.

My suggestion would be to create a black_hole table, with fields to match your needs for the update statement.

CREATE TABLE bh_table1 
 ..field defs
) ENGINE = BLACKHOLE;

Now create a trigger on the blackhole table.

DELIMITER $$

CREATE TRIGGER ai_bh_table1_each AFTER INSERT ON bh_table1 FOR EACH ROW
BEGIN
  //implicit start transaction happens here.
  UPDATE table1 t1 SET t1.field1 = NEW.field1 WHERE t1.id = NEW.id; 
  //implicit commit happens here.
END $$ 

DELIMITER ;

You can do the update statement as an insert into the blackhole.

INSERT INTO bh_table1 (id, field1) 
  SELECT id, field1 
  FROM same_table_with_lots_of_rows
  WHERE filter_that_still_leaves_lots_of_rows;

This will still be a lot slower than your initial update.
Let me know how it turns out.