withinweb

Information and support for products of withinweb.com

withinweb header image 2

SQL Transactions in PHPmyAdmin

July 26th, 2012 · No Comments

A transaction is a collection of SQL statements.  So a task may for example, may consist of a number of update statements.  If one of these should fail you may end up with some tables correctly updated but others not correctly updated particularly if one update has data from a previous update.

To overcome this you define the SQL statements as a transaction. If one SQL statement should fail, you roll back the data to its initial state.  Only when all the statements are successful would you commit the transaction.

When you are entering SQL statements into mySQL you may use the begin / commit  / rollback transaction commands.

So, say we are working with a person table and do an INSERT statement, we can either commit or rollback the transaction.

The problem with PHPmyAdmin is that all the lines have to be one command so it is not easy to demonstrate.

However, we can demonstrate it using the following SQL statements and check the results for each.

Copy the following lines in to the SQL editor in PHPmyAdmin:

begin;
INSERT INTO person (firstname, lastname) VALUES (“fred”, “blogs”);
commit;

Examine the person table.  We should see that a new record has been created.

Now copy the following lines into the SQL editor in PHPmyAdmin:

begin;
INSERT INTO person (firstname, lastname) VALUES (“fred”, “blogs”);
rollback;

In this case the transaction has been rolled back and the record has not been created.

Tags: SQL databases

0 responses so far ↓

  • There are no comments yet...Kick things off by filling out the form below.

You must log in to post a comment.