Isolating Yourself - MySQL

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', 
clientA> 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.

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

blog comments powered by Disqus