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

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