G'day,
"D. Richard Hipp" <[EMAIL PROTECTED]>
24/11/2003 03:22 AM
To: [EMAIL PROTECTED]
cc:
Subject: [sqlite] Concurrency in SQLite
> Please, give me some examples of the kinds of things you are
> doing which could benefit from improved concurrency.
> * Are you holding transactions open for an extended period
> of time? Why?
This is my situation. I have a large amount of data flowing into a
database which shows historical records (maybe a couple of thousand
inserts per second). Queries are much rarer. To keep inserts efficient I
hold transactions open for one second at a time. The most important change
for me is one that I introduced into my copy: Blocking locks. These are
important because there is only an instant between the last transaction
closing and the next beginning. In this scenareo the poll-based locking
mechnism currently used by sqlite is just not lucky enough to try at that
instant. Only blocking locks with their operating-system support are
sufficient to ensure that the readers get in at all. I also have a
situation where I have multiple writers on the database that can run into
the same problem.
If you could ensure that readers could still read the untouched version of
database blocks while a writer is working on "dirty" version of the same
blocks I wouldn't have any problems as far as reading is going. Writing
would still be problem, though. It's not the amount of concurrency that's
a problem for me. One at a time is fine. It's just the ability to schedule
the accesses that do happen very tightly together that I care about.
> * How many processes do you have trying to access the database
> at once?
Usually at most two or three.
> * How do you currently handle SQLITE_BUSY replies? Do you use
> the sqlite_busy_handler() or sqlite_busy_timeout() APIs?
The problem with both of these apis is that they use timers beetween
attempts. If I could put a blocking lock on the database in the busy
handler, allow the database access to occur, then get called back to
unlock the database, it would be almost as good as the current blocking
lock situation.
> * How large are your databases?
Usually less than a gig :)
> * Do you ever put database files on a shared filesystem?
No.
Benjamin.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
