SQL Transactions...

Animal

I have had to create a normalised database in the My Query browser program which i have done and now i have to create some SQL Transaction statements. I think i have got the basic SQL queries done however i don't understand the whole transaction situation? Was wondering if anyone has any expirience with these and could give me some useful pointers as to what to do...

-animal-

Shorty

A transaction isn't massively different to a normal SQL statement. It just allows you to control data integrity.

You can run a series of SQL queries inside a safe wrapper. Say you want to update two tables and want no one to see that data until both tables are successfully updated, put the statements in a tranasction.

Example:

You have two queries....

update mydatabase.online
set online = 1
where user ="harry";

update mydatabase.activity
set onlinetoday = 1
where user = "harry";

... now say that you have to make sure both queries run or you will have inconsistencies in your data. Right now, query 1 could fail or query 2 could fail. Use a transaction means you can run your queries first and then "roll them back" if one fails. This would be done using the "rollback" statement. To start the transaction, add the "begin" keyword in your SQL statement. Eg...

begin;

update mydatabase.online
set online = 1
where user ="harry";

update mydatabase.activity
set onlinetoday = 1
where user = "harry";

Now at this stage the queries have run but they are not commited to the database. If you get a MySQL error on query 2, you can return the database back to the state it was before you started this transaction by using the rollback command.

rollback

If both queries worked fine, you can commit it permanently to the database.

commit

A good code example (using PHP) can be found here: http://www.devarticles.com/c/a/MySQL/Using-Transactions-with-MySQL-4.0-and-PHP/