github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/transaction_enhance.sql (about) 1 -- truncate table 2 drop table if exists atomic_table_10; 3 create table atomic_table_10(c1 int,c2 varchar(25)); 4 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 5 start transaction ; 6 truncate table atomic_table_10; 7 -- @session:id=2&user=sys:dump&password=111 8 use transaction_enhance; 9 select * from atomic_table_10; 10 -- @session 11 select * from atomic_table_10; 12 commit; 13 select * from atomic_table_10; 14 15 drop table if exists atomic_table_10; 16 create table atomic_table_10(c1 int,c2 varchar(25)); 17 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 18 start transaction ; 19 truncate table atomic_table_10; 20 -- @session:id=2&user=sys:dump&password=111 21 select * from atomic_table_10; 22 -- @session 23 select * from atomic_table_10; 24 rollback; 25 select * from atomic_table_10; 26 27 drop table if exists atomic_table_10; 28 create table atomic_table_10(c1 int,c2 varchar(25)); 29 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 30 begin ; 31 truncate table atomic_table_10; 32 -- @bvt:issue#8848 33 -- @session:id=2&user=sys:dump&password=111 34 insert into atomic_table_10 values (6,"a"),(7,"b"),(8,"c"); 35 select * from atomic_table_10; 36 -- @session 37 -- @bvt:issue 38 select * from atomic_table_10; 39 commit; 40 select * from atomic_table_10; 41 42 -- drop table 43 44 drop table if exists atomic_table_11; 45 create table atomic_table_11(c1 int,c2 varchar(25)); 46 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 47 begin; 48 drop table atomic_table_11; 49 -- @bvt:issue#9429 50 -- @session:id=2&user=sys:dump&password=111 51 insert into atomic_table_11 values (6,"a"); 52 select * from atomic_table_11; 53 -- @session 54 -- @bvt:issue 55 commit; 56 select * from atomic_table_11; 57 58 drop table if exists atomic_table_11; 59 create table atomic_table_11(c1 int,c2 varchar(25)); 60 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 61 begin; 62 drop table atomic_table_11; 63 -- @bvt:issue#9429 64 -- @session:id=2&user=sys:dump&password=111 65 insert into atomic_table_11 values (6,"a"); 66 select * from atomic_table_11; 67 -- @session 68 rollback ; 69 select * from atomic_table_11; 70 -- @bvt:issue 71 drop table if exists atomic_table_11; 72 create table atomic_table_11(c1 int,c2 varchar(25)); 73 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 74 begin; 75 -- @session:id=2&user=sys:dump&password=111 76 drop table atomic_table_11; 77 -- @session 78 drop table atomic_table_11; 79 commit; 80 81 --alter table 82 drop table if exists atomic_table_12; 83 create table atomic_table_12(c1 int,c2 varchar(25)); 84 insert into atomic_table_12 values (3,"a"),(4,"b"),(5,"c"); 85 begin; 86 alter table atomic_table_12 add index key1(c1); 87 alter table atomic_table_12 alter index key1 visible; 88 -- @session:id=2&user=sys:dump&password=111 89 show create table atomic_table_12; 90 -- @session 91 commit; 92 show create table atomic_table_12; 93 show index from atomic_table_12; 94 95 use transaction_enhance; 96 drop table if exists atomic_table_12_1; 97 create table atomic_table_12_1(c1 int,c2 varchar(25)); 98 insert into atomic_table_12_1 values (3,"a"),(4,"b"),(5,"c"); 99 begin; 100 alter table atomic_table_12_1 add index key1(c1); 101 alter table atomic_table_12_1 alter index key1 visible; 102 -- @session:id=2&user=sys:dump&password=111 103 show create table atomic_table_12_1; 104 -- @session 105 rollback; 106 show create table atomic_table_12_1; 107 show index from atomic_table_12_1; 108 109 use transaction_enhance; 110 drop table if exists atomic_table_12_2; 111 drop table if exists atomic_table_13; 112 create table atomic_table_12_2(c1 int primary key,c2 varchar(25)); 113 insert into atomic_table_12_2 values (3,"a"),(4,"b"),(5,"c"); 114 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 115 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 116 begin; 117 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_2(c1); 118 -- @session:id=2&user=sys:dump&password=111 119 show create table atomic_table_12_2; 120 insert into atomic_table_13 values (8,"h"); 121 select * from atomic_table_13; 122 -- @session 123 insert into atomic_table_13 values (6,"h"); 124 commit; 125 show create table atomic_table_13; 126 127 drop table if exists atomic_table_12_3; 128 drop table if exists atomic_table_13; 129 create table atomic_table_12_3(c1 int primary key,c2 varchar(25)); 130 insert into atomic_table_12_3 values (3,"a"),(4,"b"),(5,"c"); 131 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 132 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 133 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_3(c1); 134 begin; 135 alter table atomic_table_13 drop foreign key ffa; 136 -- @session:id=2&user=sys:dump&password=111 137 insert into atomic_table_13 values (8,"h"); 138 select * from atomic_table_13; 139 -- @session 140 commit; 141 show create table atomic_table_13; 142 143 drop table if exists atomic_table_12_4; 144 drop table if exists atomic_table_13; 145 create table atomic_table_12_4(c1 int primary key,c2 varchar(25)); 146 insert into atomic_table_12_4 values (3,"a"),(4,"b"),(5,"c"); 147 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 148 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 149 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_4(c1); 150 begin; 151 alter table atomic_table_13 drop foreign key ffa; 152 -- @session:id=2&user=sys:dump&password=111 153 insert into atomic_table_13 values (8,"h"); 154 select * from atomic_table_13; 155 -- @session 156 rollback ; 157 show create table atomic_table_13; 158 159 drop table if exists atomic_table_12_5; 160 drop table if exists atomic_table_13; 161 create table atomic_table_12_5(c1 int,c2 varchar(25)); 162 insert into atomic_table_12_5 values (3,"a"),(4,"b"),(5,"c"); 163 alter table atomic_table_12_5 add index key1(c1); 164 begin; 165 alter table atomic_table_12_5 drop index key1; 166 -- @session:id=2&user=sys:dump&password=111 167 show create table atomic_table_12_5; 168 select * from atomic_table_12_5; 169 -- @session 170 commit; 171 show index from atomic_table_12_5; 172 173 -- w-w conflict 174 drop table if exists atomic_table_14; 175 create table atomic_table_14(c1 int,c2 varchar(25)); 176 insert into atomic_table_14 values (3,"a"),(4,"b"),(5,"c"); 177 start transaction ; 178 alter table atomic_table_14 add index key1(c1); 179 -- @session:id=2&user=sys:dump&password=111 180 drop table atomic_table_14; 181 -- @session 182 -- @bvt:issue#9429 183 insert into atomic_table_14 values (6,"a"),(7,"b"); 184 select * from atomic_table_14; 185 -- @bvt:issue 186 commit; 187 select * from atomic_table_14; 188 189 drop table if exists atomic_table_15; 190 create table atomic_table_15(c1 int,c2 varchar(25)); 191 begin; 192 insert into atomic_table_15 values (6,"a"),(7,"b"); 193 truncate table atomic_table_15; 194 -- @session:id=2&user=sys:dump&password=111 195 drop table atomic_table_15; 196 -- @session 197 select * from atomic_table_15; 198 commit; 199 select * from atomic_table_15; 200 201 drop table if exists atomic_table_16; 202 create table atomic_table_16(c1 int,c2 varchar(25)); 203 begin; 204 insert into atomic_table_16 values (6,"a"),(7,"b"); 205 drop table atomic_table_16; 206 -- @session:id=2&user=sys:dump&password=111 207 drop table atomic_table_16; 208 -- @session 209 commit; 210 select * from atomic_table_16; 211 212 drop table if exists atomic_table_17; 213 create table atomic_table_17(c1 int,c2 varchar(25)); 214 begin; 215 insert into atomic_table_17 values (6,"a"),(7,"b"); 216 drop table atomic_table_17; 217 -- @session:id=2&user=sys:dump&password=111 218 alter table atomic_table_17 add constraint unique key (c1); 219 update atomic_table_17 set c1=8 where c2="b"; 220 -- @session 221 commit; 222 select * from atomic_table_17; 223 224 -- create/drop database，sequence ,create/drop account 225 start transaction ; 226 create database tdb; 227 -- @session:id=2&user=sys:dump&password=111 228 use tdb; 229 -- @session 230 drop database tdb; 231 commit; 232 233 begin; 234 create sequence seq_01 as int start 30; 235 -- @session:id=2&user=sys:dump&password=111 236 select nextval('seq_01'); 237 -- @session 238 commit; 239 select nextval('seq_01'); 240 241 drop table if exists atomic_table_11; 242 drop account if exists trans_acc1; 243 create account trans_acc1 admin_name "admin" identified by "111"; 244 begin; 245 drop account trans_acc1; 246 -- @session:id=2&user=trans_acc1:admin&password=111 247 select count(*) from mo_catalog.mo_account where account_name='trans_acc1'; 248 -- @session 249 commit; 250 select count(*) from mo_catalog.mo_account where account_name='trans_acc1'; 251 252 -- autocommit 253 drop table if exists atomic_table_18; 254 create table atomic_table_18(c1 int,c2 varchar(25)); 255 insert into atomic_table_18 values (6,"a"),(7,"b"); 256 set autocommit=0; 257 alter table atomic_table_18 add index key1(c1); 258 alter table atomic_table_18 alter index key1 visible; 259 -- @session:id=2&user=sys:dump&password=111 260 show create table atomic_table_18; 261 -- @session 262 rollback; 263 show create table atomic_table_18; 264 show index from atomic_table_18; 265 266 truncate table atomic_table_18; 267 -- @session:id=2&user=sys:dump&password=111 268 drop table atomic_table_18; 269 -- @session 270 select * from atomic_table_18; 271 commit; 272 select * from atomic_table_18; 273 274 create table atomic_table_18(c1 int,c2 varchar(25)); 275 insert into atomic_table_18 values (6,"a"),(7,"b"); 276 drop table atomic_table_18; 277 -- @session:id=2&user=sys:dump&password=111 278 drop table atomic_table_18; 279 -- @session 280 select * from atomic_table_18; 281 commit; 282 select * from atomic_table_18; 283 set autocommit=1; 284 drop account if exists trans_acc1;