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

     1  drop table if exists dis_table_02;
     2  create table dis_table_02(a int not null auto_increment,b varchar(25) not null,c datetime,primary key(a),key bstr (b),key cdate (c) );
     3  insert into dis_table_02(b,c) values ('aaaa','2020-09-08');
     4  insert into dis_table_02(b,c) values ('aaaa','2020-09-08');
     5  
     6  begin;
     7  update dis_table_02 set b='tittttt' where a>1;
     8  
     9  -- @session:id=1{
    10  use ww_conflict;
    11  begin;
    12  -- @wait:0:commit
    13  update dis_table_02 set b='dpqweoe' where a>1;
    14  -- @session}
    15  
    16  commit;
    17  
    18  -- @session:id=1{
    19  start transaction;
    20  -- @session}
    21  
    22  drop table if exists dis_table_02;
    23  
    24  -- transcation: w-w conflict
    25  drop table if exists dis_table_02;
    26  create table dis_table_02(a int not null auto_increment,b varchar(25) not null,c datetime,primary key(a),key bstr (b),key cdate (c) );
    27  insert into dis_table_02(b,c) values ('aaaa','2020-09-08');
    28  insert into dis_table_02(b,c) values ('aaaa','2020-09-08');
    29  begin;
    30  alter table dis_table_02 rename column a to newA;
    31  -- @session:id=1{
    32  use ww_conflict;
    33  begin;
    34  -- @wait:0:commit
    35  update dis_table_02 set b='dpqweoe' where a>1;
    36  update dis_table_02 set b='dpqweoe' where newA>1;
    37  commit;
    38  select * from dis_table_02;
    39  -- @session}
    40  select * from dis_table_02;
    41  drop table dis_table_02;
    42  
    43  ---------------------------------------------------
    44  -- alter table add primary key
    45  drop table if exists dis_table_02;
    46  create table dis_table_02(a int not null default 10,b varchar(25) not null,c datetime);
    47  insert into dis_table_02(b,c) values ('aaaa','2020-09-08');
    48  insert into dis_table_02(b,c) values ('bbbb','2020-09-08');
    49  begin;
    50  alter table dis_table_02 add constraint primary key (b);
    51  -- @session:id=1{
    52  use ww_conflict;
    53  begin;
    54  -- @wait:0:commit
    55  update dis_table_02 set b='dpqweoe' where a>1;
    56  commit;
    57  select * from dis_table_02;
    58  -- @session}
    59  select * from dis_table_02;
    60  drop table dis_table_02;
    61  
    62  -------------------------------------------------------------
    63  -- alter table drop primary key
    64  drop table if exists dis_table_02;
    65  create table dis_table_02(a int not null default 10,b varchar(25) not null,c datetime,primary key (b));
    66  insert into dis_table_02(b,c) values ('aaaa','2020-09-08');
    67  insert into dis_table_02(b,c) values ('bbbb','2020-09-08');
    68  begin;
    69  alter table dis_table_02 drop primary key;
    70  -- @session:id=1{
    71  use ww_conflict;
    72  begin;
    73  -- @wait:0:commit
    74  insert into dis_table_02 values(10, 'aaaa', '1998-09-12');
    75  commit;
    76  select * from dis_table_02;
    77  -- @session}
    78  select * from dis_table_02;
    79  drop table dis_table_02;