Hi there, I've been thinking about locking a lot lately and I'm having some trouble... I'd like to show my 'locking design' based on what I know of sqlite3 to see if I understand every possible aspects of locking.
For the following, let's use a single database file, shared through the network (say with NFS or anything similar) and many different computers/threads connecting to this database file. (So one file, no replication, all transaction on the same single file). The operations would be read mostly, and rarely writes within the same table. Readers will always try to get a SHARED lock, but if there is already a writter with its EXCLUSIVE lock, then the readers will fail with SQLITE_BUSY. The process attempting to read should then retry to get the SHARED lock over and over after a little delay... correct? This is done automatically (except for the retries) by doing a simple query (no transactions)... correct? Writers will try to get a PENDING lock until it converts automagically into an EXCLUSIVE lock (once all SHARED locks are released). Writting is done and the lock is released allowing readers to get their SHARED locks. (I believe if there is already a pending lock, it will get the SQLITE_BUSY and will have to retry?) This is done automatically by starting a transaction (+commit)... correct? It really seem simple, but somehow, I can't believe it... imagine a situation where there's 5000 threads reading most of the time and 5% of them try to write... there will be a moment where many many threads will just keep retrying to get their respective locks (some kind of contention)... wouldn't it be better to (or is it possible) to setup a locking queue? I would like to write: you are 5th in line, the 4 before you want to read. Another way would be to use versioning. I know SQLite3 doesnt support versionning, but is there a way to emulate it? Say, making a database design in which only reads and inserts are possible (upates are inserts of a new version of the same entry)... the reader would query the latest insert ID somehow and query the data below this id... but even this would still require locking. I've been reading the docs on SQLite3 on the locking (http://www.sqlite.org/lockingv3.html) but I would still like to read more about locking designs... could anyone recommend a link to such a design discussion? Not necessarilly based on sqlite, as long as it can be applied to it... Thanks in advance for your corrections and suggestions! Simon -- When Earth was the only inhabited planet in the Galaxy, it was a primitive place, militarily speaking. The only weapon they had ever invented worth mentioning was a crude and inefficient nuclear-reaction bomb for which they had not even developed the logical defense. - Asimov _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
