MySQL Cookbook (2007)

Chapter 15. Performing Transactions

Introduction

The MySQL server can handle multiple clients at the same time because it is multithreaded. To deal with contention among clients, the server performs any necessary locking so that two clients cannot modify the same data at once. However, as the server executes SQL statements, it’s very possible that successive statements received from a given client will be interleaved with statements from other clients. If a client issues multiple statements that are dependent on each other, the fact that other clients may be updating tables in between those statements can cause difficulties. Statement failures can be problematic, too, if a multiple-statement operation does not run to completion. Suppose that you have a flight table containing information about airline flight schedules and you want to update the row for Flight 578 by choosing a pilot from among those available. You might do so using three statements as follows:

SELECT @p_val := pilot_id FROM pilot WHERE available = 'yes' LIMIT 1;

UPDATE pilot SET available = 'no' WHERE pilot_id = @p_val;

UPDATE flight SET pilot_id = @p_val WHERE flight_id = 578;

The first statement chooses one of the available pilots, the second marks the pilot as unavailable, and the third assigns the pilot to the flight. That’s straightforward enough in practice, but in principle there are a couple of significant difficulties with the process:

Concurrency issues

If two clients want to schedule pilots, it’s possible that both of them would run the initial SELECT query and retrieve the same pilot ID number before either of them has a chance to set the pilot’s status to unavailable. If that happens, the same pilot would be scheduled for two flights at once.

Integrity issues

All three statements must execute successfully as a unit. For example, if the SELECT and the first UPDATE run successfully, but the second UPDATE fails, the pilot’s status is set to unavailable without the pilot being assigned a flight. The database will be left in an inconsistent state.

To prevent concurrency and integrity problems in these types of situations, transactions are helpful. A transaction groups a set of statements and guarantees the following properties:

§ No other client can update the data used in the transaction while the transaction is in progress; it’s as though you have the server all to yourself. For example, other clients cannot modify the pilot or flight records while you’re booking a pilot for a flight. By preventing other clients from interfering with the operations you’re performing, transactions solve concurrency problems arising from the multiple-client nature of the MySQL server. In effect, transactions serialize access to a shared resource across multiple-statement operations.

§ Statements in a transaction are grouped and are committed (take effect) as a unit, but only if they all succeed. If an error occurs, any actions that occurred prior to the error are rolled back, leaving the relevant tables unaffected as though none of the statements had been issued at all. This keeps the database from becoming inconsistent. For example, if an update to the flights table fails, rollback causes the change to the pilots table to be undone, leaving the pilot still available. Rollback frees you from having to figure out how to undo a partially completed operation yourself.

This chapter shows the syntax for the SQL statements that begin and end transactions. It also describes how to implement transactional operations from within programs, using error detection to determine whether to commit or roll back. The final recipe discusses some workarounds that you can use to simulate transactions in applications that use nontransactional storage engines. Sometimes it’s sufficient to lock your tables across multiple statements using LOCK TABLE and UNLOCK TABLE. This prevents other clients from interfering, although there is no rollback if any of the statements fail. Another alternative may be to rewrite statements so that they don’t require transactions.

Scripts related to the examples shown here are located in the transactions directory of the recipes distribution.

Choosing a Transactional Storage Engine

Problem

You want to use transactions.

Solution

Check your MySQL server to determine which transactional storage engines it supports.

Discussion

MySQL supports several storage engines, but not all of them support transactions. To use transactions, you must use a transaction-safe storage engine. Currently, the transactional engines include InnoDB, NDB, and BDB, and others may become available. To see which of them your MySQL server supports, check the output from the SHOW ENGINES statement:

mysql>SHOW ENGINES\G

*************************** 1. row ***************************

Engine: MyISAM

Support: DEFAULT

Comment: Default engine as of MySQL 3.23 with great performance

*************************** 2. row ***************************

Engine: MEMORY

Support: YES

Comment: Hash based, stored in memory, useful for temporary tables

*************************** 3. row ***************************

Engine: InnoDB

Support: YES

Comment: Supports transactions, row-level locking, and foreign keys

*************************** 4. row ***************************

Engine: BerkeleyDB

Support: YES

Comment: Supports transactions and page-level locking

...

The output shown is for MySQL 5.0. The transactional engines can be determined from the Comment values; those that actually are available have YES or DEFAULT as the Support value. In MySQL 5.1, SHOW ENGINES output includes a Transaction column that indicates explicitly which engines support transactions.

After determining which transactional storage engines are available, you can create a table that uses a given engine by adding an ENGINE = tbl_engine clause to your CREATE TABLE statement:

CREATE TABLE t1 (i INT) ENGINE = InnoDB;

CREATE TABLE t2 (i INT) ENGINE = BDB;

If you have an existing application that uses nontransactional tables, but you need to modify it to perform transactions, you can alter the tables to use a transactional storage engine. For example, MyISAM tables are nontransactional and trying to use them for transactions will yield incorrect results because they do not support rollback. In this case, you can use ALTER TABLE to convert the tables to a transactional type. Suppose that t is a MyISAM table. To make it an InnoDB table, do this:

ALTER TABLE t ENGINE = InnoDB;

One thing to consider before altering a table is that changing it to use a transactional storage engine may affect its behavior in other ways. For example, the MyISAM engine provides more flexible handling of AUTO_INCREMENT columns than do other storage engines. If you rely on MyISAM-only sequence features, changing the storage engine will cause problems. See Chapter 11 for more information.

Performing Transactions Using SQL

Problem

You need to issue a set of statements that must succeed or fail as a unit—that is, you need to perform a transaction.

Solution

Manipulate MySQL’s auto-commit mode to enable multiple-statement transactions, and then commit or roll back the statements depending on whether they succeed or fail.

Discussion

This recipe describes the SQL statements that control transactional behavior in MySQL. The immediately following recipes discuss how to perform transactions from within programs. Some APIs require that you implement transactions by issuing the SQL statements discussed in this recipe; others provide a special mechanism that enables transaction management without writing SQL directly. However, even in the latter case, the API mechanism will map program operations onto transactional SQL statements, so reading this recipe will give you a better understanding of what the API is doing on your behalf.

MySQL normally operates in auto-commit mode, which commits the effect of each statement immediately as soon as it executes. (In effect, each statement is its own transaction.) To perform a transaction, you must disable auto-commit mode, issue the statements that make up the transaction, and then either commit or roll back your changes. In MySQL, you can do this two ways:

§ Issue a START TRANSACTION (or BEGIN) statement to suspend auto-commit mode, and then issue the statements that make up the transaction. If the statements succeed, record their effect in the database and terminate the transaction by issuing a COMMIT statement:

§ mysql>CREATE TABLE t (i INT) ENGINE = InnoDB;

§ mysql> START TRANSACTION;

§ mysql> INSERT INTO t (i) VALUES(1);

§ mysql> INSERT INTO t (i) VALUES(2);

§ mysql> COMMIT;

§ mysql> SELECT * FROM t;

§ +------+

§ | i |

§ +------+

§ | 1 |

§ | 2 |

+------+

If an error occurs, don’t use COMMIT. Instead, cancel the transaction by issuing a ROLLBACK statement. In the following example, t remains empty after the transaction because the effects of the INSERT statements are rolled back:

mysql>CREATE TABLE t (i INT) ENGINE = InnoDB;

mysql> START TRANSACTION;

mysql> INSERT INTO t (i) VALUES(1);

mysql> INSERT INTO t (x) VALUES(2);

ERROR 1054 (42S22): Unknown column 'x' in 'field list'

mysql> ROLLBACK;

mysql> SELECT * FROM t;

Empty set (0.00 sec)

§ Another way to group statements is to turn off auto-commit mode explicitly by setting the autocommit session variable to 0. After that, each statement you issue becomes part of the current transaction. To end the transaction and begin the next one, issue a COMMIT or ROLLBACKstatement:

§ mysql>CREATE TABLE t (i INT) ENGINE = InnoDB;

§ mysql> SET autocommit = 0;

§ mysql> INSERT INTO t (i) VALUES(1);

§ mysql> INSERT INTO t (i) VALUES(2);

§ mysql> COMMIT;

§ mysql> SELECT * FROM t;

§ +------+

§ | i |

§ +------+

§ | 1 |

§ | 2 |

+------+

To turn auto-commit mode back on, use this statement:

mysql>SET autocommit = 1;

NOT EVERYTHING CAN BE UNDONE

Transactions have their limits, because not all statements can be part of a transaction. For example, if you issue a DROP DATABASE statement, don’t expect to restore the database by executing a ROLLBACK.

Performing Transactions from Within Programs

Problem

You’re writing a program that needs to implement transactional operations.

Solution

Use the transaction abstraction provided by your language API, if it has such a thing. If it doesn’t, use the API’s usual statement execution mechanism to issue the transactional SQL statements directly using the usual API database calls.

Discussion

When you issue statements interactively with the mysql program (as in the examples shown in the previous recipe), you can see by inspection whether statements succeed or fail and determine on that basis whether to commit or roll back. From within a non-interactive SQL script stored in a file, that doesn’t work so well. You cannot commit or roll back conditionally according to statement success or failure, because MySQL includes no IF/THEN/ELSE construct for controlling the flow of the script. (There is an IF() function, but that’s not the same thing.) For this reason, it’s most common to perform transactional processing from within a program, because you can use your API language to detect errors and take appropriate action. This recipe discusses some general background on how to do this. The next recipes provide language-specific details for the MySQL APIs for Perl, Ruby, PHP, Python, and Java.

Every MySQL API supports transactions, even if only in the sense that you can explicitly issue transaction-related SQL statements such as START TRANSACTION and COMMIT. However, some APIs also provide a transaction abstraction that enables you to control transactional behavior without working directly with SQL. This approach hides the details and provides better portability to other database engines that have different underlying transaction SQL syntax. An API abstraction is available for each of the languages that we use in this book.

The next few recipes each implement the same example to illustrate how to perform program-based transactions. They use a table t containing the following initial rows that show how much money two people have:

+------+------+

| name | amt |

+------+------+

| Eve | 10 |

| Ida | 0 |

+------+------+

The sample transaction is a simple financial transfer that uses two UPDATE statements to give six dollars of Eve’s money to Ida:

UPDATE money SET amt = amt - 6 WHERE name = 'Eve';

UPDATE money SET amt = amt + 6 WHERE name = 'Ida';

The intended result is that the table should look like this:

+------+------+

| name | amt |

+------+------+

| Eve | 4 |

| Ida | 6 |

+------+------+

It’s necessary to execute both statements within a transaction to ensure that both of them take effect at once. Without a transaction, Eve’s money disappears without being credited to Ida if the second statement fails. By using a transaction, the table will be left unchanged if statement failure occurs.

The sample programs for each language are located in the transactions directory of the recipes distribution. If you compare them, you’ll see that they all employ a similar framework for performing transactional processing:

§ The statements of the transaction are grouped within a control structure, along with a commit operation.

§ If the status of the control structure indicates that it did not execute successfully to completion, the transaction is rolled back.

That logic can be expressed as follows, where block represents the control structure used to group statements:

block:

statement 1

statement 2

...

statementn

commit

if the block failed:

roll back

If the statements in the block succeed, you reach the end of the block and commit them. Otherwise, occurrence of an error raises an exception that triggers execution of the error-handling code where you roll back the transaction.

The benefit of structuring your code as just described is that it minimizes the number of tests needed to determine whether to roll back. The alternative—checking the result of each statement within the transaction and rolling back on individual statement errors—quickly turns your code into an unreadable mess.

A subtle point to be aware of when rolling back within languages that raise exceptions is that it may be possible for the rollback itself to fail, causing another exception to be raised. If you don’t deal with that, your program itself may terminate. To handle this, issue the rollback within another block that has an empty exception handler. The sample programs do this as necessary.

Those sample programs that disable auto-commit mode explicitly when performing a transaction take care to enable auto-commit afterward. In applications that perform all database processing in transactional fashion, it’s unnecessary to do this. Just disable auto-commit mode once after you connect to the database server, and leave it off.

CHECKING HOW API TRANSACTION ABSTRACTIONS MAP ONTO SQL STATEMENTS

For APIs that provide a transaction abstraction, you can see how the interface maps onto the underlying SQL statements: enable the general query log for your MySQL server and then watch the logfile to see what statements the API executes when you run a transactional program. (See the MySQL Reference Manual for instructions on enabling the log.)

Using Transactions in Perl Programs

Problem

You want to perform a transaction in a Perl DBI script.

Solution

Use the standard DBI transaction support mechanism.

Discussion

The Perl DBI transaction mechanism is based on explicit manipulation of auto-commit mode:

1. Turn on the RaiseError attribute if it’s not enabled and disable PrintError if it’s on. You want errors to raise exceptions without printing anything, and leaving PrintError enabled can interfere with failure detection in some cases.

2. Disable the AutoCommit attribute so that a commit will be done only when you say so.

3. Execute the statements that make up the transaction within an eval block so that errors raise an exception and terminate the block. The last thing in the block should be a call to commit(), which commits the transaction if all its statements completed successfully.

4. After the eval executes, check the $@ variable. If $@ contains the empty string, the transaction succeeded. Otherwise, the eval will have failed due to the occurrence of some error and $@ will contain an error message. Invoke rollback() to cancel the transaction. If you want to display an error message, print $@ before calling rollback().

The following code shows how to follow those steps to perform our sample transaction:

# set error-handling and auto-commit attributes correctly

$dbh->{RaiseError} = 1; # raise exception if an error occurs

$dbh->{PrintError} = 0; # don't print an error message

$dbh->{AutoCommit} = 0; # disable auto-commit

eval

{

# move some money from one person to the other

$dbh->do ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");

$dbh->do ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");

# all statements succeeded; commit transaction

$dbh->commit ();

};

if ($@) # an error occurred

{

print "Transaction failed, rolling back. Error was:\n$@\n";

# roll back within eval to prevent rollback

# failure from terminating the script

eval { $dbh->rollback (); };

}

The code shown does not save the current values of the error-handling and auto-commit attributes before executing the transaction or restore them afterward. If you save and restore them, your transaction-handling code becomes more general because it does not affect other parts of your program that might use different attribute values, but more lines of code are required. To make transaction processing easier (while avoiding repetition of the extra code if you execute multiple transactions), create a couple of convenience functions to handle the processing that occurs before and after the eval:

sub transaction_init

{

my $dbh = shift;

my $attr_ref = {}; # create hash in which to save attributes

$attr_ref->{RaiseError} = $dbh->{RaiseError};

$attr_ref->{PrintError} = $dbh->{PrintError};

$attr_ref->{AutoCommit} = $dbh->{AutoCommit};

$dbh->{RaiseError} = 1; # raise exception if an error occurs

$dbh->{PrintError} = 0; # don't print an error message

$dbh->{AutoCommit} = 0; # disable auto-commit

return ($attr_ref); # return attributes to caller

}

sub transaction_finish

{

my ($dbh, $attr_ref, $error) = @_;

if ($error) # an error occurred

{

print "Transaction failed, rolling back. Error was:\n$error\n";

# roll back within eval to prevent rollback

# failure from terminating the script

eval { $dbh->rollback (); };

}

# restore error-handling and auto-commit attributes

$dbh->{AutoCommit} = $attr_ref->{AutoCommit};

$dbh->{PrintError} = $attr_ref->{PrintError};

$dbh->{RaiseError} = $attr_ref->{RaiseError};

}

By using those two functions, our sample transaction can be simplified considerably:

$ref = transaction_init ($dbh);

eval

{

# move some money from one person to the other

$dbh->do ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'");

$dbh->do ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'");

# all statements succeeded; commit transaction

$dbh->commit ();

};

transaction_finish ($dbh, $ref, $@);

In Perl DBI, an alternative to manipulating the AutoCommit attribute manually is to begin a transaction by invoking begin_work(). This method disables AutoCommit and causes it to be enabled again automatically when you invoke commit() or rollback() later.

Using Transactions in Ruby Programs

Problem

You want to perform a transaction in a Ruby DBI script.

Solution

Use the standard DBI transaction support mechanism. Actually, Ruby provides two mechanisms.

Discussion

The Ruby DBI module provides a couple of ways to perform transactions, although both of them rely on manipulation of auto-commit mode. One approach uses a begin/rescue block, and you invoke the commit and rollback methods explicitly:

begin

dbh['AutoCommit'] = false

dbh.do("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")

dbh.do("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")

dbh.commit

dbh['AutoCommit'] = true

rescue DBI::DatabaseError => e

puts "Transaction failed"

puts "#{e.err}: #{e.errstr}"

begin # empty exception handler in case rollback fails

dbh.rollback

dbh['AutoCommit'] = true

rescue

end

Ruby also supports a transaction method, which is associated with a code block and which commits or rolls back automatically depending on whether the code block succeeds or fails:

begin

dbh['AutoCommit'] = false

dbh.transaction do |dbh|

dbh.do("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'")

dbh.do("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'")

end

dbh['AutoCommit'] = true

rescue DBI::DatabaseError => e

puts "Transaction failed"

puts "#{e.err}: #{e.errstr}"

dbh['AutoCommit'] = true

end

With the transaction method, there is no need to invoke commit or rollback explicitly yourself. transaction does raise an exception if it rolls back, so the example still uses a begin/rescue block for error detection.

Using Transactions in PHP Programs

Problem

You want to perform a transaction in a PHP script.

Solution

Use the standard PEAR DB transaction support mechanism.

Discussion

The PEAR DB module supports a transaction abstraction that can be used to perform transactions. Use the autoCommit() method to disable auto-commit mode. Then, after issuing your statements, invoke either commit() or rollback() to commit or roll back the transaction.

The following code uses exceptions to signal transaction failure, which means that PHP 5 is required. (Earlier versions of PHP do not support exceptions.) The PEAR DB transaction-handling methods do not raise exceptions themselves when they fail, so the example program uses status checking within the try block to determine when to raise its own exception:

try

{

$result =& $conn->autoCommit (FALSE);

if (PEAR::isError ($result))