[Commits] c58f7147cd7: Fix rocksdb.hermitage test to work with Range Locking.

Sergei Petrunia psergey at askmonty.org
Mon Jan 28 22:50:40 EET 2019


revision-id: c58f7147cd79ddcc0ddeb8acdd505916b12291b8 (fb-prod201801-202-gc58f7147cd7)
parent(s): da32581b18cf52c578d8529e8a0df38dda6ebe4f
author: Sergei Petrunia
committer: Sergei Petrunia
timestamp: 2019-01-28 23:50:40 +0300
message:

Fix rocksdb.hermitage test to work with Range Locking.

Transactional Isolation with Range Locking is similar to InnoDB, so
REPEATABLE-READ is closer to (but not the same as) READ-COMMITTED.

(The result differences in hermitage-range_locking.result were
checked against InnoDB).

---
 .../suite/rocksdb/r/hermitage-range_locking.result | 652 +++++++++++++++++++++
 .../suite/rocksdb/t/hermitage-range_locking.test   |  15 +
 mysql-test/suite/rocksdb/t/hermitage.inc           |  14 +-
 mysql-test/suite/rocksdb/t/hermitage.test          |   3 +
 4 files changed, 678 insertions(+), 6 deletions(-)

diff --git a/mysql-test/suite/rocksdb/r/hermitage-range_locking.result b/mysql-test/suite/rocksdb/r/hermitage-range_locking.result
new file mode 100644
index 00000000000..af6a0028629
--- /dev/null
+++ b/mysql-test/suite/rocksdb/r/hermitage-range_locking.result
@@ -0,0 +1,652 @@
+DROP TABLE IF EXISTS test;
+connect  con1,localhost,root,,;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+connect  con2,localhost,root,,;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+connect  con3,localhost,root,,;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+connection con1;
+create table test (id int primary key, value int) engine=rocksdb;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test;
+id	value
+1	10
+2	20
+update test set value = 101 where id = 1;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+connection con1;
+rollback;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = 101 where id = 1;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+connection con1;
+update test set value = 11 where id = 1;
+commit;
+connection con2;
+select * from test;
+id	value
+1	11
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = 11 where id = 1;
+connection con2;
+update test set value = 22 where id = 2;
+connection con1;
+select * from test where id = 2;
+id	value
+2	20
+connection con2;
+select * from test where id = 1;
+id	value
+1	10
+connection con1;
+commit;
+connection con2;
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = 11 where id = 1;
+update test set value = 19 where id = 2;
+connection con2;
+update test set value = 12 where id = 1;
+connection con1;
+commit;
+connection con2;
+connection con3;
+select * from test;
+id	value
+1	11
+2	19
+connection con2;
+update test set value = 18 where id = 2;
+connection con3;
+select * from test;
+id	value
+1	11
+2	19
+connection con2;
+commit;
+connection con3;
+select * from test;
+id	value
+1	12
+2	18
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where value = 30;
+id	value
+connection con2;
+insert into test (id, value) values(3, 30);
+commit;
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+3	30
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = value + 10;
+connection con2;
+select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors';
+select * from test;
+id	value
+1	10
+2	20
+delete from test where value = 20;
+connection con1;
+commit;
+connection con2;
+select * from test;
+id	value
+2	30
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id = 1;
+id	value
+1	10
+connection con2;
+select * from test where id = 1;
+id	value
+1	10
+connection con1;
+update test set value = 11 where id = 1;
+connection con2;
+update test set value = 12 where id = 1;
+connection con1;
+commit;
+connection con2;
+select * from test;
+id	value
+1	12
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id = 1;
+id	value
+1	10
+connection con2;
+select * from test where id = 1;
+id	value
+1	10
+select * from test where id = 2;
+id	value
+2	20
+update test set value = 12 where id = 1;
+update test set value = 18 where id = 2;
+commit;
+connection con1;
+select * from test where id = 2;
+id	value
+2	18
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where value % 5 = 0;
+id	value
+1	10
+2	20
+connection con2;
+update test set value = 12 where value = 10;
+commit;
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+1	12
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id = 1;
+id	value
+1	10
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+update test set value = 12 where id = 1;
+update test set value = 18 where id = 2;
+commit;
+connection con1;
+delete from test where value = 20;
+select * from test where id = 2;
+id	value
+2	18
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id in (1,2);
+id	value
+1	10
+2	20
+connection con2;
+select * from test where id in (1,2);
+id	value
+1	10
+2	20
+connection con1;
+update test set value = 11 where id = 1;
+connection con2;
+update test set value = 21 where id = 2;
+connection con1;
+commit;
+connection con2;
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+connection con2;
+select * from test where value % 3 = 0;
+id	value
+connection con1;
+insert into test (id, value) values(3, 30);
+connection con2;
+insert into test (id, value) values(4, 42);
+connection con1;
+commit;
+connection con2;
+commit;
+select * from test where value % 3 = 0;
+id	value
+3	30
+4	42
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+3	30
+4	42
+connection default;
+drop table test;
+disconnect con1;
+disconnect con2;
+disconnect con3;
+DROP TABLE IF EXISTS test;
+connect  con1,localhost,root,,;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+connect  con2,localhost,root,,;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+connect  con3,localhost,root,,;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+connection con1;
+create table test (id int primary key, value int) engine=rocksdb;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test;
+id	value
+1	10
+2	20
+update test set value = 101 where id = 1;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+connection con1;
+rollback;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = 101 where id = 1;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+connection con1;
+update test set value = 11 where id = 1;
+commit;
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = 11 where id = 1;
+connection con2;
+update test set value = 22 where id = 2;
+connection con1;
+select * from test where id = 2;
+id	value
+2	20
+connection con2;
+select * from test where id = 1;
+id	value
+1	10
+connection con1;
+commit;
+connection con2;
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = 11 where id = 1;
+update test set value = 19 where id = 2;
+connection con2;
+update test set value = 12 where id = 1;
+connection con1;
+commit;
+connection con2;
+connection con3;
+select * from test;
+id	value
+1	11
+2	19
+connection con2;
+update test set value = 18 where id = 2;
+connection con3;
+select * from test;
+id	value
+1	11
+2	19
+connection con2;
+commit;
+connection con3;
+select * from test;
+id	value
+1	11
+2	19
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where value = 30;
+id	value
+connection con2;
+insert into test (id, value) values(3, 30);
+commit;
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+update test set value = value + 10;
+connection con2;
+select variable_value into @a from information_schema.global_status where variable_name='rocksdb_snapshot_conflict_errors';
+select * from test;
+id	value
+1	10
+2	20
+delete from test where value = 20;
+connection con1;
+commit;
+connection con2;
+select * from test;
+id	value
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id = 1;
+id	value
+1	10
+connection con2;
+select * from test where id = 1;
+id	value
+1	10
+connection con1;
+update test set value = 11 where id = 1;
+connection con2;
+update test set value = 12 where id = 1;
+connection con1;
+commit;
+connection con2;
+select * from test;
+id	value
+1	12
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id = 1;
+id	value
+1	10
+connection con2;
+select * from test where id = 1;
+id	value
+1	10
+select * from test where id = 2;
+id	value
+2	20
+update test set value = 12 where id = 1;
+update test set value = 18 where id = 2;
+commit;
+connection con1;
+select * from test where id = 2;
+id	value
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where value % 5 = 0;
+id	value
+1	10
+2	20
+connection con2;
+update test set value = 12 where value = 10;
+commit;
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id = 1;
+id	value
+1	10
+connection con2;
+select * from test;
+id	value
+1	10
+2	20
+update test set value = 12 where id = 1;
+update test set value = 18 where id = 2;
+commit;
+connection con1;
+delete from test where value = 20;
+select * from test where id = 2;
+id	value
+2	20
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where id in (1,2);
+id	value
+1	10
+2	20
+connection con2;
+select * from test where id in (1,2);
+id	value
+1	10
+2	20
+connection con1;
+update test set value = 11 where id = 1;
+connection con2;
+update test set value = 21 where id = 2;
+connection con1;
+commit;
+connection con2;
+commit;
+connection con1;
+truncate table test;
+insert into test (id, value) values (1, 10), (2, 20);
+begin;
+connection con2;
+begin;
+connection con3;
+begin;
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+connection con2;
+select * from test where value % 3 = 0;
+id	value
+connection con1;
+insert into test (id, value) values(3, 30);
+connection con2;
+insert into test (id, value) values(4, 42);
+connection con1;
+commit;
+connection con2;
+commit;
+select * from test where value % 3 = 0;
+id	value
+3	30
+4	42
+connection con1;
+select * from test where value % 3 = 0;
+id	value
+3	30
+4	42
+connection default;
+drop table test;
+disconnect con1;
+disconnect con2;
+disconnect con3;
diff --git a/mysql-test/suite/rocksdb/t/hermitage-range_locking.test b/mysql-test/suite/rocksdb/t/hermitage-range_locking.test
new file mode 100644
index 00000000000..55203af9cf8
--- /dev/null
+++ b/mysql-test/suite/rocksdb/t/hermitage-range_locking.test
@@ -0,0 +1,15 @@
+--source include/have_rocksdb.inc
+
+# Range locking uses InnoDB-like transaction isolation, which 
+# means the results differ from "true" Repeatable Read.
+--source suite/rocksdb/include/have_range_locking.inc
+
+
+# Hermitage is an attempt to test transaction isolation levels.
+# https://github.com/ept/hermitage
+
+let $trx_isolation = READ COMMITTED;
+--source hermitage.inc
+
+let $trx_isolation = REPEATABLE READ;
+--source hermitage.inc
diff --git a/mysql-test/suite/rocksdb/t/hermitage.inc b/mysql-test/suite/rocksdb/t/hermitage.inc
index 17baf5b6c57..ae26bb7a1f9 100644
--- a/mysql-test/suite/rocksdb/t/hermitage.inc
+++ b/mysql-test/suite/rocksdb/t/hermitage.inc
@@ -108,6 +108,8 @@ select * from test where value % 3 = 0;
 commit;
 
 --source hermitage_init.inc
+let $RC_OR_RANGE_LOCKING=`select @@tx_isolation='READ-COMMITTED' OR @@rocksdb_use_range_locking=1`;
+let $RR_AND_NOT_RANGE_LOCKING=`select @@tx_isolation='REPEATABLE-READ' AND @@rocksdb_use_range_locking=0`;
 connection con1;
 update test set value = value + 10;
 connection con2;
@@ -117,13 +119,13 @@ send delete from test where value = 20;
 connection con1;
 commit;
 connection con2;
-if ($trx_isolation == "READ COMMITTED")
+if ($RC_OR_RANGE_LOCKING)
 {
   reap;
   # RC: Returns 2 => 30
   select * from test;
 }
-if ($trx_isolation == "REPEATABLE READ")
+if ($RR_AND_NOT_RANGE_LOCKING)
 {
   --error ER_LOCK_DEADLOCK
   reap;
@@ -147,13 +149,13 @@ send update test set value = 12 where id = 1;
 connection con1;
 commit;
 connection con2;
-if ($trx_isolation == "READ COMMITTED")
+if ($RC_OR_RANGE_LOCKING)
 {
   reap;
   # RC: Returns 1 => 12
   select * from test;
 }
-if ($trx_isolation == "REPEATABLE READ")
+if ($RR_AND_NOT_RANGE_LOCKING)
 {
   --error ER_LOCK_DEADLOCK
   reap;
@@ -200,12 +202,12 @@ update test set value = 12 where id = 1;
 update test set value = 18 where id = 2;
 commit;
 connection con1;
-if ($trx_isolation == "READ COMMITTED")
+if ($RC_OR_RANGE_LOCKING)
 {
   delete from test where value = 20; # doesn't delete anything
   select * from test where id = 2; # shows 2 => 18
 }
-if ($trx_isolation == "REPEATABLE READ")
+if ($RR_AND_NOT_RANGE_LOCKING)
 {
   --error ER_LOCK_DEADLOCK
   delete from test where value = 20;
diff --git a/mysql-test/suite/rocksdb/t/hermitage.test b/mysql-test/suite/rocksdb/t/hermitage.test
index e4138e8d89f..51f3f286a0e 100644
--- a/mysql-test/suite/rocksdb/t/hermitage.test
+++ b/mysql-test/suite/rocksdb/t/hermitage.test
@@ -1,5 +1,8 @@
 --source include/have_rocksdb.inc
 
+# See hermitage-range_locking variant
+--source suite/rocksdb/include/not_range_locking.inc
+
 # Hermitage is an attempt to test transaction isolation levels.
 # https://github.com/ept/hermitage
 


More information about the commits mailing list