Yolinux.com Tutorial

MySQL InnoDB: Transactions and Foreign Keys

The MySQL database requires InnoDB to support transactions, foreign keys and referential integrity. This tutorial shows how to configure the MySQL database for the InnoDB database engine and examples showing the use of transactions and foreign keys.

For an introduction to installation, configuration and use of the MySQL database see the YoLinux MySQL tutorial.

Tutorial Table of Contents:

Related YoLinux Tutorials:

°Linux and MySQL

°MySQL Workbench

°SQL Workbench/J

°List of Linux Databases

°PostgreSQL

°MySQL C API

°SQLite

°Apache, Tomcat, MySQL and Java

°YoLinux Tutorials Index




Free Information Technology Magazines and Document Downloads
TradePub link image


MySQL Database Engines:

MySQL is a SQL database processing layer on top of a storage engine. The default engine is MyISAM which is very fast but does not support higher level functionality such as foreign keys or transactions. One must use the InnoDB database storage engine to support foreign keys and transactions. Since the purchase of MySQL by Oracle, the Oracle corporation has been developing the InnoDB database storage layer to include even more capabilities and to match the performance of MyISAM. The future of the MySQL database will be to use InnoDB. Other database storage engines are available but MyISAM and InnoDB are the most commonly used.

The database engine is set as a default or specified for a given table using the ALTER statement or during creation.

  • Set as default: mysql> SET storage_engine=InnoDB;
  • Alter the table after creation: mysql> ALTER TABLE employee TYPE=InnoDB;
  • Specify during table creation:
    CREATE TABLE employee (
        IDpk  INTEGER NOT NULL AUTO_INCREMENT,
        ssn   CHAR(11) NOT NULL, 
        name  CHAR(64),
        phone CHAR(32),
        PRIMARY KEY (IDpk)
    ) ENGINE = InnoDB;
    

List the storage engines available: mysql> SHOW ENGINES;

+------------+---------+----------------------------------------------------------------+
| Engine     | Support | Comment                                                        |
+------------+---------+----------------------------------------------------------------+
| MyISAM     | DEFAULT | Default engine as of MySQL 3.23 with great performance         |
| MEMORY     | YES     | Hash based, stored in memory, useful for temporary tables      |
| InnoDB     | YES     | Supports transactions, row-level locking, and foreign keys     |
| BerkeleyDB | YES     | Supports transactions and page-level locking                   |
| BLACKHOLE  | NO      | /dev/null storage engine (anything you write to it disappears) |
| EXAMPLE    | NO      | Example storage engine                                         |
| ARCHIVE    | NO      | Archive storage engine                                         |
| CSV        | NO      | CSV storage engine                                             |
| ndbcluster | NO      | Clustered, fault-tolerant, memory-based tables                 |
| FEDERATED  | NO      | Federated MySQL storage engine                                 |
| MRG_MYISAM | YES     | Collection of identical MyISAM tables                          |
| ISAM       | NO      | Obsolete storage engine                                        |
+------------+---------+----------------------------------------------------------------+


MySQL configuration for InnoDB:

This configures the MySQL database to allow the use of the InnoDB data storage engine.

File: /etc/my.cnf (MySQL 5.0 / RHEL 5.5)

[mysqld] #bind-address    = XXX.XXX.XXX.XXX
port        = 3306 socket      = /var/lib/mysql/mysql.sock
skip-external-locking 
...
..
...
thread_cache_size = 32
# starting with 2*(#CPUs)*(#Disks)... (see /proc/cpuinfo)
innodb_thread_concurrency = 8

...
..
...

# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/

#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size = 4096M
innodb_additional_mem_pool_size = 20M

#innodb_flush_log_at_trx_commit = 1
#Use:     (0=dramatic performance increase, w/ data loss on crash)
#         (2=no data loss)
innodb_flush_log_at_trx_commit = 2

innodb_flush_method=O_DIRECT
innodb_rollback_on_timeout=1

default-storage_engine=innodb
...
..
Note that this is not a complete MySQL database configuration file but it is representative of the changes to a standard configuration required to support InnoDB.


MySQL Transactions:

Database transaction support refers to the need to handle a bundle of SQL transactions as a single action. The entire group of SQL instructions must all occur or have none occur at all. This is often referred to as ACID: Atomicity, Consistency, Isolation and Durability.

The requirement is to support actions such as the following banking account transaction example:

  1. Subtract $500.00 from the account of Mark
  2. Add $500.00 to the account of John
It is essential that both occur. If a system failure occurs and only one transaction occurs, then the database would be in error and in an inconsistent state.

ACID:
  • Atomicity: The transaction must either all be carried out successfully, or none at all. If aborted, the previous operations are rolled back to their former state.
  • Consistency: The database is always in a proper state and not in error.
  • Isolation: Data being used for one transaction cannot be used by another transaction until the first transaction is complete.
  • Durability: Once a transaction has completed correctly, its effects should remain, and not be reversible (unless another transaction or set of SQL commands purposefully directs a change).
-- Disable auto commit:
SET autocommit=0;

START TRANSACTION;
UPDATE account1 SET balance=balance-500;
UPDATE account2 SET balance=balance+500;
COMMIT;
Note:
  • The tables "account1" and "account2" must be defined with the type InnoDB.
  • By default, MySQL runs with auto commit mode enabled.
    Auto commit is disabled for a series of statements with the directive START TRANSACTION.
  • START TRANSACTION WITH CONSISTENT SNAPSHOT;
    Auto-commit remains disabled until you end the transaction with COMMIT or ROLLBACK. The auto commit mode then reverts to its previous state.
  • BEGIN and BEGIN WORK are aliases for START TRANSACTION
    This is NOT the same as the BEGIN/END compound statements which do NOT define a transaction.
  • MySQL.com documentation: MySQL START TRANSACTION, COMMIT, and ROLLBACK Syntax


JDBC and MySQL transactions:

The following Java JDBC transaction example uses a MySQL database configured in the YoLinux.com MySQL tutorial.

Note that the transaction of two inserts is surrounded by the JDBC calls to setAutoCommit(false) and commit(). The state is later returned to setAutoCommit(true). Upon failure on either insert, the exception block will execute and a JDBC call is made to rollback().

This ensures that both database actions (the transaction), are executed or neither are executed.

File: JdbcProg.java
import java.io.*;
import java.sql.*;
import javax.sql.*;
import java.util.*;

public class JdbcProg {
  public static void main(String[] args) 
  {
      Connection  dbConn = null;
      Statement statement1 = null;
      Statement statement2 = null;

      try
      {
          Class.forName("com.mysql.jdbc.Driver").newInstance();
          System.out.println("MySQL Driver Class Instantiation");
      }
      catch (InstantiationException ie)
      {
          System.out.println("Class Instantiation Exception: " + ie);
      }
      catch (ClassNotFoundException cnf)
      {
          System.out.println("Class Not Found Exception: " + cnf);
      } 
      catch (IllegalAccessException iae)
      {
           System.out.println("Illegal Access Exception: " + iae);
      }

     
      try
      {
            dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bedrock", "root", "wess-hpc12-MySQL");
            System.out.println("MySQL connection made");

            // Setup Transaction
            // The following block executes a total of two inserts 
            dbConn.setAutoCommit(false);

            String insert1 = "INSERT INTO employee ( Name, Dept ) VALUES( 'Joe Bossmann', 'Sales' )";
            statement1 = dbConn.createStatement();
            statement1.executeUpdate(insert1);

            String insert2 = "INSERT INTO employee ( Name, Dept ) VALUES( 'Mr. Grunt', 'Sales' )";
            statement2 = dbConn.createStatement();
            statement2.executeUpdate(insert2);

            dbConn.commit();
      }
      catch (SQLException ex)          // executes upon SQL exception: This block will perform a rollback()
      {
           try
           {
               if( dbConn != null){
                  dbConn.rollback();
               }
           } catch (SQLException ex2) {
              System.out.println("Caught SQL Exception: " + ex2);
           }

           while (ex != null) {
               System.out.println ("SQL Exception:  " + ex.getMessage ());
               ex = ex.getNextException ();
           }
      }
      finally  // This always gets executed when the try block exits with or without an exception: No rollback()
      {
           try
           {
               if(statement1 != null)
               {
                   statement1.close();
               }

               if(statement2 != null)
               {
                   statement2.close();
               }

               if( dbConn != null){
                  dbConn.setAutoCommit(true);
                  dbConn.close();
               }
           } catch (SQLException exf) {
              System.out.println("Caught SQL Exception: " + exf);
           }
      }
       
  }
}
Compile:
export CLASSPATH=/usr/java/latest/lib/tools.jar:/opt/java/lib/mysql-connector-java-5.1.14-bin.jar:./
javac JdbcProg.java
Run:
java JdbcProg


PHP and MySQL transactions:

<?php
// trans.php
function begin()
{
@mysql_query("BEGIN");
}
function commit()
{
@mysql_query("COMMIT");
}
function rollback()
{
@mysql_query("ROLLBACK");
}
@mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());
@mysql_select_db("bedrock") or die(mysql_error());
$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";
begin(); // transaction begins
$result = @mysql_query($query);
if(!$result)
{
rollback(); // transaction rolls back
echo "transaction rolled back";
exit;
}
else
{
commit(); // transaction is committed
echo "Database transaction was successful";
}
?>


MySQL Foreign Keys:

Support for foreign keys requires the InnoDB data storage engine and MySQL version 3.23.44 and later

The foreign key is the tie between database tables where an index (record locator) is stored in one table (referencing table) and used to find the specified data record in another table (referenced table). Foreign keys are subject to the following constraints:
  • Neither table can be a TEMPORARY table.
  • BLOB and TEXT columns cannot be included in a foreign key. (Typically integers are used to point to an index.)
InnoDB supports the following five options:
  • CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. This will allow one delete of a record from parent table to automatically spawn a delete from a child table, using only one delete statement. To avoid deleting the child record, one must first set the foreign key value of the parent record to NULL.
    • ON DELETE CASCADE
    • ON UPDATE CASCADE (MySQL version 4.0.8 and later)
  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier.
    • ON DELETE SET NULL
    • ON UPDATE SET NULL (MySQL version 4.0.8 and later)
  • NO ACTION: Do not delete record if there is a related foreign key value in the referenced table. One must delete the parent record first.
  • RESTRICT: Rejects the delete or update operation for the parent table. Same as NO ACTION.
    This is the default: ON DELETE RESTRICT (same as ON DELETE NO ACTION)
  • SET DEFAULT: recognized but rejected and not handled by InnoDB. Don't use this!
Example:
CREATE TABLE employee (
    IDpk    INTEGER NOT NULL AUTO_INCREMENT,
    ssn     CHAR(11) NOT NULL, 
    name    CHAR(64),
    phone   CHAR(32),
    dept_fk INTEGER NOT NULL,
    PRIMARY KEY (IDpk)
) ENGINE = InnoDB;

CREATE TABLE department (
    IDpk        INTEGER NOT NULL AUTO_INCREMENT,
    dept_name   CHAR(64),
    PRIMARY KEY (IDpk)
) ENGINE = InnoDB;

ALTER TABLE employee ADD FOREIGN KEY(dept_fk) REFERENCES department (IDpk) ON DELETE CASCADE;

INSERT INTO department (dept_name) VALUES ('accounting');
INSERT INTO department (dept_name) VALUES ('engineering');

INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-6789','Matt','1-800-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-7890','Mark','1-801-555-1212', 2);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-8901','Luke','1-802-555-1212', 1);
INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-9012','John','1-803-555-1212', 2);

Now we demonstrate the cascading delete:

mysql> SELECT * FROM employee;
+------+-------------+------+----------------+---------+
| IDpk | ssn         | name | phone          | dept_fk |
+------+-------------+------+----------------+---------+
|    1 | 123-45-6789 | Matt | 1-800-555-1212 |       1 |
|    2 | 123-45-7890 | Mark | 1-801-555-1212 |       2 |
|    3 | 123-45-8901 | Luke | 1-802-555-1212 |       1 |
|    4 | 123-45-9012 | John | 1-803-555-1212 |       2 |
+------+-------------+------+----------------+---------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM department;
+------+-------------+
| IDpk | dept_name   |
+------+-------------+
|    1 | accounting  |
|    2 | engineering |
+------+-------------+
2 rows in set (0.00 sec)

mysql>  DELETE FROM department WHERE dept_name='engineering';
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM employee;
+------+-------------+------+----------------+---------+
| IDpk | ssn         | name | phone          | dept_fk |
+------+-------------+------+----------------+---------+
|    1 | 123-45-6789 | Matt | 1-800-555-1212 |       1 |
|    3 | 123-45-8901 | Luke | 1-802-555-1212 |       1 |
+------+-------------+------+----------------+---------+
2 rows in set (0.00 sec)
What does this mean? If the "engineering" department record is removed, then the employees in the group are fired and removed from the employee table. The other employees are retained.

If this is performed with the default MyISAM database storage engine, then no change will occur to the employee table. The commands will be accepted but the cascading delete will not happen. The InnoDB database storage engine is required.

To see how a table was generated and with what options, execute the command:

SHOW CREATE TABLE table_name_to_check


Links:


Books:

book cover image "MySQL, Second edition"
by Paul DuBois
ISBN # 0735712123, New Riders Publishing

Amazon.com
book cover image "Managing and Using MySQL"
by George Reese, Randy Jay Yarger, Tim King
ISBN # 0596002114, O'Reilly

Amazon.com
book cover image "MySQL Cookbook"
by Paul DuBois
ISBN # 0596001452, O'Reilly

Amazon.com
book cover image "High Performance MySQL"
by Jeremy D. Zawodny, Derek J. Balling
ISBN # 0596003064, O'Reilly

Amazon.com
book cover image "The Definitive Guide to MySQL, Second Edition"
by Michael Kofler
ISBN # 1590591445, APress

Amazon.com
book cover image "MySQL Certification Study Guide"
by Paul Dubois, Stefan Hinz, Carsten Pedersen
ISBN # 0672326329, Sams

Amazon.com

   

    Bookmark and Share


Advertisements





Copyright © 2011 by Greg Ippolito