Home arrow MySQL arrow Page 5 - Using Transactions In MySQL (Part 1)

A Question of Commitment - MySQL

mysqlOne 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.

TABLE OF CONTENTS:
  1. Using Transactions In MySQL (Part 1)
  2. Money, Money, Money...
  3. The Acid Test
  4. Turning the Tables
  5. A Question of Commitment
  6. Rules of the Game
  7. Artificial Intelligence
  8. Time Out
By: icarus, (c) Melonfire
Rating: starstarstarstarstar / 74
November 03, 2003

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement
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.
  1. 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.

  2. 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:

    1. 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)


    2. 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)


    3. 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)


  3. 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.



     
     
    >>> More MySQL Articles          >>> More By icarus, (c) Melonfire
     

    blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: