This is Shacho writing.
Well, I’ve promoted our product for a while, so let’s talk about something else now, a funny thing I’ve noticed while developing DB Tracklayer.

Mixed MyIsam/InnoDB Transactions on MySQL

This also is information for someone from Oracle.
In Oracle(or maybe I should say normally), we consider about transaction like this. When you want to or need to handle a series of manipulation with one decision, you handle the process in a transaction and then Commit or Rollback.
I guess transaction is usually like this.
Today, we won’t consider about transaction levels and locks.

Transactions need to start and end explicitly, and what you have to care is whether there are anythings to end the transaction implicitly. (We are not considering levels and locks now)
You’ll see a bitter outcome if you write something like Truncate that ends transactions implicitly, without understanding the Statements that end transactions implicitly, and the process up to there would be all Committed.
If you understand this point, then it’s not something difficult to use. (Remember? We are not considering levels and locks now)

We can handle as above as Oracle has one engine for Schemas.
I guess usually you don’t think about DB engines or storage engines when you manipulate on Oracle(or I just don’t know it?).
On the other hand, in MySQL, you can set storage engine on a Table basis and this means that there are possibility that a Schema(database) may have mixed engine in itself.
I believe that Schema should be separated on engine basis in physics design in such case, but it’s not always the case that such physics design exists.

Well, then what will happen if you execute a process with mixed MyIsam/InnoDB tables in a transaction?
Hmm… cannot give an answer immediately…

Ah, frustrating! So let’s experiment that actually.

It’s also browsed from the other session when step executing the SQL sentences below.

① create table test1 ( col1 int ) engine=InnoDB;
② create table test2 ( col1 int ) engine=MyISAM;
③ start transaction;
④ insert into test1 values (11);
⑤ insert into test2 values (21);
⑥ commit;

① mysql> create table test1 ( col1 int ) engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table test1;
+——-+——————————————————————————————-+
| Table | Create Table |
+——-+——————————————————————————————-+
| test1 | CREATE TABLE `test1` (
`col1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+——-+——————————————————————————————-+
1 row in set (0.00 sec)

② mysql> create table test2 ( col1 int ) engine=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table test2;
+——-+——————————————————————————————-+
| Table | Create Table |
+——-+——————————————————————————————-+
| test2 | CREATE TABLE `test2` (
`col1` int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+——-+——————————————————————————————-+
1 row in set (0.00 sec)

③ mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

④ mysql> insert into test1 values (11);
Query OK, 1 row affected (0.01 sec)

・Check from the same session after the execution
mysql> select * from test1;
+——+
| col1 |
+——+
| 11 |
+——+
1 row in set (0.00 sec)

・Check from the other session
mysql> select * from test1;
Empty set (0.01 sec)the

⑤ mysql> insert into test2 values (21);
Query OK, 1 row affected (0.00 sec)

・Check from the same session after the execution
mysql> select * from test1;
+——+
| col1 |
+——+
| 11 |
+——+
1 row in set (0.00 sec)

mysql> select * from test2;
+——+
| col1 |
+——+
| 21 |
+——+
1 row in set (0.00 sec)

・Check from the other session
mysql> select * from test1;
Empty set (0.00 sec)

mysql> select * from test2;
+——+
| col1 |
+——+
| 21 |
+——+
1 row in set (0.00 sec)

・From above, we can see that the InnoDB table is still in transaction
and the MyISAM table, no matter if it is still in the transaction, would be committed automatically (it doesn’t have the idea itself)
Even if MyISAM table data was set permanent, the transaction would not be committed or interrupted.

⑥ mysql> commit;
Query OK, 0 rows affected (0.04 sec)

・Check from the same session
mysql> select * from test1;
+——+
| col1 |
+——+
| 11 |
+——+
1 row in set (0.00 sec)

mysql> select * from test2;
+——+
| col1 |
+——+
| 21 |
+——+
1 row in set (0.00 sec)

・Check from the other session
mysql> select * from test1;
+——+
| col1 |
+——+
| 11 |
+——+
1 row in set (0.00 sec)

mysql> select * from test2;
+——+
| col1 |
+——+
| 21 |
+——+
1 row in set (0.00 sec)

・From the result above, we understand that transaction was not interrupted at ⑤.

・Therefore, my conclusion is;
・You don’t have to mind about mixed engine.
・Publishing the transaction toward the engines which don’t support transaction is meaningless, but also harmless, so you don’t have to mind(you are free to do so expressly)

Well, it wasn’t much different from what I’ve expected, but I didn’t have confidence so it was good to try this actually.
Let’s step in to the substantial part some other day.

Well, I think that’s it for now.

Then, see you.