Hello!
I think, there is another way for high-concurrency SQLite-based systems.
**It is not entirely universal**, but I hope it may be used for
high-traffic web sites and similar kind of systems, where each individual
transaction (such as a page retrieval or a form submission) is very
short and can be replayed automatically in the case of transaction
failure.
The main idea is in additional abstraction layer on the top of
SQLite. This layer is high level transaction management system.
Each high-level optimistic transaction is (almost) fully ACID,
but many high-level transactions executes in the scope of single
low-level SQLite transaction. This way the count of file buffers
flushing is decreased significantly.
Below is very simplified example. Let imagine high-level transaction:
BEGIN
SELECT ...
-- potentially slow processing occurred here
SELECT ...
-- potentially slow processing occurred here
INSERT ...
UPDATE ...
DELETE ...
COMMIT
This transaction in many cases can be split into two independent
transactions:
1. Read-only transaction
2. Read-write optimistic transaction with conflict detection
BEGIN
SELECT ...
-- potentially slow processing occurred here
SELECT ...
-- potentially slow processing occurred here
COMMIT
BEGIN
SELECT ... -- additional short SELECTs for conflict detection
-- ROLLBACK if some conflict was detected
INSERT ...
UPDATE ...
DELETE ...
COMMIT
Because the first transaction is read-only, it may be performed
in parallel with similar transactions. That is, no locking
conflicts is occurred on this phase.
Because the second transaction contains optimistic conflict
detection, it can be combined with many similar transactions
into one low-level SQLite transaction. This way, the count of
file buffers flushing is reduced greatly. This transaction
will be relatively short because no application-specific slow
processing take place on that phase.
The main trick is how to split the original transaction
automatically. It may be very hard if original transaction
is expressed directly in SQL. I hope, such transformation can be
performed much more easily if operations are expressed in terms
of some object-relational mapping system. Such ORM can begin
read-only SQLite transaction and then delay all the modifications
till the end of high-level transaction. If application code
will try execute direct SQL data modification query, then
the read-only transaction will degrade smoothly to unbatched
read-write transaction.
On typical web-site the majority of all transactions is read-only.
If the web site must perform 1000 read-only transactions per second
and 20 read-write transactions per second, then this 20 read-write
transactions can be slightly delayed and then processed in the
batch as single low-level SQLite transaction.
But what if optimistic locking conflict is detected in one
of this 20 transactions? Does it means the others 19 high-level
transaction must be rolled back, as well as low-level SQLite
transaction? No, it is not. Only transaction with conflicts
will be excluded from low-level SQLite transaction, and then
the entire process of this HTTP request will be reiterated
by the web-server.
In the near future I'll try to implement SQLite-oriented ORM
which will demonstrate this approach.
Best regards,
Alexander mailto:[EMAIL PROTECTED]
