HomeMySQL Page 4 - Using Transactions In MySQL (Part 2)
Peeping Tom - MySQL
This 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.
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.
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.
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.