github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/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  -- @wait:0:commit
   143  -- duplicate 50 will not rollback the txn
   144  Insert into test_11 values(50,50);
   145  -- txn does not rollback. record (5,4) also exists.
   146  select * from test_11;
   147  -- @session}
   148  
   149  select * from test_11;
   150  commit;
   151  set autocommit=1;
   152  
   153  -- @session:id=1{
   154  commit;
   155  set autocommit=1;
   156  select * from test_11;
   157  -- @session}
   158  
   159  select * from test_11;
   160  
   161  -- -------------------------------------------------------
   162  drop table if exists test_11;
   163  create table test_11 (c int primary key,d int);
   164  Insert into test_11 values(1,1);
   165  Insert into test_11 values(2,2);
   166  set autocommit=0;
   167  select * from test_11;
   168  Insert into test_11 values(50,50);
   169  select * from test_11;
   170  -- @session:id=1{
   171  set autocommit=0;
   172  select * from test_11;
   173  -- @wait:0:commit
   174  Insert into test_11 values(50,50);
   175  select * from test_11;
   176  -- @session}
   177  select * from test_11;
   178  
   179  -- @session:id=1{
   180  delete from test_11 where c = 50;
   181  select * from test_11;
   182  -- @session}
   183  select * from test_11;
   184  
   185  commit;
   186  set autocommit=1;
   187  -- @session:id=1{
   188  commit;
   189  set autocommit=1;
   190  select * from test_11;
   191  -- @session}
   192  select * from test_11;
   193  
   194  -- -------------------------------------------------------
   195  drop table if exists test_11;
   196  create table test_11 (c int primary key,d int);
   197  Insert into test_11 values(1,1);
   198  Insert into test_11 values(2,2);
   199  set autocommit=0;
   200  select * from test_11;
   201  Insert into test_11 values(50,50);
   202  select * from test_11;
   203  -- @session:id=1{
   204  set autocommit=0;
   205  select * from test_11;
   206  -- @wait:0:commit
   207  Insert into test_11 values(50,50);
   208  select * from test_11;
   209  -- @session}
   210  select * from test_11;
   211  
   212  -- @session:id=1{
   213  select * from test_11;
   214  update test_11 set c = 100 where d = 50;
   215  select * from test_11;
   216  -- @session}
   217  select * from test_11;
   218  -- @wait:1:commit
   219  Insert into test_11 values(100,50);
   220  
   221  commit;
   222  set autocommit=1;
   223  -- @session:id=1{
   224  commit;
   225  set autocommit=1;
   226  select * from test_11;
   227  -- @session}
   228  select * from test_11;
   229  
   230  -- -------------------------------------------------------
   231  drop table if exists test_11;
   232  create table test_11 (c int primary key,d int);
   233  Insert into test_11 values(1,1);
   234  Insert into test_11 values(2,2);
   235  set autocommit=0;
   236  select * from test_11;
   237  Insert into test_11 values(50,50);
   238  select * from test_11;
   239  -- @session:id=1{
   240  set autocommit=0;
   241  select * from test_11;
   242  -- @wait:0:commit
   243  Insert into test_11 values(50,50);
   244  select * from test_11;
   245  -- @session}
   246  select * from test_11;
   247  
   248  -- @session:id=1{
   249  select * from test_11;
   250  update test_11 set c = 100 where d = 50;
   251  select * from test_11;
   252  -- @session}
   253  select * from test_11;
   254  -- @wait:1:commit
   255  update test_11 set c = 101 where c = 50;
   256  
   257  commit;
   258  set autocommit=1;
   259  -- @session:id=1{
   260  commit;
   261  set autocommit=1;
   262  select * from test_11;
   263  -- @session}
   264  select * from test_11;
   265  
   266  -- -------------------------------------------------------
   267  drop table if exists test_11;
   268  create table test_11 (c int primary key,d int);
   269  Insert into test_11 values(1,1);
   270  Insert into test_11 values(2,2);
   271  set autocommit=0;
   272  select * from test_11;
   273  Insert into test_11 values(50,50);
   274  select * from test_11;
   275  -- @session:id=1{
   276  set autocommit=0;
   277  select * from test_11;
   278  -- @wait:0:commit
   279  Insert into test_11 values(50,50);
   280  select * from test_11;
   281  -- @session}
   282  select * from test_11;
   283  
   284  -- @session:id=1{
   285  select * from test_11;
   286  update test_11 set c = 100 where d = 50;
   287  select * from test_11;
   288  -- @session}
   289  select * from test_11;
   290  -- @wait:1:commit
   291  update test_11 set c = 100 where d = 50;
   292  
   293  commit;
   294  set autocommit=1;
   295  -- @session:id=1{
   296  commit;
   297  set autocommit=1;
   298  select * from test_11;
   299  -- @session}
   300  select * from test_11;
   301  
   302  
   303  -- -------------------------------------------------------
   304  drop table if exists test_11;
   305  set autocommit=0;
   306  create table test_11 (c int primary key,d int);
   307  Insert into test_11 values(1,1);
   308  Insert into test_11 values(2,2);
   309  select * from test_11;
   310  -- @session:id=1{
   311  set autocommit=0;
   312  select * from test_11;
   313  -- @session}
   314  
   315  commit;
   316  set autocommit=1;
   317  select * from test_11;
   318  -- @session:id=1{
   319  commit;
   320  set autocommit=1;
   321  select * from test_11;
   322  -- @session}
   323  
   324  -- -------------------------------------------------------
   325  drop table if exists test_11;
   326  create table test_11 (c int primary key,d int);
   327  Insert into test_11 values(1,1);
   328  Insert into test_11 values(2,2);
   329  select * from test_11;
   330  -- @session:id=1{
   331  set autocommit=0;
   332  select * from test_11;
   333  -- @session}
   334  
   335  set autocommit=0;
   336  drop table test_11;
   337  select * from test_11;
   338  -- @session:id=1{
   339  select * from test_11;
   340  -- @session}
   341  
   342  commit;
   343  set autocommit=1;
   344  select * from test_11;
   345  -- @session:id=1{
   346  commit;
   347  set autocommit=1;
   348  select * from test_11;
   349  -- @session}
   350  
   351  -- -------------------------------------------------------
   352  drop table if exists test_11;
   353  set autocommit=0;
   354  create table test_11 (c int primary key,d int);
   355  Insert into test_11 values(1,1);
   356  Insert into test_11 values(2,2);
   357  select * from test_11;
   358  -- @session:id=1{
   359  set autocommit=0;
   360  select * from test_11;
   361  -- @session}
   362  
   363  -- drop table test_11;
   364  select * from test_11;
   365  -- @session:id=1{
   366  select * from test_11;
   367  -- @session}
   368  commit;
   369  set autocommit=1;
   370  
   371  select * from test_11;
   372  -- @session:id=1{
   373  commit;
   374  set autocommit=1;
   375  select * from test_11;
   376  -- @session}
   377  
   378  drop table if exists test_11;
   379  
   380  
   381  
   382  
   383