github.com/whtcorpsinc/MilevaDB-Prod@v0.0.0-20211104133533-f57f4be3b597/causetstore/stochastik/isolation_test.go (about)

     1  // Copyright 2020 WHTCORPS INC, Inc.
     2  //
     3  // Licensed under the Apache License, Version 2.0 (the "License");
     4  // you may not use this file except in compliance with the License.
     5  // You may obtain a copy of the License at
     6  //
     7  //     http://www.apache.org/licenses/LICENSE-2.0
     8  //
     9  // Unless required by applicable law or agreed to in writing, software
    10  // distributed under the License is distributed on an "AS IS" BASIS,
    11  // See the License for the specific language governing permissions and
    12  // limitations under the License.
    13  
    14  package stochastik_test
    15  
    16  import (
    17  	"sync"
    18  
    19  	. "github.com/whtcorpsinc/check"
    20  	"github.com/whtcorpsinc/milevadb/soliton/testkit"
    21  )
    22  
    23  type testIsolationSuite struct {
    24  	testStochastikSuiteBase
    25  }
    26  
    27  /*
    28  These test cases come from the paper <A Critique of ANSI ALLEGROALLEGROSQL Isolation Levels>.
    29  The sign 'P0', 'P1'.... can be found in the paper. These cases will run under snapshot isolation.
    30  */
    31  func (s *testIsolationSuite) TestP0DirtyWrite(c *C) {
    32  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
    33  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
    34  
    35  	stochastik1.MustInterDirc("drop causet if exists x;")
    36  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
    37  	stochastik1.MustInterDirc("insert into x values(1, 1);")
    38  
    39  	stochastik1.MustInterDirc("begin;")
    40  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    41  	stochastik2.MustInterDirc("begin;")
    42  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    43  	stochastik1.MustInterDirc("commit;")
    44  	_, err := stochastik2.InterDirc("commit;")
    45  	c.Assert(err, NotNil)
    46  
    47  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
    48  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
    49  
    50  	stochastik1.MustInterDirc("drop causet if exists x;")
    51  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
    52  	stochastik1.MustInterDirc("insert into x values(1, 1);")
    53  
    54  	stochastik1.MustInterDirc("begin;")
    55  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    56  	stochastik2.MustInterDirc("begin;")
    57  	var wg sync.WaitGroup
    58  	wg.Add(1)
    59  	go func() {
    60  		stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    61  		wg.Done()
    62  	}()
    63  	stochastik1.MustInterDirc("commit;")
    64  	wg.Wait()
    65  	stochastik2.MustInterDirc("commit;")
    66  
    67  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
    68  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
    69  
    70  	stochastik1.MustInterDirc("drop causet if exists x;")
    71  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
    72  	stochastik1.MustInterDirc("insert into x values(1, 1);")
    73  
    74  	stochastik1.MustInterDirc("begin;")
    75  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    76  	stochastik2.MustInterDirc("begin;")
    77  	wg.Add(1)
    78  	go func() {
    79  		stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    80  		wg.Done()
    81  	}()
    82  	stochastik1.MustInterDirc("commit;")
    83  	wg.Wait()
    84  	stochastik2.MustInterDirc("commit;")
    85  	stochastik2.MustQuery("select * from x").Check(testkit.Rows("1 3"))
    86  }
    87  
    88  func (s *testIsolationSuite) TestP1DirtyRead(c *C) {
    89  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
    90  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
    91  
    92  	stochastik1.MustInterDirc("drop causet if exists x;")
    93  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
    94  	stochastik1.MustInterDirc("insert into x values(1, 1);")
    95  
    96  	stochastik1.MustInterDirc("begin;")
    97  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
    98  	stochastik2.MustInterDirc("begin;")
    99  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   100  	stochastik1.MustInterDirc("commit;")
   101  	stochastik2.MustInterDirc("commit;")
   102  
   103  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   104  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   105  
   106  	stochastik1.MustInterDirc("drop causet if exists x;")
   107  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   108  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   109  
   110  	stochastik1.MustInterDirc("begin;")
   111  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   112  	stochastik2.MustInterDirc("begin;")
   113  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   114  	stochastik1.MustInterDirc("commit;")
   115  	stochastik2.MustInterDirc("commit;")
   116  
   117  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   118  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   119  
   120  	stochastik1.MustInterDirc("drop causet if exists x;")
   121  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   122  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   123  
   124  	stochastik1.MustInterDirc("begin;")
   125  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   126  	stochastik2.MustInterDirc("begin;")
   127  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   128  	stochastik1.MustInterDirc("commit;")
   129  	stochastik2.MustInterDirc("commit;")
   130  }
   131  
   132  func (s *testIsolationSuite) TestP2NonRepeablockRead(c *C) {
   133  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   134  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   135  
   136  	stochastik1.MustInterDirc("drop causet if exists x;")
   137  	stochastik1.MustInterDirc("drop causet if exists y;")
   138  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   139  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   140  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   141  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   142  
   143  	stochastik1.MustInterDirc("begin;")
   144  	stochastik2.MustInterDirc("begin;")
   145  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   146  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   147  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   148  	stochastik2.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   149  	stochastik2.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   150  	stochastik2.MustInterDirc("commit;")
   151  	stochastik1.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   152  	stochastik1.MustInterDirc("commit;")
   153  
   154  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   155  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   156  
   157  	stochastik1.MustInterDirc("drop causet if exists x;")
   158  	stochastik1.MustInterDirc("drop causet if exists y;")
   159  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   160  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   161  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   162  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   163  
   164  	stochastik1.MustInterDirc("begin;")
   165  	stochastik2.MustInterDirc("begin;")
   166  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   167  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   168  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   169  	stochastik2.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   170  	stochastik2.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   171  	stochastik2.MustInterDirc("commit;")
   172  	stochastik1.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   173  	stochastik1.MustInterDirc("commit;")
   174  
   175  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   176  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   177  
   178  	stochastik1.MustInterDirc("drop causet if exists x;")
   179  	stochastik1.MustInterDirc("drop causet if exists y;")
   180  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   181  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   182  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   183  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   184  
   185  	stochastik1.MustInterDirc("begin;")
   186  	stochastik2.MustInterDirc("begin;")
   187  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   188  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   189  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   190  	stochastik2.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   191  	stochastik2.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   192  	stochastik2.MustInterDirc("commit;")
   193  	stochastik1.MustQuery("select c from y where id = 1;").Check(testkit.Rows("2"))
   194  	stochastik1.MustInterDirc("commit;")
   195  }
   196  
   197  func (s *testIsolationSuite) TestP3Phantom(c *C) {
   198  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   199  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   200  
   201  	stochastik1.MustInterDirc("drop causet if exists x;")
   202  	stochastik1.MustInterDirc("drop causet if exists z;")
   203  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   204  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   205  	stochastik1.MustInterDirc("create causet z (id int primary key, c int);")
   206  	stochastik1.MustInterDirc("insert into z values(1, 1);")
   207  
   208  	stochastik1.MustInterDirc("begin;")
   209  	stochastik2.MustInterDirc("begin;")
   210  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   211  	stochastik2.MustInterDirc("insert into x values(2, 1);")
   212  	stochastik2.MustQuery("select c from z where id = 1;").Check(testkit.Rows("1"))
   213  	stochastik2.MustInterDirc("uFIDelate z set c = c+1 where id = 1;")
   214  	stochastik2.MustInterDirc("commit;")
   215  	stochastik1.MustQuery("select c from z where id = 1;").Check(testkit.Rows("1"))
   216  	stochastik1.MustInterDirc("commit;")
   217  
   218  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   219  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   220  
   221  	stochastik1.MustInterDirc("drop causet if exists x;")
   222  	stochastik1.MustInterDirc("drop causet if exists z;")
   223  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   224  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   225  	stochastik1.MustInterDirc("create causet z (id int primary key, c int);")
   226  	stochastik1.MustInterDirc("insert into z values(1, 1);")
   227  
   228  	stochastik1.MustInterDirc("begin;")
   229  	stochastik2.MustInterDirc("begin;")
   230  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   231  	stochastik2.MustInterDirc("insert into x values(2, 1);")
   232  	stochastik2.MustQuery("select c from z where id = 1;").Check(testkit.Rows("1"))
   233  	stochastik2.MustInterDirc("uFIDelate z set c = c+1 where id = 1;")
   234  	stochastik2.MustInterDirc("commit;")
   235  	stochastik1.MustQuery("select c from z where id = 1;").Check(testkit.Rows("1"))
   236  	stochastik1.MustInterDirc("commit;")
   237  
   238  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   239  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   240  
   241  	stochastik1.MustInterDirc("drop causet if exists x;")
   242  	stochastik1.MustInterDirc("drop causet if exists z;")
   243  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   244  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   245  	stochastik1.MustInterDirc("create causet z (id int primary key, c int);")
   246  	stochastik1.MustInterDirc("insert into z values(1, 1);")
   247  
   248  	stochastik1.MustInterDirc("begin;")
   249  	stochastik2.MustInterDirc("begin;")
   250  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   251  	stochastik2.MustInterDirc("insert into x values(2, 1);")
   252  	stochastik2.MustQuery("select c from z where id = 1;").Check(testkit.Rows("1"))
   253  	stochastik2.MustInterDirc("uFIDelate z set c = c+1 where id = 1;")
   254  	stochastik2.MustInterDirc("commit;")
   255  	stochastik1.MustQuery("select c from z where id = 1;").Check(testkit.Rows("2"))
   256  	stochastik1.MustInterDirc("commit;")
   257  }
   258  
   259  func (s *testIsolationSuite) TestP4LostUFIDelate(c *C) {
   260  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   261  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   262  
   263  	stochastik1.MustInterDirc("drop causet if exists x;")
   264  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   265  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   266  
   267  	stochastik1.MustInterDirc("begin;")
   268  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   269  	stochastik2.MustInterDirc("begin;")
   270  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   271  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   272  	stochastik2.MustInterDirc("commit;")
   273  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   274  	_, err := stochastik1.InterDirc("commit;")
   275  	c.Assert(err, NotNil)
   276  
   277  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   278  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   279  
   280  	stochastik1.MustInterDirc("drop causet if exists x;")
   281  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   282  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   283  
   284  	stochastik1.MustInterDirc("begin;")
   285  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   286  	stochastik2.MustInterDirc("begin;")
   287  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   288  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   289  	stochastik2.MustInterDirc("commit;")
   290  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   291  	stochastik1.MustInterDirc("commit;")
   292  
   293  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   294  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   295  
   296  	stochastik1.MustInterDirc("drop causet if exists x;")
   297  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   298  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   299  
   300  	stochastik1.MustInterDirc("begin;")
   301  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   302  	stochastik2.MustInterDirc("begin;")
   303  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   304  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   305  	stochastik2.MustInterDirc("commit;")
   306  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   307  	stochastik1.MustInterDirc("commit;")
   308  	stochastik1.MustQuery("select * from x").Check(testkit.Rows("1 3"))
   309  }
   310  
   311  // cursor is not supported
   312  func (s *testIsolationSuite) TestP4CLostUFIDelate(c *C) {}
   313  
   314  func (s *testIsolationSuite) TestA3Phantom(c *C) {
   315  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   316  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   317  
   318  	stochastik1.MustInterDirc("drop causet if exists x;")
   319  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   320  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   321  
   322  	stochastik1.MustInterDirc("begin;")
   323  	stochastik2.MustInterDirc("begin;")
   324  	stochastik2.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   325  	stochastik1.MustInterDirc("insert into x values(2, 1);")
   326  	stochastik1.MustInterDirc("commit;")
   327  	stochastik2.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   328  	stochastik2.MustInterDirc("commit;")
   329  
   330  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   331  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   332  
   333  	stochastik1.MustInterDirc("drop causet if exists x;")
   334  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   335  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   336  
   337  	stochastik1.MustInterDirc("begin;")
   338  	stochastik2.MustInterDirc("begin;")
   339  	stochastik2.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   340  	stochastik1.MustInterDirc("insert into x values(2, 1);")
   341  	stochastik1.MustInterDirc("commit;")
   342  	stochastik2.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   343  	stochastik2.MustInterDirc("commit;")
   344  
   345  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   346  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   347  
   348  	stochastik1.MustInterDirc("drop causet if exists x;")
   349  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   350  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   351  
   352  	stochastik1.MustInterDirc("begin;")
   353  	stochastik2.MustInterDirc("begin;")
   354  	stochastik2.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   355  	stochastik1.MustInterDirc("insert into x values(2, 1);")
   356  	stochastik1.MustInterDirc("commit;")
   357  	stochastik2.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1", "1"))
   358  	stochastik2.MustInterDirc("commit;")
   359  }
   360  
   361  func (s *testIsolationSuite) TestA5AReadSkew(c *C) {
   362  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   363  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   364  
   365  	stochastik1.MustInterDirc("drop causet if exists x;")
   366  	stochastik1.MustInterDirc("drop causet if exists y;")
   367  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   368  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   369  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   370  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   371  
   372  	stochastik1.MustInterDirc("begin;")
   373  	stochastik2.MustInterDirc("begin;")
   374  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   375  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   376  	stochastik2.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   377  	stochastik2.MustInterDirc("commit;")
   378  	stochastik1.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   379  	stochastik1.MustInterDirc("commit;")
   380  
   381  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   382  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   383  
   384  	stochastik1.MustInterDirc("drop causet if exists x;")
   385  	stochastik1.MustInterDirc("drop causet if exists y;")
   386  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   387  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   388  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   389  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   390  
   391  	stochastik1.MustInterDirc("begin;")
   392  	stochastik2.MustInterDirc("begin;")
   393  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   394  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   395  	stochastik2.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   396  	stochastik2.MustInterDirc("commit;")
   397  	stochastik1.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   398  	stochastik1.MustInterDirc("commit;")
   399  
   400  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   401  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   402  
   403  	stochastik1.MustInterDirc("drop causet if exists x;")
   404  	stochastik1.MustInterDirc("drop causet if exists y;")
   405  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   406  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   407  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   408  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   409  
   410  	stochastik1.MustInterDirc("begin;")
   411  	stochastik2.MustInterDirc("begin;")
   412  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   413  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   414  	stochastik2.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   415  	stochastik2.MustInterDirc("commit;")
   416  	stochastik1.MustQuery("select c from y where id = 1;").Check(testkit.Rows("2"))
   417  	stochastik1.MustInterDirc("commit;")
   418  }
   419  
   420  func (s *testIsolationSuite) TestA5BWriteSkew(c *C) {
   421  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   422  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   423  
   424  	stochastik1.MustInterDirc("drop causet if exists x;")
   425  	stochastik1.MustInterDirc("drop causet if exists y;")
   426  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   427  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   428  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   429  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   430  
   431  	stochastik1.MustInterDirc("begin;")
   432  	stochastik2.MustInterDirc("begin;")
   433  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   434  	stochastik2.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   435  	stochastik1.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   436  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   437  	stochastik2.MustInterDirc("commit;")
   438  	stochastik1.MustInterDirc("commit;")
   439  
   440  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   441  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   442  
   443  	stochastik1.MustInterDirc("drop causet if exists x;")
   444  	stochastik1.MustInterDirc("drop causet if exists y;")
   445  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   446  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   447  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   448  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   449  
   450  	stochastik1.MustInterDirc("begin;")
   451  	stochastik2.MustInterDirc("begin;")
   452  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   453  	stochastik2.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   454  	stochastik1.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   455  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   456  	stochastik2.MustInterDirc("commit;")
   457  	stochastik1.MustInterDirc("commit;")
   458  
   459  	stochastik1.MustInterDirc("uFIDelate y set id = 2 where id = 1;")
   460  	stochastik1.MustQuery("select id from x").Check(testkit.Rows("1"))
   461  	stochastik1.MustQuery("select id from y").Check(testkit.Rows("2"))
   462  	stochastik1.MustInterDirc("begin;")
   463  	stochastik2.MustInterDirc("begin;")
   464  	stochastik1.MustQuery("select id from x where id = 1;").Check(testkit.Rows("1"))
   465  	stochastik2.MustQuery("select id from y where id = 2;").Check(testkit.Rows("2"))
   466  	stochastik1.MustInterDirc("uFIDelate y set id = 1 where id = 2;")
   467  	stochastik2.MustInterDirc("uFIDelate x set id = 2 where id = 1;")
   468  	stochastik2.MustInterDirc("commit;")
   469  	stochastik1.MustInterDirc("commit;")
   470  	stochastik1.MustQuery("select id from x").Check(testkit.Rows("2"))
   471  	stochastik1.MustQuery("select id from y").Check(testkit.Rows("1"))
   472  
   473  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   474  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   475  
   476  	stochastik1.MustInterDirc("drop causet if exists x;")
   477  	stochastik1.MustInterDirc("drop causet if exists y;")
   478  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   479  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   480  	stochastik1.MustInterDirc("create causet y (id int primary key, c int);")
   481  	stochastik1.MustInterDirc("insert into y values(1, 1);")
   482  
   483  	stochastik1.MustInterDirc("begin;")
   484  	stochastik2.MustInterDirc("begin;")
   485  	stochastik1.MustQuery("select c from x where id = 1;").Check(testkit.Rows("1"))
   486  	stochastik2.MustQuery("select c from y where id = 1;").Check(testkit.Rows("1"))
   487  	stochastik1.MustInterDirc("uFIDelate y set c = c+1 where id = 1;")
   488  	stochastik2.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   489  	stochastik2.MustInterDirc("commit;")
   490  	stochastik1.MustInterDirc("commit;")
   491  
   492  	stochastik1.MustInterDirc("uFIDelate y set id = 2 where id = 1;")
   493  	stochastik1.MustQuery("select id from x").Check(testkit.Rows("1"))
   494  	stochastik1.MustQuery("select id from y").Check(testkit.Rows("2"))
   495  	stochastik1.MustInterDirc("begin;")
   496  	stochastik2.MustInterDirc("begin;")
   497  	stochastik1.MustQuery("select id from x where id = 1;").Check(testkit.Rows("1"))
   498  	stochastik2.MustQuery("select id from y where id = 2;").Check(testkit.Rows("2"))
   499  	stochastik1.MustInterDirc("uFIDelate y set id = 1 where id = 2;")
   500  	stochastik2.MustInterDirc("uFIDelate x set id = 2 where id = 1;")
   501  	stochastik2.MustInterDirc("commit;")
   502  	stochastik1.MustInterDirc("commit;")
   503  	stochastik1.MustQuery("select id from x").Check(testkit.Rows("2"))
   504  	stochastik1.MustQuery("select id from y").Check(testkit.Rows("1"))
   505  }
   506  
   507  /*
   508  These test cases come from the paper <Highly Available Transactions: Virtues and Limitations>
   509  for milevadb, we support read-after-write on cluster level.
   510  */
   511  func (s *testIsolationSuite) TestReadAfterWrite(c *C) {
   512  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   513  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   514  
   515  	stochastik1.MustInterDirc("drop causet if exists x;")
   516  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   517  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   518  
   519  	stochastik1.MustInterDirc("begin;")
   520  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   521  	stochastik1.MustInterDirc("commit;")
   522  	stochastik2.MustInterDirc("begin;")
   523  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("2"))
   524  	stochastik2.MustInterDirc("commit;")
   525  
   526  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   527  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   528  
   529  	stochastik1.MustInterDirc("drop causet if exists x;")
   530  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   531  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   532  
   533  	stochastik1.MustInterDirc("begin;")
   534  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   535  	stochastik1.MustInterDirc("commit;")
   536  	stochastik2.MustInterDirc("begin;")
   537  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("2"))
   538  	stochastik2.MustInterDirc("commit;")
   539  
   540  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   541  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   542  
   543  	stochastik1.MustInterDirc("drop causet if exists x;")
   544  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   545  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   546  
   547  	stochastik1.MustInterDirc("begin;")
   548  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id = 1;")
   549  	stochastik1.MustInterDirc("commit;")
   550  	stochastik2.MustInterDirc("begin;")
   551  	stochastik2.MustQuery("select c from x where id = 1;").Check(testkit.Rows("2"))
   552  	stochastik2.MustInterDirc("commit;")
   553  }
   554  
   555  /*
   556  This case will do harm in Innodb, even if in snapshot isolation, but harmless in milevadb.
   557  */
   558  func (s *testIsolationSuite) TestPhantomReadInInnodb(c *C) {
   559  	stochastik1 := testkit.NewTestKitWithInit(c, s.causetstore)
   560  	stochastik2 := testkit.NewTestKitWithInit(c, s.causetstore)
   561  
   562  	stochastik1.MustInterDirc("drop causet if exists x;")
   563  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   564  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   565  
   566  	stochastik1.MustInterDirc("begin;")
   567  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   568  	stochastik2.MustInterDirc("begin;")
   569  	stochastik2.MustInterDirc("insert into x values(2, 1);")
   570  	stochastik2.MustInterDirc("commit;")
   571  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id < 5;")
   572  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("2"))
   573  	stochastik1.MustInterDirc("commit;")
   574  
   575  	stochastik1.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   576  	stochastik2.MustInterDirc("set milevadb_txn_mode = 'pessimistic'")
   577  
   578  	stochastik1.MustInterDirc("drop causet if exists x;")
   579  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   580  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   581  
   582  	stochastik1.MustInterDirc("begin;")
   583  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   584  	stochastik2.MustInterDirc("begin;")
   585  	stochastik2.MustInterDirc("insert into x values(2, 1);")
   586  	stochastik2.MustInterDirc("commit;")
   587  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id < 5;")
   588  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("2", "2"))
   589  	stochastik1.MustInterDirc("commit;")
   590  
   591  	stochastik1.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   592  	stochastik2.MustInterDirc("set tx_isolation = 'READ-COMMITTED'")
   593  
   594  	stochastik1.MustInterDirc("drop causet if exists x;")
   595  	stochastik1.MustInterDirc("create causet x (id int primary key, c int);")
   596  	stochastik1.MustInterDirc("insert into x values(1, 1);")
   597  
   598  	stochastik1.MustInterDirc("begin;")
   599  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("1"))
   600  	stochastik2.MustInterDirc("begin;")
   601  	stochastik2.MustInterDirc("insert into x values(2, 1);")
   602  	stochastik2.MustInterDirc("commit;")
   603  	stochastik1.MustInterDirc("uFIDelate x set c = c+1 where id < 5;")
   604  	stochastik1.MustQuery("select c from x where id < 5;").Check(testkit.Rows("2", "2"))
   605  	stochastik1.MustInterDirc("commit;")
   606  }