Home arrow MySQL arrow Page 3 - Using Transactions In MySQL (Part 1)

The Acid Test - MySQL

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

  1. Using Transactions In MySQL (Part 1)
  2. Money, Money, Money...
  3. The Acid Test
  4. Turning the Tables
  5. A Question of Commitment
  6. Rules of the Game
  7. Artificial Intelligence
  8. Time Out
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 74
November 03, 2003

print this article


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.

>>> 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: