The Three R’s - MySQL

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:

mysql> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
Query OK, 0 rows affected (0.51 sec)
mysql> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
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