Home arrow MySQL arrow Page 10 - Using Transactions In MySQL (Part 2)

End Work - MySQL

mysqlThis 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.

  1. Using Transactions In MySQL (Part 2)
  2. Isolating Yourself
  3. The Three Rís
  4. Peeping Tom
  5. Locks and Keys
  6. Nothing Like the Real Thing
  7. Holding Pattern
  8. Timberrrrrrrrrr!
  9. Perl of Wisdom
  10. End Work
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 41
December 22, 2003

print this article


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.

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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates


Dev Shed Tutorial Topics: