github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/atomicity.sql (about) 1 drop table if exists test_11; 2 create table test_11 (c int primary key,d int); 3 4 begin; 5 Insert into test_11 values(1,1); 6 Insert into test_11 values(2,2); 7 Rollback; 8 select * from test_11 ; 9 10 begin; 11 Insert into test_11 values(1,1); 12 Insert into test_11 values(2,2); 13 commit; 14 select * from test_11 ; 15 16 drop table if exists test_11; 17 create table test_11 (c int primary key,d int); 18 Insert into test_11 values(1,1); 19 Insert into test_11 values(2,2); 20 Insert into test_11 values(3,1); 21 Insert into test_11 values(4,2); 22 begin; 23 delete from test_11 where c < 3; 24 update test_11 set d = c + 1 where c >= 3; 25 rollback; 26 select * from test_11 ; 27 28 begin; 29 delete from test_11 where c <3; 30 update test_11 set d = c + 1 where c >= 3; 31 commit; 32 select * from test_11 ; 33 34 drop table if exists test_11; 35 begin; 36 create table test_11 (c int primary key,d int); 37 Insert into test_11 values(1,1); 38 Insert into test_11 values(2,2); 39 Insert into test_11 values(3,1); 40 Insert into test_11 values(4,2); 41 rollback; 42 select * from test_11 ; 43 44 begin; 45 create table test_11 (c int primary key,d int); 46 Insert into test_11 values(1,1); 47 Insert into test_11 values(2,2); 48 Insert into test_11 values(3,1); 49 Insert into test_11 values(4,2); 50 delete from test_11 where c <3; 51 update test_11 set d = c + 1 where c >= 3; 52 commit; 53 select * from test_11; 54 55 drop table if exists test_11; 56 create table test_11 (c int primary key,d int); 57 Insert into test_11 values(1,1); 58 Insert into test_11 values(2,2); 59 begin; 60 Insert into test_11 values(3,1); 61 Insert into test_11 values(4,2); 62 rollback; 63 select * from test_11; 64 65 drop table if exists test_11; 66 create table test_11 (c int primary key,d int); 67 Insert into test_11 values(1,1); 68 Insert into test_11 values(2,2); 69 begin; 70 Insert into test_11 values(3,1); 71 Insert into test_11 values(4,2); 72 commit; 73 drop table if exists test_11; 74 select * from test_11 ; 75 76 begin; 77 create table test_12(col1 int primary key,col2 varchar(25)); 78 create unique index id_01 on test_12(col2); 79 select * from test_12; 80 show create table test_12; 81 -- @session:id=1{ 82 show create table test_12; 83 -- @session} 84 rollback ; 85 show create table test_12; 86 select * from test_12; 87 88 start transaction; 89 create table test_12(col1 int primary key,col2 varchar(25)); 90 insert into test_12 values(1,'a'),(2,'b'); 91 -- @session:id=1{ 92 use atomicity; 93 select * from test_12; 94 -- @wait:0:commit 95 create table test_12(col1 int,col2 varchar(25)); 96 insert into test_12 values (90,'tt'); 97 -- @session} 98 select * from test_12; 99 show create table test_12; 100 commit; 101 show create table test_12; 102 select * from test_12; 103 drop table test_12; 104 105 start transaction; 106 create table test_12(col1 int primary key auto_increment,col2 varchar(25)); 107 insert into test_12(col2) values('c'),('d'),('e'); 108 create index id_01 on test_12(col2); 109 select * from test_12; 110 show create table test_12; 111 commit; 112 show create table test_12; 113 select * from test_12; 114 115 create database s_db_1; 116 begin; 117 use s_db_1; 118 create table test_13(col1 int primary key,col2 varchar(25)); 119 rollback; 120 drop database s_db_1; 121 use s_db_1; 122 select * from test_13; 123 124 create database s_db_1; 125 start transaction ; 126 use s_db_1; 127 create table test_13(col1 int primary key,col2 varchar(25)); 128 -- @session:id=1{ 129 130 create database s_db_1; 131 -- @session} 132 commit; 133 drop database s_db_1; 134 135 begin; 136 use atomicity; 137 create table test_14(col1 int primary key,col2 varchar(25), unique key col2(col2)); 138 insert into test_14 values(1,'a'),(2,'b'); 139 create view test_view_1 as select * from test_14; 140 -- @session:id=1{ 141 use atomicity; 142 select * from test_view_1; 143 -- @session} 144 show create table test_14; 145 select * from test_view_1; 146 rollback ; 147 select * from test_14; 148 select * from test_view_1; 149 show create table test_14; 150 151 start transaction ; 152 use atomicity; 153 create temporary table test_15(col1 int,col2 float); 154 insert into test_15 values(1,20.98),(2,30.34); 155 -- @session:id=1{ 156 use atomicity; 157 select * from test_15; 158 -- @session} 159 select * from test_15; 160 rollback ; 161 select * from test_15; 162 163 start transaction ; 164 use atomicity; 165 create external table test_ex_table_1(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 166 select num_col1 ,num_col2 from test_ex_table_1; 167 create table test_16(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)); 168 insert into test_16 select * from test_ex_table_1; 169 rollback ; 170 select num_col1 ,num_col2 from test_ex_table_1; 171 select num_col1 ,num_col2 from test_16; 172 173 begin; 174 use atomicity; 175 create external table test_ex_table_1(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)) infile{"filepath"='$resources/external_table_file/ex_table_number.csv'} fields terminated by ',' enclosed by '\"' lines terminated by '\n'; 176 select num_col1 ,num_col2 from test_ex_table_1; 177 create table test_16(num_col1 tinyint,num_col2 smallint,num_col3 int,num_col4 bigint,num_col5 tinyint unsigned,num_col6 smallint unsigned,num_col7 int unsigned,num_col8 bigint unsigned ,num_col9 float(5,3),num_col10 double,num_col11 decimal(38,19)); 178 insert into test_16 select * from test_ex_table_1; 179 -- @session:id=1{ 180 use atomicity; 181 select num_col1 ,num_col2 from test_ex_table_1; 182 -- @session} 183 commit; 184 select num_col1 ,num_col2 from test_ex_table_1; 185 select num_col1 ,num_col2 from test_16; 186 187 ------------------------------------------------------------ 188 drop table if exists alter01; 189 create table alter01 (col1 int, col2 decimal); 190 show create table alter01; 191 insert into alter01 values(1, 3412.324); 192 insert into alter01 values (-10, 323943.2343); 193 194 begin; 195 alter table alter01 change col1 col1New float; 196 rollback; 197 show create table alter01; 198 select * from alter01; 199 drop table alter01; 200 201 ------------------------------------------------------------ 202 drop table if exists alter01; 203 create table alter01 (col1 int primary key, col2 decimal); 204 show create table alter01; 205 insert into alter01 values(1, 3412.324); 206 insert into alter01 values (-10, 323943.2343); 207 208 begin; 209 alter table alter01 modify col1 float not null; 210 rollback; 211 show create table alter01; 212 select * from alter01; 213 drop table alter01; 214 215 ------------------------------------------------------------ 216 drop table if exists alter01; 217 create table alter01 (col1 int primary key, col2 decimal); 218 show create table alter01; 219 insert into alter01 values(1, 3412.324); 220 insert into alter01 values (-10, 323943.2343); 221 222 begin; 223 alter table alter01 change col1 col1New float not null; 224 rollback; 225 show create table alter01; 226 select * from alter01; 227 drop table alter01; 228 229 -------------------------------------------------------- 230 drop table if exists rename01; 231 create table rename01(c int primary key,d int); 232 begin; 233 insert into rename01 values(1,1); 234 insert into rename01 values(2,2); 235 alter table rename01 rename column c to `euwhbnfew`; 236 rollback; 237 select * from rename01; 238 show create table rename01; 239 240 drop table rename01; 241 242 --------------------------------------------------------- 243 drop table if exists pri01; 244 create table pri01(col1 int ,col2 int); 245 begin; 246 insert into pri01 values(1,1); 247 insert into pri01 values(2,2); 248 alter table pri01 add constraint primary key(col1); 249 show create table pri01; 250 rollback; 251 select * from pri01; 252 show create table pri01; 253 254 drop table pri01; 255 256 --insert duplicate data to null table 257 CREATE TABLE IF NOT EXISTS indup_07( 258 col1 INT primary key, 259 col2 VARCHAR(20) NOT NULL, 260 col3 VARCHAR(30) NOT NULL, 261 col4 BIGINT default 30 262 ); 263 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(24,'66','77',1),(25,'99','88',1),(22,'11','33',1) on duplicate key update col1=col1+col2; 264 select * from indup_07; 265 266 --update out of date range 267 insert into indup_07 values(24,'1','1',100) on duplicate key update col1=2147483649; 268 269 --transaction 270 begin; 271 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(33,'66','77',1) on duplicate key update col1=col1+1,col2='888'; 272 select * from indup_07; 273 rollback ; 274 select * from indup_07; 275 start transaction ; 276 insert into indup_07 values(22,'11','33',1), (23,'22','55',2),(33,'66','77',1) on duplicate key update col1=col1+1,col2='888'; 277 select * from indup_07; 278 commit; 279 select * from indup_07;