MyISAM versus InnoDB Tables in MySQL

This is a worked example comparing 2 MySQL table types:

1. MyISAM – you cannot rollback DML on these tables.
2. InnoDB – you can (sometimes) rollback DML on these tables.

First, create a table of each type:

 

UNIX > mysql -u root -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 10370

Server version: 5.0.67 Source distribution

 

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> use test;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

 

Database changed

mysql> create table andrews_myisam

    -> (col1 varchar(10)) engine = myisam;

Query OK, 0 rows affected (0.00 sec)

 

mysql> create table andrews_innodb

    -> (col1 varchar(10)) engine = innodb;

Query OK, 0 rows affected (0.38 sec)

 

mysql>

 

Then insert a row in each table and rollback the insert. Note the warning that you cannot rollback non-transactional tables:

 

mysql> insert into andrews_myisam values ('MYISAM');

Query OK, 1 row affected (0.06 sec)

 

mysql> insert into andrews_innodb values ('INNODB');

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+---------------------------------------------------------------+

| Level   | Code | Message                                                       |

+---------+------+---------------------------------------------------------------+

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

+---------+------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql>

 

Query the tables and note that the rollback has failed in BOTH tables and the inserted rows are still there:

 

mysql> select * from andrews_myisam;

+--------+

| col1   |

+--------+

| MYISAM |

+--------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

This is because MySQL’s default behaviour is to commit DML immediately. To alter this, you need to set autocommit = 0. Do this then repeat the test. This time the DML deletes the rows inserted above:

 

mysql> set autocommit = 0;

Query OK, 0 rows affected (0.00 sec)

 

mysql> delete from andrews_myisam;

Query OK, 1 row affected (0.00 sec)

 

mysql> delete from andrews_innodb;

Query OK, 1 row affected (0.00 sec)

 

mysql> rollback;

Query OK, 0 rows affected, 1 warning (0.00 sec)

 

mysql> show warnings;

+---------+------+---------------------------------------------------------------+

| Level   | Code | Message                                                       |

+---------+------+---------------------------------------------------------------+

| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |

+---------+------+---------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_myisam;

Empty set (0.00 sec)

 

mysql> select * from andrews_innodb;

+--------+

| col1   |

+--------+

| INNODB |

+--------+

1 row in set (0.00 sec)

 

mysql>

 

The delete statement on the MyISAM table could not be rolled back, as you might expect. Setting autocommit to zero prevented the delete statement on the InnoDB being committed immediately. The rollback therefore worked and the inserted row reappeared in the table.