MySQL

Understanding Transaction Processing

The two most commonly used engines are MyISAM and InnoDB. The MyISAM does not support explicit transaction management and the InnoDB does. If you need transaction processing functionality in your applications, be sure to use the correct InnoDB engine type.

Transaction processing is used to maintain database integrity by ensuring that batches of MySQL operations execute completely or not at all.

Transaction processing is a mechanism used to manage sets of MySQL operations that must be executed in batches to ensure that databases never contain the results of partial operations. With transaction processing, you can ensure that sets of operations are not aborted mid-processing they either execute in their entirety or not at all (unless explicitly instructed otherwise). If no error occurs, the entire set of statements is committed (written) to the database tables. If an error does occur, a rollback (undo) can occur to restore the database to a known and safe state.

When working with transactions and transaction processing, there are a few keywords that'll keep reappearing. Here are the terms you need to know:

  • Transaction A block of SQL statements

  • Rollback The process of undoing specified SQL statements

  • Commit Writing unsaved SQL statements to the database tables

  • Savepoint A temporary placeholder in a transaction set to which you can issue a rollback (as opposed to rolling back an entire transaction)

The key to managing transactions involves breaking your SQL statements into logical chunks and explicitly stating when data should be rolled back and when it should not.

The MySQL statement used to mark the start of a transaction is

START TRANSACTION

Using ROLLBACK

The MySQL ROLLBACK command is used to roll back (undo) MySQL statements.

ROLLBACK can only be used within a transaction (after a START TRANSACTION command has been issued).

Which Statements Can You Roll Back? Transaction processing is used to manage INSERT, UPDATE, and DELETE statements. You cannot roll back SELECT statements. (There would not be much point in doing so anyway.) You cannot roll back CREATE or DROP operations. These statements may be used in a transaction block, but if you perform a rollback they will not be undone.

Using COMMIT

MySQL statements are usually executed and written directly to the database tables. This is known as an implicit commit the commit (write or save) operation happens automatically.

Within a transaction block, however, commits do not occur implicitly. To force an explicit commit, the COMMIT statement is used.

Implicit Transaction Closes After a COMMIT or ROLLBACK statement has been executed, the transaction is automatically closed (and future changes will implicitly commit).

Using Savepoints

Simple ROLLBACK and COMMIT statements enable you to write or undo an entire transaction. Although this works for simple transactions, more complex transactions might require partial commits or rollbacks.

To support the rollback of partial transactions, you must be able to put placeholders at strategic locations in the transaction block. Then, if a rollback is required, you can roll back to one of the placeholders.

These placeholders are called savepoints, and to create one use the SAVEPOINT statement

Each savepoint takes a unique name that identifies it so that, when you roll back, MySQL knows where you are rolling back to.

The More Savepoints the Better You can have as many savepoints as you'd like within your MySQL code, and the more the better. Why? Because the more savepoints you have the more flexibility you have in managing rollbacks exactly as you need them.

Releasing Savepoints Savepoints are automatically released after a transaction completes (a ROLLBACK or COMMIT is issued). As of MySQL 5, savepoints can also be explicitly released using RELEASE SAVEPOINT.

Changing the Default Commit Behavior

The default MySQL behavior is to automatically commit any and all changes. In other words, any time you execute a MySQL statement, that statement is actually being performed against the tables, and the changes made occur immediately. To instruct MySQL to not automatically commit changes, you need to use the following statement:

SET autocommit=0;

The autocommit flag determines whether changes are committed automatically without requiring a manual COMMIT statement. Setting autocommit to 0 (false) instructs MySQL to not automatically commit changes (until the flag is set back to true).

Flag Is Connection Specific The autocommit flag is per connection, not server-wide.

For more information visit http://dev.mysql.com/doc/refman/5.0/en/commit.html.