Learning Mysql

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 anew artist and album to the database. You want to ensure that either both actions succeed or neither do, and you want to carryout the process incomplete isolation from other users you don’t want to insert tracks fora 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 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 |

Download 4.24 Mb.

Share with your friends: