MySQL Transactions Overview

Date: Sep 13, 2002

Sample Chapter is provided courtesy of Sams.

Return to the article

Transactions are a new addition to MySQL but not to relational database systems in general. If you have used an enterprise database system, such as Oracle or Microsoft SQL Server, the transactional concept should seem familiar. If this is your first venture into relational databases, this hour will bring you up to speed and provide an overview of using transactions in MySQL.

In this hour, you will learn about

• The basic properties of transactions

• Berkeley DB, InnoDB, and Gemini table types

What Are Transactions?

A transaction is a sequential group of database manipulation operations, which is performed as if it were one single work unit. In other words, a transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail.

A good example would be a banking transaction, specifically a transfer of $100 between two accounts. In order to deposit money into one account, you must first take money from another account. Without using transactions, you would have to write SQL statements that do the following:

1. Check that the balance of the first account is greater than $100.

2. Deduct $100 from the first account.

3. Add $100 to the second account.

Additionally, you would have to write your own error-checking routines within your program, specifically to stop the sequence of events should the first account not have more than $100 or should the deduction statement fail. This all changes with transactions, for if any part of the operation fails, the entire transaction is rolled back. This means that the tables and the data inside them revert to their previous state.

Properties of Transactions

Transactions have the following four standard properties, usually referred to by the acronym ACID:

• Atomicity ensures that all operations within the work unit are completed successfully; otherwise, the transaction is aborted at the point of failure, and previous operations are rolled back to their former state.

• Consistency ensures that the database properly changes states upon a successfully committed transaction.

• Isolation enables transactions to operate independently of and transparent to each other.

• Durability ensures that the result or effect of a committed transaction persists in case of a system failure.

In MySQL, transactions begin with the statement BEGIN WORK and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the bulk of the transaction.

COMMIT and ROLLBACK

When a successful transaction is completed, the COMMIT command should be issued so that the changes to all involved tables will take effect. If a failure occurs, a ROLLBACK command should be issued to return every table referenced in the transaction to its previous state.

If transactions were not used in application development, a large amount of programming time would be spent on intricate error checking. For example, suppose your application handles customer order information, with tables holding general order information as well as line items for that order. To insert an order into the system, the process would be something like the following:

1. Insert a master record into the master order table.

2. Retrieve the ID from the master order record you just entered.

3. Insert records into the line items table for each item ordered.

If you are not in a transactional environment, you will be left with some straggly data floating around your tables; if the addition of the record into the master order table succeeds, but steps 2 or 3 fail, you are left with a master order without any line items. The responsibility then falls on you to use programming logic and check that all relevant records in multiple tables have been added or go back and delete all the records that have been added and offer error messages to the user. This is extremely time-consuming, both in man-hours as well as in program-execution time.

In a transactional environment, you'd never get to the point of childless rows, as a transaction either fails completely or is completely successful.

Row-Level Locking

Transactional table types support row-level locking, which differs from the table-level locking that is enforced in MyISAM and other nontransactional table types. With tables that support row-level locking, only the row touched by an INSERT, UPDATE, or DELETE statement is inaccessible until a COMMIT is issued.

Rows affected by a SELECT query will have shared locks, unless otherwise specified by the programmer. A shared lock allows for multiple concurrent SELECT queries of the data. However, if you hold an exclusive lock on a row, you are the only one who can read or modify that row until the lock is released. Locks are released when transactions end through a COMMIT or ROLLBACK statement.

Setting an exclusive lock requires you to add the FOR UPDATE clause to your query. In the sequence below, you can see how locks are used to check available inventory in a product catalog before processing an order. This example builds on the previous example by adding more condition-checking.

1. Begin transaction.

BEGIN WORK;

2. Check available inventory for a product with a specific ID, using a table called inventory and a field called qty.

SELECT qty FROM inventory WHERE id = 'ABC-001' FOR UPDATE;

3. If the result is less than the amount ordered, rollback the transaction to release the lock.

ROLLBACK;

4. If the result is greater than the amount ordered, continue issuing a statement that reserves the required amount for the order.

UPDATE inventory SET qty = qty - [amount ordered] WHERE id =
'ABC-001';

5. Insert a master record into the master order table.

6. Retrieve the ID from the master order record you just entered.

7. Insert records into the line items table for each item ordered.

8. If steps 5 through 7 are successful, commit the transaction and release the lock.

COMMIT;

While the transaction remains uncommitted and the lock remains in effect, no other users can access the record in the inventory table for the product with the ID of ABC-001. If a user requests the current quantity for the item with the ID of ABC-002, that row still operates under the shared lock rules and can be read.

Transaction-Safe Table Types in MySQL

To use transactions in MySQL, you must use a transaction-safe table type. The default MySQL table type, MyISAM, does not support transactions. BerkeleyDB and InnoDB are the transaction-safe table types available in the open source MySQL, version 3.23.34 and greater, whereas the Gemini table is used for transactions in NuSphere's Enhanced MySQL.

BerkeleyDB

BerkeleyDB is a product from Sleepycat Software (http://www.sleepycat.com/), which provides MySQL with a transaction-safe table type. Support for BerkeleyDB tables began with version 3.23.34 of the open source MySQL and requires a specific compilation parameter when compiling MySQL from source. Most users do not compile MySQL from source and instead rely on whatever has been installed by their Internet Service Provider. If this is true for you, there are two options: ask your Internet Service Provider to build a version of MySQL with support for BerkeleyDB table types, or download and install the MySQL-Max binary distribution for Windows or Linux/UNIX and work with the table type in a development environment.

If your MySQL installation supports BerkeleyDB tables, simply add a TYPE=BDB definition to the table creation statement. For example, the following code creates a BerkeleyDB table called test, with two fields:

mysql> CREATE TABLE test (
  -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  -> sample_text VARCHAR(25)
  -> ) TYPE=BDB;

The BerkeleyDB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BerkeleyDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini table types.

You can learn more about the BerkeleyDB table type in the MySQL manual at http://www.mysql.com/doc/B/D/BDB.html.

InnoDB

InnoDB is the more popular and stable transaction-safe table type in open source MySQL and was designed specifically for high performance with large volumes of data, as well as overall CPU efficiency—two very important features in Web application development. As with the BerkeleyDB table type, InnoDB is not the default table type, and support for it did not appear until version 3.23.34 of the open source MySQL.

Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source. If your MySQL version does not have InnoDB support, ask your Internet Service Provider to build a version of MySQL with support for InnoDB table types, or download and install the MySQL-Max binary distribution for Windows or Linux/UNIX and work with the table type in a development environment.

If your MySQL installation supports InnoDB tables, simply add a TYPE=InnoDB definition to the table creation statement. For example, the following code creates an InnoDB table called test2, with two fields:

mysql> CREATE TABLE test2 (
  -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  -> sample_text VARCHAR(25)
  -> ) TYPE=InnoDB;

You can learn much more about the InnoDB table type in the MySQL manual at http://www.mysql.com/doc/I/n/InnoDB.html.

Gemini

The Gemini table type is available only in NuSphere's Enhanced MySQL product and not in the open source version of MySQL. If you have purchased Enhanced MySQL or have access to it through your Internet Service Provider, simply add a TYPE=Gemini definition to the table creation statement. For example, the following code creates a Gemini table called test2, with two fields:

mysql> CREATE TABLE test3 (
  -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  -> sample_text VARCHAR(25)
  -> ) TYPE=Gemini;

You can learn much more about the Gemini table type in the NuSphere Tech Library, at http://www.nusphere.com/products/tech_library.htm.

Summary

Transactions are groups of operations performed as one single work unit. The four basic properties of transactions are atomicity, consistency, isolation, and durability, which form the acronym "ACID."

To use transactions in MySQL, you must use a transaction-safe table type, such as BerkeleyDB or InnoDB. If you own NuSphere Enhanced MySQL, the transaction-safe table type is called Gemini.

A transaction fails if any operation within it fails. If none of the operations fail, then you can COMMIT the transaction, which finalizes all the changes made while it was active. If a transaction fails, use the ROLLBACK command to return all affected tables to their previous state.

Workshop

The Workshop is designed to help you anticipate possible questions, review what you've learned, and begin learning how to put your knowledge into practice.

Quiz

1. What are the four basic properties of transactions? Hint: think "ACID."

2. What SQL statement should be used to indicate that subsequent statements form a transaction?

3. If query #3 of a 5-query transaction fails, what SQL command should be issued so affected tables revert to their previous state?

Answers

1. Atomicity, consistency, isolation, and durability.

2. BEGIN WORK

3. ROLLBACK

Activity

Based on the table design of your contact management system, think about how the tables relate to each other and how transactions would help maintain the integrity of your data. Think about inserting records in logical groupings, as well as deleting related records.

© 2024 Pearson Education, Informit. All rights reserved.

800 East 96th Street, Indianapolis, Indiana 46240