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

Peeping Tom - 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 illustrate, let's consider a simple example. Let's assume we have two separate transactions, started by two different clients, A and B. Client A is adding a new account to a table containing bank account balances, while client B is executing a query to obtain the sum of all account balances. At a high level of isolation, the record added by client A will not appear in the sum calculated by client B; at a lower level, it will.

Assuming an isolation level of REPEATABLE READ (the default), here's what things might look like.

As you can see in this example, even though client A's transaction has completed, client B cannot see the changes made by that transaction until its own transaction is complete. There is thus a high isolation level between transactions in this example, which ensures that clients do not read incorrect data.

Now, let's repeat the transaction with a lower isolation level - say, READ UNCOMMITTED.

The difference should now be glaringly obvious; client B can now see the changes made by client A even before client A commits its transaction. This is a classic dirty read, and it can seriously affect the accuracy of calculations due to the low isolation level between transactions.

The choice of isolation level is thus something that every developer working with transactions must give sufficient thought to, and an optimum level should be chosen for the specific application being developed.

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