search

midenok / mariadb

MariaDB server is a community developed fork of MySQL server. Started by core members of the original MySQL team, MariaDB actively works with outside developers to deliver the most featureful, stable, and sanely licensed open SQL server in the industry.
GNU General Public License v2.0
0 stars 0 forks source link

MDEV-16226 TRX_ID-based System Versioning refactoring #88

Open midenok opened 3 years ago

midenok commented 3 years ago

Reproduce: compare non-trx vs trx @ concurrency

--source include/have_innodb.inc

set timestamp= 1;
create or replace table t1 (pk int primary key, x int) with system versioning;
insert into t1 values (1, 0);

update t1 set x= x + 1;
set timestamp= 3;
update t1 set x= x + 2;
--connect con1, localhost, root
set timestamp= 2;
update t1 set x= x + 3;
--disconnect con1
--connection default
select *, row_start, row_end from t1 for system_time all order by row_end, pk;

drop tables t1;

set timestamp= 1;
create or replace table t1 (
  pk int primary key,
  x int,
  row_start bigint unsigned as row start invisible,
  row_end bigint unsigned as row end invisible,
  period for system_time (row_start, row_end)) with system versioning engine innodb;
insert into t1 values (1, 0);

update t1 set x= x + 1;
set timestamp= 3;
update t1 set x= x + 2;
--connect con1, localhost, root
set timestamp= 2;
update t1 set x= x + 3;
--disconnect con1
--connection default
select *, row_start, row_end, trt_commit_ts(row_start), trt_commit_ts(row_end) from t1 for system_time all order by row_end, pk;

drop tables t1;

Result

select *, row_start, row_end from t1 for system_time all order by row_end, pk;
pk      x       row_start       row_end
1       1       1970-01-01 03:00:01.000000      1970-01-01 03:00:03.000000
1       6       1970-01-01 03:00:02.000000      2038-01-19 06:14:07.999999
select *, row_start, row_end, trt_commit_ts(row_start), trt_commit_ts(row_end) from t1 for system_time all order by row_end, pk;
pk      x       row_start       row_end trt_commit_ts(row_start)        trt_commit_ts(row_end)
1       0       35      38      1970-01-01 03:00:01.000000      1970-01-01 03:00:01.000000
1       1       38      41      1970-01-01 03:00:01.000000      1970-01-01 03:00:03.000000
1       3       41      44      1970-01-01 03:00:03.000000      1970-01-01 03:00:02.000000
1       6       44      18446744073709551615    1970-01-01 03:00:02.000000      2038-01-19 06:14:07.999999

As we see trx versioning allows all events independent of timestamps (row_start later than row_end). non-trx skips such events:

int vers_insert_history_row(TABLE *table)
{
  ...
  /*
    Skip concurrent events when they happen at same query time (row_start == row_end)
    or when they happen with time jitter (row_start > row_end).
  */
  if (row_start->cmp(row_start->ptr, row_end->ptr) >= 0)
    return 0;
midenok commented 1 year ago

Links

Look how rollback from undo log works. It finds clustered index records to recover, so it knows where are they.

TODO Investigate

midenok commented 1 year ago

Row is located by undo record

--source include/have_innodb.inc
create table t (x int) engine=innodb;
insert t values (1);
start transaction;
update t set x= 2;
rollback;
select * from t;
drop tables t;
#0  row_undo_search_clust_to_pcur (node=0x7f4b4005f608) at ../src/storage/innobase/row/row0undo.cc:167
#1  0x00005636ead177a9 in row_undo_mod_parse_undo_rec (node=0x7f4b4005f608, dict_locked=false) at ../src/storage/innobase/row/row0umod.cc:1139
#2  0x00005636ead16b04 in row_undo_mod (node=0x7f4b4005f608, thr=0x7f4b40058d88) at ../src/storage/innobase/row/row0umod.cc:1191
#3  0x00005636ead100ff in row_undo (node=0x7f4b4005f608, thr=0x7f4b40058d88) at ../src/storage/innobase/row/row0undo.cc:405
#4  0x00005636ead0fdf5 in row_undo_step (thr=0x7f4b40058d88) at ../src/storage/innobase/row/row0undo.cc:452
#5  0x00005636eac4511d in que_thr_step (thr=0x7f4b40058d88) at ../src/storage/innobase/que/que0que.cc:586
#6  0x00005636eac44633 in que_run_threads_low (thr=0x7f4b40058d88) at ../src/storage/innobase/que/que0que.cc:644
#7  0x00005636eac443e4 in que_run_threads (thr=0x7f4b40058d88) at ../src/storage/innobase/que/que0que.cc:664
#8  0x00005636ead7cc78 in trx_t::rollback_low (this=0x7f4b62335b80, savept=0x0) at ../src/storage/innobase/trx/trx0roll.cc:124
#9  0x00005636ead798db in trx_rollback_for_mysql_low (trx=0x7f4b62335b80) at ../src/storage/innobase/trx/trx0roll.cc:195
#10 0x00005636ead79522 in trx_rollback_for_mysql (trx=0x7f4b62335b80) at ../src/storage/innobase/trx/trx0roll.cc:230
#11 0x00005636ea94a47c in innobase_rollback (hton=0x5636eca7e4f8, thd=0x7f4b40002108, rollback_trx=true) at ../src/storage/innobase/handler/ha_innodb.cc:4590
#12 0x00005636e9f357bc in ha_rollback_trans (thd=0x7f4b40002108, all=true) at ../src/sql/handler.cc:2254
#13 0x00005636ea5ffccf in trans_rollback (thd=0x7f4b40002108) at ../src/sql/transaction.cc:372
#14 0x00005636ea3bbdb2 in mysql_execute_command (thd=0x7f4b40002108, is_called_from_prepared_stmt=false) at ../src/sql/sql_parse.cc:5399
#15 0x00005636ea3af28f in mysql_parse (thd=0x7f4b40002108, rawbuf=0x7f4b4002e190 "rollback", length=8, parser_state=0x7f4b515651e8) at ../src/sql/sql_parse.cc:7760
midenok commented 1 year ago 
--source include/have_innodb.inc
create table t (x int) engine=innodb;
insert t values (1), (2), (3);
start transaction;
insert t values (4);
update t set x= x + 10;
insert t values (5);
update t set x= x + 20;
rollback;
select * from t;
drop tables t;

Undo node created

#0  0x00005578d5b273ed in trx_roll_graph_build (trx=0x7f12086beb80) at ../src/storage/innobase/trx/trx0roll.cc:827
#1  0x00005578d5b26dae in trx_rollback_start (trx=0x7f12086beb80, roll_limit=0) at ../src/storage/innobase/trx/trx0roll.cc:860
#2  0x00005578d5b26a0c in trx_rollback_step (thr=0x7f11dc0594c8) at ../src/storage/innobase/trx/trx0roll.cc:920
#3  0x00005578d59efff1 in que_thr_step (thr=0x7f11dc0594c8) at ../src/storage/innobase/que/que0que.cc:594
#4  0x00005578d59ef493 in que_run_threads_low (thr=0x7f11dc0594c8) at ../src/storage/innobase/que/que0que.cc:644
#5  0x00005578d59ef244 in que_run_threads (thr=0x7f11dc0594c8) at ../src/storage/innobase/que/que0que.cc:664
#6  0x00005578d5b27acb in trx_t::rollback_low (this=0x7f12086beb80, savept=0x0) at ../src/storage/innobase/trx/trx0roll.cc:123

Get next undo record

#0  trx_undo_get_prev_rec (block=@0x7f12000ec340: 0x7f12085bf2b0, rec=978, page_no=46, offset=488, shared=true, mtr=0x7f12000ec370) at ../src/storage/innobase/trx/trx0undo.cc:216
#1  0x00005578d5abbf79 in row_undo_rec_get (node=0x7f11dc03bad8) at ../src/storage/innobase/row/row0undo.cc:325
#2  0x00005578d5abaee8 in row_undo (node=0x7f11dc03bad8, thr=0x7f11dc058d88) at ../src/storage/innobase/row/row0undo.cc:390
#3  0x00005578d5abac55 in row_undo_step (thr=0x7f11dc058d88) at ../src/storage/innobase/row/row0undo.cc:452
#4  0x00005578d59eff7d in que_thr_step (thr=0x7f11dc058d88) at ../src/storage/innobase/que/que0que.cc:586
#5  0x00005578d59ef493 in que_run_threads_low (thr=0x7f11dc058d88) at ../src/storage/innobase/que/que0que.cc:644
#6  0x00005578d59ef244 in que_run_threads (thr=0x7f11dc058d88) at ../src/storage/innobase/que/que0que.cc:664
#7  0x00005578d5b27ad8 in trx_t::rollback_low (this=0x7f12086beb80, savept=0x0) at ../src/storage/innobase/trx/trx0roll.cc:124

For rollback trx_undo_get_prev_rec() is used, for purge trx_undo_get_next_rec() is used.

Undo rec is copied

#0  trx_undo_rec_copy (undo_rec=0x7f12086203d2 "\003\365\034\n\022", heap=0x7f11dc0590a8) at ../src/storage/innobase/include/trx0rec.h:55
#1  0x00005578d5abc1fa in row_undo_rec_get (node=0x7f11dc03bad8) at ../src/storage/innobase/row/row0undo.cc:341
341             node->undo_rec = trx_undo_rec_copy(undo_page->page.frame + offset,
342                                                node->heap);

Getting undo_no

#0  trx_undo_rec_get_undo_no (undo_rec=0x7f11dc059128 "") at ../src/storage/innobase/include/trx0rec.h:62
#1  0x00005578d5abc395 in row_undo_rec_get (node=0x7f11dc03bad8) at ../src/storage/innobase/row/row0undo.cc:371
371             trx->undo_no = node->undo_no = trx_undo_rec_get_undo_no(
372                     node->undo_rec);