Ah, it sounds like we're building essentially the same thing -- mine is also for a financial setting. You're right, a mirror-replicating mode might be adequate, and is certainly simpler... But part of me is afraid that it leaves you vulnerable to failure conditions that a full two-phase commit system wouldn't.
For example, I'd like the master to actively reject all transactions that aren't committed to at least one slave, else we're operating without a net -- if the master is lost during such a period, there is unrecoverable data loss. (And even if the data eventually becomes available when the master comes back, until then the backup is incomplete and thus putting up a new server is risky.) I'd rather take the service down than risk getting stuck with a huge bill due to an accounting error. As I mentioned, I'm currently just using a serialized two-phase commit approach, and that's fine but has very limited throughput. Realistically, even that throughput is probably fine to start, but I'd like to at least understand the alternatives in case I want to go there in the future. It seems like one alternative might involve undo/redo logs, but I'm not quite seeing it. How does MySQL or Postgres do it? Does SQLite simply lack some crucial feature that is required to do it for real? -david Virgilio Alexandre Fornazin wrote: > The best you can do actually with SQLite is a 'mirror-replicating' mode > engine > that works like Microsoft Windows Active Directory Database, to build a king > of > High Availability / Load Balancing server. > > You have a farm of servers (or workstations, etc) receiving SQL commands by > a > channel (socket, pipe, whatever). They force a 'election' do decide what´s > the > best servers to become master, then they agree on that and transactions (all > > commands that modify the database) are first applied to the master server > then > 'mirrored' to slave servers. In this design, if a 'child' server cannot > complete > the transaction that are completed by master and any other server, there is > a > critical problem in that slave node, and you must consider it offline or > some kind of state that you need to check if by hand or by one tool you > might > develop for this. In this scenario, you can do a 'load balance' in SELECT´s, > distributing querying belong all servers, creating affinities for tables, > buffering most used tables in a memory database, etc. > > I´m currently implementing services for finantial stock exchanging services > that > works in the way I told you, if you are planning something that we can have > in the > way SQLite is (not tied to any kind of restrictive license), we can share > knowledge > and implement a solution like that. (PS: I don't have full time to work on > it, but > I can help in free hours) > > -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Griggs, Donald > Sent: segunda-feira, 12 de maio de 2008 10:47 > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Distributed transaction best practices > > Hi David, > > > Regarding: "What are the recommended "best practices" around using > SQLite in a distributed scenario?" [two-phase commit, etc.] > > I trust that someone with some actual relevant knowledge will reply to > your query later, but I imagine that many would say the the "recommend > best practice" is *not* to use sqlite, since sqlite was designed to be > an elegant embedded database -- without even one server -- let alone > multiple synchronized ones. > > I take it you have strong reasons for rejecting, say, Postgres, which > now implements two-phase commmit right out of the box? > > http://www.postgresql.org/docs/current/static/sql-prepare-transaction.ht > ml > > You may already know everything in articles such as this one > > http://en.wikipedia.org/wiki/Two_phase_commit#Distributed_two-phase_comm > it_protocol > And its references (I don't claim to), but I'm listing it here just it > case it's helpful to you. > > On the other hand, if you *do* develop a solid "distributed sqlite" > implementation, I'm sure others would be interested. > > Regards, > Donald Griggs > > > > > This email and any attachments have been scanned for known viruses using > multiple scanners. We believe that this email and any attachments are virus > free, however the recipient must take full responsibility for virus > checking. > This email message is intended for the named recipient only. It may be > privileged and/or confidential. If you are not the named recipient of this > email please notify us immediately and do not copy it or use it for any > purpose, nor disclose its contents to any other person. > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
