This is a well-written article by Joel about MySQL transactions. If you are new to MySQL 4.0, then you must read this so you can ensure data integrity.
With the arrival of MySQL 4.0, you finally have full support of Transactions. This course will help you get started using this great new feature using PHP.
Requirements for this Article
- MySQL and PHP installed on a local or remote server.
- Knowledge of using the MySQL Monitor program.
- MySQL connectivity using PHP.
- MySQL Version 4.0.12 using InnoDB type tables.
You have heard of Transacations but you are not sure on how they are used. They are simple to utilize after you learn the basics.
Lets start with defining Transactions. A good definition would be the following:
"Transaction Processing is used to maintain database integrity by ensuring that SQL operations execute completely or not at all."
Thats fine for a definition but a visual example would be better.
If you go to the store and take 3 of the 10 items on the shelf, that leaves 7 left for others to purchase. You decide to checkout and make your way to the purchase your items. If you agree to buy the items then you give the merchant your money and COMMIT to the Transaction. After you receive your receipt the Transaction is completed.
You have learned all there is to know about Transactions.
Not really, but its a start.
Here are the steps for using Transactions in MySQL:
- BEGIN the Transaction.
- Update, insert or delete entries in the database.
- If you like the changes to the database, then you COMMIT to the Transaction.
- If you do not like the changes then you ROLLBACK the changes to the original condition of the database.
Note: You must use InnoDB type tables or Transactions, will not work.
Lets create a basic table in your database called "trans", and make the table type "innodb".
CREATE TABLE trans
(
id int not null auto_increment,
item varchar(30) not null,
quantity varchar(10) not null,
primary key(id)
)type=innodb;
Heres the contruction of the table:
mysql> DESC trans;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | | PRI | NULL | auto_increment |
| item | varchar(30) | | | | |
| quantity | varchar(10) | | | | |
3 rows in set (0.00 sec)
Insert some data into the table:
mysql> INSERT INTO trans (id,item,quantity) VALUES (NULL,'Computer','5');
Query OK, 1 row affected (0.00 sec)
Begin the Transaction by using the BEGIN command and update the entry:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE trans SET quantity ='4' WHERE id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
View the results of the update:
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 4 |
1 row in set (0.00 sec)
If you do not like the changes, then use the ROLLBACK command to revert back to the original version of the table.
mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)
Notice the table has reverted back to the original insertion:
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 5 |
1 row in set (0.01 sec)
Lets update the table using another Transaction and commit to the changes:
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> UPDATE trans SET quantity ='2' WHERE id=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 2 |
1 row in set (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
After you use the COMMIT command, the table will take on the changes and remain that way until they are modified.
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 2 |
1 row in set (0.00 sec)
Now that you understand the basics of Transactions lets create a PHP script that will insert new data into the table.
Here is the code for the transaction script:
<?php
// trans.php
function begin()
{
@mysql_query("BEGIN");
}
function commit()
{
@mysql_query("COMMIT");
}
function rollback()
{
@mysql_query("ROLLBACK");
}
@mysql_connect("localhost","username", "password") or die(mysql_error());
@mysql_select_db("test") or die(mysql_error());
$query = "INSERT INTO trans (id,item,quantity)
values (null,'Baseball',4)";
begin(); // transaction begins
$result = @mysql_query($query);
if(!$result)
{
rollback(); // transaction rolls back
echo "you rolled back";
exit;
}
else
{
commit(); // transaction is committed
echo "your insertion was successful";
}
?>
Explanation of the script
- Functions are created for the BEGIN, COMMIT and ROLLBACK commands.
- The script connects to the server and runs the query of inserting data into the table.
- If the query is successful then it COMMITS the Transaction.
- If the query is unsuccessful then the Transaction will ROLLBACK.
Here is the table after the script executes:
mysql> SELECT * FROM trans;
| id | item | quantity |
| 1 | Computer | 2 |
| 2 | Baseball | 4 |
2 rows in set (0.00 sec)
I hope that this gets you started using Transactions in MySQL 4.0 using PHP. Its a great feature and will open up new ideas when building web applications.
Copyright 2003 - Written by Joel Philip for DevArticles - All Rights Reserved.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
More MySQL Articles
More By Joel Philip
 developerWorks - FREE Tools!
|
Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download the latest trial version of IBM Data Studio V1.1 at no cost. IBM Data Studio is a comprehensive data management solution that helps you effectively design, develop, deploy and manage your data, databases, and database applications throughout the data management life cycle utilizing a consistent and integrated user interface. Unlike other client-side data management solutions that focus on only one aspect of the application lifecycle or database administration, Data Studio complements the Rational Software Delivery platform, providing unparalleled flexibility for a heterogeneous data server environment across platforms.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to download a free trial version of IBM Rational Business Developer V7.1. Rational Business Developer offers rapid and simplified development of business applications and services through Enterprise Generation Language (EGL) tools, generating Java or mainframe solutions while shielding developers from technical complexities.
FREE! Go There Now!
|
|
|
|
In this tutorial, you can learn how to install and configure the IBM Rational Asset Manager Eclipse client, explore the different views in the Asset Management perspective, learn various search techniques, work with existing assets, and submit a new asset.
FREE! Go There Now!
|
|
|
|
Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
FREE! Go There Now!
|
|
|
|
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
FREE! Go There Now!
|
|
|
|
Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
FREE! Go There Now!
|
|
|
|
Whether you are creating new applications or modifying existing ones, managing integration of new components with traditional z/OS elements is a critical part of building and deploying modern applications. Listen to this webcast to see how IBM can help you optimize your development process using an IDE like Rational Developer for System z that integrates with management tools, such as ClearCase to manage your application development on mainframes.
FREE! Go There Now!
|
|
|
|
The discipline of assembling and delivering software is maturing beyond standard developer-centric compile/test software builds. The end-to-end software development lifecycle is emerging as the new focus moves “Beyond the Build.” Join this on demand webcast to learn about methods for streamlining software delivery and key capabilities of the IBM Rational Build Forge framework for automating build and release management in environments of any size.
FREE! Go There Now!
|
|
|
|
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
|
|
|
|
All FREE IBM® developerWorks Tools!
|
/ 49 
