Using Transactions In MySQL (Part 1) - A Question of Commitment (
Page 5 of 8 ) 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> INSERT INTO users (name, pass) VALUES ('alan',
mysql> PASSWORD('ferret'));
Query OK, 1 row affected (0.06 sec)
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.
mysql> INSERT INTO groups (uid, grp) VALUES (LAST_INSERT_ID(), 'hr'),
(LAST_INSERT_ID(), 'admin');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM groups;
+-----+-------+
| uid | grp |
+-----+-------+
| 1 | hr |
| 1 | admin |
+-----+-------+
2 rows in set (0.00 sec)
- Set up the user's mailbox using the ID generated by the first step.
mysql> INSERT INTO mailboxes (uid, host, mboxname, mboxpass) VALUES
(LAST_INSERT_ID(), 'my.pop.server', 'my.name', 'my.pass'); Query OK, 1 row affected (0.01 sec)
mysql> SELECT * FROM mailboxes;
+-----+---------------+----------+----------+
| uid | host | mboxname | mboxpass |
+-----+---------------+----------+----------+
| 1 | my.pop.server | my.name | my.pass |
+-----+---------------+----------+----------+
1 row in set (0.01 sec)
- 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.
Let's try cancelling first:
mysql> ROLLBACK;
Query OK, 0 rows affected (0.03 sec)
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:
mysql> COMMIT;
Query OK, 0 rows affected (0.01 sec)
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)
mysql> SELECT * FROM mailboxes;
+-----+---------------+----------+----------+
| uid | host | mboxname | mboxpass |
+-----+---------------+----------+----------+
| 3 | my.pop.server | my.name | my.pass |
+-----+---------------+----------+----------+
1 row in set (0.00 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.
|
