Thanks for all these links, I have some reading to do! Also, to clarify one point, we're not doing multi-
master writes/replication -- just multi-
threaded writes/replication.
Incidentally, the latest plan is to use http://sqlite.org/sessionintro.html to do the following:
1) Spin up multiple write threads
2) Every write thread opens its own database handle
3) Every write thread creates a new "session" object before each write command, and then creates a "changeset" afterwards
4) The first write thread to process a write command calls sqlite3changebatch_new() to create a new batch (initially empty)
5) It then calls sqlite3changebatch_add() to add the changeset to that batch, which returns SQLITE_OK to indicate that it does not conflict with anything in the (currently empty) batch
6) The next write thread calls sqlite3changebatch_add() on the existing batch, providing the write command's changeset
7) If sqlite3changebatch_add() returns SQLITE_OK then it creates a patchset from the changeset, and sends it to the slave.
8) Slaves apply and commit patchsets within the same batch in any order
9) On the other hand, if sqlite3changebatch_add() returns SQLITE_CONSTRAINT, then that means the new changeset conflicts with one or more existing changesets in the batch. In this scenario, it increments the batchID, calls sqlite3changebatch_zero() and then sqlite3changebatch_add() again (to initialize the new batch). It then creates and sends a patchset to the slave for committing in any order with changes in the new batch.
Re: SQLite's performance, I find the database is consistently underestimated in every regard -- performance, stability, functionality, etc. And the database isn't half as amazing as the team behind it. Those are some of the most solid engineers -- in a true sense of engineering (of which programming very rarely is) -- that I've met.
Incidentally, the latest plan is to use http://sqlite.org/sessionintro.html to do the following:
1) Spin up multiple write threads
2) Every write thread opens its own database handle
3) Every write thread creates a new "session" object before each write command, and then creates a "changeset" afterwards
4) The first write thread to process a write command calls sqlite3changebatch_new() to create a new batch (initially empty)
5) It then calls sqlite3changebatch_add() to add the changeset to that batch, which returns SQLITE_OK to indicate that it does not conflict with anything in the (currently empty) batch
6) The next write thread calls sqlite3changebatch_add() on the existing batch, providing the write command's changeset
7) If sqlite3changebatch_add() returns SQLITE_OK then it creates a patchset from the changeset, and sends it to the slave.
8) Slaves apply and commit patchsets within the same batch in any order
9) On the other hand, if sqlite3changebatch_add() returns SQLITE_CONSTRAINT, then that means the new changeset conflicts with one or more existing changesets in the batch. In this scenario, it increments the batchID, calls sqlite3changebatch_zero() and then sqlite3changebatch_add() again (to initialize the new batch). It then creates and sends a patchset to the slave for committing in any order with changes in the new batch.
Re: SQLite's performance, I find the database is consistently underestimated in every regard -- performance, stability, functionality, etc. And the database isn't half as amazing as the team behind it. Those are some of the most solid engineers -- in a true sense of engineering (of which programming very rarely is) -- that I've met.