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

Isolating Yourself - 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


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.

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
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: