Transaction Isolation

Transaction Isolation in MyRocks

Overview

MyRocks supports two of the four transaction isolation levels: Read committed and Repeatable reads (see https://en.wikipedia.org/wiki/Isolation_(database_systems)#Isolation_levels).

These transaction isolation modes use snapshots. A snapshot is a RocksDB object that provides the ability to see a consistent view of the database from a specific point in time. For SELECT queries, the data that will be visible is always the data that existed at the time the snapshot was created. For INSERT and UPDATES an error will be generated if an attempt is made to change a row that has been changed by another connection since the snapshot was created.

In Repeatable read mode, a snapshot is held for the duration of the transaction, so all statements in the transaction see the same view of the database. In Read committed mode, a snapshot is held for the duration of a statement, so each statement can see any changes that occurred before the statement started and none of the changes since then.

Differences between MyRocks and other database systems

Due to the way MyRocks implements these transaction isolation levels, there may be various differences between MyRocks and other database systems.

MyRocks attempts to delay the point at which it takes a snapshot as long as possible. The affects of this will show up in Repeatable read mode. In some database systems the transaction can only see changes that occurred before the transaction started. In MyRocks we delay the start of the snapshot until the first data is read, which occurs sometime after the transaction starts. That means that the following scenario may get different results.

<client 1>                              | <client 2>
CREATE TABLE t1(pk INT PRIMARY KEY);    |
INSERT INTO t1 VALUES(1);               |
SET SESSION TRANSACTION ISOLATION LEVEL |
    REPEATABLE READ;                    |
                                        | SET SESSION TRANSACTION ISOLATION LEVEL
                                        |     REPEATABLE READ;
BEGIN                                   |
                                        | INSERT INTO t1 VALUES(2);
SELECT COUNT(*) FROM t1                 |

On MyRocks the result will be 2 while on other systems it may be 1.