github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/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  drop table if exists test_11;
    72  begin;
    73  create table test_11 (c int primary key,d int);
    74  Insert into test_11 values(1,1);
    75  Insert into test_11 values(2,2);
    76  select * from test_11;
    77  -- @session:id=1{
    78  delete from test_11 where c = 1;
    79  select * from test_11;
    80  -- @session}
    81  Insert into test_11 values(1,1);
    82  select * from test_11;
    83  
    84  commit;
    85  select * from test_11;
    86  -- @session:id=1{
    87  select * from test_11;
    88  -- @session}
    89  
    90  -- -------------------------------------------------------
    91  drop table if exists test_11;
    92  create table test_11 (c int primary key,d int);
    93  Insert into test_11 values(1,1);
    94  Insert into test_11 values(2,2);
    95  begin;
    96  Insert into test_11 values(3,1);
    97  Insert into test_11 values(4,2);
    98  select * from test_11;
    99  -- @session:id=1{
   100  select * from test_11;
   101  Insert into test_11 values(5,4);
   102  select * from test_11;
   103  -- @session}
   104  select * from test_11;
   105  Insert into test_11 values(50,50);
   106  -- @session:id=1{
   107  Insert into test_11 values(50,50);
   108  select * from test_11;
   109  -- @session}
   110  select * from test_11;
   111  commit;
   112  -- @session:id=1{
   113  select * from test_11;
   114  -- @session}
   115  select * from test_11;
   116  
   117  -- -------------------------------------------------------
   118  drop table if exists test_11;
   119  create table test_11 (c int primary key,d int);
   120  Insert into test_11 values(1,1);
   121  Insert into test_11 values(2,2);
   122  begin;
   123  select * from test_11;
   124  -- @session:id=1{
   125  select * from test_11;
   126  Insert into test_11 values(50,50);
   127  select * from test_11;
   128  -- @session}
   129  
   130  Insert into test_11 values(50,50);
   131  select * from test_11;
   132  commit;
   133  -- @session:id=1{
   134  select * from test_11;
   135  -- @session}
   136  select * from test_11;
   137  
   138  -- -------------------------------------------------------
   139  drop table if exists test_11;
   140  create table test_11 (c int primary key,d int);
   141  Insert into test_11 values(1,1);
   142  Insert into test_11 values(2,2);
   143  begin;
   144  select * from test_11;
   145  Insert into test_11 values(50,50);
   146  select * from test_11;
   147  -- @session:id=1{
   148  select * from test_11;
   149  Insert into test_11 values(50,50);
   150  select * from test_11;
   151  -- @session}
   152  select * from test_11;
   153  
   154  -- @session:id=1{
   155  select * from test_11;
   156  delete from test_11 where c = 50;
   157  select * from test_11;
   158  -- @session}
   159  select * from test_11;
   160  
   161  commit;
   162  -- @session:id=1{
   163  select * from test_11;
   164  -- @session}
   165  select * from test_11;
   166  
   167  -- -------------------------------------------------------
   168  drop table if exists test_11;
   169  create table test_11 (c int primary key,d int);
   170  Insert into test_11 values(1,1);
   171  Insert into test_11 values(2,2);
   172  begin;
   173  select * from test_11;
   174  Insert into test_11 values(50,50);
   175  select * from test_11;
   176  -- @session:id=1{
   177  select * from test_11;
   178  Insert into test_11 values(50,50);
   179  select * from test_11;
   180  -- @session}
   181  select * from test_11;
   182  
   183  -- @session:id=1{
   184  select * from test_11;
   185  update test_11 set c = 100 where d = 50;
   186  select * from test_11;
   187  -- @session}
   188  select * from test_11;
   189  Insert into test_11 values(100,50);
   190  
   191  commit;
   192  -- @session:id=1{
   193  select * from test_11;
   194  -- @session}
   195  select * from test_11;
   196  
   197  -- -------------------------------------------------------
   198  drop table if exists test_11;
   199  create table test_11 (c int primary key,d int);
   200  Insert into test_11 values(1,1);
   201  Insert into test_11 values(2,2);
   202  begin;
   203  select * from test_11;
   204  Insert into test_11 values(50,50);
   205  select * from test_11;
   206  -- @session:id=1{
   207  select * from test_11;
   208  Insert into test_11 values(50,50);
   209  select * from test_11;
   210  -- @session}
   211  select * from test_11;
   212  
   213  -- @session:id=1{
   214  select * from test_11;
   215  update test_11 set c = 100 where d = 50;
   216  select * from test_11;
   217  -- @session}
   218  select * from test_11;
   219  update test_11 set c = 101 where c = 50;
   220  
   221  commit;
   222  -- @session:id=1{
   223  select * from test_11;
   224  -- @session}
   225  select * from test_11;
   226  
   227  -- -------------------------------------------------------
   228  drop table if exists test_11;
   229  create table test_11 (c int primary key,d int);
   230  Insert into test_11 values(1,1);
   231  Insert into test_11 values(2,2);
   232  begin;
   233  select * from test_11;
   234  Insert into test_11 values(50,50);
   235  select * from test_11;
   236  -- @session:id=1{
   237  select * from test_11;
   238  Insert into test_11 values(50,50);
   239  select * from test_11;
   240  -- @session}
   241  select * from test_11;
   242  
   243  -- @session:id=1{
   244  select * from test_11;
   245  update test_11 set c = 100 where d = 50;
   246  select * from test_11;
   247  -- @session}
   248  select * from test_11;
   249  update test_11 set c = 100 where d = 50;
   250  
   251  commit;
   252  -- @session:id=1{
   253  select * from test_11;
   254  -- @session}
   255  select * from test_11;
   256  
   257  -- -------------------------------------------------------
   258  drop table if exists test_11;
   259  begin;
   260  create table test_11 (c int primary key,d int);
   261  Insert into test_11 values(1,1);
   262  Insert into test_11 values(2,2);
   263  select * from test_11;
   264  -- @session:id=1{
   265  select * from test_11;
   266  -- @session}
   267  
   268  commit;
   269  select * from test_11;
   270  -- @session:id=1{
   271  select * from test_11;
   272  -- @session}
   273  
   274  -- -------------------------------------------------------
   275  drop table if exists test_11;
   276  create table test_11 (c int primary key,d int);
   277  Insert into test_11 values(1,1);
   278  Insert into test_11 values(2,2);
   279  select * from test_11;
   280  -- @session:id=1{
   281  select * from test_11;
   282  -- @session}
   283  
   284  begin;
   285  drop table test_11;
   286  select * from test_11;
   287  -- @session:id=1{
   288  select * from test_11;
   289  -- @session}
   290  
   291  commit;
   292  select * from test_11;
   293  -- @session:id=1{
   294  select * from test_11;
   295  -- @session}
   296  
   297  -- -------------------------------------------------------
   298  drop table if exists test_11;
   299  begin;
   300  create table test_11 (c int primary key,d int);
   301  Insert into test_11 values(1,1);
   302  Insert into test_11 values(2,2);
   303  select * from test_11;
   304  -- @session:id=1{
   305  select * from test_11;
   306  -- @session}
   307  
   308  -- drop table test_11;
   309  select * from test_11;
   310  -- @session:id=1{
   311  select * from test_11;
   312  -- @session}
   313  commit;
   314  
   315  select * from test_11;
   316  -- @session:id=1{
   317  select * from test_11;
   318  -- @session}
   319  
   320  drop table if exists test_11;
   321  
   322  drop table if exists t1;
   323  create table t1 (a int not null, b int);
   324  insert into t1 values (1, 1);
   325  begin;
   326  select * from t1;
   327  update t1 set a=null where b=1;
   328  select * from t1;
   329  commit;
   330  drop table if exists t1;