mysql ROLLBACK & COMMIT

captainmerton · 07-29-2009, 06:54 PM

I'm looking for some guidance on how to use mysql BEGIN, ROLLBACK and COMMITS. I have situations where i am making 2 or 3 updates to tables in seperate queries and if one fails i want them all rolled back. Can someone help me with the following:

Whats the difference between BEGIN and START TRANSACTION?

How can I best incorporate error handling eg. at present i'm running queries using the following code:

PHP Code:


			
$result = mysql_query($query) OR die('Cannot perform query!'.mysql_error());

On a die statement does mysql look back for a BEGIN or START TRANSACTION and roll back to there or will I have to tell it to ROLLBACK?

JaoudeStudios · 07-30-2009, 04:42 AM

First of all are you using the correct database engine? InnoDb?

Also it is worth looking into MySQLi, it has many more features. Especially as MySQL api has been deprecated for sometime now!

captainmerton · 07-30-2009, 10:42 AM

I'm running mysql 5 on my own machine installed myself - but i'll be deploying the app to a shared hosting site again mysql 5. Is the shared site likely to have innoDb installed? I believe a "SHOW ENGINES" command on the locally installed instance of mysql 5 will tell me whether I have it.

Do you have any links to good articles on MySQLi?

captainmerton · 07-30-2009, 10:48 AM

Is it worth using PDO?

tony · 07-30-2009, 02:42 PM

MySQLi could be more handy in this, since you are using mysql as a database, but PDO is also helpful as an ORM tool, you can use it to abstract what kind of database you are using.

There is a tutorial for transactions in MySQL that could explain it better then I could.I haven't use transactions in MySQL, it should be the same as others but better informed then sorry.

I found this tutorial with a simple example for MySQLi, it might help.

I don't know much about the subject (I should learn mysqli soon though :S), but I hope this helps.

captainmerton · 07-30-2009, 03:02 PM

Thanks for that Tony. I dont think i need to think about database abstraction just now but will read up on MySQLi. Definitely need to use transactions in mysql and locking as well probably.

tony · 07-30-2009, 03:15 PM

Yeah I need to get better at my DB knowledge too.

JaoudeStudios · 07-30-2009, 05:16 PM

The InnoDB is not just good for transactions but also row locking instead of table locking. This is why I favour InnoDB over MyIsam.

captainmerton · 07-30-2009, 06:24 PM

Well table locking is no use to me especially if i got decent traffic so i will look into InnoDB. Thanks for the advice guys.

devnull · 08-06-2009, 10:21 PM

Transactions are pretty easy to implement just execute the following in this order;

Code:

# Start the SQL Transaction
mysql_query("begin transaction");

Then execute any update/delete/insert queries

Code:

mysql_query("insert into......");
mysql_query("insert into......");
mysql_query("insert into......");

and finish off with

Code:

# Commit the changes
mysql_query("commit");

If any of the queries fail or a commit is not received(in the case of doing a die), the transaction(any queries executed) won't be committed to the database. Its as easy as that.