Peeping Tom - MySQL

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