[mysql-dde] Re: The mutual conflict problem
- From: Fabricio Mota <fabricio.mota@xxxxxxxxx>
- To: mysql-dde@xxxxxxxxxxxxx
- Date: Mon, 12 Dec 2005 20:32:22 -0300
That's like below:
Suppose about your model, with a master server managing unique keys, and two
servers, *Sa *and *Sb*.
- *Sa *tries to insert a value *k*, and requires it to Master.
- Master keeps the requesting.
- A few miliseconds after, *Sb *tries to insert a value *k*, too. Quite as,
It requires to Master.
- Master denies *Sb*, because *Sa *has already a intention (firstly) to
insert *k*. So, the trivial is that *Sb* raises an exception "*Key violation
with value k".*
- Here, *Sa *has its transaction still opened. But, due another unrelated
problem, such as a Foreign Key violated in a next command, *Sa *fails its
transaction.
This, *Sa *raises an exception "*Foreign Key violated with value f" *and
rollback*.*
Remember that some steps before *Sb *failed with message "*Key violation
with value k" *and rolled back, too.
Result: *Sb* failed because *there is already a value k inside the database*,
but if user tries to query it, it does not exist!!!!
What I propose, based on:
1) If there is a mutual conflict for the same unique value, one of the
concurrent transactions must win, and the other will fail.
2) Both the transactions agree about the winner.
3) If the winner gives up to use the disputed resource, caused by any
subject, it *must *return this resource back to the loser. If not, there is
no coherence neither justice in the protocol.
Is that, when a transaction is presumed to lose a battle, it must wait until
the end of war to ensure that the trophy does or does not belong to him. :)
In other words, the loser must be blocked until the "winner" transaction
commit or rollback. If commit, the loser announce his defeat. If not, he
perform his operation as well as if nothing bad has occurred.
FM
2005/12/12, Peter B. Volk <PeterB.Volk@xxxxxxx>:
>
> I didn't quite get that one.
>
>
> ----- Original Message -----
> From: "Fabricio Mota" <fabricio.oliveira@xxxxxxxxxxxxxxxx>
> To: "Fabricio Mota" <fabricio.oliveira@xxxxxxxxxxxxxxxx>;
> <mysql-dde@xxxxxxxxxxxxx>
> Sent: Monday, December 12, 2005 6:00 PM
> Subject: [mysql-dde] Re: The mutual conflict problem
>
>
> > Still other problem:
> >
> > Both to my suggestion and to yours, we must consider that:
> >
> > If Server Sa has the priority (no matter by what manner it's
> stabilished),
> > and if Sb knows to be deferred, then it must block its transaction and
> wait
> > for the end of Sa transaction. Do you agree?
> >
> > That's because: if we notice Sb with "key violation message", and Sa,
> istead
> > of commit, rolls back??
> > We will get the same problem of mutual abort.
> >
> > FM
> >
> > ----- Original Message -----
> > From: "Fabricio Mota" <fabricio.oliveira@xxxxxxxxxxxxxxxx>
> > To: <mysql-dde@xxxxxxxxxxxxx>
> > Sent: Monday, December 12, 2005 2:49 PM
> > Subject: Re: [mysql-dde] Re: The mutual conflict problem
> >
> >
> > > Hey man,
> > >
> > > And if we:
> > >
> > > 1) Keep such as a 'mailbox' that contains a pair indexname -
> indexvalue,
> > > for opened transactions.
> > >
> > > Once the transaction is open, with a insertion of k to an index I, a
> pair
> > > I-k is inserted into this box. When the transaction is finished
> (aborted
> > > or commited), the item is removed.
> > >
> > > This could ensure that no transaction will be 'violated', and the
> other
> > > servers could always check the index and the I-box to check about key
> > > violations or key intention to violate.
> > >
> > > 2) Stablish such as a order-priority among servers (maybe changeable
> along
> > > the time). That is: if Server Sa has a biggest priority than server
> Sb,
> > > when both agree to give priority to Sa.
> > >
> > > So, if we optate to Check-Before-Insert (CBI) with Local Indexes, so
> when
> > > Sb request to Sa such as "Sa, is the value k existing for you?", Sa
> must
> > > verify both in index and in I-box.
> > >
> > > But if we optate to Sync-After-Insert (SAI) with Global Indexes, so
> when
> > > Sb send to Sa such as "Sa, sync the pair k-Sb into your Global
> Index!",
> Sa
> > > must verify both in index and in I-box, before raises an exception
> "key
> > > violation with k" or ack it.
> > >
> > > Hey, what do you think?
> > >
> > > FM
> > >
> > > ----- Original Message -----
> > > From: "Fabricio Mota" <fabricio.oliveira@xxxxxxxxxxxxxxxx>
> > > To: <mysql-dde@xxxxxxxxxxxxx>
> > > Sent: Monday, December 12, 2005 2:18 PM
> > > Subject: [mysql-dde] Re: The mutual conflict problem
> > >
> > >
> > >> Really, really... I must agree with you.
> > >>
> > >>
> > >> But I still want to try suggesting another solution that does not
> depend
> > >> on
> > >> a single master.
> > >> I don't like no much this idea of a master.
> > >>
> > >> ----- Original Message -----
> > >> From: "Peter B. Volk" <PeterB.Volk@xxxxxxx>
> > >> To: <mysql-dde@xxxxxxxxxxxxx>
> > >> Sent: Monday, December 12, 2005 1:01 PM
> > >> Subject: [mysql-dde] Re: The mutual conflict problem
> > >>
> > >>
> > >>> Ok I guess the list strips the images down:
> > >>>
> > >>> here are the links:
> > >>>
> > >>> www.inf.tu-dresden.de/~s7205538/dde/unique_management.jpg
> > >>> www.inf.tu-dresden.de/~s7205538/dde/unique_management_sequenc.jpg
> > >>>
> > >>> Peter
> > >>> ----- Original Message -----
> > >>> From: "Peter B. Volk" <PeterB.Volk@xxxxxxx>
> > >>> To: <mysql-dde@xxxxxxxxxxxxx>
> > >>> Sent: Monday, December 12, 2005 3:56 PM
> > >>> Subject: [mysql-dde] Re: The mutual conflict problem
> > >>>
> > >>>
> > >>>> Hey,
> > >>>> Well I could be solved by having GI-Key Masters. I've added 2
> diagramms
> > >>>> to
> > >>>> show a few details.
> > >>>>
> > >>>> Peter
> > >>>> ----- Original Message -----
> > >>>> From: "Fabricio Mota" <fabricio.oliveira@xxxxxxxxxxxxxxxx>
> > >>>> To: "dde" <mysql-dde@xxxxxxxxxxxxx>
> > >>>> Sent: Monday, December 12, 2005 2:19 PM
> > >>>> Subject: [mysql-dde] The mutual conflict problem
> > >>>>
> > >>>>
> > >>>> > Hey Guy!
> > >>>> > We have a problem with uniqueness management with any approach we
> > >>>> > use.
> > >>>> That is:
> > >>>> >
> > >>>> > Suppose Clients C1 and C2, connected respectivelly through
> servers
> S1
> > >>> and
> > >>>> S2.
> > >>>> > Both clients try to insert a value k, into a unique-indexed
> column.
> > >>>> Suppose they try to insert the value approximately during the same
> > >>>> time.
> > >>>> >
> > >>>> > If we think about to validate global uniqueness among servers
> before
> > >>>> > to
> > >>>> insert, then:
> > >>>> >
> > >>>> > 1 - C1 tries to insert it through S1 / at same time, C2 tries it
> > >>>> > through
> > >>>> S2.
> > >>>> > 2 - S1 validates k / C1 locally. Uniqueness is ok. S2 validates k
> /
> > >>>> > C2
> > >>>> locally, too. Both are ok.
> > >>>> > 3 - S1 request S2 about uniqueness of k. At aproximately the same
> > >>>> > time,
> > >>> S2
> > >>>> request the same for S1.
> > >>>> > 4a - if we consider a fully isolated transaction approach, then
> S1
> > >>>> > will
> > >>>> perform C1 command, and S2 will perform C2 command, too. That's an
> > >>>> inconsistence!
> > >>>> > 4b - if we consider the value k is shared during transactions,
> both
> > >>>> servers will abort, because k is already there during remote
> > >>>> validation,
> > >>>> causing a mutual abort.
> > >>>> >
> > >>>> > If we think about to validate global uniqueness among servers
> after
> > >>>> > to
> > >>>> insert, replicating global indexes, then:
> > >>>> >
> > >>>> > 1 - C1 tries to insert it through S1 / at same time, C2 tries it
> > >>>> > through
> > >>>> S2.
> > >>>> > 2 - S1 validates k / C1 locally. Uniqueness is ok. S2 validates k
> /
> > >>>> > C2
> > >>>> locally, too. Both are ok.
> > >>>> > 3 - S1 tries to sync S2 about its new value k. At aproximately
> the
> > >>>> > same
> > >>>> time, S2 tries to sync S1, too.
> > >>>> > 4a - if we consider a fully isolated transaction approach, then
> S1
> > >>>> > will
> > >>>> update S2 with its k, and S2 will update S1 with its own k, too.
> That's
> > >>>> an
> > >>>> inconsistence, again.
> > >>>> > 4b - if we consider the value k is shared during transactions,
> both
> > >>>> servers will abort, because k is already there during remote
> > >>>> validation,
> > >>>> causing a mutual abort.
> > >>>> >
> > >>>> > If we consider the hypothesis 4b, what criterion could be used
> for
> > >>>> > both
> > >>>> server to agree in which transaction to abort?
> > >>>> >
> > >>>> > Or so, what could we do in respect to it?
> > >>>> >
> > >>>> > Fabricio Mota
> > >>>> > Analista de Sistemas
> > >>>> > FCPC - Divisão de Planejamento do Cadastro Comercial
> > >>>> > MySql-DDE discussion list
> > >>>> > www.freelists.org/
> > >>>> >
> > >>>>
> > >>>>
> > >>>>
> > >>>>
> > >>>> MySql-DDE discussion list
> > >>>> www.freelists.org/
> > >>>>
> > >>>
> > >>> MySql-DDE discussion list
> > >>> www.freelists.org/
> > >>>
> > >>>
> > >>
> > >> MySql-DDE discussion list
> > >> www.freelists.org/
> > >>
> > >>
> > >
> >
> > MySql-DDE discussion list
> > www.freelists.org/
> >
>
> MySql-DDE discussion list
> www.freelists.org/
>
>
--
Sem mais,
Fabricio Mota
Oda Mae Brown - Aprecie sem moderação.
http://www.odamaebrown.com.br
MySql-DDE discussion list
www.freelists.org/
Other related posts:
