github.com/pingcap/tiflow@v0.0.0-20240520035814-5bf52d54e205/tests/integration_tests/savepoint/data/prepare.sql (about)

     1  drop database if exists `savepoint`;
     2  create database `savepoint`;
     3  use `savepoint`;
     4  
     5  create table t(id int key, a int, index idx(a));
     6  
     7  -- Test savepoint in optimistic transaction.
     8  
     9  -- Check savepoint with same name will overwrite the old
    10  begin optimistic;
    11  savepoint s1;
    12  insert into t values (1, 1);
    13  savepoint s1;
    14  insert into t values (2, 2);
    15  rollback to s1;
    16  commit;
    17  select * from t order by id;
    18  
    19  --  Check rollback to savepoint will delete the later savepoint.
    20  delete from t;
    21  begin optimistic;
    22  insert into t values (1, 1);
    23  savepoint s1;
    24  insert into t values (2, 2);
    25  savepoint s2;
    26  insert into t values (3, 3);
    27  savepoint s3;
    28  rollback to s2;
    29  commit;
    30  
    31  --  Check rollback to savepoint will rollback insert.
    32  delete from t;
    33  begin optimistic;
    34  insert into t values (1, 1), (2, 2);
    35  savepoint s1;
    36  insert into t values (3, 3);
    37  rollback to s1;
    38  insert into t values (3, 5);
    39  commit;
    40  
    41  --  Check rollback to savepoint will rollback insert onduplicate update.
    42  delete from t;
    43  insert into t values (1, 1);
    44  begin optimistic;
    45  insert into t values (2, 2);
    46  savepoint s1;
    47  insert into t values (1, 1), (2, 2), (3, 3) on duplicate key update a=a+1;
    48  rollback to s1;
    49  commit;
    50  insert into t values (3, 3);
    51  
    52  --  Check rollback to savepoint will rollback update.
    53  delete from t;
    54  begin optimistic;
    55  insert into t values (1, 1), (2, 2);
    56  savepoint s1;
    57  update t set a=a+1 where id = 1;
    58  rollback to s1;
    59  update t set a=a+1 where id = 2;
    60  commit;
    61  update t set a=a+1 where id in (1, 2);
    62  
    63  --  Check rollback to savepoint will rollback update.
    64  delete from t;
    65  insert into t values (1, 1), (2, 2);
    66  begin optimistic;
    67  insert into t values (3, 3);
    68  update t set a=a+1 where id in (1, 3);
    69  savepoint s1;
    70  update t set a=a+1 where id in (2, 3);
    71  rollback to s1;
    72  commit;
    73  
    74  --  Check rollback to savepoint will rollback delete.
    75  delete from t;
    76  insert into t values (1, 1), (2, 2);
    77  begin optimistic;
    78  insert into t values (3, 3);
    79  savepoint s1;
    80  delete from t where id in (1, 3);
    81  rollback to s1;
    82  commit;
    83  
    84  -- Test savepoint in pessimistic transaction.
    85  delete from t;
    86  
    87  begin pessimistic;
    88  savepoint s1;
    89  insert into t values (1, 1);
    90  savepoint s1;
    91  insert into t values (2, 2);
    92  rollback to s1;
    93  commit;
    94  
    95  --  Check rollback to savepoint will delete the later savepoint.
    96  delete from t;
    97  begin pessimistic;
    98  insert into t values (1, 1);
    99  savepoint s1;
   100  insert into t values (2, 2);
   101  savepoint s2;
   102  insert into t values (3, 3);
   103  savepoint s3;
   104  rollback to s2;
   105  commit;
   106  
   107  --  Check rollback to savepoint will rollback insert.
   108  delete from t;
   109  begin pessimistic;
   110  insert into t values (1, 1), (2, 2);
   111  savepoint s1;
   112  insert into t values (3, 3);
   113  rollback to s1;
   114  insert into t values (3, 5);
   115  commit;
   116  
   117  --  Check rollback to savepoint will rollback insert onduplicate update and release lock.
   118  delete from t;
   119  insert into t values (1, 1);
   120  begin pessimistic;
   121  insert into t values (2, 2);
   122  savepoint s1;
   123  insert into t values (1, 1), (2, 2), (3, 3) on duplicate key update a=a+1;
   124  rollback to s1;
   125  commit;
   126  insert into t values (3, 3);
   127  
   128  --  Check rollback to savepoint will rollback update.
   129  delete from t;
   130  begin pessimistic;
   131  insert into t values (1, 1), (2, 2);
   132  savepoint s1;
   133  update t set a=a+1 where id = 1;
   134  rollback to s1;
   135  update t set a=a+1 where id = 2;
   136  commit;
   137  update t set a=a+1 where id in (1, 2);
   138  
   139  --  Check rollback to savepoint will rollback update.
   140  delete from t;
   141  insert into t values (1, 1), (2, 2);
   142  begin pessimistic;
   143  insert into t values (3, 3);
   144  update t set a=a+1 where id in (1, 3);
   145  savepoint s1;
   146  update t set a=a+1 where id in (2, 3);
   147  rollback to s1;
   148  commit;
   149  
   150  --  Check rollback to savepoint will rollback delete.
   151  delete from t;
   152  insert into t values (1, 1), (2, 2);
   153  begin pessimistic;
   154  insert into t values (3, 3);
   155  savepoint s1;
   156  delete from t where id in (1, 3);
   157  rollback to s1;
   158  commit;
   159  
   160  create table finish_mark (id int PRIMARY KEY);
   161