HomeMySQL Page 3 - Using Transactions In MySQL (Part 1)
The Acid Test - MySQL
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.
Any RDBMS which supports transactions must conform to the so-called "ACID rules", which specify the fundamental principles for truly secure transactions.
Atomicity: The transaction must be treated as an indivisible unit, and all the statements within it must be successful for the transaction to be considered successful. In the event of a transaction failure, the system should be returned to its pre-transaction state.
Consistency: Once a transaction has been completed, the system must be in a consistent state, with all of its integrity constraints satisfied.
Isolation: The changes made by a transaction should be invisible to other transactions while it is in progress.
Durability: Once a transaction has been completed, the changes it has wrought must be remembered by the system even in the event of a system failure.
Is MySQL ACID-compliant? Well, yes and no.
You see, MySQL supports a number of different table formats. There's the ancient ISAM format, the newer MyISAM format (which is intended to supplant the ISAM format), the BerkeleyDB format, and the latest entrant, the InnoDB format. Each of these table formats has its own advantages and disadvantages, and not all of them support transactions in the ACID-recommended manner.
As of MySQL 4.0, native ACID-compliant transactions are only possible with InnoDB and BerkeleyDB tables; for other table types, transactional environments need to be implemented at the application level, through the use of table locks or other mechanisms. My focus in this tutorial will primarily be on transactions with the InnoDB table type; however, for users constrained to older table types, a brief discussion of how to simulate a transactional environment will appear in the second part of this tutorial.
