I Didn't Know That - MySQL, MyISAM and Auto-Commit

i have been using MySQL - on and off - for the past four years or so, and have become fairly comfortable with its normal usage scenarios (i.e. usage that does not involve clustering, replication, etc). Hence, i was quite embarrassed to be stumped by a problem which, on hindsight, should be common knowledge to someone familiar with the database.

i had set up a MySQL instance, added a new schema, and created a few tables using the standard DML CREATE statement:

CREATE TABLE TBL_MASTER
(
    MASTER_ID    VARCHAR(15),
    FIELD        VARCHAR(255),
    PRIMARY KEY (MASTER_ID)
);

CREATE TABLE TBL_CHILD1
(
    CHILD1_ID    VARCHAR(15),
    MASTER_ID    VARCHAR(15),
    FIELD        VARCHAR(255),
    PRIMARY KEY (CHILD1_ID),
    FOREIGN KEY (MASTER_ID) REFERENCES TBL_MASTER (MASTER_ID)
);

CREATE TABLE TBL_CHILD2
(
    CHILD2_ID    VARCHAR(15),
    MASTER_ID    VARCHAR(15),
    FIELD        VARCHAR(255),
    PRIMARY KEY (CHILD1_ID),
    FOREIGN KEY (MASTER_ID) REFERENCES TBL_MASTER (MASTER_ID)
);

Then, i wired up the MySQL instance to an Apache Tomcat servlet container by using the MySQL JDBC connector driver and configuring the Tomcat datasource to point to the database schema that was created. Most importantly, i had configured the datasource to set the database connections to auto-commit = false, as i would need a group of separate database update statements to be invoked as a single transaction.

A basic connectivity test (SELECT 1 FROM DUAL) showed that it was working perfectly at this point.

Next, it was time for the web application deployed in Tomcat to actually do something, so i had the application run the following (simplified) Java code fragment. (Note: datasource is the JDBC datasource object obtained from the Tomcat container via JNDI.)

Connection connection = null;
Statement stmtMaster = null;
Statement stmtChild1 = null;
Statement stmtChild2 = null;

try
{
    connection = datasource.getConnection();
   
    System.out.println(connection.getAutoCommit()); // For debugging purposes
   
    stmtMaster = connection.createStatement();
    stmtMaster.executeUpdate("INSERT INTO TBL_MASTER (MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");
   
    stmtChild1 = connection.createStatement();
    stmtChild1.executeUpdate("INSERT INTO TBL_CHILD1 (CHILD1_ID, MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");
   
    stmtChild2 = connection.createStatement();
    stmtChild2.executeUpdate("INSERT INTO TBL_CHILD2 (CHILD2_ID, MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");
   
    connection.commit();
}
catch (final SQLException ex)
{
    if (connection != null)
    {
        try
        {
            connection.rollback();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    ex.printStackTrace();
}
finally
{
    if (stmtMaster != null)
    {
        try
        {
            stmtMaster.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    if (stmtChild1 != null)
    {
        try
        {
            stmtChild1.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    if (stmtChild2 != null)
    {
        try
        {
            stmtChild2.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
   
    if (connection != null)
    {
        try
        {
            connection.close();
        }
        catch (final SQLException ignore)
        {
            //
        }
    }
}

In the code fragment above, the line

stmtChild1.executeUpdate("INSERT INTO TBL_CHILD1 (CHILD1_ID, MASTER_ID, FIELD) VALUES ('000000000000001', NULL)");

was obviously wrong. The number of column values that i was trying to insert was less than the number of columns declared; i had carelessly missed out one column value. So when the application was run, an exception occurred, and the stack trace correctly pointed out the errant line above as the culprit. And there should be no record added into any of those three tables, since all changes were rolled back as part of the exception handling. Or so i thought.

Looking into the contents of the TBL_MASTER table using the MySQL Query Browser, it appeared that the record ('000000000000001', NULL) had in fact been inserted and committed. But yet, the line

System.out.println(connection.getAutoCommit()); // For debugging purposes

had printed out the value false, indicating that auto-commit has been correctly turned off. So what's going on here?

After some research, i found out that:

1. When i set up the MySQL instance, the default database engine was initialised to MyISAM, and this setting was not changed.

2. When creating the tables, since the storage engine was not explicitly specified, the default engine - in this case MyISAM - was used.

3. MyISAM is a non-transaction-safe storage engine, meaning, all statements are immediately committed, regardless of the auto-commit mode. Hence, rollback would not work here. (See more information on the MyISAM storage engine here, and also, Comparing Transaction and Non-Transaction Engines.) As a comparison, the InnoDB storage engine - another popular storage engine in MySQL - is a transaction-safe storage engine.

Hence, in order to achieve what i want with the code fragment above, i would need those three tables to use InnoDB (or another transaction-safe storage engine). And in order to create a table that uses the InnoDB storage engine (instead of MyISAM), i could do one of three things:

1. Change the default storage engine to InnoDB. This can be done via the MySQL Administrator. Under Health > System Variables > Table Types, change the value of the variable table_type to InnoDB. This will take effect on ALL tables created from this point on, whenever the storage engine is not explicitly declared during table creation.

2. Before executing the DML statement to create the table, set the storage engine by executing

SET storage_engine=InnoDB;

This will take effect on all tables created in the current session (as long as not overridden by (3) below).

3. Declare the storage engine explicitly when creating the table itself, e.g.

CREATE TABLE TBL_MASTER
(
    MASTER_ID VARCHAR(15),
    FIELD VARCHAR(255),
    PRIMARY KEY (MASTER_ID)
) ENGINE = InnoDB;

HTH