github.com/matrixorigin/matrixone@v1.2.0/test/distributed/cases/pessimistic_transaction/isolation_1.sql (about)

     1  drop table if exists test_11;
     2  create table test_11 (c int primary key,d int);
     3  Insert into test_11 values(1,1);
     4  Insert into test_11 values(2,2);
     5  begin;
     6  Insert into test_11 values(3,1);
     7  Insert into test_11 values(4,2);
     8  select * from test_11;
     9  
    10  -- @session:id=1{
    11  use isolation_1;
    12  begin;
    13  select * from test_11;
    14  -- @session}
    15  commit;
    16  
    17  select * from test_11;
    18  -- @session:id=1{
    19  commit;
    20  select * from test_11;
    21  -- @session}
    22  
    23  -- -------------------------------------------------------
    24  drop table if exists test_11;
    25  create table test_11 (c int primary key,d int);
    26  Insert into test_11 values(1,1);
    27  Insert into test_11 values(2,2);
    28  begin;
    29  Insert into test_11 values(3,1);
    30  Insert into test_11 values(4,2);
    31  select * from test_11;
    32  
    33  -- @session:id=1{
    34  begin;
    35  select * from test_11;
    36  -- @session}
    37  
    38  delete from test_11 where c =1;
    39  select * from test_11;
    40  -- @session:id=1{
    41  select * from test_11;
    42  -- @session}
    43  
    44  update test_11 set d = c +1 where c > 2;
    45  select * from test_11;
    46  -- @session:id=1{
    47  select * from test_11;
    48  -- @session}
    49  
    50  commit;
    51  select * from test_11;
    52  -- @session:id=1{
    53  commit;
    54  select * from test_11;
    55  -- @session}
    56  
    57  -- -------------------------------------------------------
    58  drop table if exists test_11;
    59  begin;
    60  create table test_11 (c int primary key,d int);
    61  Insert into test_11 values(1,1);
    62  Insert into test_11 values(2,2);
    63  select * from test_11;
    64  -- @session:id=1{
    65  begin;
    66  select * from test_11;
    67  -- @session}
    68  
    69  commit;
    70  select * from test_11;
    71  
    72  -- @session:id=1{
    73  commit;
    74  select * from test_11;
    75  -- @session}
    76  
    77  -- -------------------------------------------------------
    78  drop table if exists test_11;
    79  create table test_11 (c int primary key,d int);
    80  Insert into test_11 values(1,1);
    81  Insert into test_11 values(2,2);
    82  begin;
    83  Insert into test_11 values(3,1);
    84  Insert into test_11 values(4,2);
    85  select * from test_11;
    86  
    87  -- @session:id=1{
    88  begin;
    89  select * from test_11;
    90  Insert into test_11 values(5,4);
    91  select * from test_11;
    92  -- @session}
    93  
    94  select * from test_11;
    95  Insert into test_11 values(50,50);
    96  
    97  -- @session:id=1{
    98  Insert into test_11 values(51,50);
    99  select * from test_11;
   100  -- @session}
   101  
   102  select * from test_11;
   103  commit;
   104  
   105  -- @session:id=1{
   106  commit;
   107  select * from test_11;
   108  -- @session}
   109  
   110  select * from test_11;
   111  
   112  -- -------------------------------------------------------
   113  drop table if exists test_11;
   114  create table test_11 (c int primary key,d int);
   115  Insert into test_11 values(1,1);
   116  Insert into test_11 values(2,2);
   117  begin;
   118  Insert into test_11 values(3,1);
   119  Insert into test_11 values(4,2);
   120  select * from test_11;
   121  
   122  -- @session:id=1{
   123  begin;
   124  select * from test_11;
   125  Insert into test_11 values(5,4);
   126  select * from test_11;
   127  -- @session}
   128  
   129  select * from test_11;
   130  Insert into test_11 values(50,50);
   131  
   132  -- @session:id=1{
   133  -- @wait:0:commit
   134  Insert into test_11 values(50,50);
   135  select * from test_11;
   136  -- @session}
   137  
   138  select * from test_11;
   139  commit;
   140  
   141  -- @session:id=1{
   142  commit;
   143  select * from test_11;
   144  -- @session}
   145  
   146  select * from test_11;
   147  
   148  -- -------------------------------------------------------
   149  drop table if exists test_11;
   150  create table test_11 (c int primary key,d int);
   151  Insert into test_11 values(1,1);
   152  Insert into test_11 values(2,2);
   153  begin;
   154  select * from test_11;
   155  Insert into test_11 values(50,50);
   156  select * from test_11;
   157  -- @session:id=1{
   158  begin;
   159  select * from test_11;
   160  -- @wait:0:commit
   161  Insert into test_11 values(50,50);
   162  select * from test_11;
   163  -- @session}
   164  select * from test_11;
   165  
   166  -- @session:id=1{
   167  delete from test_11 where c = 50;
   168  select * from test_11;
   169  -- @session}
   170  select * from test_11;
   171  
   172  commit;
   173  -- @session:id=1{
   174  commit;
   175  select * from test_11;
   176  -- @session}
   177  select * from test_11;
   178  
   179  -- -------------------------------------------------------
   180  -- @bvt:issue#10585
   181  drop table if exists test_11;
   182  create table test_11 (c int primary key,d int);
   183  Insert into test_11 values(1,1);
   184  Insert into test_11 values(2,2);
   185  begin;
   186  select * from test_11;
   187  Insert into test_11 values(50,50);
   188  select * from test_11;
   189  -- @session:id=1{
   190  begin;
   191  select * from test_11;
   192  -- @wait:0:commit
   193  Insert into test_11 values(50,50);
   194  select * from test_11;
   195  -- @session}
   196  select * from test_11;
   197  
   198  -- @session:id=1{
   199  select * from test_11;
   200  update test_11 set c = 100 where d = 50;
   201  select * from test_11;
   202  -- @session}
   203  select * from test_11;
   204  Insert into test_11 values(100,50);
   205  
   206  commit;
   207  -- @session:id=1{
   208  commit;
   209  select * from test_11;
   210  -- @session}
   211  select * from test_11;
   212  -- @bvt:issue
   213  -- -------------------------------------------------------
   214  -- @bvt:issue#10585
   215  drop table if exists test_11;
   216  create table test_11 (c int primary key,d int);
   217  Insert into test_11 values(1,1);
   218  Insert into test_11 values(2,2);
   219  begin;
   220  select * from test_11;
   221  Insert into test_11 values(50,50);
   222  select * from test_11;
   223  -- @session:id=1{
   224  begin;
   225  select * from test_11;
   226  -- @wait:0:commit
   227  Insert into test_11 values(50,50);
   228  select * from test_11;
   229  -- @session}
   230  select * from test_11;
   231  
   232  -- @session:id=1{
   233  select * from test_11;
   234  update test_11 set c = 100 where d = 50;
   235  select * from test_11;
   236  -- @session}
   237  select * from test_11;
   238  update test_11 set c = 101 where c = 50;
   239  
   240  commit;
   241  -- @session:id=1{
   242  commit;
   243  select * from test_11;
   244  -- @session}
   245  select * from test_11;
   246  -- @bvt:issue
   247  -- -------------------------------------------------------
   248  -- @bvt:issue#10585
   249  drop table if exists test_11;
   250  create table test_11 (c int primary key,d int);
   251  Insert into test_11 values(1,1);
   252  Insert into test_11 values(2,2);
   253  begin;
   254  select * from test_11;
   255  Insert into test_11 values(50,50);
   256  select * from test_11;
   257  -- @session:id=1{
   258  begin;
   259  select * from test_11;
   260  -- @wait:0:commit
   261  Insert into test_11 values(50,50);
   262  select * from test_11;
   263  -- @session}
   264  select * from test_11;
   265  
   266  -- @session:id=1{
   267  select * from test_11;
   268  update test_11 set c = 100 where d = 50;
   269  select * from test_11;
   270  -- @session}
   271  select * from test_11;
   272  update test_11 set c = 100 where d = 50;
   273  
   274  commit;
   275  -- @session:id=1{
   276  commit;
   277  select * from test_11;
   278  -- @session}
   279  select * from test_11;
   280  -- @bvt:issue
   281  
   282  -- -------------------------------------------------------
   283  drop table if exists test_11;
   284  begin;
   285  create table test_11 (c int primary key,d int);
   286  Insert into test_11 values(1,1);
   287  Insert into test_11 values(2,2);
   288  select * from test_11;
   289  -- @session:id=1{
   290  begin;
   291  select * from test_11;
   292  -- @session}
   293  
   294  commit;
   295  select * from test_11;
   296  -- @session:id=1{
   297  commit;
   298  select * from test_11;
   299  -- @session}
   300  
   301  -- -------------------------------------------------------
   302  drop table if exists test_11;
   303  create table test_11 (c int primary key,d int);
   304  Insert into test_11 values(1,1);
   305  Insert into test_11 values(2,2);
   306  select * from test_11;
   307  -- @session:id=1{
   308  begin;
   309  select * from test_11;
   310  -- @session}
   311  
   312  begin;
   313  drop table test_11;
   314  select * from test_11;
   315  -- @session:id=1{
   316  select * from test_11;
   317  -- @session}
   318  
   319  commit;
   320  select * from test_11;
   321  -- @session:id=1{
   322  commit;
   323  select * from test_11;
   324  -- @session}
   325  -- -------------------------------------------------------
   326  drop table if exists test_11;
   327  begin;
   328  create table test_11 (c int primary key,d int);
   329  Insert into test_11 values(1,1);
   330  Insert into test_11 values(2,2);
   331  select * from test_11;
   332  -- @session:id=1{
   333  begin;
   334  select * from test_11;
   335  -- @session}
   336  
   337  -- drop table test_11;
   338  select * from test_11;
   339  -- @session:id=1{
   340  select * from test_11;
   341  -- @session}
   342  commit;
   343  
   344  select * from test_11;
   345  -- @session:id=1{
   346  commit;
   347  select * from test_11;
   348  -- @session}
   349  
   350  drop table if exists test_11;
   351  
   352  
   353  
   354