End Work - 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.

TABLE OF CONTENTS:

By: icarus, (c) Melonfire

Poor Best

Rating:

/ 41

December 22, 2003

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

And that's about all we have time for today. Over the last few pages, I took you deeper into the world of MySQL transactions, explaining how the transaction isolation level can affect the integrity of your transactions in a multi-user environment. I showed you how to control the isolation level, and also demonstrated the impact it has with a simple (and very likely) example.

Next, I explained how MySQL implements isolation through the use of locks, and demonstrated how to use this knowledge to implement a transaction with non-transactional tables like the MyISAM format. I explained the difference between read and write locks, showed you how a simulated transaction works, and gave you a quick rundown on the drawbacks of this approach: long wait times, no rollback mechanism and no true durability.

Finally, after a brief detour into the MySQL binary log, I wrapped things up with a sample application that demonstrated how transactions can be used at the application level - a program to perform a transaction, detect errors if any and either roll it back or commit it to the system. This program was written in Perl; however, it's fairly easy to write equivalent code in PHP, Python or any other language.

There's a lot more to MySQL transactions than what you've just learnt - but this will suffice to get you going. In case you'd like to learn more, I'd recommend the following links:

MySQL transaction commands, at http://www.mysql.com/doc/en/Transactional_Commands.html

MySQL table types, at http://www.mysql.com/doc/en/Table_types.html

The InnoDB transaction model, at http://www.mysql.com/doc/en/InnoDB_transaction_model.html

MySQL table locking, at http://www.mysql.com/doc/en/Locking_methods.html

The MySQL binary log, at http://www.mysql.com/doc/en/Binary_log.html

Until next time...take care!

Note: All examples in this article have been tested on MySQL 4.0.14. Examples are illustrative only, and are not meant for a production environment. Melonfire provides no warranties or support for the source code described in this article.