mysql commit/rollback and executing three or more different sql statements in one query

Question

Hi,

My situation is i need to update 4 tables upon a request.

I execute four statements like a delete two inserts and one update.

What if in any given time after executing two queries the database fails or for any other reason the code exits (probably of any sort of db error/exception).

If that happens then many reference would get wrong and oops :-(. Data will be totally wrong and will have incomplete references.

Is there an option like executing many sql statements in a one shot like multiple insert into statement?

Or if there is a commit mechanism(even if there one i have to learn it to use in mysql) ?

so, at any point i dont want an invalid entry to take place.

for example...

// start save point

// execute all four statements one by one

// at this line commit

I am using codeigniter framework.

I would like to have any kind of reply to this. (suggestions/comments/answers).

Thank you.

Answer 1

You are looking for transactions. Do a START TRANSACTION at the start of the sequence and COMMIT at the end. If something goes wrong do ROLLBACK. Either all the changes will get made or none.

(If you are using a programming language with a data access layer, that may provide methods to call instead of explicitly executing COMMIT et al as queries.)

You will need to be using InnoDB tables to support transactions and other data integrity features that MyISAM can't provide.

Answer 2

if you use PDo, there is an transaction start, after that you can execute you four statements and then commit or roll back.