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's no snapshot isolation level in MySQL. It uses snapshot for Consistent Nonlocking Reads, but it doesn't mean it supports snapshot isolation.

According to the Wikipedia page, only databases below support snapshot isolation.

Snapshot isolation has been adopted by several major database management systems, such as SQL Anywhere, InterBase, Firebird, Oracle, PostgreSQL and Microsoft SQL Server (2005 and later)

In snapshot isolation,

the transaction itself will successfully commit only if no updates it has made conflict with any concurrent updates made since that snapshot

But the REPEATABLE READ level doesn't do this at all, though it uses snapshot.

Answer 2

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.

Answer 3

Using the variable tx_isolation, you can set the transaction isolation locally and globally.

You can set it in the session as follows

SET tx_isolation = 'READ-COMMITTED';

You can set it globally as well

SET GLOBAL tx_isolation = 'READ-COMMITTED';

but that affects new DB Connections going forward. You still have to set it in the current session