github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/transaction/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  create table ct_05(a int,b varchar(25) primary key);
    58  begin;
    59  load data infile '$resources/load_data/ct_file.csv' into table ct_05;
    60  commit;
    61  select * from ct_05;
    62  
    63  --unique index and secondary index conflict
    64  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));
    65  start transaction ;
    66  insert into ct_06 select 5678,'high',487,'comment test';
    67  insert into ct_06 select 5679,'lower',487,'define';
    68  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');
    69  select * from ct_06;
    70  commit;
    71  select * from ct_06;
    72  
    73  begin;
    74  insert into ct_06 values (500,'number1',908,'ooooffff');
    75  -- @session:id=1{
    76  start transaction ;
    77  insert into ct_06 values (501,'number2',908,'zzzztttt');
    78  commit;
    79  select * from ct_06;
    80  -- @session}
    81  commit;
    82  select * from ct_06;
    83  
    84  --comprimary key conflict
    85  create table ct_07(a int,b varchar(25),c date, d double,primary key(a,c));
    86  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);
    87  begin;
    88  insert into ct_07 values (3,'90','2111-02-09',10.01);
    89  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);
    90  select * from ct_07;
    91  commit;
    92  select * from ct_07;