github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/optimistic/transaction_enhance.result (about) 1 drop table if exists atomic_table_10; 2 create table atomic_table_10(c1 int,c2 varchar(25)); 3 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 4 start transaction ; 5 truncate table atomic_table_10; 6 use transaction_enhance; 7 select * from atomic_table_10; 8 c1 c2 9 3 a 10 4 b 11 5 c 12 select * from atomic_table_10; 13 c1 c2 14 commit; 15 select * from atomic_table_10; 16 c1 c2 17 drop table if exists atomic_table_10; 18 create table atomic_table_10(c1 int,c2 varchar(25)); 19 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 20 start transaction ; 21 truncate table atomic_table_10; 22 select * from atomic_table_10; 23 c1 c2 24 3 a 25 4 b 26 5 c 27 select * from atomic_table_10; 28 c1 c2 29 rollback; 30 select * from atomic_table_10; 31 c1 c2 32 3 a 33 4 b 34 5 c 35 drop table if exists atomic_table_10; 36 create table atomic_table_10(c1 int,c2 varchar(25)); 37 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 38 begin ; 39 truncate table atomic_table_10; 40 insert into atomic_table_10 values (6,"a"),(7,"b"),(8,"c"); 41 42 select * from atomic_table_10; 43 c1 c2 44 3 a 45 4 b 46 5 c 47 6 a 48 7 b 49 8 c 50 select * from atomic_table_10; 51 c1 c2 52 commit; 53 select * from atomic_table_10; 54 c1 c2 55 drop table if exists atomic_table_11; 56 create table atomic_table_11(c1 int,c2 varchar(25)); 57 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 58 begin; 59 drop table atomic_table_11; 60 insert into atomic_table_11 values (6,"a"); 61 62 select * from atomic_table_11; 63 c1 c2 64 3 a 65 4 b 66 5 c 67 6 a 68 commit; 69 select * from atomic_table_11; 70 SQL parser error: table "atomic_table_11" does not exist 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 drop table atomic_table_11; 76 insert into atomic_table_11 values (6,"a"); 77 78 select * from atomic_table_11; 79 c1 c2 80 3 a 81 4 b 82 5 c 83 6 a 84 rollback ; 85 86 select * from atomic_table_11; 87 c1 c2 88 3 a 89 4 b 90 5 c 91 6 a 92 drop table if exists atomic_table_11; 93 create table atomic_table_11(c1 int,c2 varchar(25)); 94 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 95 begin; 96 drop table atomic_table_11; 97 drop table atomic_table_11; 98 commit; 99 w-w conflict 100 drop table if exists atomic_table_12; 101 create table atomic_table_12(c1 int,c2 varchar(25)); 102 insert into atomic_table_12 values (3,"a"),(4,"b"),(5,"c"); 103 begin; 104 alter table atomic_table_12 add index key1(c1); 105 alter table atomic_table_12 alter index key1 visible; 106 show create table atomic_table_12; 107 Table Create Table 108 atomic_table_12 CREATE TABLE `atomic_table_12` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 109 commit; 110 show create table atomic_table_12; 111 Table Create Table 112 atomic_table_12 CREATE TABLE `atomic_table_12` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`c1`)\n) 113 show index from atomic_table_12; 114 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 115 atomic_table_12 1 key1 1 c1 A 0 NULL NULL YES YES NULL 116 use transaction_enhance; 117 drop table if exists atomic_table_12_1; 118 create table atomic_table_12_1(c1 int,c2 varchar(25)); 119 insert into atomic_table_12_1 values (3,"a"),(4,"b"),(5,"c"); 120 begin; 121 alter table atomic_table_12_1 add index key1(c1); 122 alter table atomic_table_12_1 alter index key1 visible; 123 show create table atomic_table_12_1; 124 Table Create Table 125 atomic_table_12_1 CREATE TABLE `atomic_table_12_1` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 126 rollback; 127 show create table atomic_table_12_1; 128 Table Create Table 129 atomic_table_12_1 CREATE TABLE `atomic_table_12_1` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 130 show index from atomic_table_12_1; 131 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 132 use transaction_enhance; 133 drop table if exists atomic_table_12_2; 134 drop table if exists atomic_table_13; 135 create table atomic_table_12_2(c1 int primary key,c2 varchar(25)); 136 insert into atomic_table_12_2 values (3,"a"),(4,"b"),(5,"c"); 137 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 138 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 139 begin; 140 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_2(c1); 141 show create table atomic_table_12_2; 142 Table Create Table 143 atomic_table_12_2 CREATE TABLE `atomic_table_12_2` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 144 insert into atomic_table_13 values (8,"h"); 145 select * from atomic_table_13; 146 c1 c2 147 3 d 148 4 e 149 5 f 150 8 h 151 insert into atomic_table_13 values (6,"h"); 152 internal error: Cannot add or update a child row: a foreign key constraint fails 153 commit; 154 show create table atomic_table_13; 155 Table Create Table 156 atomic_table_13 CREATE TABLE `atomic_table_13` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`c1`),\nCONSTRAINT `ffa` FOREIGN KEY (`c1`) REFERENCES `atomic_table_12_2` (`c1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 157 drop table if exists atomic_table_12_3; 158 drop table if exists atomic_table_13; 159 create table atomic_table_12_3(c1 int primary key,c2 varchar(25)); 160 insert into atomic_table_12_3 values (3,"a"),(4,"b"),(5,"c"); 161 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 162 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 163 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_3(c1); 164 begin; 165 alter table atomic_table_13 drop foreign key ffa; 166 insert into atomic_table_13 values (8,"h"); 167 internal error: Cannot add or update a child row: a foreign key constraint fails 168 select * from atomic_table_13; 169 c1 c2 170 3 d 171 4 e 172 5 f 173 commit; 174 show create table atomic_table_13; 175 Table Create Table 176 atomic_table_13 CREATE TABLE `atomic_table_13` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 177 drop table if exists atomic_table_12_4; 178 drop table if exists atomic_table_13; 179 create table atomic_table_12_4(c1 int primary key,c2 varchar(25)); 180 insert into atomic_table_12_4 values (3,"a"),(4,"b"),(5,"c"); 181 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 182 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 183 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_4(c1); 184 begin; 185 alter table atomic_table_13 drop foreign key ffa; 186 insert into atomic_table_13 values (8,"h"); 187 internal error: Cannot add or update a child row: a foreign key constraint fails 188 select * from atomic_table_13; 189 c1 c2 190 3 d 191 4 e 192 5 f 193 rollback ; 194 show create table atomic_table_13; 195 Table Create Table 196 atomic_table_13 CREATE TABLE `atomic_table_13` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`c1`),\nCONSTRAINT `ffa` FOREIGN KEY (`c1`) REFERENCES `atomic_table_12_4` (`c1`) ON DELETE RESTRICT ON UPDATE RESTRICT\n) 197 drop table if exists atomic_table_12_5; 198 drop table if exists atomic_table_13; 199 create table atomic_table_12_5(c1 int,c2 varchar(25)); 200 insert into atomic_table_12_5 values (3,"a"),(4,"b"),(5,"c"); 201 alter table atomic_table_12_5 add index key1(c1); 202 begin; 203 alter table atomic_table_12_5 drop index key1; 204 show create table atomic_table_12_5; 205 Table Create Table 206 atomic_table_12_5 CREATE TABLE `atomic_table_12_5` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`c1`)\n) 207 select * from atomic_table_12_5; 208 c1 c2 209 3 a 210 4 b 211 5 c 212 commit; 213 show index from atomic_table_12_5; 214 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 215 drop table if exists atomic_table_14; 216 create table atomic_table_14(c1 int,c2 varchar(25)); 217 insert into atomic_table_14 values (3,"a"),(4,"b"),(5,"c"); 218 start transaction ; 219 alter table atomic_table_14 add index key1(c1); 220 drop table atomic_table_14; 221 insert into atomic_table_14 values (6,"a"),(7,"b"); 222 223 select * from atomic_table_14; 224 225 commit; 226 w-w conflict 227 select * from atomic_table_14; 228 SQL parser error: table "atomic_table_14" does not exist 229 drop table if exists atomic_table_15; 230 create table atomic_table_15(c1 int,c2 varchar(25)); 231 begin; 232 insert into atomic_table_15 values (6,"a"),(7,"b"); 233 truncate table atomic_table_15; 234 drop table atomic_table_15; 235 select * from atomic_table_15; 236 c1 c2 237 commit; 238 internal error: w-w conflict: truncate error 239 select * from atomic_table_15; 240 SQL parser error: table "atomic_table_15" does not exist 241 drop table if exists atomic_table_16; 242 create table atomic_table_16(c1 int,c2 varchar(25)); 243 begin; 244 insert into atomic_table_16 values (6,"a"),(7,"b"); 245 drop table atomic_table_16; 246 drop table atomic_table_16; 247 commit; 248 w-w conflict 249 select * from atomic_table_16; 250 SQL parser error: table "atomic_table_16" does not exist 251 drop table if exists atomic_table_17; 252 create table atomic_table_17(c1 int,c2 varchar(25)); 253 begin; 254 insert into atomic_table_17 values (6,"a"),(7,"b"); 255 drop table atomic_table_17; 256 alter table atomic_table_17 add constraint unique key (c1); 257 update atomic_table_17 set c1=8 where c2="b"; 258 commit; 259 w-w conflict 260 select * from atomic_table_17; 261 c1 c2 262 start transaction ; 263 create database tdb; 264 use tdb; 265 invalid database tdb 266 drop database tdb; 267 commit; 268 begin; 269 create sequence seq_01 as int start 30; 270 internal error: Only CREATE of DDL is supported in transactions 271 select nextval('seq_01'); 272 SQL parser error: table "seq_01" does not exist 273 commit; 274 select nextval('seq_01'); 275 SQL parser error: table "seq_01" does not exist 276 drop table if exists atomic_table_11; 277 drop account if exists trans_acc1; 278 create account trans_acc1 admin_name "admin" identified by "111"; 279 begin; 280 drop account trans_acc1; 281 internal error: administrative command is unsupported in transactions 282 select count(*) from mo_catalog.mo_account where account_name='trans_acc1'; 283 count(*) 284 1 285 commit; 286 select count(*) from mo_catalog.mo_account where account_name='trans_acc1'; 287 count(*) 288 1 289 drop table if exists atomic_table_18; 290 create table atomic_table_18(c1 int,c2 varchar(25)); 291 insert into atomic_table_18 values (6,"a"),(7,"b"); 292 set autocommit=0; 293 alter table atomic_table_18 add index key1(c1); 294 alter table atomic_table_18 alter index key1 visible; 295 show create table atomic_table_18; 296 Table Create Table 297 atomic_table_18 CREATE TABLE `atomic_table_18` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 298 rollback; 299 show create table atomic_table_18; 300 Table Create Table 301 atomic_table_18 CREATE TABLE `atomic_table_18` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 302 show index from atomic_table_18; 303 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Index_params Visible Expression 304 truncate table atomic_table_18; 305 drop table atomic_table_18; 306 select * from atomic_table_18; 307 c1 c2 308 commit; 309 internal error: w-w conflict: truncate error 310 select * from atomic_table_18; 311 SQL parser error: table "atomic_table_18" does not exist 312 create table atomic_table_18(c1 int,c2 varchar(25)); 313 insert into atomic_table_18 values (6,"a"),(7,"b"); 314 drop table atomic_table_18; 315 drop table atomic_table_18; 316 no such table transaction_enhance.atomic_table_18 317 select * from atomic_table_18; 318 SQL parser error: table "atomic_table_18" does not exist 319 commit; 320 select * from atomic_table_18; 321 SQL parser error: table "atomic_table_18" does not exist 322 set autocommit=1; 323 drop account if exists trans_acc1;