CODEWITHSUNDEEP
mysqlmyisam
Max Smith
Max Smith

Reputation: 405

Can multiple updates in one MySQL query emulate transaction behavior?

I'm using MySQL MyISAM, and this cannot be changed. However, I'm only familiar with the transaction approach. As MyISAM does not support transactions, I'm thinking of combining two update queries into one like this:

UPDATE table1, table2 SET table1.col1='value1', table2.col1='value2'
WHERE table1.col3='567' AND table2.col6='876'

Does this guarantee that if any of the sub-queries fail, the other one won't be executed as well and the query will return FALSE?

To test this, I purposefully replaced valid field name with invalid one for table1 and then for table 2. In both cases phpMyAdmin returned error and neither of the queries were executed.

Will this behavior be observed in case of any other query errors?

Upvotes: 1

Views: 219

Answers (1)

Leeft
Leeft

Reputation: 3837

You can only rely on anything not executing after the first error is thrown (and these errors are usually not on the programmer but on the database level, making them hard to predict). Anything which has been written to the database at that point will not be rolled back (after all, you're using MyISAM tables which do not support that).

It's not the answer you want to hear, but if you want predictable and reliable transactional behaviour, really the only way is to use an engine that supports them (such as InnoDB).

Upvotes: 1

Related Questions