github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_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  -- @bvt:issue#6949
    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  -- @pattern
    91  Insert into test_11 values(1,1);
    92  select * from test_11;
    93  
    94  commit;
    95  set @@autocommit=1;
    96  
    97  select * from test_11;
    98  -- @session:id=1{
    99  select * from test_11;
   100  -- @session}
   101  -- @bvt:issue
   102  
   103  -- -------------------------------------------------------
   104  drop table if exists test_11;
   105  create table test_11 (c int primary key,d int);
   106  Insert into test_11 values(1,1);
   107  Insert into test_11 values(2,2);
   108  
   109  set @@autocommit=0;
   110  Insert into test_11 values(3,1);
   111  Insert into test_11 values(4,2);
   112  select * from test_11;
   113  -- @session:id=1{
   114  select * from test_11;
   115  Insert into test_11 values(5,4);
   116  select * from test_11;
   117  -- @session}
   118  select * from test_11;
   119  Insert into test_11 values(50,50);
   120  -- @session:id=1{
   121  -- @wait:0:commit
   122  -- @pattern
   123  Insert into test_11 values(50,50);
   124  select * from test_11;
   125  -- @session}
   126  select * from test_11;
   127  commit;
   128  set @@autocommit=1;
   129  
   130  -- @session:id=1{
   131  select * from test_11;
   132  -- @session}
   133  select * from test_11;
   134  
   135  -- -------------------------------------------------------
   136  drop table if exists test_11;
   137  create table test_11 (c int primary key,d int);
   138  Insert into test_11 values(1,1);
   139  Insert into test_11 values(2,2);
   140  
   141  set @@autocommit=0;
   142  select * from test_11;
   143  -- @session:id=1{
   144  select * from test_11;
   145  Insert into test_11 values(50,50);
   146  select * from test_11;
   147  -- @session}
   148  -- @pattern
   149  Insert into test_11 values(50,50);
   150  select * from test_11;
   151  commit;
   152  set @@autocommit=1;
   153  -- @session:id=1{
   154  select * from test_11;
   155  -- @session}
   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  
   164  set @@autocommit=0;
   165  select * from test_11;
   166  Insert into test_11 values(50,50);
   167  select * from test_11;
   168  -- @session:id=1{
   169  select * from test_11;
   170  -- @wait:0:commit
   171  -- @pattern
   172  Insert into test_11 values(50,50);
   173  select * from test_11;
   174  -- @session}
   175  select * from test_11;
   176  
   177  -- @session:id=1{
   178  select * from test_11;
   179  delete from test_11 where c = 50;
   180  select * from test_11;
   181  -- @session}
   182  select * from test_11;
   183  
   184  commit;
   185  set @@autocommit=1;
   186  
   187  -- @session:id=1{
   188  select * from test_11;
   189  -- @session}
   190  select * from test_11;
   191  
   192  -- -------------------------------------------------------
   193  drop table if exists test_11;
   194  create table test_11 (c int primary key,d int);
   195  Insert into test_11 values(1,1);
   196  Insert into test_11 values(2,2);
   197  
   198  set @@autocommit=0;
   199  select * from test_11;
   200  Insert into test_11 values(50,50);
   201  select * from test_11;
   202  -- @session:id=1{
   203  select * from test_11;
   204  -- @wait:0:commit
   205  -- @pattern
   206  Insert into test_11 values(50,50);
   207  select * from test_11;
   208  -- @session}
   209  select * from test_11;
   210  
   211  -- @session:id=1{
   212  select * from test_11;
   213  update test_11 set c = 100 where d = 50;
   214  select * from test_11;
   215  -- @session}
   216  select * from test_11;
   217  -- @pattern
   218  Insert into test_11 values(100,50);
   219  
   220  commit;
   221  set @@autocommit=1;
   222  
   223  -- @session:id=1{
   224  select * from test_11;
   225  -- @session}
   226  select * from test_11;
   227  
   228  -- -------------------------------------------------------
   229  drop table if exists test_11;
   230  create table test_11 (c int primary key,d int);
   231  Insert into test_11 values(1,1);
   232  Insert into test_11 values(2,2);
   233  
   234  set @@autocommit=0;
   235  select * from test_11;
   236  Insert into test_11 values(50,50);
   237  select * from test_11;
   238  -- @session:id=1{
   239  select * from test_11;
   240  -- @wait:0:commit
   241  -- @pattern
   242  Insert into test_11 values(50,50);
   243  select * from test_11;
   244  -- @session}
   245  select * from test_11;
   246  
   247  -- @session:id=1{
   248  select * from test_11;
   249  update test_11 set c = 100 where d = 50;
   250  select * from test_11;
   251  -- @session}
   252  select * from test_11;
   253  update test_11 set c = 101 where c = 50;
   254  
   255  commit;
   256  set @@autocommit=1;
   257  
   258  -- @session:id=1{
   259  select * from test_11;
   260  -- @session}
   261  select * from test_11;
   262  
   263  -- -------------------------------------------------------
   264  drop table if exists test_11;
   265  create table test_11 (c int primary key,d int);
   266  Insert into test_11 values(1,1);
   267  Insert into test_11 values(2,2);
   268  
   269  set @@autocommit=0;
   270  select * from test_11;
   271  Insert into test_11 values(50,50);
   272  select * from test_11;
   273  -- @session:id=1{
   274  select * from test_11;
   275  -- @wait:0:commit
   276  -- @pattern
   277  Insert into test_11 values(50,50);
   278  select * from test_11;
   279  -- @session}
   280  select * from test_11;
   281  
   282  -- @session:id=1{
   283  select * from test_11;
   284  update test_11 set c = 100 where d = 50;
   285  select * from test_11;
   286  -- @session}
   287  select * from test_11;
   288  update test_11 set c = 100 where d = 50;
   289  
   290  commit;
   291  set @@autocommit=1;
   292  -- @session:id=1{
   293  select * from test_11;
   294  -- @session}
   295  select * from test_11;
   296  
   297  -- -------------------------------------------------------
   298  drop table if exists test_11;
   299  set @@autocommit=0;
   300  
   301  create table test_11 (c int primary key,d int);
   302  Insert into test_11 values(1,1);
   303  Insert into test_11 values(2,2);
   304  select * from test_11;
   305  -- @session:id=1{
   306  select * from test_11;
   307  -- @session}
   308  
   309  commit;
   310  set @@autocommit=1;
   311  select * from test_11;
   312  -- @session:id=1{
   313  select * from test_11;
   314  -- @session}
   315  
   316  -- -------------------------------------------------------
   317  drop table if exists test_11;
   318  create table test_11 (c int primary key,d int);
   319  Insert into test_11 values(1,1);
   320  Insert into test_11 values(2,2);
   321  select * from test_11;
   322  -- @session:id=1{
   323  select * from test_11;
   324  -- @session}
   325  
   326  set @@autocommit=0;
   327  drop table test_11;
   328  -- test_11 does not exist. the txn will not be rollback.
   329  -- so the test_11 will be deleted after the txn is committed.
   330  select * from test_11;
   331  -- @session:id=1{
   332  select * from test_11;
   333  -- @session}
   334  
   335  commit;
   336  set @@autocommit=1;
   337  select * from test_11;
   338  -- @session:id=1{
   339  select * from test_11;
   340  -- @session}
   341  
   342  -- -------------------------------------------------------
   343  drop table if exists test_11;
   344  set @@autocommit=0;
   345  create table test_11 (c int primary key,d int);
   346  Insert into test_11 values(1,1);
   347  Insert into test_11 values(2,2);
   348  select * from test_11;
   349  -- @session:id=1{
   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  select * from test_11;
   364  -- @session}
   365  
   366  drop table if exists test_11;
   367  create database test_drop_db;
   368  set autocommit=0;
   369  use test_drop_db;
   370  drop database test_drop_db;
   371  show databases like '%test_drop_db%';
   372  set autocommit=1;