Logo         Community
  Trog
Services
The Company
Community
Columns
Your Account
Contact Us
 
 
Using Transactions In MySQL (part 2)
Learn to avoid data corruption with MySQL's various isolation levels, and see examples of transaction usage at the application level.

| Isolating Yourself |

Let's begin with a simple example. While a transaction is in progress, open up a new client session and see if the changes made by the transaction are visible. Consider the following example, which illustrates by using two clients, A and B. A begins a transaction; while it is in progress, B attempts to view the changes being made by it before it has been committed.


clientA> START TRANSACTION;
Query OK, 0 rows affected (0.37 sec)

clientA> INSERT INTO USERS (name, pass) VALUES ('paul', PASSWORD('paul'));
Query OK, 1 row affected (0.38 sec)

clientA> SELECT * FROM users;
+----+------+------------------+
| id | name | pass            |
+----+------+------------------+
|  1 | paul | 29bb48b07ee5526b |
+----+------+------------------+
1 row in set (0.10 sec)

clientB> SELECT * FROM users;
Empty set (0.08 sec)


What you've just seen is an example of the isolation property in action. As noted in the first section of this article, isolation implies that the changes made by a transaction become visible only after the transaction has been committed. Thus, as the example above demonstrates, client B cannot see the transaction being executed by client A while it is in progress. However, once client A commits the transaction, client B can view the results.


clientA> COMMIT;
Query OK, 0 rows affected (0.00 sec)

clientB> SELECT * FROM users;
+----+------+------------------+
| id | name | pass            |
+----+------+------------------+
|  1 | paul | 29bb48b07ee5526b |
+----+------+------------------+
1 row in set (0.00 sec)


Isolation between different transactions is of tremendous importance in a multi-user environment. If the changes made by a transaction are visible while still in progress, other transactions may mistakenly use this data for calculations, or as the basis for future operations. If the transaction making the changes then rolls them back, calculations made on the basis of the old data will be invalid, and much confusion will reign. It is, therefore, extremely important that transactions be insulated from each other while still in progress.

There is, however, a flip side to this. The level of insulation between transactions is inversely correlated to their performance. The higher the isolation level, the more work MySQL has to do to keep users from seeing each other's modifications and the slower things run. For simple transactions that expose a low risk of data corruption, a lower isolation can speed things up without any significant increase in risk; on the other hand, a high isolation level is necessary for mission-critical applications (like banking) even at the cost of some degradation in performance.


How to do Everything with PHP & MySQL
How to do Everything with PHP & MySQL, the best-selling book by Melonfire, explains how to take full advantage of PHP's built-in support for MySQL and link the results of database queries to Web pages. You'll get full details on PHP programming and MySQL database development, and then you'll learn to use these two cutting-edge technologies together. Easy-to-follow sample applications include a PHP online shopping cart, a MySQL order tracking system, and a PHP/MySQL news publishing system.

Read more, or grab your copy now!


previous page more like this  print this article  next page
 
Search...
 
In trog...
Logging With PHP
Building A Quick-And-Dirty PHP/MySQL Publishing System
Output Buffering With PHP
Date/Time Processing With PHP
Creating Web Calendars With The PEAR Calendar Class
more...
 
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
more...
 
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
more...
 
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
more...
 
In cut!...
American Chai
The Core
Possession
more...
 
Find out how you can use this article on your own Web site!


Copyright © 1998-2018 Melonfire. All rights reserved
Terms and Conditions | Feedback