Skip to content

Latest commit

 

History

History
142 lines (108 loc) · 4.56 KB

mysql-rocksdb.md

File metadata and controls

142 lines (108 loc) · 4.56 KB
Raw

Testing MySQL (MyRocks) transaction isolation levels

These tests were run with MySQL 5.6.27.

Setup (before every test case):

create table test (id int primary key, value int) engine=rocksdb;
insert into test (id, value) values (1, 10), (2, 20);

To see the current isolation level:

select @@tx_isolation;

Predicate-Many-Preceders (PMP)

MyRocks "repeatable read" prevents Predicate-Many-Preceders (PMP) for read predicates:

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where value = 30; -- T1. Returns nothing
insert into test (id, value) values(3, 30); -- T2
commit; -- T2
select * from test where value % 3 = 0; -- T1. Still returns nothing
commit; -- T1

MyRocks "repeatable read" does prevent Predicate-Many-Preceders (PMP) for write predicates -- example from Postgres documentation:

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
update test set value = value + 10; -- T1
select * from test where value = 20; -- T2. Returns 2 => 20
delete from test where value = 20;  -- T2, Fails ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction: Timeout on index: t1.test.PRIMARY

Lost Update (P4)

MyRocks "repeatable read" does prevent (???) Lost Update (P4):

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where id = 1; -- T1
select * from test where id = 1; -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 11 where id = 1; -- T2, fails almost immediately with ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction: Timeout on index: t1.test.PRIMARY
commit; -- T1
commit; -- T2

Read Skew (G-single)

MyRocks "repeatable read" prevents Read Skew (G-single) on a read-only transaction:

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where id = 1; -- T1. Shows 1 => 10
select * from test where id = 1; -- T2
select * from test where id = 2; -- T2
update test set value = 12 where id = 1; -- T2
update test set value = 18 where id = 2; -- T2
commit; -- T2
select * from test where id = 2; -- T1. Shows 2 => 20
commit; -- T1

MyRocks "repeatable read" prevents Read Skew (G-single) -- test using predicate dependencies:

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where value % 5 = 0; -- T1
update test set value = 12 where value = 10; -- T2
commit; -- T2
select * from test where value % 3 = 0; -- T1. Returns nothing
commit; -- T1

MyRocks "repeatable read" does prevent (???) Read Skew (G-single) on a write predicate:

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where id = 1; -- T1. Shows 1 => 10
select * from test; -- T2
update test set value = 12 where id = 1; -- T2
update test set value = 18 where id = 2; -- T2
commit; -- T2
delete from test where value = 20; -- T1. Fails ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Write Skew (G2-item)

MyRocks "repeatable read" does not prevent Write Skew (G2-item):

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where id in (1,2); -- T1
select * from test where id in (1,2); -- T2
update test set value = 11 where id = 1; -- T1
update test set value = 21 where id = 2; -- T2
commit; -- T1
commit; -- T2

Anti-Dependency Cycles (G2)

MyRocks "repeatable read" does not prevent Anti-Dependency Cycles (G2):

set session transaction isolation level repeatable read; begin; -- T1
set session transaction isolation level repeatable read; begin; -- T2
select * from test where value % 3 = 0; -- T1
select * from test where value % 3 = 0; -- T2
insert into test (id, value) values(3, 30); -- T1
insert into test (id, value) values(4, 42); -- T2
commit; -- T1
commit; -- T2
select * from test where value % 3 = 0; -- Either. Returns 3 => 30, 4 => 42