using transactions

[heaps21]

Hi, I recently posted a similar question in the php forum but have since been told it is a mysql question so here goes...

When a save button is clicked on my web page there are inserts to many different tables that need to take place. I was told (by raf) that wrapping all the sql commands in a transatcion will ensure that either all or none of the commands are completed, which is exactly what i need.

Being relatively new to mysql (i.e. only really knowing about select, insert, update etc) I have struggled to find a simple example of how to use this, and any requirements for using it. I had a look on the mysql manual but didn't really understand it.

So, my question is, can anyone provide a simple example of its use that I can adapt for my needs?

Any help would be much appreciated.
Andy.

[sad69]

This article may help:
http://www.devshed.com/c/a/MySQL/Using-Transactions-In-MySQL-Part-1/

Sadiq.

[heaps21]

Unfortunately, whatever version of mysql i am using, it doesnt support the table types required for transactions apparently... this has stumped me a bit - any suggestions?

Cheers

[sad69]

Yes, two suggestions:
upgrade to a newer version of MySQL, or if you refuse, you can simulate transactions using the techniques described in this tutorial:
http://php.weblogs.com/mysql_transactions

Sadiq.

[heaps21]

ah cheers, that is a nice help! :thumbsup:

[raf]

You should realy update to version that supports transactions.

the article describes a technique from the pre-tranactions age (should be about 3 years ago for MySQL) and it goes without saying that it wount be a performant way to assure data-integrity + it'll result in a lott more code for you.