How to set InnoDB in MySQL to isolation level of snapshots

Question

I am currently working on a school project that should characterize MySQL performance in relation to different levels of isolation. I tested things on READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE. Now, I would like to test things using snapshot isolation.

I understand that when using the default REPEATABLE READ in InnoDB, snapshot isolation is shared, but I wonder if it is possible to set the isolation level only for highlighting snapshots? How can I do it?

Answer 1

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

You can also start a transaction as follows:

 START TRANSACTION WITH CONSISTENT SNAPSHOT; 

The WITH CONSISTENT SNAPSHOT parameter starts sequential reads for storage engines that can use it. This applies only to InnoDB. The effect is the same as for START TRANSACTION , followed by SELECT from any InnoDB table. See Section 13.6.8.2, “Consistent Non-Blocking Reads” . The WITH CONSISTENT SNAPSHOT parameter does not change the current isolation level of a transaction , so it provides a consistent snapshot only if the current isolation level is one that allows consistent reading ( REPEATABLE READ or SERIALIZABLE ).

So, you need to set the isolation level to REPEATABLE READ or SERIALIZABLE and start the transaction using the syntax above.

Answer 2

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

You can set it in a session as follows

 SET tx_isolation = 'READ-COMMITTED'; 

You can also install it globally.

 SET GLOBAL tx_isolation = 'READ-COMMITTED'; 

but this affects new database connections. You still need to set it in the current session

Answer 3

There is no snapshot isolation level in MySQL. It uses the snapshot for “Consistent non-blocking reads,” but that does not mean that it supports snapshot isolation.

According to the Wikipedia page , only the 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 isolation of snapshots

the transaction itself will be successfully committed only if no updates are in conflict with any simultaneous updates made since the moment the snapshot was taken

But the REPEATABLE READ level does not do this at all, although it uses a snapshot.