MyIsam engine transaction support

mysql myisam
Headshota picture Headshota · Nov 7, 2011 · Viewed 29.7k times · Source

I was testing transaction support in innoDB tables, and just for the curriosity I tried to run the same transaction on MyIsam table, and surprisingly it worked. I am assuming that on myIsam table queries are executed one after another not in one atomic operation and I don't get any errors from START TRANSACTION and COMMIT and ROLLBACK operations. I am interested, is MyIsam engine just ignoring this operations or does it perform some actions?

Answer

Maxim Krizhanovsky picture Maxim Krizhanovsky · Nov 7, 2011

MyISAM effectively works in auto-commit mode (as it's not a transactional engine), and it just ignores the commit/rollback.

Actually storage engine is a different layer in the MySQL architecture, separated from the SQL parser, the SQL layer communicates to the storage engine with lower-level API, and that's the reason there is a common SQL and engines, supporting different subset of featured. You can see very high-level overview of the architecture here

Related questions

How can I check MySQL engine type for a specific table?

My MySQL database contains several tables using different storage engines (specifically myisam and innodb). How can I find out which tables are using which engine?

Read More
How to properly create composite primary keys - MYSQL

Here is a gross oversimplification of an intense setup I am working with. table_1 and table_2 both have auto-increment surrogate primary keys as the ID. info is a table that contains information about both table_1 and table_2. table_1 (id, field) …

Read More
MyISAM versus InnoDB

I'm working on a projects which involves a lot of database writes, I'd say (70% inserts and 30% reads). This ratio would also include updates which I consider to be one read and one write. The reads can be dirty (e.g. …

Read More