MySQL - what happens when an UPDATE command is interrupted?

Question

I issued a long running UPDATE query (an incorrect query) via the MySQL command line client and stopped it with Ctrl-C after a few seconds. The command had not finished running. Will my database be updated for some entries or does it happen in a transaction and everything is rolled back?

mysql> <LONG RUNNING INCORRECT UPDATE STATEMENT>
^CCtrl-C -- sending "KILL QUERY 12088743" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> 

Update: All the tables involved in the query are InnoDB tables.

Answer 1

FOR INNODB : The Mysql manual says that InnoDB(a transactional storage engine) provides full ACID complaince. So therefore it will do complete all the operations at once or will not do the operations and rollback in case of an interruption. This is the default engine from MySQL 5.5 and up.

MySQL includes components such as the InnoDB storage engine that adhere closely to the ACID model, so that data is not corrupted and results are not distorted by exceptional conditions such as software crashes and hardware malfunctions.

FOR MYISAM :However for MyISAM storage engine which is non-transactional. Such storage engines follow a model where data is written one statement at a time . This is done using atomic operations. So if you interrupt the process then you will get it upto the point until you interrupted.

The nontransactional storage engines in MySQL Server (such as MyISAM) follow a different paradigm for data integrity called “atomic operations”. MyISAM tables effectively always operate in autocommit = 1 mode. Because changed data is written to disk one statement at a time, it is harder to guarantee the consistency of a sequence of related DML operations, which could be interrupted partway through. Thus, this mode is suitable for read-mostly workloads. In transactional terms, while each specific update is running, no other user can interfere with it, there can never be an automatic rollback, and there are no dirty reads.

However you can use LOCK TABLES as a workaround. This was the default storage engine before MySQL 5.5. *

So the answer depends on which storage engine you are using. Hope that helps :)

Answer 2

In InnoDb with autocommit enabled and a simple UPDATE it will initiate a rollback entirely. Rollbacks are important for ACID compliance, but can be a source of issues so careful use of forced rollback can mitigate those issues, which arise rarely compared to those of not having rollback.

There was a bug addressed in a previous version, where this did not occur as it should have: http://bugs.mysql.com/bug.php?id=45923.

From this question on the difference between autocommit on as a global var, vs. utilizing START TRANSACTION/COMMIT behavior you can learn more about some best practices.