Transactions with MySQL
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
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.