Using Transactions In MySQL (Part 2) - End Work (
Page 10 of 10 )
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.
/ 39 
