How to set InnoDB in MySQL to the snapshot isolation level

Question

I'm working on a school project now that needs to characterize the performance of MySQL with regards to different isolation levels. I've tested things on READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Now I'd like to test things using snapshot isolation.

I understand that when using the default for REPEATABLE READ in InnoDB, snapshot isolation is used in conjunction, but I'm wondering, is it's possible to set the isolation level to snapshot isolation only? How would I do that?

Answer 1

There is no global snapshot isolation level. From MySQL docs, START TRANSACTION syntax:

You can also begin a transaction like this:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by a SELECT from any InnoDB table. See Section 13.6.8.2, “Consistent Nonlocking Reads”. The WITH CONSISTENT SNAPSHOT option does not change the current transaction isolation level, so it provides a consistent snapshot only if the current isolation level is one that permits consistent read (REPEATABLE READ or SERIALIZABLE).

So, you'll have to set isolation level to REPEATABLE READ or SERIALIZABLE and start your transactions with the above syntax.