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