What are the recommended "best practices" around using SQLite in a distributed scenario?
(I saw Shailesh's email in March; I'm considering building a two-phase commit layer atop SQLite nodes and looking for advice.) Clearly, this is a big topic including failover, recovery, load-balancing, and so on. But right now I'm most curious about the transaction layer. The most obvious approach is to just process all transactions with a basic serialized two-phase commit protocol: 1) Master begins transaction in the local database 2) Master sends query to all slaves 3) Slaves begin transaction in their local database 4) Slaves send approval to the master 5) Master commits to the local database 6) Master sends commit command to all slaves 7) Slaves commit the transaction 8) Goto 1 This works great and is what I have now. But this can only process one transaction at a time, and the commit speed is no faster than the slowest slave latency. I'm curious if you can recommend a better way? (I'm sure this is well documented somewhere; any suggestions?) For example, it seems that transactions could be "pipelined" to decouple throughput from latency: after all, barring edge conditions, every slave should approve every transaction. (And any failed transaction would probably result in all future transactions failing as well -- eg disk fail/full/corrupt.) So the master could send a constant stream of transactions, and the slave could send a constant stream of approvals, and everybody is happy. The only tricky case is in the rare case the slave actually does need to abort the transaction. Assuming there are 10 transactions that haven't yet been committed, the slave could commit the first 3 and then abort the remaining 7. This would look like: 1) Master begins 10 transaction in the local database 2) Master sends 10 queries to slave 3) Slave begins 3 transactions successfully, but the next 7 fail. 4) So the slave send 3 approval messages, and then 7 abort messages 5) Master commits the first 3, and then rolls back the remaining 7 6) Master sends commit messages for the first 3, and rollbacks for 7 7) Slave commits the first 3, and rolls back the last 7 Seems pretty straightforward in theory. But my question is how to actually accomplish step 5 with SQLite. I read a big discussion on nested transactions in the list archives from a year ago, and I understand they're not currently supported. But I'm not sure what I want to do is actually nested transactions (else I'd nest infinitely deep before committing anything). Rather, I'm thinking of some kind of... serial commits, or savepoints or something. Basically, maintain a set of "savepoints" that I can revert the entire database to at any time. Then I just commit transactions in order, and create a new "savepoint" after each. Later, when I get the approval from the slave, I delete the associated savepoint (to save resources, presumably). Alternatively, if I get a rollback from a slave, then I just revert to the savepoint before that transaction and -- effect -- rollback every transaction made after. Can you think of any good way to accomplish this? One horrifically bad way would be to copy the database after each transaction. One less horrible way would be to manually maintain an undo log (just create SQL statements that -- when applied in reverse -- gradually roll back the database to any previous state). http://www.sqlite.org/cvstrac/wiki?p=UndoRedo But I'm curious if there are any better ways to go about it. I'm guessing this is a pretty well-explored area -- anybody who's gone ahead care to share their wisdom? Thanks! -david _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users