On 17 Sep 2009, at 5:56pm, Kelly Jones wrote: > % 99+% of the time, there won't be two updates "at the same time". In > other words, copy 1's change will almost always propagate to copy 2 > before copy 2 does another update.
Doesn't really matter as long as you have the other 1% of the time. > % In my app, almost all the UPDATEs are of the form "WHERE rowid IN > (...)". I don't do global updates like "WHERE foo='bar'". If you can get rid of the 'almost' in that sentence, you have something worth talking about. Then you just have the following problem: Start with multiple copies of a table with 120 entries. Client 1 inserts a new row in the database. It is assigned id=121. Client 2 inserts a new row in the database. It is also assigned id=121. Now you have two pieces of the data with the same id. So it turns out you can't really use id (or rowid, or whatever you call it) either. You have to work out which records are new since the last synch which, once again, can only really be done by keeping some information centrally. Which I understand you want to avoid. There /are/ situations where you can safely implement multi-master systems. One is if each record in your database belongs to a particular one of your sites. For instance, a company has six branches, spread over the country. Any branch can gain a new customer, modify customer details, or lose one of their existing customers. But modifications and deletions for a customer in the database are always done by the branch that recruited that customer -- one copy of the database. So what you have in your tables is a field which says which of your sites can modify that record. If any other site tries to modify information to do with those records, the system won't let them. This places a restriction on how your database is used, but if your organisation can work within this restriction (and it sometimes fits well with how the organisation works anyway) then it might be a solution you can use. And you can vary it, as long as all your sites cooperate. For instance, you could have a rule that on the last day of every month Head Office can make any modification it likes to any record, and no other sites can modify anything. > I read your other post as well, and still think this is worth > pursuing. Is there a place I can read up on proposed solutions and why > they fail? There are entire books on the problems encountered when trying to synchronize copies of database. Find anything called 'Designing multi- user database systems' or 'Distributed database systems'. There is no satisfactory solution to the problem that doesn't require showing information to a human and getting the human to pick which interpretation of the data is best. The programming for a proper solution gets more and more intricate and requires more and more locking and cross-checking until you realize it's simpler to implement a server/client architecture. In fact there are patents granted on methods of synchronising databases, and if you try to implement the method you find it doesn't work. You just walked into one of those problems that looks easy until you figure out it's impossible, like the travelling salesman problem. Don't worry about it. Simon. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
