Transaction-Safe Table Types

In much of this book, you have studied the MyISAM table type. This is the default table type for MySQL. This table type is well tried and tested; MyISAM tables are fast and compact, but they have one important limitation.

MyISAM tables have no inherent concept of transactions. They ensure safe atomic processing: They make sure that a single SQL statement can be executed without interference in the data by other threads.

But other than by locking tables, they cannot help us where we need to perform a sequence of SQL queries, all of which need to be processed to keep the data consistent. We might think of this entire sequence as a single business transaction, no part of which should be performed without performing the whole.

Performing Transactions Using Transaction-Safe Tables

Table locking is a somewhat crude mechanism: The client that has obtained the locks it needs can do its work, but other clients have to be held up until the first client is finished. This hampers overall system performance if many clients get held up. A better way of achieving the same goal is to use transaction-safe tables.

A single business transaction may be a sequence of SQL queries, all of which must be performed without interference to keep data consistent. The queries may include any kinds of SQL, such as SELECT, UPDATE, INSERT, and other commands. With transaction-safe tables, the effect of that sequence of queries—the entire transaction—is written to the database in one go.

There are several advantages of using transaction-safe tables:

• You can group several SQL statements into a single operation, and any write operations can either be implemented in the database (committed) or discarded (rolled-back).

• If an update fails, the database will be restored to its previous state.

• If MySQL crashes, the database will be restored to its state before the crash.

Newer versions of MySQL (starting with 3.23, and enhanced in version 4) have two transaction-safe table types: InnoDB and BDB.

MySQL's Transaction-Safe Table Types

InnoDB is a transaction-safe table handler distributed with MySQL. It facilitates the commit, rollback, and crash recovery operations, which are central to transaction-safe processing. InnoDB has been designed with high volumes of transactions and high performance in mind, and is claimed to have the fasted executing code of any relational database. In this lesson, there is only time to give you an introduction to InnoDB (albeit a hands-on one). For more information about it, visit the InnoDB Web site at http://www.innodb.com/.

The other transaction-safe table type available with MySQL is BDB, which stands for BerkeleyDB. BDB is developed by Sleepycat and can be used in many other database engines besides MySQL. You can learn more about BDB at http://www.sleepycat.com/.

The MySQL interface to BDB is not currently considered as advanced as that for InnoDB. Therefore in the rest of this lesson, we'll put more emphasis on InnoDB than BDB; however, the principles of transactions are the same in both cases, so you should be able to apply the skills you learn to either table type.

In summary, transaction-safe tables such as InnoDB and BDB enable you to group a sequence of queries into an indivisible transaction. In the following examples, you will use the InnoDB table type to understand how to do this.

Installing InnoDB

If you installed MySQL version 4 or later from source, your mysqld binary will already contain support for InnoDB tables. With version 3.23, you will need to include the --with-innodb option when you run ./configure; for example:

# ./configure 
							--with-innodb
							...
						

MySQL binaries have InnoDB included in the MySQL-Pro and MySQL-Max distributions. If you installed a standard MySQL binary, you should download a -Pro or -Max distribution, unpack it, and replace your old mysqld with the new one. These binary distributions are identical to the normal distributions in all except the mysqld executable.

After you have either compiled MySQL or installed the right mysqld, you will need to edit the option file my.cnf (or my.ini). Day 15, “Administration,” explains where to find this file.

You will need to add (or in newer versions, just uncomment) the following line in my.cnf, in the section under [mysqld]:

[mysqld]
...
innodb_data_file_path = ibdata1:10M:autoextend

This line defines the file that InnoDB uses for its data storage area. The preceding example sets this area to 10MB (though you may want to make yours higher), with the capability to auto-extend this space (in other words, grow) if required.

(With versions of MySQL prior to 4.0.2, you will not be able to use the :autoextend option shown here.)

Many other options are available for configuring InnoDB: You should find these commented-out in your my.cnf file, with instructions on how to use these options in the MySQL Technical Reference. However, in this lesson, our aim is to keep things simple. You can get InnoDB working with just the one line shown previously; all other options are unnecessary, and they will just set themselves to their default values if left undeclared.

After you have completed these changes, restart mysqld_safe. If all is well, MySQL should start up again without any problem. However, if an error is reported or mysqld refuses to start, look in the hostname.err file of MySQL's data directory for clues as to what went wrong. Also check the InnoDB options in my.cnf again very carefully.

Creating an InnoDB Table

With mysqld started, you're ready to try out InnoDB tables and perform some transactions. Start by creating a table of product stock levels; we'll use InnoDB tables to repeat the example we looked at earlier in this lesson:

mysql> CREATE TABLE stock_level
    -> (product_id INT NOT NULL PRIMARY KEY, qty int NOT NULL) TYPE=InnoDB;
Query OK, 0 rows affected (0.34 sec)

The CREATE TABLE statement is almost the same as you've seen before, except for the TYPE=InnoDB clause, which should be added at the end. This sets the table type to be InnoDB rather than the default MyISAM.

Alternatively, if the table already exists, you can simply change the table type to InnoDB using ALTER TABLE, like this:

mysql> ALTER TABLE stock_level TYPE=InnoDB;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0
						

Running Transaction-Based Queries

You should now have an InnoDB-type table ready to run some transaction-based queries on.

Let's recall the problem of stock levels we had from earlier today. We have two clients querying the table stock_level for the same product. There are 10 items of a particular product line in stock. Client A wants to order 3, and client B wants to order 8.

We're looking to transaction-safe tables to give us a way of processing the requests by the two clients, without allowing a situation of both orders being processed (effectively allowing more products to be ordered than there really are).

First, put some data into your table:

mysql> INSERT INTO stock_level SET product_id=1234, qty=10;
Query OK, 1 row affected (0.00 sec)

Now open two windows and start a mysql console session in each. The first window represents client A, and the second, client B.

A transaction consists of several queries. To begin a transaction, you have to tell MySQL that the transaction is beginning.

To do this, use the command BEGIN on client A, like this:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

It's as simple as that. If you use BEGIN on MyISAM and other non-transaction safe tables, it will have no effect.

The point of BEGIN on a transaction-safe table is that the write queries that follow will not automatically commit—update the data files on disk—until you issue a further command to do this.

Now do the same on client B:

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

Issue a SELECT query on client A to see how many items are in stock:

mysql> SELECT qty FROM stock_level WHERE product_id=1234;
+-----+
| qty |
+-----+
|  10 |
+-----+
1 row in set (0.00 sec)

As expected, 10 items are in stock. If 3 items are ordered by client A, the stock level of 10 is enough to allow 3 items to be ordered, so the order can be processed. (Imagine that there are some other queries that we don't see here, such as applying a debit to the customer's account because a purchase is taking place.)

The stock level needs to be updated to reduce it by 3. We'll use a relative update:

mysql> UPDATE stock_level SET qty=qty-3 WHERE product_id=1234;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

To see what's happening—still in client A's window—check how many items are now left in stock:

mysql> SELECT qty FROM stock_level WHERE product_id=1234;
+-----+
| qty |
+-----+
|   7 |
+-----+
1 row in set (0.00 sec)

No surprises here. But now switch to client B, and have a look what it sees:

mysql> SELECT qty FROM stock_level WHERE product_id=1234;
+-----+
| qty |
+-----+
|  10 |
+-----+
1 row in set (0.00 sec)

Client B thinks the stock level is still 10! Why?

Client A is still processing the transaction; it has updated its own picture of the data but hasn't allowed the changes to be committed to disk yet.

The point is, a client may want to process a number of other queries, perhaps on several tables, during which time it must maintain a consistent picture. What client A sees is what existed when it issued the BEGIN, plus the effect of any changes it has made to the data itself, but not changes made by other clients.

Client B now wants to process its own order for 8 items. It thinks there are 10 items in stock and goes ahead (just like client A) with updating the customer's account and other data, but—importantly—without committing to disk yet. Client B updates the stock level:

mysql> UPDATE stock_level SET qty=qty-8 WHERE product_id=1234;
						

When you run this, you see that client B's update query hangs without executing. Why? InnoDB allows row-level locking. Just as you saw entire tables being locked at the beginning of this lesson, client A is holding a write lock on this one row of the table. So client B has to wait until A releases the lock on the row for product 1234. (If you had updated any other row in the table, thread B would not have been held up, and would have processed the update—through as yet uncommitted—immediately.)

Finally, client A issues a COMMIT. This saves all its changes to disk:

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

The changes are written to the data file.

Now client B's UPDATE query immediately finishes because A's lock is released, and B is granted access to update the table:

mysql> UPDATE stock_level SET qty=qty-8 WHERE product_id=1234;
Query OK, 1 row affected (41.70 sec)
Rows matched: 1  Changed: 1  Warnings: 0

The application won't know that it had to wait (or why), although the delay of more than 40 seconds tells a human user that another client has been locking the row while it was waiting!

Client B now checks the stock level again:

mysql> SELECT qty FROM stock_level WHERE product_id=1234;
+-----+
| qty |
+-----+
|  -1 |
+-----+
1 row in set (0.00 sec)

Ouch. There's clearly something wrong here, as -1 is returned. If these queries were being run by application code, you should at this point (before you commit) run SELECTs to check that the situation is permissible. Just as your code should have checked that the quantity ordered was not more than the stock level before, it also should check that the stock level is not less than zero now.

The current status is not legal within the application. The order should not have been placed. The stock level should not have been updated, neither should the customer's account have been charged nor other update queries been processed. So what can be done to turn back the clock?

The answer is a rollback. A rollback restores the state of the database to that which existed when the issuing client issued its BEGIN. So client B, instead of committing the data to disk, issues a ROLLBACK:

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

At this point, the application code might loop back to the beginning and try to process the order again. Issuing another SELECT, client B sees this:

mysql> SELECT qty FROM stock_level WHERE product_id=1234;
+-----+
| qty |
+-----+
|   7 |
+-----+
1 row in set (0.00 sec)

The rollback has undone all of client B's changes, without saving them to the live database. A quick inspection reveals that with a qty of just 7, an order for 8 items cannot be accepted.

Why is the solution using transaction-safe tables better than the table locking solution that we saw earlier today?

For a start, InnoDB's row-level locking rather than table-level locking means that only essential rows are locked. So the rest of the table is accessible while one client locks just the rows it needs.

In a real-world situation, two clients of the same application would more typically be updating different rows of the relevant tables. That two clients should be trying to order the same product simultaneously would be a low probability (though it must be guarded against), but by not needing the rest of the table to be locked, performance would be better.

Another reason that this method is better is that execution of all client threads can proceed without having to obtain locks first. A client has to wait only at points when updates interfere directly with another client (as illustrated here), which in most cases might never occur.

In short, transaction-safe tables allow much faster and more efficient processing than other table types.