github.com/matrixorigin/matrixone@v0.7.0/test/distributed/cases/transaction/autocommit_1.sql (about)

     1  -- @suit
     2  
     3  -- @case
     4  -- @desc:Test echo autocommit
     5  -- @label:bvt
     6  
     7  
     8  SELECT @@session.autocommit;
     9  
    10  
    11  SET @@session.autocommit=1;
    12  SELECT @@session.autocommit;
    13  
    14  SET @@session.autocommit= 0;
    15  SELECT @@session.autocommit;
    16  
    17  SET @@session.autocommit=OFF;
    18  SELECT @@session.autocommit;
    19  
    20  SET @@session.autocommit=ON;
    21  SELECT @@session.autocommit;
    22  
    23  --error You have an error in your SQL syntax;
    24  SET @@session.autocommit=foo;
    25  SELECT @@session.autocommit;
    26  commit;
    27  
    28  SET @@session.autocommit=OFF;
    29  SELECT @@session.autocommit;
    30  commit;
    31  
    32  SET @@session.autocommit=ON;
    33  SELECT @@session.autocommit;
    34  commit;
    35  
    36  -- convert to the system variable bool type failed
    37  SET @@session.autocommit=foo;
    38  SELECT @@session.autocommit;
    39  commit;
    40  
    41  
    42  -- @case
    43  -- @desc:Test implicit transaction commit (commit;,rollback;)
    44  -- @label:bvt
    45  
    46  set autocommit=0;
    47  select @@autocommit;
    48  commit;
    49  
    50  -- Test implicit transaction rollback
    51  drop database if exists db;
    52  create database db;
    53  show databases like 'db';
    54  use db;
    55  create table tab1(a int, b int);
    56  create view view_tab1 as select * from tab1;
    57  insert into tab1 values (2000, 3000);
    58  rollback;
    59  select * from tab1;
    60  commit;
    61  
    62  
    63  -- Test implicit transaction commit
    64  drop database if exists db;
    65  create database db;
    66  show databases like 'db';
    67  use db;
    68  create table tab1(a int, b int);
    69  
    70  -- test table tab1 DML and commit; rollback;
    71  insert into tab1 values (2000, 3000);
    72  insert into tab1 values (10, 10);
    73  commit;
    74  select * from tab1;
    75  update tab1 set a=100000 where b=3000;
    76  select * from tab1;
    77  rollback;
    78  select * from tab1;
    79  update tab1 set a=100000 where b=3000;
    80  commit;
    81  select * from tab1;
    82  delete from tab1 where a=10;
    83  rollback;
    84  select * from tab1;
    85  delete from tab1 where a=10;
    86  commit;
    87  select * from tab1;
    88  
    89  
    90  -- test view view_tab1 DML and commit; rollback;(view nonsupport insert/delete/update)
    91  create view view_tab1 as select * from tab1;
    92  select * from view_tab1;
    93  commit;
    94  
    95  insert into view_tab1 values (200, 300);
    96  commit;
    97  
    98  drop database db;
    99  
   100  use autocommit_1;
   101  commit;
   102  
   103  -- test rollback
   104  drop table if exists t1;
   105  create table t1(col1 varchar(255));
   106  insert into t1 values ('helloworld');
   107  rollback;
   108  -- echo error
   109  select * from t1;
   110  commit;
   111  
   112  -- test commit
   113  drop table if exists t2;
   114  create table t2 (a varchar(255));
   115  insert into t2 values ('hello');
   116  commit;
   117  select * from t2;
   118  commit;
   119  drop table t2;
   120  
   121  
   122  -- @case
   123  -- @desc:Test implicit transaction uncommitted, modify AUTOCOMMIT value, mo throw exception
   124  -- @label:bvt
   125  
   126  drop table if exists t3;
   127  create table t3(a int);
   128  insert into t3 values (10),(20),(30);
   129  
   130  -- echo mo throw exception
   131  set @@autocommit=ON;
   132  select @@autocommit;
   133  
   134  -- echo mo throw exception
   135  set @@autocommit=OFF;
   136  select @@autocommit;
   137  
   138  -- echo mo throw exception
   139  set @@autocommit=1;
   140  select @@autocommit;
   141  
   142  -- echo mo throw exception
   143  set @@autocommit=0;
   144  select @@autocommit;
   145  
   146  rollback;
   147  
   148  
   149  drop table if exists tab3;
   150  create table tab3 (a int, b varchar(25));
   151  insert into tab3 values (10, 'aa'),(20, 'bb'),(30, 'cc');
   152  -- echo mo throw exception
   153  set @@autocommit=ON;
   154  select @@autocommit;
   155  
   156  -- echo mo throw exception
   157  set @@autocommit=OFF;
   158  select @@autocommit;
   159  
   160  -- echo mo throw exception
   161  set @@autocommit=1;
   162  select @@autocommit;
   163  
   164  -- echo mo throw exception
   165  set @@autocommit=0;
   166  commit;
   167  
   168  select * from tab3;
   169  update tab3 set a=1000 where b='aa';
   170  select * from tab3;
   171  rollback;
   172  delete from tab3 where b='cc';
   173  select * from tab3;
   174  commit;
   175  select * from tab3;
   176  commit;
   177  
   178  drop table tab3;
   179  
   180  -- test An implicit transaction has uncommitted content.
   181  -- Turning on an explicit transaction forces the previously uncommitted content to be committed
   182  
   183  drop table if exists t4;
   184  create table t4(a varchar(225), b int);
   185  insert into t4 values ('aa', 1000),('bb', 2000);
   186  
   187  begin;
   188  select * from t4;
   189  update t4 set a='xxxx' where b=1000;
   190  select * from t4;
   191  rollback;
   192  
   193  select * from t4;
   194  update t4 set a='xxxx' where b=1000;
   195  select * from t4;
   196  commit;
   197  select * from t4;
   198  
   199  create view view_t4 as select * from t4;
   200  
   201  begin;
   202  select * from view_t4;
   203  delete from t4 where a='bb';
   204  rollback;
   205  
   206  select * from t4;
   207  select * from view_t4;
   208  commit;
   209  
   210  
   211  -- @case
   212  -- @desc:Test explicit transaction commit (commit;rollback;)
   213  -- @label:bvt
   214  
   215  set autocommit=1;
   216  select @@autocommit;
   217  
   218  drop database if exists test_xx;
   219  begin;
   220  create database test_xx;
   221  
   222  -- echo internal error: Uncommitted transaction exists. Please commit or rollback first.
   223  SET @@session.autocommit=1;
   224  SELECT @@session.autocommit;
   225  
   226  -- echo internal error: Uncommitted transaction exists. Please commit or rollback first.
   227  SET @@session.autocommit= 0;
   228  SELECT @@session.autocommit;
   229  
   230  -- echo internal error: Uncommitted transaction exists. Please commit or rollback first.
   231  SET @@session.autocommit=OFF;
   232  SELECT @@session.autocommit;
   233  
   234  -- echo internal error: Uncommitted transaction exists. Please commit or rollback first.
   235  SET @@session.autocommit=ON;
   236  SELECT @@session.autocommit;
   237  commit;
   238  
   239  show databases like 'test_xx';
   240  commit;
   241  drop database test_xx;
   242  
   243  
   244  -- Test explicit transaction rollback;
   245  drop database if exists db;
   246  begin;
   247  create database db;
   248  show databases like 'db';
   249  use db;
   250  
   251  create table table3(a int, b int);
   252  insert into table3 values (2000, 3000);
   253  create view view_table3 as select * from table3;
   254  select * from table3;
   255  select * from view_table3;
   256  rollback;
   257  
   258  -- echo error
   259  select * from table3;
   260  select * from view_table3;
   261  
   262  
   263  -- Test explicit transaction commit;
   264  
   265  drop database if exists db;
   266  begin;
   267  create database db;
   268  show databases like 'db';
   269  use db;
   270  create table table3(a int, b int);
   271  
   272  
   273  -- test table table3 DML and commit; rollback;
   274  insert into table3 values (2000, 3000);
   275  insert into table3 values (10, 10);
   276  commit;
   277  select * from table3;
   278  
   279  begin;
   280  update table3 set a=100000 where b=3000;
   281  select * from table3;
   282  rollback;
   283  select * from table3;
   284  
   285  begin;
   286  update table3 set a=100000 where b=3000;
   287  commit;
   288  select * from table3;
   289  
   290  begin;
   291  delete from table3 where a=10;
   292  rollback;
   293  select * from table3;
   294  
   295  begin;
   296  delete from table3 where a=10;
   297  commit;
   298  select * from table3;
   299  
   300  -- Test start transaction;rollback;commit;
   301  
   302  drop table if exists t3;
   303  start transaction;
   304  create table t3 (b varchar(255));
   305  insert into t3 values ('helloworld');
   306  rollback ;
   307  select * from t3;
   308  
   309  drop table if exists t4;
   310  start transaction;
   311  create table t4 (a int);
   312  insert into t4 values (10),(20);
   313  commit;
   314  select * from t4;
   315  drop table t4;
   316  
   317  
   318  -- Test explicit transactions are nested, Uncommitted content is forced to be submitted
   319  drop table if exists t5;
   320  start transaction;
   321  
   322  create table t5(a int);
   323  insert into t5 values(10),(20),(30);
   324  -- execute error
   325  drop table t5;
   326  
   327  start transaction;
   328  insert into t5 values(100),(2000),(3000);
   329  -- execute error
   330  set @autocommit=0;
   331  begin;
   332  select * from t5;
   333  
   334  insert into t5 values(1),(2),(3);
   335  rollback;
   336  
   337  select * from t5;
   338  
   339  begin;
   340  select * from t5;
   341  insert into t5 values(100),(2000),(3000);
   342  delete from t5;
   343  
   344  begin;
   345  select * from t5;
   346  rollback;
   347  
   348  select * from t5;
   349  
   350  drop table t5
   351  
   352  
   353  
   354  -- Test explicit transactions  include set command;
   355      start transaction;
   356  -- execute error
   357  set @@a=0;
   358  rollback;
   359  
   360  set @@b=0;
   361  -- execute error
   362  commit;
   363  -- execute error
   364  select @@b;
   365  
   366  
   367  -- Test AUTOCOMMIT=1 Each DML statement is a separate transaction
   368  
   369  drop database if exists db;
   370  create database db;
   371  show databases like 'db';
   372  use db;
   373  create table t6(a int, b int);
   374  
   375  -- test table t6 DML and commit; rollback;
   376  insert into t6 values (2000, 3000);
   377  insert into t6 values (10, 10);
   378  
   379  select * from t6;
   380  update t6 set a=100000 where b=3000;
   381  select * from t6;
   382  delete from t6 where a=10;
   383  
   384  select * from t6;
   385  
   386  
   387  -- test view view_t6 DML and commit; rollback;
   388  create view view_t6 as select * from t6;
   389  select * from view_t6;
   390  
   391  insert into view_t6 values (200, 300);
   392  insert into view_t6 values (10, 10);
   393  
   394  
   395  select * from view_t6;
   396  update view_t6 set a=100000 where b=3000;
   397  select * from view_t6;
   398  delete from view_t6 where a=10;
   399  select * from view_t6;
   400  
   401  drop database db;
   402  
   403  use autocommit_1;
   404  
   405  
   406  
   407  -- @case
   408  -- @desc:Test Nested explicit transactions within implicit transactions
   409  -- @label:bvt
   410  
   411  set @@autocommit=0;
   412  select @@autocommit;
   413  
   414  create table t7(a int);
   415  insert into t7 values (500);
   416  commit;
   417  
   418  
   419  begin;
   420  insert into t7 values (1000);
   421  commit;
   422  insert into t7 values (2000);
   423  rollback;
   424  select * from t7;
   425  drop table t7;
   426  commit;
   427  drop table t7;
   428  
   429  create table t8(a int);
   430  insert into t8 values (500);
   431  rollback;
   432  
   433  
   434  begin;
   435  insert into t8 values (1000);
   436  create table t9 (a char(25));
   437  commit;
   438  
   439  insert into t9 values ('hello');
   440  rollback;
   441  select * from t9;
   442  commit;
   443  drop table t9;
   444  
   445  set @@autocommit=on;
   446