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.

DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

Comments On: Using Transactions In MySQL (Part 2)

>>> Be the FIRST to comment on this article!