[mysql-dde] The mutual conflict problem

  • From: "Fabricio Mota" <fabricio.oliveira@xxxxxxxxxxxxxxxx>
  • To: "dde" <mysql-dde@xxxxxxxxxxxxx>
  • Date: Mon, 12 Dec 2005 11:19:31 -0200

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/

Other related posts: