Advanced Querying - Learning MySQL - page 273

Transaction examples
Because transactions are the key feature that make InnoDB different from MyISAM,
we'll conclude this section with an introductory example that shows how they work.
Suppose you decide you want to add a new artist and album to the database. You want
to ensure that either both actions succeed or neither do, and you want to carry out the
process in complete isolation from other users; you don't want to insert tracks for a
peculiar artist ID if the artist_id values is already taken for another artist, or other
people using your data until it's finalized. To do this with a transaction, we need to
first, change the table type for artist and album to InnoDB:
mysql> ALTER TABLE artist type = InnoDB;
Query OK, 7 rows affected, 1 warning (0.54 sec)
Records: 7 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE album type = InnoDB;
Query OK, 14 rows affected, 1 warning (0.01 sec)
Records: 14 Duplicates: 0 Warnings: 0
With the InnoDB tables, we can now perform the following transaction:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO artist VALUES (8, "The Cure");
Query OK, 1 row affected (0.04 sec)
mysql> INSERT INTO album VALUES (8, 1, "Disintegration");
Query OK, 1 row affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)
The first statement, START TRANSACTION , tells MySQL that you're beginning a set of
statements you want in isolation and to be treated as a block or atomic entity . You then
execute the two statements that modify the database. At the conclusion, you tell
MySQL to COMMIT —that is, end the transaction and make the changes to the database.
Transactions also allow you to abort or rollback—that is, undo everything that's in the
transaction. Let's try an example where we do just that:
mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO artist VALUES (9, "The Wh");
Query OK, 1 row affected (0.01 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)
mysql> SELECT * FROM artist;
+-----------+---------------------------+
| artist_id | artist_name |