github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/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  -- @wait:0:commit
   110  Insert into test_11 values(50,50);
   111  select * from test_11;
   112  -- @session}
   113  select * from test_11;
   114  commit;
   115  -- @session:id=1{
   116  select * from test_11;
   117  -- @session}
   118  select * from test_11;
   119  
   120  -- -------------------------------------------------------
   121  drop table if exists test_11;
   122  create table test_11 (c int primary key,d int);
   123  Insert into test_11 values(1,1);
   124  Insert into test_11 values(2,2);
   125  begin;
   126  select * from test_11;
   127  -- @session:id=1{
   128  select * from test_11;
   129  Insert into test_11 values(50,50);
   130  select * from test_11;
   131  -- @session}
   132  
   133  Insert into test_11 values(50,50);
   134  select * from test_11;
   135  commit;
   136  -- @session:id=1{
   137  select * from test_11;
   138  -- @session}
   139  select * from test_11;
   140  
   141  -- -------------------------------------------------------
   142  drop table if exists test_11;
   143  create table test_11 (c int primary key,d int);
   144  Insert into test_11 values(1,1);
   145  Insert into test_11 values(2,2);
   146  begin;
   147  select * from test_11;
   148  Insert into test_11 values(50,50);
   149  select * from test_11;
   150  -- @session:id=1{
   151  select * from test_11;
   152  -- @wait:0:commit
   153  Insert into test_11 values(50,50);
   154  select * from test_11;
   155  -- @session}
   156  select * from test_11;
   157  
   158  -- @session:id=1{
   159  select * from test_11;
   160  delete from test_11 where c = 50;
   161  select * from test_11;
   162  -- @session}
   163  select * from test_11;
   164  
   165  commit;
   166  -- @session:id=1{
   167  select * from test_11;
   168  -- @session}
   169  select * from test_11;
   170  
   171  -- -------------------------------------------------------
   172  drop table if exists test_11;
   173  create table test_11 (c int primary key,d int);
   174  Insert into test_11 values(1,1);
   175  Insert into test_11 values(2,2);
   176  begin;
   177  select * from test_11;
   178  Insert into test_11 values(50,50);
   179  select * from test_11;
   180  -- @session:id=1{
   181  select * from test_11;
   182  -- @wait:0:commit
   183  Insert into test_11 values(50,50);
   184  select * from test_11;
   185  -- @session}
   186  select * from test_11;
   187  
   188  -- @session:id=1{
   189  select * from test_11;
   190  update test_11 set c = 100 where d = 50;
   191  select * from test_11;
   192  -- @session}
   193  select * from test_11;
   194  Insert into test_11 values(100,50);
   195  
   196  commit;
   197  -- @session:id=1{
   198  select * from test_11;
   199  -- @session}
   200  select * from test_11;
   201  
   202  -- -------------------------------------------------------
   203  drop table if exists test_11;
   204  create table test_11 (c int primary key,d int);
   205  Insert into test_11 values(1,1);
   206  Insert into test_11 values(2,2);
   207  begin;
   208  select * from test_11;
   209  Insert into test_11 values(50,50);
   210  select * from test_11;
   211  -- @session:id=1{
   212  select * from test_11;
   213  -- @wait:0:commit
   214  Insert into test_11 values(50,50);
   215  select * from test_11;
   216  -- @session}
   217  select * from test_11;
   218  
   219  -- @session:id=1{
   220  select * from test_11;
   221  update test_11 set c = 100 where d = 50;
   222  select * from test_11;
   223  -- @session}
   224  select * from test_11;
   225  update test_11 set c = 101 where c = 50;
   226  
   227  commit;
   228  -- @session:id=1{
   229  select * from test_11;
   230  -- @session}
   231  select * from test_11;
   232  
   233  -- -------------------------------------------------------
   234  drop table if exists test_11;
   235  create table test_11 (c int primary key,d int);
   236  Insert into test_11 values(1,1);
   237  Insert into test_11 values(2,2);
   238  begin;
   239  select * from test_11;
   240  Insert into test_11 values(50,50);
   241  select * from test_11;
   242  -- @session:id=1{
   243  select * from test_11;
   244  -- @wait:0:commit
   245  Insert into test_11 values(50,50);
   246  select * from test_11;
   247  -- @session}
   248  select * from test_11;
   249  
   250  -- @session:id=1{
   251  select * from test_11;
   252  update test_11 set c = 100 where d = 50;
   253  select * from test_11;
   254  -- @session}
   255  select * from test_11;
   256  update test_11 set c = 100 where d = 50;
   257  
   258  commit;
   259  -- @session:id=1{
   260  select * from test_11;
   261  -- @session}
   262  select * from test_11;
   263  
   264  -- -------------------------------------------------------
   265  drop table if exists test_11;
   266  begin;
   267  create table test_11 (c int primary key,d int);
   268  Insert into test_11 values(1,1);
   269  Insert into test_11 values(2,2);
   270  select * from test_11;
   271  -- @session:id=1{
   272  select * from test_11;
   273  -- @session}
   274  
   275  commit;
   276  select * from test_11;
   277  -- @session:id=1{
   278  select * from test_11;
   279  -- @session}
   280  
   281  -- -------------------------------------------------------
   282  drop table if exists test_11;
   283  create table test_11 (c int primary key,d int);
   284  Insert into test_11 values(1,1);
   285  Insert into test_11 values(2,2);
   286  select * from test_11;
   287  -- @session:id=1{
   288  select * from test_11;
   289  -- @session}
   290  
   291  begin;
   292  drop table test_11;
   293  select * from test_11;
   294  -- @session:id=1{
   295  select * from test_11;
   296  -- @session}
   297  
   298  commit;
   299  select * from test_11;
   300  -- @session:id=1{
   301  select * from test_11;
   302  -- @session}
   303  
   304  -- -------------------------------------------------------
   305  drop table if exists test_11;
   306  begin;
   307  create table test_11 (c int primary key,d int);
   308  Insert into test_11 values(1,1);
   309  Insert into test_11 values(2,2);
   310  select * from test_11;
   311  -- @session:id=1{
   312  select * from test_11;
   313  -- @session}
   314  
   315  -- drop table test_11;
   316  select * from test_11;
   317  -- @session:id=1{
   318  select * from test_11;
   319  -- @session}
   320  commit;
   321  
   322  select * from test_11;
   323  -- @session:id=1{
   324  select * from test_11;
   325  -- @session}
   326  
   327  drop table if exists test_11;
   328  
   329  drop table if exists t1;
   330  create table t1 (a int not null, b int);
   331  insert into t1 values (1, 1);
   332  begin;
   333  select * from t1;
   334  update t1 set a=null where b=1;
   335  select * from t1;
   336  commit;
   337  drop table if exists t1;
   338  
   339  -- -------------------------------------------------------
   340  drop table if exists rename01;
   341  create table rename01 (c int primary key,d int);
   342  insert into rename01 values(1,1);
   343  insert into rename01 values(2,2);
   344  begin;
   345  insert into rename01 values(3,1);
   346  insert into rename01 values(4,2);
   347  alter table rename01 rename column c to `newCCCC`;
   348  select * from rename01;
   349  -- @session:id=1{
   350  use isolation;
   351  -- @wait:0:commit
   352  insert into rename01 (c, d) values (5,7);
   353  insert into rename01 (newCCCC, d) values (5,7);
   354  select * from rename01;
   355  -- @session}
   356  select * from rename01;
   357  drop table rename01;