github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/isolation.sql (about)

     1  drop table if exists test_11;
     2  create table test_11 (c int primary key,d int);
     3  Insert into test_11 values(1,1);
     4  Insert into test_11 values(2,2);
     5  begin;
     6  Insert into test_11 values(3,1);
     7  Insert into test_11 values(4,2);
     8  select * from test_11;
     9  
    10  -- @session:id=1{
    11  use isolation;
    12  select * from test_11;
    13  -- @session}
    14  commit;
    15  
    16  select * from test_11;
    17  -- @session:id=1{
    18  select * from test_11;
    19  -- @session}
    20  
    21  -- -------------------------------------------------------
    22  drop table if exists test_11;
    23  create table test_11 (c int primary key,d int);
    24  Insert into test_11 values(1,1);
    25  Insert into test_11 values(2,2);
    26  begin;
    27  Insert into test_11 values(3,1);
    28  Insert into test_11 values(4,2);
    29  select * from test_11;
    30  
    31  -- @session:id=1{
    32  select * from test_11;
    33  -- @session}
    34  
    35  delete from test_11 where c =1;
    36  select * from test_11;
    37  -- @session:id=1{
    38  select * from test_11;
    39  -- @session}
    40  
    41  update test_11 set d = c +1 where c > 2;
    42  select * from test_11;
    43  -- @session:id=1{
    44  select * from test_11;
    45  -- @session}
    46  
    47  commit;
    48  select * from test_11;
    49  -- @session:id=1{
    50  select * from test_11;
    51  -- @session}
    52  
    53  -- -------------------------------------------------------
    54  drop table if exists test_11;
    55  begin;
    56  create table test_11 (c int primary key,d int);
    57  Insert into test_11 values(1,1);
    58  Insert into test_11 values(2,2);
    59  select * from test_11;
    60  -- @session:id=1{
    61  select * from test_11;
    62  -- @session}
    63  
    64  commit;
    65  select * from test_11;
    66  -- @session:id=1{
    67  select * from test_11;
    68  -- @session}
    69  
    70  -- -------------------------------------------------------
    71  -- @bvt:issue#6949
    72  drop table if exists test_11;
    73  begin;
    74  create table test_11 (c int primary key,d int);
    75  Insert into test_11 values(1,1);
    76  Insert into test_11 values(2,2);
    77  select * from test_11;
    78  -- @session:id=1{
    79  delete from test_11 where c = 1;
    80  select * from test_11;
    81  -- @session}
    82  Insert into test_11 values(1,1);
    83  select * from test_11;
    84  
    85  commit;
    86  select * from test_11;
    87  -- @session:id=1{
    88  select * from test_11;
    89  -- @session}
    90  -- @bvt:issue
    91  
    92  -- -------------------------------------------------------
    93  drop table if exists test_11;
    94  create table test_11 (c int primary key,d int);
    95  Insert into test_11 values(1,1);
    96  Insert into test_11 values(2,2);
    97  begin;
    98  Insert into test_11 values(3,1);
    99  Insert into test_11 values(4,2);
   100  select * from test_11;
   101  -- @session:id=1{
   102  select * from test_11;
   103  Insert into test_11 values(5,4);
   104  select * from test_11;
   105  -- @session}
   106  select * from test_11;
   107  Insert into test_11 values(50,50);
   108  -- @session:id=1{
   109  Insert into test_11 values(50,50);
   110  select * from test_11;
   111  -- @session}
   112  select * from test_11;
   113  commit;
   114  -- @session:id=1{
   115  select * from test_11;
   116  -- @session}
   117  select * from test_11;
   118  
   119  -- -------------------------------------------------------
   120  drop table if exists test_11;
   121  create table test_11 (c int primary key,d int);
   122  Insert into test_11 values(1,1);
   123  Insert into test_11 values(2,2);
   124  begin;
   125  select * from test_11;
   126  -- @session:id=1{
   127  select * from test_11;
   128  Insert into test_11 values(50,50);
   129  select * from test_11;
   130  -- @session}
   131  
   132  Insert into test_11 values(50,50);
   133  select * from test_11;
   134  commit;
   135  -- @session:id=1{
   136  select * from test_11;
   137  -- @session}
   138  select * from test_11;
   139  
   140  -- -------------------------------------------------------
   141  drop table if exists test_11;
   142  create table test_11 (c int primary key,d int);
   143  Insert into test_11 values(1,1);
   144  Insert into test_11 values(2,2);
   145  begin;
   146  select * from test_11;
   147  Insert into test_11 values(50,50);
   148  select * from test_11;
   149  -- @session:id=1{
   150  select * from test_11;
   151  Insert into test_11 values(50,50);
   152  select * from test_11;
   153  -- @session}
   154  select * from test_11;
   155  
   156  -- @session:id=1{
   157  select * from test_11;
   158  delete from test_11 where c = 50;
   159  select * from test_11;
   160  -- @session}
   161  select * from test_11;
   162  
   163  commit;
   164  -- @session:id=1{
   165  select * from test_11;
   166  -- @session}
   167  select * from test_11;
   168  
   169  -- -------------------------------------------------------
   170  drop table if exists test_11;
   171  create table test_11 (c int primary key,d int);
   172  Insert into test_11 values(1,1);
   173  Insert into test_11 values(2,2);
   174  begin;
   175  select * from test_11;
   176  Insert into test_11 values(50,50);
   177  select * from test_11;
   178  -- @session:id=1{
   179  select * from test_11;
   180  Insert into test_11 values(50,50);
   181  select * from test_11;
   182  -- @session}
   183  select * from test_11;
   184  
   185  -- @session:id=1{
   186  select * from test_11;
   187  update test_11 set c = 100 where d = 50;
   188  select * from test_11;
   189  -- @session}
   190  select * from test_11;
   191  Insert into test_11 values(100,50);
   192  
   193  commit;
   194  -- @session:id=1{
   195  select * from test_11;
   196  -- @session}
   197  select * from test_11;
   198  
   199  -- -------------------------------------------------------
   200  drop table if exists test_11;
   201  create table test_11 (c int primary key,d int);
   202  Insert into test_11 values(1,1);
   203  Insert into test_11 values(2,2);
   204  begin;
   205  select * from test_11;
   206  Insert into test_11 values(50,50);
   207  select * from test_11;
   208  -- @session:id=1{
   209  select * from test_11;
   210  Insert into test_11 values(50,50);
   211  select * from test_11;
   212  -- @session}
   213  select * from test_11;
   214  
   215  -- @session:id=1{
   216  select * from test_11;
   217  update test_11 set c = 100 where d = 50;
   218  select * from test_11;
   219  -- @session}
   220  select * from test_11;
   221  update test_11 set c = 101 where c = 50;
   222  
   223  commit;
   224  -- @session:id=1{
   225  select * from test_11;
   226  -- @session}
   227  select * from test_11;
   228  
   229  -- -------------------------------------------------------
   230  drop table if exists test_11;
   231  create table test_11 (c int primary key,d int);
   232  Insert into test_11 values(1,1);
   233  Insert into test_11 values(2,2);
   234  begin;
   235  select * from test_11;
   236  Insert into test_11 values(50,50);
   237  select * from test_11;
   238  -- @session:id=1{
   239  select * from test_11;
   240  Insert into test_11 values(50,50);
   241  select * from test_11;
   242  -- @session}
   243  select * from test_11;
   244  
   245  -- @session:id=1{
   246  select * from test_11;
   247  update test_11 set c = 100 where d = 50;
   248  select * from test_11;
   249  -- @session}
   250  select * from test_11;
   251  update test_11 set c = 100 where d = 50;
   252  
   253  commit;
   254  -- @session:id=1{
   255  select * from test_11;
   256  -- @session}
   257  select * from test_11;
   258  
   259  -- -------------------------------------------------------
   260  drop table if exists test_11;
   261  begin;
   262  create table test_11 (c int primary key,d int);
   263  Insert into test_11 values(1,1);
   264  Insert into test_11 values(2,2);
   265  select * from test_11;
   266  -- @session:id=1{
   267  select * from test_11;
   268  -- @session}
   269  
   270  commit;
   271  select * from test_11;
   272  -- @session:id=1{
   273  select * from test_11;
   274  -- @session}
   275  
   276  -- -------------------------------------------------------
   277  drop table if exists test_11;
   278  create table test_11 (c int primary key,d int);
   279  Insert into test_11 values(1,1);
   280  Insert into test_11 values(2,2);
   281  select * from test_11;
   282  -- @session:id=1{
   283  select * from test_11;
   284  -- @session}
   285  
   286  begin;
   287  drop table test_11;
   288  select * from test_11;
   289  -- @session:id=1{
   290  select * from test_11;
   291  -- @session}
   292  
   293  commit;
   294  select * from test_11;
   295  -- @session:id=1{
   296  select * from test_11;
   297  -- @session}
   298  
   299  -- -------------------------------------------------------
   300  drop table if exists test_11;
   301  begin;
   302  create table test_11 (c int primary key,d int);
   303  Insert into test_11 values(1,1);
   304  Insert into test_11 values(2,2);
   305  select * from test_11;
   306  -- @session:id=1{
   307  select * from test_11;
   308  -- @session}
   309  
   310  -- drop table test_11;
   311  select * from test_11;
   312  -- @session:id=1{
   313  select * from test_11;
   314  -- @session}
   315  commit;
   316  
   317  select * from test_11;
   318  -- @session:id=1{
   319  select * from test_11;
   320  -- @session}
   321  
   322  drop table if exists test_11;
   323  
   324  drop table if exists t1;
   325  create table t1 (a int not null, b int);
   326  insert into t1 values (1, 1);
   327  begin;
   328  select * from t1;
   329  update t1 set a=null where b=1;
   330  select * from t1;
   331  commit;
   332  drop table if exists t1;