I strongly agree with Doug. I've been using SQLite in multithreaded, single process mode, and after reading the draft, I thought that perhaps another thread could provide a locking service. As Doug states, this "client-server" environment would allow for finer locking capabilities. I suppose that under this scheme, the road would be paved to implement row-level locking in the near or not-so-near future. I like the idea of having a delegate thread to act as a lock manager because all the locking would be "funneled" through an independent entity. This solution would isolate client threads from having to set/clear flags, as stated in the draft, section 4.5. I believe this is an implementation detail that should be hidden as much as possible from client threads
Having an in-memory locking manager could very well improve SQLite's concurrency efficiently, since slower disk access could be reduced, in favor of speedier RAM access. In one of my older projects (SQLite wasn't available yet), I had to implement data file locking on a NFS-based system, across machines. Due to the fact that NFS v2 was used, the lock manager found in v3 wasn't available. We decided to lock the entire database via lock file, but there was a problem: this operation wasn't atomic. So we created a directory instead for the very same purpose. This method was a bit slower, but atomic. We had no choice but choosing a directory-based locking mechanism because using a file would open the door for race conditions, which could damage the database beyond repair. So I would think that RAM-based locks could improve SQLite's concurrency tremendously.
Regarding semantics, I also believe that section 4.3 "Defer write locks by default" seems like a good, elegant solution.
In short, I'd like SQLite to stay serverless (but perhaps implement a lock manager as described earlier), light-weight, embedded, and ACID. Its current implementation, where data and indices reside in the same logical file, is IMHO, very clean and I'd like to see it stay like this in future implementations.
Best regards,
-- Tito
On 22 nov 2003, at 19:27, Doug Currie wrote:
My uses of SQLite are multithreaded (mostly) single process. So, an approach that would appeal to me is "client-server" where the server is a thread in my process, and the clients are other threads. Adding a lock manager to the server thread would permit fine grained locking with ACID properties.
I used the phrase "client-server" above because it is the closest proposed model to what I'd like to see... but in a single process no server is really necessary as long as all the client threads are using a lock manager service appropriately.
This approach could be combined with any of the interprocess approaches described on the "Improving Concurrency In SQLite" web page, or with the present coarse grained locking for interprocess access. In other words, run multi-threaded fine grain locking and access within each process, and coordinate between the processes using coarse grained file locks.
In fact, given a multi-threaded fine grain locking SQLite, one could choose between using the present coarse locking between processes, or creating an SQLite server process which spawns threads on behalf of incoming inter-process clients. Leave the decision to use (and implementation of) interprocess communication and a server process to users (or someday an optional layer on top of SQLite).
As to the semantics of BEGIN, I am strongly in favor of "4.3 Defer write locks by default." This gives the best concurrency for the least effort on the part of the SQL programmer. There is typically little need for early write locking, and as you point out in section 4.3 there is a workaround when it is needed. Some database have a syntax "SELECT FOR UPDATE..." or something like that to grab a write lock early.
One approach not mentioned is row (or page or table) versioning. This provides multiple versions of a row to exist simultaneously so writers never block readers and visa versa. It is an elegant approach, but suffers the same drawbacks as Mrs. Brisby's suggestion to hold all updates until transaction COMMIT (optimistic concurrency control).
e
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
