Using Transactions In MySQL (Part 2)

In the first part of this article, I introduced you to transactions with MySQL 4.0, explaining the theory behind the transactional model and showing you how to implement a transactional environment with MySQL's InnoDB tables. I also showed you how MySQL handles COMMITs and ROLLBACKs in different situations, and explained how to control automatic commits with the AUTOCOMMIT variable.

Thus far, I've been explaining transactions under the assumption that the database is being accessed by a single user at a time. This was done for purposes of simplicity in explanation; however, in the real world, such an assumption is unlikely to hold true. Therefore, in this concluding segment, I'll be exploring the MySQL transactional model in a multi-user scenario, illustrating some of the data corruption problems that raise their ugly heads in this environment and showing you how you can use MySQL's isolation levels to reduce their likelihood.

I'll also show you how to leverage off the transaction model to build more robust SQL applications, with a sample Perl/DBI application, and - in case you're still stuck using an older version of MySQL - illustrate how you can simulate transactions with non-transactional MyISAM tables. Keep reading, this is gonna be one wild ride!

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

blog comments powered by Disqus