Transactional Support in MySQL

MySQL is virtually unique in modern relational databases in that transactions are not mandatory. Under certain circumstances, they are not even possible. In fact, with MySQL, transactional support is a property not of the MySQL server itself, but of the underlying storage engine employed. Currently, the two most popular storage engines used with MySQL are MyISAM and InnoDB, although a small number of users use BerkeleyDB:

 

MyISAM

MyISAM does not support transactions. Using a nontransactional storage engine is fine for certain applicationsin particular those that are overwhelmingly read-only. Certainly, if you do not need to manage transactions, you can improve the performance of some applications by avoiding the overhead associated with transaction management. If, on the other hand, you are building an application with a significant amount of updates and concurrent updates to the database, you will probably want to avoid MyISAM and instead rely on a transactional engine.

 

InnoDB

InnoDB is the most popular transaction-safe MySQL storage engine. It supports ACID transactions as well as row-level locking and multiversion concurrency.

 

Berkeley DB

This storage engine also supports transactions but is currently less widely used than InnoDB.

In a survey conducted by MySQL AB (http://dev.mysql.com/tech-resources/quickpolls/storage-engines.html), about 60% of respondents reported using MyISAM as their primary storage engine, while 37% used InnoDB and about 1% used BerkeleyDB. However, these figures are likely to change over the next few years, as MySQL AB releases additional storage engine types, many of which will be transactional.

This chapter assumes that you are using a transactional storage engine such as InnoDB or BerkeleyDB.

First, we need to discuss the concept of isolation levels and sessions.

8.1.1. Isolation Levels

Before we can talk sensibly about transactions and isolation levels, we need to be clear on the concept of a session. A database session is a unique connection to the database that commences when you log on to MySQL and that terminates when you disconnecteither explicitly or when MySQL notices that your client program has "gone away."

Every session has its own memory areas andmore importantlycan hold locks on data or have a unique view of certain data. Isolation levels determine the degree to which transactions in one session may affect the data seen or accessed by another session. All isolation levels are compromises between concurrency the ability for multiple sessions to perform operations on the database at the same timeand consistencythe degree to which a session sees a logical and correct view of the data regardless of what activities might be going on in other sessions.

The isolation level of a transaction also determines the degree to which that transaction conforms to the ACID properties described at the beginning of this chapter. Each of the four isolation levels represents a different balance between the isolation and concurrency of transactions. At the highest isolation levels, very few transactions will be able to execute concurrently, but the chances of one transaction interfering with another will be minimized. At the lower isolation levels, many transactions will be able to execute concurrently, but the chances of conflicts between transactions will be higher.

The ANSI standard defines four isolation levels, all of which are supported by MySQL when using the InnoDB engine:

 

READ UNCOMMITTED

This is the lowest possible isolation level. Sometimes called dirty read, this level permits a transaction to read rows that have not yet been committed. Using this isolation level might improve performance, but the idea of one user retrieving data changed by another user, which might not actually be committed, is usually unacceptable.

 

READ COMMITTED

At this isolation level, only committed rows can be seen by a transaction. Furthermore, any changes committed after a statement commences execution cannot be seen. For example, if you have a long-running SELECT statement in session A that queries from the BOOKS table, and session B inserts a row into BOOKS while A's query is still running, that new row will not be visible to the SELECT.

 

REPEATABLE READ

At this isolation level, no changes to the database that are made by other sessions since the transaction commenced can be seen within the transaction, until the transaction is committed or rolled back (cancelled). This means that if you re-execute a SELECT within your transaction, it will always show the same results (other than any updates that occurred in the same transaction).

 

SERIALIZABLE

At this isolation level, every transaction is completely isolated so that transactions behave as if they had executed serially, one after the other. In order to achieve this, the RDBMS will typically lock every row that is read, so other sessions may not modify that data until the transaction is done with it. The locks are released when you commit or cancel the transaction.

You can change the isolation level in your MySQL session with the SET statement:

 SET TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED
 |REPEATABLE READ | SERIALIZABLE}

Under normal circumstances, you should avoid changing the transaction isolation level from the default of REPEATABLE READ. In particular, think carefully before setting the isolation level to READ UNCOMMITTED or SERIALIZABLE. READ UNCOMMITTED can lead to serious problems with the integrity of the data returned by the SELECT statement, while SERIALIZABLE will have a noticeable, negative effect on performance and can also increase the chance of "deadlocks" (described later in this chapter).

8.1.2. Transaction Management Statements

Use the following transaction management statements in MySQL stored programs:

 

START TRANSACTION

Signifies the commencement of a new transaction. If an existing transaction is already in progress, then START TRANSACTION will issue an implicit COMMIT . When you issue START TRANSACTION, the autocommit property (described in the next section) is effectively and implicitly set to 0 until the transaction ends. We recommend that you explicitly commit or roll back existing transactions before any START TRANSACTION statements, since the implicit COMMIT might not be obvious to someone reading or maintaining your code.

 

COMMIT

Saves all changes made in the transaction to the database and then terminates a transaction. COMMIT also releases any locks that might be in effect, whether they are explicit locks from FOR UPDATE or LOCK TABLES or implicit locks acquired as a result of executing DML statements.

 

ROLLBACK

Undoes any changes to the database made by the transaction and then terminates that transaction. Like COMMIT, ROLLBACK releases any locks held by the transaction.

 

SAVEPOINT savepoint_name

Creates a named savepoint identifier that can be the target of a ROLLBACK TO SAVEPOINT statement.

 

ROLLBACK TO SAVEPOINT savepoint_name

Performs a rollback on all statements that have been executed since the specified savepoint was created. In this way, you can roll back only part of a transaction, preserving some subset of your changes to still be saved. You may find savepoints useful when you need to save part of your work after an error has occurred. See the section "Working with Savepoints" later in this chapter for more details.

 

SET TRANSACTION

Allows you to change the isolation level of your transaction. See the section "Isolation Levels" earlier in this chapter for more details.

 

LOCK TABLES

Allows you to explicitly lock one or more tables. Note that LOCK TABLES implicitly closes any currently open transactions . We recommend that you explicitly commit or roll back your transaction before any LOCK TABLES statements. We rarely want to lock entire tables in the normal course of transaction processing, so we don't usually include LOCK TABLES statements in our transactional code.