HomeMySQL Page 5 - Using Transactions In MySQL (Part 1)
A Question of Commitment - MySQL
One of the most-requested MySQL features - transactions - is finally available in MySQL 4.0. In this first segment of a two-part article, learn about the theory behind the transactional model, find out how it can make your SQL applications more robust, and find out how to implement a transactional environment with MySQL's InnoDB table handler.
There are three main phases in the life cycle of a transaction. Here's a flowchart illustrating these phases.
Let's now look at each of these in detail.
Starting the transaction: In order to initiate a transaction, MySQL offers the BEGIN WORK command. When you issue this command, MySQL assumes that everything following it is part of a transaction, and therefore subject to ACID rules.
mysql> BEGIN WORK; Query OK, 0 rows affected (0.00 sec)
You can also abbreviate this command to just BEGIN, or use the equivalent START TRANSACTION command.
Performing the transaction: Once the transaction has been initiated, you can use regular SQL commands to manipulate the database. The isolation principle ensures that the changes you make are not visible to other users of the database. Actually, that's not completely true, but accept it for the moment and I'll discuss the caveats a little further along.
In this example, let us assume that a transaction consists of adding a new user to the system, using the following steps:
Create a new user record in the "users" table with the user's name and password.
mysql> SELECT * FROM users; +----+------+------------------+ | id | name | pass | +----+------+------------------+ | 1 | alan | 5af23f026beddb81 | +----+------+------------------+ 1 row in set (0.02 sec)
Set the user's group memberships using the ID generated by the first step.
Ending the transaction: Once the steps involved in the transaction have been completed, you have a choice. You can either save the changes made by the entire transaction, or you can undo everything you just did and revert the tables back to the state they were in before you issued the BEGIN WORK command.
The ROLLBACK command cancels the transaction and reverts the system back to its initial state. This can be clearly verified by checking the various tables with SELECT queries - you'll see that the changes made by the INSERT statements above have not registered at all:
mysql> SELECT * FROM users; Empty set (0.01 sec)
mysql> SELECT * FROM groups; Empty set (0.01 sec)
mysql> SELECT * FROM mailboxes; Empty set (0.00 sec)
What about swinging the other way and saving the changes to disk once the transaction is done? Start the transaction again, insert the records as above, and when you're done, save the changes with the COMMIT command:
And now, when you check the various tables again, you'll see that the user records have all been saved to the system:
mysql> SELECT * FROM users; +----+------+------------------+ | id | name | pass | +----+------+------------------+ | 3 | alan | 5af23f026beddb81 | +----+------+------------------+ 1 row in set (0.01 sec)
mysql> SELECT * FROM groups; +-----+-------+ | uid | grp | +-----+-------+ | 3 | hr | | 3 | admin | +-----+-------+ 2 rows in set (0.01 sec)
Typically, an application developer would write code to check for errors during a transaction and generate a ROLLBACK if any occur (an example of such an application can be found in the second part of this article). The COMMIT and ROLLBACK commands thus play an important rule in satisfying the atomicity principle, and in ensuring the integrity of the database at all times.