vitess.io/vitess@v0.16.2/go/vt/vtexplain/testdata/insertsharded-queries.sql (about)

     1  insert into user (id, name) values(1, 'alice');
     2  insert into user (id, name) values(2, 'bob');
     3  insert ignore into user (id, name) values(2, 'bob');
     4  insert ignore into user (id, name, nickname) values(2, 'bob', 'bob');
     5  insert into user (id, name, nickname) values(2, 'bob', 'bobby') on duplicate key update nickname='bobby';
     6  insert into user (id, name, nickname, address) values(2, 'bob', 'bobby', '123 main st') on duplicate key update nickname=values(nickname), address=values(address);
     7  
     8  /*
     9  
    10  TODO(demmer) These test cases induce a fundamental race in vtgate because the
    11  insert statements race to execute on different shards, but the subsequent
    12  commit statements are executed serially in the order that the inserts were
    13  run.
    14  
    15  This means that in the test output, the commits can end up in different epochs,
    16  which causes travis test failures.
    17  
    18  insert ignore into user (id, name) values(2, 'bob'),(3, 'charlie');
    19  insert into user (id, name, nickname, address) values(2, 'bob', 'bobby', '123 main st'), (3, 'jane', 'janie', '456 elm st')on duplicate key update nickname=values(nickname), address=values(address);
    20  
    21  */
    22  
    23  /*
    24  With the multi-shard autocommit option selected all inserts happen in one
    25  round trip so there is no race
    26  */
    27  insert /*vt+ MULTI_SHARD_AUTOCOMMIT=1 */ into music_extra (id, extra) values (1, 'a'), (2, 'b'), (3, 'c');
    28  
    29  /*
    30   Explicit begin and commit are needed to make the vtexplain output predicable for tests to pass.
    31   the lookup queries gets executed without a transaction.
    32   That is evident from the fact that begin only gets executed when origin insert statement is executed
    33   and not the lookup query.
    34   */
    35  begin;
    36  insert into member (lkp, more_id, id) values ("a", 1, 1), ("b", 1, 3), ("c", 1, 1) on duplicate key update more_id = 2;
    37  commit;