摘要:

monetdb的事務(wù)隔離級(jí)別只有RR, 和mysql/innodb的具有RR和RC兩個(gè)隔離級(jí)別不同.

本文分析monetdb的RR隔離級(jí)別的實(shí)現(xiàn)方式, 以及分析這種隔離級(jí)別方式如何導(dǎo)致只有RR隔離級(jí)別.

測(cè)試流程:

測(cè)試方式:

1. 分別開(kāi)兩個(gè)mclient終端, 連接同一個(gè)mserver實(shí)例
2. 兩個(gè)client終端分別叫做客戶端A和客戶端B
3. 客戶端A開(kāi)啟auto commit, 執(zhí)行的sql為插入一條數(shù)據(jù)
   1. insert into t1 values(3);
4. 客戶端B開(kāi)啟start transaction, 之心的sql為統(tǒng)計(jì)數(shù)據(jù)的數(shù)量
   1. start transaction;
   2. select count(1) from t1;
5. 客戶端A插入一條數(shù)據(jù)后, 在客戶端B執(zhí)行select count(1) 查看數(shù)據(jù)的數(shù)量

測(cè)試結(jié)果:

1. 無(wú)論客戶端A插入多少條數(shù)據(jù)
2. 客戶端B執(zhí)行了start transaction后，通過(guò)select count(1)查詢出的數(shù)據(jù)的數(shù)量永遠(yuǎn)不變
3. 也就是說(shuō)客戶端B只能讀到start transaction執(zhí)行之前的數(shù)據(jù)
4. 客戶端B的隔離級(jí)別為可重復(fù)讀，read repeated, 即RR

RR的隔離級(jí)別的原因定位方式:

1. 客戶端A是auto commit的方式, 每插入一條數(shù)據(jù)就會(huì)自動(dòng)提交事務(wù)
2. 對(duì)客戶端B而言, 數(shù)據(jù)的可見(jiàn)性來(lái)說(shuō)，A插入的數(shù)據(jù), 都是已經(jīng)提交的?
3. 但是B看不到A插入的數(shù)據(jù), 那么對(duì)來(lái)說(shuō), 必然是存在某種規(guī)則對(duì)數(shù)據(jù)進(jìn)行了過(guò)濾
4. 要排查的話, 應(yīng)該從B的角度, 追查為什么無(wú)法拿到A插入的數(shù)據(jù)

排查過(guò)程:

一. 通過(guò)日志分析select count的執(zhí)行的核心函數(shù):

日志:

2023-11-08 08:42:24    M_DEBUG ALGO     client3              monetdb5/mal/mal_interpreter.c:716             runMALsequence       calling querylog.define
2023-11-08 08:42:24    M_DEBUG ALGO     client3              monetdb5/mal/mal_interpreter.c:687             runMALsequence       calling sql.count
2023-11-08 08:45:56    M_DEBUG ALGO     client3              monetdb5/mal/mal_interpreter.c:687             runMALsequence       calling sql.resultSet
2023-11-08 08:45:56    M_DEBUG ALGO     client3              gdk/gdk_bat.c:292                              COLnew2              -> tmp_647#0@0[lng]TSRN
2023-11-08 08:45:56    M_DEBUG ALGO     client3              gdk/gdk_bat.c:1039                             BUNappendmulti       tmp_647#0@0[lng]TSRN appending 1 values
2023-11-08 08:45:56    M_DEBUG ALGO     client3              gdk/gdk_bat.c:292                              COLnew2              -> tmp_725#0@0[oid]TSRN
2023-11-08 08:45:56    M_DEBUG ALGO     client3              gdk/gdk_batop.c:2846                           BATconstant          -> tmp_725#1@0[oid]TSRKN 23usec

核心函數(shù):

calling sql.count

二. 分析 sql.count的執(zhí)行細(xì)節(jié):

調(diào)用堆棧:

#0  segs_end (segs=0x1952b230, tr=0x7f9fb0104450, table=0x1952aa60) at /root/work/monetdb-dev/trunk/monetdb/sql/storage/bat/bat_storage.c:457
#1  0x00007fa055d07ad1 in count_col (tr=0x7f9fb0104450, c=0x1952b2d0, access=10) at /root/work/monetdb-dev/trunk/monetdb/sql/storage/bat/bat_storage.c:773
#2  0x00007fa055c85186 in SQLbasecount (cntxt=0x13b4580, mb=0x7f9fb0135570, stk=0x7f9fb013c3e0, pci=0x7f9fb0144400) at /root/work/monetdb-dev/trunk/monetdb/sql/backends/monet5/sql_rank.c:1289
#3  0x00007fa069395007 in runMALsequence (cntxt=0x13b4580, mb=0x7f9fb0135570, startpc=1, stoppc=0, stk=0x7f9fb013c3e0, env=0x0, pcicaller=0x0)at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_interpreter.c:688
#4  0x00007fa06939377e in runMAL (cntxt=0x13b4580, mb=0x7f9fb0135570, mbcaller=0x0, env=0x0) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_interpreter.c:357
#5  0x00007fa055bb85eb in SQLrun (c=0x13b4580, m=0x7f9fb01167e0) at /root/work/monetdb-dev/trunk/monetdb/sql/backends/monet5/sql_execute.c:259
#6  0x00007fa055bb9ee7 in SQLengineIntern (c=0x13b4580, be=0x7f9fb0136060) at /root/work/monetdb-dev/trunk/monetdb/sql/backends/monet5/sql_execute.c:709
#7  0x00007fa055bb74b5 in SQLengine (c=0x13b4580) at /root/work/monetdb-dev/trunk/monetdb/sql/backends/monet5/sql_scenario.c:1358
#8  0x00007fa0693b4862 in runPhase (c=0x13b4580, phase=4) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_scenario.c:453
#9  0x00007fa0693b49cc in runScenarioBody (c=0x13b4580, once=0) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_scenario.c:479
#10 0x00007fa0693b4bd8 in runScenario (c=0x13b4580, once=0) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_scenario.c:510
#11 0x00007fa0693b6fea in MSserveClient (c=0x13b4580) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_session.c:589
#12 0x00007fa0693b6863 in MSscheduleClient (command=0x7f9fb0000b70 '\333' <repeats 199 times>, <incomplete sequence \333>..., challenge=0x7f9ff7bfcce3 "gWPRtbcO", fin=0x7f9fb0002b90, fout=0x7f9fc4009630, protocol=PROTOCOL_9, blocksize=8190) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/mal/mal_session.c:445
#13 0x00007fa06947c1d4 in doChallenge (data=0x7f9fc4006790) at /root/work/monetdb-dev/trunk/monetdb/monetdb5/modules/mal/mal_mapi.c:222
#14 0x00007fa068d2e729 in THRstarter (a=0x7f9fc400bb20) at /root/work/monetdb-dev/trunk/monetdb/gdk/gdk_utils.c:1668
#15 0x00007fa068dabb23 in thread_starter (arg=0x7f9fc400bb90) at /root/work/monetdb-dev/trunk/monetdb/gdk/gdk_system.c:862
#16 0x00007fa0682ec1ca in start_thread () from /lib64/libpthread.so.0
#17 0x00007fa067f58e73 in clone () from /lib64/libc.so.6

核心函數(shù):

segs_end

static size_t
segs_end( segments *segs, sql_trans *tr, sql_table *table)
{size_t cnt = 0;lock_table(tr->store, table->base.id);segment *s = segs->h, *l = NULL;if (segs->t && SEG_IS_VALID(segs->t, tr))l = s = segs->t;for(;s; s = s->next) {if (SEG_IS_VALID(s, tr))l = s;}if (l)cnt = l->end;unlock_table(tr->store, table->base.id);return cnt;
}

SEG_IS_VALID

/* A segment is part of the current transaction is someway or is deleted by some other transaction but use to be valid */
#define SEG_IS_VALID(seg, tr) \((!seg->deleted && VALID_4_READ(seg->ts, tr)) || \(seg->deleted && OLD_VALID_4_READ(seg->ts, seg->oldts, tr)))

VALID_4_READ

/* valid* !deleted && VALID_4_READ(TS, tr)				existing or newly created segment*  deleted && TS > tr->ts && OLDTS < tr->ts		deleted after current transaction*/#define VALID_4_READ(TS,tr) \(TS == tr->tid || (tr->parent && tr_version_of_parent(tr, TS)) || TS < tr->ts)

核心數(shù)據(jù):

拿到的segment:?

(gdb) p l[0]
$47 = {start = 0, end = 25, deleted = false, ts = 1, oldts = 0, next = 0x7f9fbc027ed0, prev = 0x0
}

未拿到的segment:

(gdb) p segs->t[0]
$46 = {start = 25, end = 26, deleted = false, ts = 1875, oldts = 0, next = 0x0, prev = 0x0
}

三. 分析過(guò)濾segment的代碼邏輯:

核心處理:

/* valid* !deleted && VALID_4_READ(TS, tr)				existing or newly created segment*  deleted && TS > tr->ts && OLDTS < tr->ts		deleted after current transaction*/#define VALID_4_READ(TS,tr) \(TS == tr->tid || (tr->parent && tr_version_of_parent(tr, TS)) || TS < tr->ts)

核心數(shù)據(jù)結(jié)構(gòu):

sql_trans?

typedef struct sql_trans {char *name;ulng ts;			/* transaction start timestamp */ulng tid;			/* transaction id */sql_store store;	/* keep link into the global store */MT_Lock lock;		/* lock protecting concurrent writes to the changes list */list *changes;		/* list of changes */list *dropped;  	/* protection against recursive cascade action*/list *predicates;	/* list of read predicates logged during update transactions */list *dependencies;	/* list of dependencies created (list of sqlids from the objects) */list *depchanges;	/* list of dependencies changed (it would be tested for conflicts at the end of the transaction) */lng logchanges;		/* count number of changes to be applied to the wal */int active;			/* is active transaction */int status;			/* status of the last query */sql_catalog *cat;sql_schema *tmp;	/* each session has its own tmp schema */changeset localtmps;sql_allocator *sa;	/* transaction allocator */struct sql_trans *parent;	/* multilevel transaction support */
} sql_trans;

segment?

typedef struct segment {BUN start;BUN end;bool deleted;	/* we need to keep a dense segment set, 0 - end of last segemnt,some segments maybe deleted */ulng ts;		/* timestamp on this segment, ie tid of some active transaction or commit time of append/delete orrollback time, ie ready for reuse */ulng oldts;		/* keep previous ts, for rollbacks */struct segment *next;	/* usualy one should be enough */struct segment *prev;	/* used in destruction list */
} segment;

核心判斷邏輯:

1. (TS == tr->tid)

1. TS 是?seg->ts
2. tid是transaction id, 也就是事務(wù)id? /* transaction id */
3. tr->ts 是?/* transaction start timestamp */
4. seg->ts 和 tr->tid 進(jìn)行比較，其實(shí)使用了一些hack技巧和成員復(fù)用

2. (TS < tr->ts)

1. 重點(diǎn)是這個(gè)邏輯
2. tr->ts 是這個(gè)事務(wù)開(kāi)始的時(shí)間
3. TS 是 seg->ts, 是這個(gè)數(shù)據(jù)段被提交的時(shí)間
4. 所以這個(gè)判斷的邏輯, 就是tr這個(gè)事務(wù), 只能看到這個(gè)開(kāi)始之前被記錄的數(shù)據(jù)段里的數(shù)據(jù)
5. 也就說(shuō), 從tr這個(gè)事務(wù)開(kāi)始后, 再被提交的事務(wù), 都無(wú)法被看到
6. 從調(diào)用方的角度, 就是?read repeated 的隔離級(jí)別

如何擴(kuò)充新的隔離級(jí)別呢？

1. 注意我說(shuō)的是擴(kuò)充新的隔離級(jí)別，而不是修改已有的隔離級(jí)別
2. RR的隔離級(jí)別需要被保留
3. 使用mysql的隔離級(jí)別的參數(shù)來(lái)控制
4. 新增RC的隔離級(jí)別
5. RC的隔離級(jí)別, 可以看到已經(jīng)提交的事務(wù)的數(shù)據(jù), 而不僅僅是?TS < tr->ts 的