MySQL InnoDB: Transactions and Foreign Keys

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:

view source print ?
1 CREATE TABLE employee (
2     IDpk INTEGER NOT NULL AUTO_INCREMENT,
3     ssn   CHAR(11) NOT NULL,
4     name CHAR(64),
5     phone CHAR(32),
6     PRIMARY KEY (IDpk)
7 ) 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)

01[mysqld] #bind-address    = XXX.XXX.XXX.XXX
02port        = 3306 socket      = /var/lib/mysql/mysql.sock
03skip-external-locking 
04...
05..
06...
07thread_cache_size = 32
08# starting with 2*(#CPUs)*(#Disks)... (see /proc/cpuinfo)
09innodb_thread_concurrency = 8
10 
11...
12..
13...
14 
15# Data files must be able to hold your data and indexes.
16# Make sure that you have enough free disk space.
17 
18# Uncomment the following if you are using InnoDB tables
19innodb_data_home_dir = /var/lib/mysql/
20innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
21innodb_log_group_home_dir = /var/lib/mysql/
22 
23#
24# Set buffer pool size to 50-80% of your computer's memory
25innodb_buffer_pool_size = 4096M
26innodb_additional_mem_pool_size = 20M
27 
28#innodb_flush_log_at_trx_commit = 1
29#Use:     (0=dramatic performance increase, w/ data loss on crash)
30#         (2=no data loss)
31innodb_flush_log_at_trx_commit = 2
32 
33innodb_flush_method=O_DIRECT
34innodb_rollback_on_timeout=1
35 
36default-storage_engine=innodb
37...
38..

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:

Subtract $500.00 from the account of Mark
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).

1-- Disable auto commit:
2SET autocommit=0;
3 
4START TRANSACTION;
5UPDATE account1 SET balance=balance-500;
6UPDATE account2 SET balance=balance+500;
7COMMIT;

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

01import java.io.*;
02import java.sql.*;
03import javax.sql.*;
04import java.util.*;
05 
06public class JdbcProg {
07  public static void main(String[] args) 
08  {
09      Connection  dbConn = null;
10      Statement statement1 = null;
11      Statement statement2 = null;
12 
13      try
14      {
15          Class.forName("com.mysql.jdbc.Driver").newInstance();
16          System.out.println("MySQL Driver Class Instantiation");
17      }
18      catch (InstantiationException ie)
19      {
20          System.out.println("Class Instantiation Exception: " + ie);
21      }
22      catch (ClassNotFoundException cnf)
23      {
24          System.out.println("Class Not Found Exception: " + cnf);
25      } 
26      catch (IllegalAccessException iae)
27      {
28           System.out.println("Illegal Access Exception: " + iae);
29      }
30 
31      
32      try
33      {
34            dbConn = DriverManager.getConnection("jdbc:mysql://localhost:3306/bedrock", "root", "wess-hpc12-MySQL");
35            System.out.println("MySQL connection made");
36 
37            // Setup Transaction
38            // The following block executes a total of two inserts 
39            dbConn.setAutoCommit(false);
40 
41            String insert1 = "INSERT INTO employee ( Name, Dept ) VALUES( 'Joe Bossmann', 'Sales' )";
42            statement1 = dbConn.createStatement();
43            statement1.executeUpdate(insert1);
44 
45            String insert2 = "INSERT INTO employee ( Name, Dept ) VALUES( 'Mr. Grunt', 'Sales' )";
46            statement2 = dbConn.createStatement();
47            statement2.executeUpdate(insert2);
48 
49            dbConn.commit();
50      }
51      catch (SQLException ex)          // executes upon SQL exception: This block will perform a rollback()
52      {
53           try
54           {
55               if( dbConn != null){
56                  dbConn.rollback();
57               }
58           } catch (SQLException ex2) {
59              System.out.println("Caught SQL Exception: " + ex2);
60           }
61 
62           while (ex != null) {
63               System.out.println ("SQL Exception:  " + ex.getMessage ());
64               ex = ex.getNextException ();
65           }
66      }
67      finally  // This always gets executed when the try block exits with or without an exception: No rollback()
68      {
69           try
70           {
71               if(statement1 != null)
72               {
73                   statement1.close();
74               }
75 
76               if(statement2 != null)
77               {
78                   statement2.close();
79               }
80 
81               if( dbConn != null){
82                  dbConn.setAutoCommit(true);
83                  dbConn.close();
84               }
85           } catch (SQLException exf) {
86              System.out.println("Caught SQL Exception: " + exf);
87           }
88      }
89        
90  }
91}

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:

01<?php
02// trans.php
03function begin()
04{
05@mysql_query("BEGIN");
06}
07function commit()
08{
09@mysql_query("COMMIT");
10}
11function rollback()
12{
13@mysql_query("ROLLBACK");
14}
15@mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());
16@mysql_select_db("bedrock") or die(mysql_error());
17$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')";
18begin(); // transaction begins
19$result = @mysql_query($query);
20if(!$result)
21{
22rollback(); // transaction rolls back
23echo "transaction rolled back";
24exit;
25}
26else
27{
28commit(); // transaction is committed
29echo "Database transaction was successful";
30}
31?>

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:

01CREATE TABLE employee (
02    IDpk    INTEGER NOT NULL AUTO_INCREMENT,
03    ssn     CHAR(11) NOT NULL, 
04    name    CHAR(64),
05    phone   CHAR(32),
06    dept_fk INTEGER NOT NULL,
07    PRIMARY KEY (IDpk)
08) ENGINE = InnoDB;
09 
10CREATE TABLE department (
11    IDpk        INTEGER NOT NULL AUTO_INCREMENT,
12    dept_name   CHAR(64),
13    PRIMARY KEY (IDpk)
14) ENGINE = InnoDB;
15 
16ALTER TABLE employee ADD FOREIGN KEY(dept_fk) REFERENCES department (IDpk) ON DELETE CASCADE;
17 
18INSERT INTO department (dept_name) VALUES ('accounting');
19INSERT INTO department (dept_name) VALUES ('engineering');
20 
21INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-6789','Matt','1-800-555-1212', 1);
22INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-7890','Mark','1-801-555-1212', 2);
23INSERT INTO employee (ssn, name, phone, dept_fk) VALUES ('123-45-8901','Luke','1-802-555-1212', 1);
24INSERT 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:

MySQL.com:
- MySQL reference manual
- MySQL developers manual

Books:

	"MySQL, Second edition" by Paul DuBois ISBN # 0735712123, New Riders Publishing
	"Managing and Using MySQL" by George Reese, Randy Jay Yarger, Tim King ISBN # 0596002114, O'Reilly
	"MySQL Cookbook" by Paul DuBois ISBN # 0596001452, O'Reilly
	"High Performance MySQL" by Jeremy D. Zawodny, Derek J. Balling ISBN # 0596003064, O'Reilly
	"The Definitive Guide to MySQL, Second Edition" by Michael Kofler ISBN # 1590591445, APress
	"MySQL Certification Study Guide" by Paul Dubois, Stefan Hinz, Carsten Pedersen ISBN # 0672326329, Sams