In some applications, it is vitally important that a sequence of queries runs in the correct order and that every single query successfully completes. For example, suppose that you are creating a sequence of queries to transfer funds from one bank account to another. You would not want either of the following events to occur:
You add the funds to the second account, but when you try to subtract them from the first account the update fails, and now both accounts have the funds.
You subtract the funds from the first bank account, but the update request to add them to the second account fails, and the funds have now disappeared into thin air.
As you can see, not only is the order of queries important in this type of transaction, but it is also vital that all parts of the transaction complete successfully. But how can you ensure this happens, because surely after a query has occurred, it cannot be undone? Do you have to keep track of all parts of a transaction and then undo them all one at a time if any one fails? The answer is absolutely not, because MySQL comes with powerful transaction handling features to cover just these types of eventualities.
In addition, transactions allow concurrent access to a database by many users or programs at the same time. MySQL handles this seamlessly, ensuring that all transactions are queued up and that the users or programs take their turns and don’t tread on each other’s toes.
In order to be able to use MySQL’s transaction facility, you have to be using MySQL’s InnoDB storage engine. This is easy to do, as it’s simply another parameter that you use when creating a table. Go ahead and create a table of bank accounts by typing in the commands in Example 9-1. (Remember that to do this you will need access to the MySQL command line, and you must also have already selected a suitable database in which to create this table.)
CREATE TABLE accounts ( number INT, balance FLOAT, PRIMARY KEY(number) ) ENGINE InnoDB; DESCRIBE accounts;
The final line of this example displays the contents of the new table so you can ensure that it was created correctly. The output from it should look like this:
+---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | number | int(11) | NO | PRI | 0 | | | balance | float | YES | | NULL | | +---------+---------+------+-----+---------+-------+ 2 rows in set (0.00 sec)
Now let’s create two rows within the table so that you can practice using transactions. Type in the commands in Example 9-2.
INSERT INTO accounts(number, balance) VALUES(12345, 1025.50); INSERT INTO accounts(number, balance) VALUES(67890, 140.00); SELECT * FROM accounts;
The third line displays the contents of the table to confirm that the rows were inserted correctly. The output should look like this:
+--------+---------+ | number | balance | +--------+---------+ | 12345 | 1025.5 | | 67890 | 140 | +--------+---------+ 2 rows in set (0.00 sec)
With this table created and prepopulated, you are now ready to start using transactions.
Transactions in MySQL start with either a BEGIN or a START
TRANSACTION statement. Type in the commands in Example 9-3 to send a transaction to
MySQL.
BEGIN; UPDATE accounts SET balance=balance+25.11 WHERE number=12345; COMMIT; SELECT * FROM accounts;
The result of this transaction is displayed by the final line, and should look like this:
+--------+---------+ | number | balance | +--------+---------+ | 12345 | 1050.61 | | 67890 | 140 | +--------+---------+ 2 rows in set (0.00 sec)
As you can see, the balance of account number 12345 was increased
by 25.11 and is now 1050.61. You may also have noticed the COMMIT command in Example 9-3, which is explained next.
When you are satisfied that a series of queries in a transaction
has successfully completed, issue a COMMIT command to commit all the changes to
the database. Until a COMMIT is
received, all the changes you make are considered by MySQL to be merely
temporary. This feature gives you the opportunity to cancel a
transaction by not sending a COMMIT,
but issuing a ROLLBACK command
instead.
Using the ROLLBACK command, you
can tell MySQL to forget all the queries made since the start of a
transaction and to end the transaction. Check this out in action by
entering the funds transfer transaction in Example 9-4.
BEGIN; UPDATE accounts SET balance=balance-250 WHERE number=12345; UPDATE accounts SET balance=balance+250 WHERE number=67890; SELECT * FROM accounts;
Once you have entered these lines, you should see the following result:
+--------+---------+ | number | balance | +--------+---------+ | 12345 | 800.61 | | 67890 | 390 | +--------+---------+ 2 rows in set (0.00 sec)
The first bank account now has a value that is 250 less than before, and the second has been incremented by 250—you have transferred a value of 250 between them. But let’s assume that something went wrong and you wish to undo this transaction. All you have to do is issue the commands in Example 9-5.
You should now see the following output, showing that the two
accounts have had their previous balances restored, due to the entire
transaction being cancelled using the ROLLBACK command:
+--------+---------+ | number | balance | +--------+---------+ | 12345 | 1050.61 | | 67890 | 140 | +--------+---------+ 2 rows in set (0.00 sec)