Atomicity: MyISAM vs InnoDB

Background

A series of database operations is called a transaction. An atomic transaction is a series of database operations which either all occur, or all do not occur. If it succeeds, all associated changes on all rows it touches are made permanent. If it fails, none of the changes are made.

Objectives

• Create a table with the InnoDB storage engine;
• Investigate atomicity with the InnoDB storage engine;
• Create an identical table with the MyISAM storage engine;
• Investigate atomicity with the MyISAM storage engine;
• Observe the different results.

Steps

1. Create a Database
• Grant privileges to a database called 'atom'.

mysql>GRANT ALL ON atom.* TO 'mysql_name'@'client_host';

• Create the database.

mysql>CREATE DATABASE atom;

• Select the database.

mysql>USE atom;

2. Create an InnoDB Table
• Create a table.

mysql>CREATE TABLE t_innodb(i int primary key)
-> ENGINE = InnoDB;

3. Test Atomicity
• Insert data into the table.

mysql>INSERT INTO t_innodb VALUES (1),(2);

• Attempt a rollback.

mysql>ROLLBACK;

• Verify the data is there.

mysql>SELECT * FROM t_innodb;

Why is the data still there?

• By default, autocommit is enabled in MySQL. Find its current value.

  mysql>SELECT @@autocommit;

4. Another Atomicity Test
• Insert more data into the table.

mysql>INSERT INTO t_innodb VALUES (3),(4),(1),(5);

Was there an error caused by a PRIMARY KEY violation?

• Examine the table.

mysql>SELECT * FROM t_innodb;

• The table should look like this.

+---+
| i |
+---+
| 1 |
| 2 |
+---+

Was the INSERT statement an atomic tranaction?

5. Create an MyISAM Table
• Create a table.

mysql>CREATE TABLE t_myisam(i int primary key)
-> ENGINE = MyISAM;

6. Test with MyISAM
• Insert data into the table.

mysql>INSERT INTO t_myisam VALUES (1),(2);

• Insert more data into the table.

mysql>INSERT INTO t_myisam VALUES (3),(4),(1),(5);

Was there an error caused by a PRIMARY KEY violation?

• Examine the table.

mysql>SELECT * FROM t_myisam;

Why were only two rows inserted instead of four?

Why are the contents of this table different?

Does the MyISAM engine support atomic execution of statements?

7. Exit
• Use QUIT to exit out of the server.

  mysql>QUIT

Congratulations! You have successfully tested and compared atomicity with the MyISAM and InnoDB storage engines.