Redian新闻
>
How to prevent primary key collision for replication
avatar
l*b
2
Hi, friends,
I will make some table replications between two DBs with same structure. The
main problem so far is how to prevent the PK conflict when replicating as
the current PKs are auto increased by sequence. My first thinking is the
have composite PK keys: instance id + auto increased, e.g. 01 and 1234 as a
PK pair, 01 is instance id and 1234 is current increased PK. I heard there
are some drawback in composite PK key, e.g. in ORM, etc. However, I am not
using ORM so far. Is my solution ok?
avatar
m*n
3
cong!

【在 d********8 的大作中提到】
: 恩
avatar
i*a
4
Ms sql server? Replication takes care of pk automatically. In fact it
requires a pk for repl to work
avatar
d*8
5
co-cong

【在 m******n 的大作中提到】
: cong!
avatar
v*r
6
I guess your concerns is that your target db is inserted not only by the
replication process, so the the target db sequence might clash with the
source db sequence.
The workaround is simple, modify your target db sequence to start with a
much higher value to ensure the source/target db sequences are using
different range.
Your thought will work but the problem is you are making your application un
-scalable by "instance id + auto increased".
avatar
h*t
7
你咋把我征集版标的帖子也删了?

【在 d********8 的大作中提到】
: co-cong
avatar
l*b
8
Thank you, vbitter.
Could you advise me some examples of making your application unscalable by "
instance id + auto increased"?

un

【在 v*****r 的大作中提到】
: I guess your concerns is that your target db is inserted not only by the
: replication process, so the the target db sequence might clash with the
: source db sequence.
: The workaround is simple, modify your target db sequence to start with a
: much higher value to ensure the source/target db sequences are using
: different range.
: Your thought will work but the problem is you are making your application un
: -scalable by "instance id + auto increased".

avatar
d*8
9
抱歉,不合适

【在 h*****t 的大作中提到】
: 你咋把我征集版标的帖子也删了?
avatar
v*r
10
Luckybb,
On second thought, I was wrong. Sequence helps you Apps scale better mainly
because it can be cached in memory. So as long as your sequence is defined
with CACHE option and you are inserting right after append "instance_id"
with "sequence number" on preparing PK value, there will be a little bit
more overhead in preparing PK value, but it won't affect your application's
scalability.

"

【在 l*****b 的大作中提到】
: Thank you, vbitter.
: Could you advise me some examples of making your application unscalable by "
: instance id + auto increased"?
:
: un

avatar
h*t
11
为啥不合适?

【在 d********8 的大作中提到】
: 抱歉,不合适
avatar
l*b
12
thanks for quick and deep answer. Besides this generated PK, I am also
thinking one more solution to have composite primary key. I will have two
columns - instance_id and big int sequence number, then define them as a
composite primary key.

mainly
s

【在 v*****r 的大作中提到】
: Luckybb,
: On second thought, I was wrong. Sequence helps you Apps scale better mainly
: because it can be cached in memory. So as long as your sequence is defined
: with CACHE option and you are inserting right after append "instance_id"
: with "sequence number" on preparing PK value, there will be a little bit
: more overhead in preparing PK value, but it won't affect your application's
: scalability.
:
: "

avatar
d*8
13
为啥合适?

【在 h*****t 的大作中提到】
: 为啥不合适?
avatar
v*r
14
Not a good idea. Having a separate instance_id column, you are adding
something that is totally irrelevant and redundant to your table entity.
相关阅读
logo
联系我们隐私协议©2024 redian.news
Redian新闻
Redian.news刊载任何文章,不代表同意其说法或描述,仅为提供更多信息,也不构成任何建议。文章信息的合法性及真实性由其作者负责,与Redian.news及其运营公司无关。欢迎投稿,如发现稿件侵权,或作者不愿在本网发表文章,请版权拥有者通知本网处理。