github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/conflict_transation.sql (about)

     1  -- primary key conflict/insert into values
     2  create table ct_01(a int primary key,b varchar(25));
     3  insert into ct_01 values(1,'bell'),(2,'app'),(1,'com');
     4  insert into ct_01 values(1,'bell'),(2,'app');
     5  begin;
     6  insert into ct_01 values(3,'oppo'),(3,'zow');
     7  -- @session:id=1{
     8  use conflict_transation;
     9  start transaction ;
    10  insert into ct_01 values(2,'yooo');
    11  commit;
    12  -- @session}
    13  commit;
    14  select * from ct_01;
    15  
    16  --primary key conflict/update
    17  create table ct_02(a int primary key,b varchar(25));
    18  insert into ct_02 values(1,'bell'),(2,'app'),(3,'com');
    19  start transaction ;
    20  update ct_02 set a=5 where b='app';
    21  -- @session:id=1{
    22  begin;
    23  update ct_02 set a=5 where b='bell';
    24  commit;
    25  -- @session}
    26  commit;
    27  begin;
    28  update ct_02 set a=3 where b='bell';
    29  commit;
    30  select * from ct_02;
    31  
    32  -- primary key conflict/delete
    33  create table ct_03(a int primary key,b varchar(25));
    34  insert into ct_03 values(1,'bell'),(2,'app'),(3,'com');
    35  begin;
    36  delete from ct_03 where a=1;
    37  select * from ct_03;
    38  -- @session:id=1{
    39  begin;
    40  update ct_03 set b='urea' where a=1;
    41  select * from ct_03;
    42  commit;
    43  -- @session}
    44  commit;
    45  select * from ct_03;
    46  
    47  -- primary key conflict/insert into select
    48  create table ct_04_temp(a int,b varchar(25));
    49  insert into ct_04_temp values (1,'bell'),(2,'app'),(1,'com');
    50  create table ct_04(a int primary key,b varchar(25));
    51  begin;
    52  insert into ct_04 select * from ct_04_temp;
    53  commit;
    54  select * from ct_04;
    55  
    56  -- primary key conflict/insert infile.
    57  -- @bvt:issue#3433
    58  create table ct_05(a int,b varchar(25) primary key);
    59  begin;
    60  load data infile '$resources/load_data/ct_file.csv' into table ct_05 fields terminated by ',';
    61  commit;
    62  select * from ct_05;
    63  -- @bvt:issue
    64  
    65  --unique index and secondary index conflict
    66  create table ct_06(a bigint,b varchar(25),c int, d varchar(25),primary key(a),unique index c(c),key b(b),key d(d));
    67  start transaction ;
    68  insert into ct_06 select 5678,'high',487,'comment test';
    69  -- @bvt:issue#6949
    70  insert into ct_06 select 5679,'lower',487,'define';
    71  -- @bvt:issue
    72  insert into ct_06 values (897,'number',908,'run tools'),(898,'string',908,'ffff'),(899,'string',918,'while'),(900,'string',948,'word file'),(901,'string',902,'oooo'),(902,'string',87,'dddd'),(903,'string',87,'eeee');
    73  select * from ct_06;
    74  commit;
    75  select * from ct_06;
    76  
    77  begin;
    78  insert into ct_06 values (500,'number1',908,'ooooffff');
    79  -- @session:id=1{
    80  start transaction ;
    81  insert into ct_06 values (501,'number2',908,'zzzztttt');
    82  commit;
    83  select * from ct_06;
    84  -- @session}
    85  commit;
    86  select * from ct_06;
    87  
    88  --comprimary key conflict
    89  create table ct_07(a int,b varchar(25),c date, d double,primary key(a,c));
    90  insert into ct_07 values (1,'901','2011-09-29',0.01),(2,'187','2011-09-29',1.31),(3,'90','2111-02-09',10.01);
    91  begin;
    92  insert into ct_07 values (3,'90','2111-02-09',10.01);
    93  insert into ct_07 values (4,'11','2011-09-29',7.00),(2,'567','2011-09-29',1.31),(4,'90','2011-09-29',89.3);
    94  select * from ct_07;
    95  commit;
    96  select * from ct_07;