Transactions

MySQL 4.0 supports database transactions. Transactions allow you to group together a sequence of database queries to ensure that MySQL either completes the entire set of actions or fails to perform any of them. There’s no in-between state.

A perfect example demonstrating the importance of transactions is transferring money from a bank account. Removing money takes two steps: first, the program checks whether the account contains enough funds; if it does, it then subtracts the money from the account and places it in another.

You don’t want a second withdrawal to occur in between checking the account balance and removing the funds, because the other transaction could completely empty the account, leaving no more money for your request. This is a big problem for banks.

Creating Transaction-Supported Tables

In order to support transactions, MySQL introduced a new database table format, called InnoDB. The original format, MyISAM, can’t be used with transactions. If you’re running MySQL 4.0 or higher, you should have support for InnoDB tables. To check, run the following MySQL query:

mysql> SHOW VARIABLES LIKE 'have_innodb';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| have_innodb   | YES   |
+---------------+-------+

It should say YES. By default, MySQL enables InnoDB tables as of Version 4.0, so if this variable is set to NO, you should rebuild MySQL and remove the --without-innodb flag from your configuration.

Tip

MySQL also supports transactions ...

Get Upgrading to PHP 5 now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.