TalkPHP - mysql ROLLBACK & COMMIT

TalkPHP (http://www.talkphp.com/forums.php)

- Absolute Beginners (http://www.talkphp.com/absolute-beginners/)

- - mysql ROLLBACK & COMMIT (http://www.talkphp.com/absolute-beginners/4795-mysql-rollback-commit.html)

captainmerton

07-29-2009 06:54 PM

mysql ROLLBACK & COMMIT

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.

All times are GMT. The time now is 03:56 AM.