Benoit Zohar

You Are Viewing

A Blog Post

Transactions with MySQL

Rollback

While trying to rollback transactions on a MySQL database today I’ve been experiencing some weird, unexpected issues. Inserted data was still inserted in some of my tables but not in other tables.

SQL Transactions :

START TRANSACTION;
INSERT .... // insert some data
UPDATE .... // update some data
COMMIT; // all the data is keeped
START TRANSACTION;
INSERT ... // insert some data
UPDATE ... // insert some data
ROLLBACK;  // no data is inserted or updated

Solution

To avoid this, juste make sure all your tables are using “InnoDB” engine instead of “MyISAM“, for example, which is used by default in a lot of applications. It seems that MyISAM force the data to be written, so, even with a rollback, your rows are already in the table.

Note : in Titanium, sql transactions work like a charm with sqlite databases.

 

Leave a Reply