Performing Transactions Using SQL

15.3.1 Problem

You need to issue a set of queries that must succeed or fail as a unit.

15.3.2 Solution

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

15.3.3 Discussion

This section describes the SQL statements that control transactional behavior in MySQL. The immediately following sections discuss how to perform transactions from within programs. Some APIs require that you implement transactions by issuing the SQL statements discussed in this section; others provide a special mechanism that allows 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 section 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 as it executes. (In effect, each statement is its own transaction.) To perform a multiple-statement 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 BEGIN (or BEGIN WORK) statement to suspend auto-commit mode, then issue the queries that make up the transaction. If the queries succeed, record their effect in the database and terminate the transaction by issuing a COMMIT statement:

    mysql> CREATE TABLE t (i INT) TYPE = InnoDB;
    mysql> BEGIN;
    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) TYPE = InnoDB;
    mysql> BEGIN;
    mysql> INSERT INTO t (i) VALUES(1);
    mysql> INSERT INTO t (x) VALUES(2);
    ERROR 1054 at line 5: 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. Then each statement you issue becomes part of the current transaction. To end the transaction and begin the next one, issue a COMMIT or ROLLBACK statement:

    mysql> CREATE TABLE t (i INT) TYPE = 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 get back the database by executing a ROLLBACK.

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Similar book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net