github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/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 use transaction_enhance; 23 select * from atomic_table_10; 24 c1 c2 25 3 a 26 4 b 27 5 c 28 select * from atomic_table_10; 29 c1 c2 30 rollback; 31 select * from atomic_table_10; 32 c1 c2 33 3 a 34 4 b 35 5 c 36 drop table if exists atomic_table_10; 37 create table atomic_table_10(c1 int,c2 varchar(25)); 38 insert into atomic_table_10 values (3,"a"),(4,"b"),(5,"c"); 39 begin ; 40 truncate table atomic_table_10; 41 use transaction_enhance; 42 insert into atomic_table_10 values (6,"a"),(7,"b"),(8,"c"); 43 select * from atomic_table_10; 44 c1 c2 45 3 a 46 4 b 47 5 c 48 6 a 49 7 b 50 8 c 51 select * from atomic_table_10; 52 c1 c2 53 commit; 54 select * from atomic_table_10; 55 c1 c2 56 drop table if exists atomic_table_11; 57 create table atomic_table_11(c1 int,c2 varchar(25)); 58 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 59 begin; 60 drop table atomic_table_11; 61 62 use transaction_enhance; 63 insert into atomic_table_11 values (6,"a"); 64 select * from atomic_table_11; 65 SQL parser error: table "atomic_table_11" does not exist 66 commit; 67 select * from atomic_table_11; 68 SQL parser error: table "atomic_table_11" does not exist 69 drop table if exists atomic_table_11; 70 create table atomic_table_11(c1 int,c2 varchar(25)); 71 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 72 begin; 73 drop table atomic_table_11; 74 use transaction_enhance; 75 insert into atomic_table_11 values (6,"a"); 76 select * from atomic_table_11; 77 c1 c2 78 3 a 79 4 b 80 5 c 81 6 a 82 rollback ; 83 select * from atomic_table_11; 84 c1 c2 85 3 a 86 4 b 87 5 c 88 6 a 89 drop table if exists atomic_table_11; 90 create table atomic_table_11(c1 int,c2 varchar(25)); 91 insert into atomic_table_11 values (3,"a"),(4,"b"),(5,"c"); 92 begin; 93 use transaction_enhance; 94 drop table atomic_table_11; 95 drop table atomic_table_11; 96 no such table transaction_enhance.atomic_table_11 97 commit; 98 drop table if exists atomic_table_12; 99 create table atomic_table_12(c1 int,c2 varchar(25)); 100 insert into atomic_table_12 values (3,"a"),(4,"b"),(5,"c"); 101 begin; 102 alter table atomic_table_12 add index key1(c1); 103 alter table atomic_table_12 alter index key1 visible; 104 use transaction_enhance; 105 show create table atomic_table_12; 106 Table Create Table 107 atomic_table_12 CREATE TABLE `atomic_table_12` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 108 commit; 109 show create table atomic_table_12; 110 Table Create Table 111 atomic_table_12 CREATE TABLE `atomic_table_12` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`c1`)\n) 112 show index from atomic_table_12; 113 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression 114 atomic_table_12 1 key1 1 c1 A 0 NULL NULL YES YES NULL 115 use transaction_enhance; 116 drop table if exists atomic_table_12_1; 117 create table atomic_table_12_1(c1 int,c2 varchar(25)); 118 insert into atomic_table_12_1 values (3,"a"),(4,"b"),(5,"c"); 119 begin; 120 alter table atomic_table_12_1 add index key1(c1); 121 alter table atomic_table_12_1 alter index key1 visible; 122 use transaction_enhance; 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 Visible Expression 132 drop table if exists atomic_table_12_2; 133 drop table if exists atomic_table_13; 134 create table atomic_table_12_2(c1 int primary key,c2 varchar(25)); 135 insert into atomic_table_12_2 values (3,"a"),(4,"b"),(5,"c"); 136 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 137 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 138 begin; 139 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_2(c1); 140 use transaction_enhance; 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 internal error: Cannot add or update a child row: a foreign key constraint fails 146 select * from atomic_table_13; 147 c1 c2 148 3 d 149 4 e 150 5 f 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 use transaction_enhance; 167 insert into atomic_table_13 values (8,"h"); 168 select * from atomic_table_13; 169 c1 c2 170 3 d 171 4 e 172 5 f 173 8 h 174 commit; 175 show create table atomic_table_13; 176 Table Create Table 177 atomic_table_13 CREATE TABLE `atomic_table_13` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 178 drop table if exists atomic_table_12_4; 179 drop table if exists atomic_table_13; 180 create table atomic_table_12_4(c1 int primary key,c2 varchar(25)); 181 insert into atomic_table_12_4 values (3,"a"),(4,"b"),(5,"c"); 182 create table atomic_table_13(c1 int primary key,c2 varchar(25)); 183 insert into atomic_table_13 values (3,"d"),(4,"e"),(5,"f"); 184 alter table atomic_table_13 add constraint ffa foreign key f_a(c1) references atomic_table_12_4(c1); 185 begin; 186 alter table atomic_table_13 drop foreign key ffa; 187 use transaction_enhance; 188 insert into atomic_table_13 values (8,"h"); 189 select * from atomic_table_13; 190 c1 c2 191 3 d 192 4 e 193 5 f 194 8 h 195 rollback ; 196 show create table atomic_table_13; 197 Table Create Table 198 atomic_table_13 CREATE TABLE `atomic_table_13` (\n`c1` INT NOT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nPRIMARY KEY (`c1`)\n) 199 drop table if exists atomic_table_12_5; 200 drop table if exists atomic_table_13; 201 create table atomic_table_12_5(c1 int,c2 varchar(25)); 202 insert into atomic_table_12_5 values (3,"a"),(4,"b"),(5,"c"); 203 alter table atomic_table_12_5 add index key1(c1); 204 begin; 205 alter table atomic_table_12_5 drop index key1; 206 use transaction_enhance; 207 show create table atomic_table_12_5; 208 Table Create Table 209 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) 210 select * from atomic_table_12_5; 211 c1 c2 212 3 a 213 4 b 214 5 c 215 commit; 216 show index from atomic_table_12_5; 217 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression 218 drop table if exists atomic_table_14; 219 create table atomic_table_14(c1 int,c2 varchar(25)); 220 insert into atomic_table_14 values (3,"a"),(4,"b"),(5,"c"); 221 start transaction ; 222 alter table atomic_table_14 add index key1(c1); 223 use transaction_enhance; 224 drop table atomic_table_14; 225 insert into atomic_table_14 values (6,"a"),(7,"b"); 226 no such table transaction_enhance.atomic_table_14 227 select * from atomic_table_14; 228 SQL parser error: table "atomic_table_14" does not exist 229 commit; 230 select * from atomic_table_14; 231 SQL parser error: table "atomic_table_14" does not exist 232 drop table if exists atomic_table_15; 233 create table atomic_table_15(c1 int,c2 varchar(25)); 234 begin; 235 insert into atomic_table_15 values (6,"a"),(7,"b"); 236 truncate table atomic_table_15; 237 use transaction_enhance; 238 drop table atomic_table_15; 239 select * from atomic_table_15; 240 SQL parser error: table "atomic_table_15" does not exist 241 commit; 242 select * from atomic_table_15; 243 SQL parser error: table "atomic_table_15" does not exist 244 drop table if exists atomic_table_16; 245 create table atomic_table_16(c1 int,c2 varchar(25)); 246 begin; 247 insert into atomic_table_16 values (6,"a"),(7,"b"); 248 drop table atomic_table_16; 249 use transaction_enhance; 250 drop table atomic_table_16; 251 SQL parser error: table "atomic_table_16" does not exist 252 commit; 253 select * from atomic_table_16; 254 SQL parser error: table "atomic_table_16" does not exist 255 drop table if exists atomic_table_17; 256 create table atomic_table_17(c1 int,c2 varchar(25)); 257 begin; 258 insert into atomic_table_17 values (6,"a"),(7,"b"); 259 drop table atomic_table_17; 260 use transaction_enhance; 261 alter table atomic_table_17 add constraint unique key (c1); 262 SQL parser error: table "atomic_table_17" does not exist 263 update atomic_table_17 set c1=8 where c2="b"; 264 no such table transaction_enhance.atomic_table_17 265 commit; 266 select * from atomic_table_17; 267 SQL parser error: table "atomic_table_17" does not exist 268 start transaction ; 269 create database tdb; 270 use tdb; 271 invalid database tdb 272 drop database tdb; 273 commit; 274 begin; 275 create sequence seq_01 as int start 30; 276 internal error: Only CREATE of DDL is supported in transactions 277 use transaction_enhance; 278 select nextval('seq_01'); 279 SQL parser error: table "seq_01" does not exist 280 commit; 281 select nextval('seq_01'); 282 SQL parser error: table "seq_01" does not exist 283 drop table if exists atomic_table_11; 284 drop account if exists trans_acc1; 285 create account trans_acc1 admin_name "admin" identified by "111"; 286 begin; 287 drop account trans_acc1; 288 internal error: administrative command is unsupported in transactions 289 use transaction_enhance; 290 select count(*) from mo_catalog.mo_account where account_name='trans_acc1'; 291 count(*) 292 1 293 commit; 294 select count(*) from mo_catalog.mo_account where account_name='trans_acc1'; 295 count(*) 296 1 297 drop table if exists atomic_table_18; 298 create table atomic_table_18(c1 int,c2 varchar(25)); 299 insert into atomic_table_18 values (6,"a"),(7,"b"); 300 set autocommit=0; 301 alter table atomic_table_18 add index key1(c1); 302 alter table atomic_table_18 alter index key1 visible; 303 use transaction_enhance; 304 show create table atomic_table_18; 305 Table Create Table 306 atomic_table_18 CREATE TABLE `atomic_table_18` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 307 rollback; 308 show create table atomic_table_18; 309 Table Create Table 310 atomic_table_18 CREATE TABLE `atomic_table_18` (\n`c1` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL\n) 311 show index from atomic_table_18; 312 Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment Visible Expression 313 truncate table atomic_table_18; 314 use transaction_enhance; 315 drop table atomic_table_18; 316 select * from atomic_table_18; 317 SQL parser error: table "atomic_table_18" does not exist 318 commit; 319 select * from atomic_table_18; 320 SQL parser error: table "atomic_table_18" does not exist 321 set autocommit=1; 322 drop table if exists atomic_table_18; 323 create table atomic_table_18(c1 int,c2 varchar(25)); 324 insert into atomic_table_18 values (6,"a"),(7,"b"); 325 set autocommit=0; 326 drop table atomic_table_18; 327 328 use transaction_enhance; 329 drop table atomic_table_18; 330 SQL parser error: table "atomic_table_18" does not exist 331 select * from atomic_table_18; 332 SQL parser error: table "atomic_table_18" does not exist 333 commit; 334 select * from atomic_table_18; 335 SQL parser error: table "atomic_table_18" does not exist 336 set autocommit=1; 337 drop account if exists trans_acc1; 338 339 drop table if exists alter01; 340 drop table if exists alter02; 341 create table alter01(col1 int primary key,col2 varchar(25)); 342 insert into alter01 values (3,"a"),(4,"b"),(5,"c"); 343 begin; 344 alter table alter01 modify col1 float; 345 use transaction_enhance; 346 insert into alter01 values (8,"h"); 347 select * from alter01; 348 col1 col2 349 3.0 a 350 4.0 b 351 5.0 c 352 8.0 h 353 insert into alter01 values (6,"h"); 354 commit; 355 select * from alter01; 356 col1 col2 357 3.0 a 358 4.0 b 359 5.0 c 360 8.0 h 361 6.0 h 362 drop table if exists alter01; 363 drop table if exists alter02; 364 create table alter01(col1 int not null ,col2 varchar(25)); 365 insert into alter01 values (3,"a"),(4,"b"),(5,"c"); 366 begin; 367 alter table alter01 modify col1 float; 368 use transaction_enhance; 369 insert into alter01 values (8,"h"); 370 select * from alter01; 371 col1 col2 372 3.0 a 373 4.0 b 374 5.0 c 375 8.0 h 376 insert into alter01 values (6,"h"); 377 commit; 378 select * from alter01; 379 col1 col2 380 3.0 a 381 4.0 b 382 5.0 c 383 8.0 h 384 6.0 h 385 drop table if exists atomic_table_12_5; 386 create table atomic_table_12_5(c1 int,c2 varchar(25)); 387 insert into atomic_table_12_5 values (3,"a"),(4,"b"),(5,"c"); 388 alter table atomic_table_12_5 add index key1(c1); 389 begin; 390 alter table atomic_table_12_5 change c1 clNew double; 391 use transaction_enhance; 392 insert into atomic_table_12_5 values (8,"h"); 393 show create table atomic_table_12_5; 394 Table Create Table 395 atomic_table_12_5 CREATE TABLE `atomic_table_12_5` (\n`clnew` DOUBLE DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`clnew`)\n) 396 select * from atomic_table_12_5; 397 clnew c2 398 3.0 a 399 4.0 b 400 5.0 c 401 8.0 h 402 show create table atomic_table_12_5; 403 Table Create Table 404 atomic_table_12_5 CREATE TABLE `atomic_table_12_5` (\n`clnew` DOUBLE DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`clnew`)\n) 405 drop table if exists alter01; 406 create table alter01(col1 int primary key,col2 varchar(25)); 407 insert into alter01 values (3,"a"),(4,"b"),(5,"c"); 408 begin; 409 alter table alter01 change col1 col1New float; 410 use transaction_enhance; 411 insert into alter01 values (8,"h"); 412 select * from alter01; 413 col1new col2 414 3.0 a 415 4.0 b 416 5.0 c 417 8.0 h 418 insert into alter01 values (6,"h"); 419 select * from alter01; 420 col1new col2 421 3.0 a 422 4.0 b 423 5.0 c 424 8.0 h 425 6.0 h 426 drop table if exists atomic_table_12_5; 427 create table atomic_table_12_5(c1 int,c2 varchar(25)); 428 insert into atomic_table_12_5 values (3,"a"),(4,"b"),(5,"c"); 429 alter table atomic_table_12_5 add index key1(c1); 430 begin; 431 alter table atomic_table_12_5 rename column c1 to clNew; 432 use transaction_enhance; 433 insert into atomic_table_12_5 values (8,"h"); 434 show create table atomic_table_12_5; 435 Table Create Table 436 atomic_table_12_5 CREATE TABLE `atomic_table_12_5` (\n`clnew` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`clnew`)\n) 437 select * from atomic_table_12_5; 438 clnew c2 439 3 a 440 4 b 441 5 c 442 8 h 443 show create table atomic_table_12_5; 444 Table Create Table 445 atomic_table_12_5 CREATE TABLE `atomic_table_12_5` (\n`clnew` INT DEFAULT NULL,\n`c2` VARCHAR(25) DEFAULT NULL,\nKEY `key1` (`clnew`)\n) 446 drop table if exists alter01; 447 create table alter01(col1 int primary key,col2 varchar(25)); 448 insert into alter01 values (3,"a"),(4,"b"),(5,"c"); 449 begin; 450 alter table alter01 rename column col1 to col1New; 451 use transaction_enhance; 452 insert into alter01 values (8,"h"); 453 select * from alter01; 454 col1new col2 455 3 a 456 4 b 457 5 c 458 8 h 459 insert into alter01 values (6,"h"); 460 select * from alter01; 461 col1new col2 462 3 a 463 4 b 464 5 c 465 8 h 466 6 h 467 drop table if exists alter01; 468 create table alter01(col1 int,col2 varchar(25)); 469 insert into alter01 values (3,"a"),(4,"b"),(5,"c"); 470 begin; 471 alter table alter01 add constraint primary key (col1); 472 use transaction_enhance; 473 insert into alter01 values (5,"h"); 474 Duplicate entry '5' for key 'col1' 475 select * from alter01; 476 col1 col2 477 3 a 478 4 b 479 5 c 480 insert into alter01 values (6,"h"); 481 select * from alter01; 482 col1 col2 483 3 a 484 4 b 485 5 c 486 6 h 487 drop table if exists alter01; 488 create table alter01(col1 int primary key,col2 varchar(25)); 489 insert into alter01 values (3,"a"),(4,"b"),(5,"c"); 490 begin; 491 alter table alter01 drop primary key; 492 use transaction_enhance; 493 insert into alter01 values (5,"h"); 494 select * from alter01; 495 col1 col2 496 3 a 497 4 b 498 5 c 499 5 h 500 insert into alter01 values (6,"h"); 501 select * from alter01; 502 col1 col2 503 3 a 504 4 b 505 5 c 506 5 h 507 6 h