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