@GreenFlux
Doing all of the Adds while all users are connected helps a lot because it reduces the length of the โwindowโ where two or more users can create records with the same sequential key., but it does not eliminate the โwindowโ where the problem can occur.
Imagine you have two users called A and B who do the following: 1. User A read the table and the highest Id is current 100. 2. User B read the table and the highest id is still 100. 3. User A adds a record and it is assigned the Id 101. Remember that the client is running the MAX formula locally using the data they read in step 1.
- User B adds a record and it is assigned same Id value of 101. This happens because this client is running the MAX formula locally using the data they read in step2. 5. User B does a sync. This write a records with Id 101. 6. User A does a sync. The server looks to see if a record with Id 101 already exists. It finds the record that user B added, so it turns user Aโs Add into an Update. This overwrites user Bโs data in that record with user Aโs data. In database lingo, turning an Add (aka Insert) into an Update is said to be an โUpsertโ. We are talking about adding other conflict resolution strategies in the future and allowing you to choose among them, but we currently do an โUpsertโ when such a conflict occurs.
The longer the duration between step 2 and step 5, the more likely the duplicate Id problem. That windows can be really long if either or both of the users are offline between steps 2 and 5.
By the way I want to thank you for jumping in and helping out on the community. It really helps. I only jumped in on this one because this is an obscure area of database lore that is not widely understood. I have been working on databases and transaction processing for fifty years. I managed database and transaction
processing development groups at Burroughs, Tandem Computers, and Microsoft for almost that entire time.
LinkedIn
Twitter
