Home arrow MySQL arrow Page 3 - Using Transactions In MySQL (Part 2)

The Three Rís - MySQL

mysqlThis concluding segment looks at the MySQL transactional model in a multi-user scenario, illustrating some of the data corruption problems that are likely to arise and explaining how to control them using MySQL's various isolation levels. It also includes a sample Perl application demonstrating transaction usage at the application level, and shows you how to emulate transactions with non-transactional MyISAM tables.

  1. Using Transactions In MySQL (Part 2)
  2. Isolating Yourself
  3. The Three Rís
  4. Peeping Tom
  5. Locks and Keys
  6. Nothing Like the Real Thing
  7. Holding Pattern
  8. Timberrrrrrrrrr!
  9. Perl of Wisdom
  10. End Work
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 41
December 22, 2003

print this article


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 |+------------------+| READ-UNCOMMITTED |+------------------+1 row in set (0.13 sec)

>>> More MySQL Articles          >>> More By icarus, (c) Melonfire

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: