Logo         Community
The Company
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.

| The Three Rs |

In order to understand the various isolation levels, it is necessary to first understand some of the problems that a low isolation level can cause. The ANSI specification defines three potential problems:

Dirty reads: A dirty read is said to occur if a transaction can view the changes made by another transaction before they have been committed.

Unrepeatable reads: An unrepeatable read is said to occur if a transaction sees different result sets from the same query while it is in progress. Typically, this occurs due to table UPDATEs committed by other transactions.

Phantom reads: A phantom read is a variant of an unrepeatable read. It occurs when a transaction sees a new row while it is in progress. Typically, this occurs due to new records INSERTed and committed by other transactions.

I'm not going to get into the details of each of these problems at this stage, you should take a look at the links at the end of this article for more information. Suffice to say that they do exist, and in large RDBMS, steps need to be taken to prevent them from corrupting the system with misleading data while simultaneously not causing too great an impact on performance.

In order to help database administrators achieve this optimal balance between transaction isolation and security, four different isolation levels are available, ranging from very secure to insecure; these four levels are defined in terms of the three problems described above.

READ UNCOMMITTED: The least secure isolation level, this permits dirty, unrepeatable and phantom reads.

READ COMMITTED: Offering more security than the READ UNCOMMITTED level, this level does not allow a transaction to see the uncommitted data of other transactions; however, it still allows unrepeatable and phantom reads.

REPEATABLE READ: The default isolation level in MySQL, this level eliminates dirty reads and unrepeatable reads. Phantom reads are still theoretically possible, but in reality are almost impossible to reproduce.

SERIALIZABLE: SERIALIZABLE eliminates phantom reads as well to offer the most secure isolation between transactions; it's also the slowest.

MySQL makes it possible to control the isolation level via the special TRANSACTION ISOLATION LEVEL variable. Consider the following examples, which illustrate:

Query OK, 0 rows affected (0.51 sec)

Query OK, 0 rows affected (0.51 sec)

You can obtain the current value of this variable at any time with a fast SELECT, as below:

mysql> SELECT @@tx_isolation;
| @@tx_isolation  |
1 row in set (0.13 sec)

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
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
In the hitg report...
Crime Scenes
Animal Attraction
Lord Of The Strings
In boombox...
Patience - George Michael
Think Tank - Blur
My Private Nation - Train
In colophon...
Hostage - Robert Crais
The Dead Heart - Douglas Kennedy
Right As Rain - George Pelecanos
In cut!...
American Chai
The Core
Find out how you can use this article on your own Web site!

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