Using Transactions In MySQL (Part 1) (
Page 1 of 8 )
One of the most-requested MySQL features - transactions - is finally available in MySQL 4.0. In this first segment of a two-part article, learn about the theory behind the transactional model, find out how it can make your SQL applications more robust, and find out how to implement a transactional environment with MySQL's InnoDB table handler. Back in the good old days, the only people who took MySQL seriously were the geeks, who used it to power their Web sites, and the evangelists, who held it up to the public at large as a shining example of what the open-source community was capable of producing when left to its own devices. Despite MySQL's numerous strengths at the time - speed being the primary one - very few people looked at it as a viable alternative to enterprise-scale RDBMSs like Oracle and Microsoft SQL Server; rather, it was considered competent for small and medium-scale applications, but inadequate for large-scale enterprise deployment.
Today, however, things have changed. MySQL is now in use in more than 30,000 installations across the planet and, with large corporate houses like SAP taking an interest in its future development, seems slated to give commercial RDBMS vendors a serious run for their money in the enterprise market. The reasons for this gradual shift are not hard to find: while earlier versions of MySQL (the 3.23.x series) were pretty basic, lacking support for advanced features like transaction processing, data replication, subqueries and multi-table operations, newer versions (the 4.x series) have moved aggressively to close the feature set gap and now include many new capabilities (including all the items mentioned above).
One of the newest features in MySQL, and one of the most frequently-requested ones on the MySQL wishlist, is transactions. Transactions are found in almost all commercial RDBMSs, and their omission from the MySQL canon has been a source of much hand-wringing amongst MySQL enthusiasts for the last few years. Despite initial resistance to the idea from MySQL HQ (which was understandably concerned about reducing the speed of its RDBMS engine by adding transactional support to it), transactions have finally made an appearance in MySQL 4.0.
That's where this article comes in. Over this two-part tutorial, I will be introducing you to transactions, which allow you to build more robust Web applications and simultaneously reduce the possibility of data corruption in your DBMS. Regardless of whether you've a novice who's never heard of transactions before, or someone who's been tinkering with MySQL for a while (like me), I think you'll find the next few pages interesting. So come on in, and let's get started.
