Re: [pysqlite] database locked

thank you to everyone who responded to my query.  I'll try to increase my 
understanding through questions over the next couple days.

Dennis Lee Bieber wrote:
> 	I presume multiple connections are active... 

yes, in this environment, there are anywhere from one to 20 processes accessing 
the database at any one time.  Currently, I only use the database for one thing 
(tracking metadata) but I'm probably going to expand it to do message source 
reputation classification.  This will probably double the number of processes 
accessing the database.

(Question: what's the better solution for setting up databases when you have two 
tables, 150,000 entries or more and the other with potentially millions.  Should 
each table have its own set of files or should I put both tables in one file?)

> As I recall, SQLite can
> not commit changes until all other in-work transactions have exited --
> and "in-work" means some other connection has performed even just a read
> operation, even if no data modification has been done. In essence, for
> one transaction to COMMIT, all others must close first (commiting a
> read-only transaction is probably permitted, but if multiples are trying
> to update/insert, most will have to rollback and retry).

interesting.  I thought I could do one commit while reads were outstanding but 
your description would explain behavior I've seen (as load increases, so does 
the time to completion.)

(Question: is there any relationship between the number of records returned in 
time the database is locked?  There is one point in my application where I query 
for a "screens" worth of data and get far more than I need because I need to 
sort it and massage it for presentation.  Would there be value in leading SQL 
light sort and then return only the first X elements?)

> 	A long timeout value probably is going to result in even MORE
> problems, as the transaction attempting to commit is going to block more
> and more other transactions from even starting. The transaction trying
> to commit is waiting for all others to exit, and new transactions can
> not be started while one is in a write(wait) mode.

then the timeout doesn't do what I thought it did.  :-) before your explanation, 
I thought it meant that the attempts to update would be queued behind the lock 
and then as the system lock released, it would allow the transactions to complete.

> 	As others have mentioned, your solution is, in your own words,
> "retrying until" you succeed... The key is that "retry" for SQLite means
> "rollback and repeat the entire transaction". Hopefully your
> transactions don't consist of "get parameters from user; select
> record(s); display to user; wait for user to edit screen; collect edits;
> update record(s); commit" (if it is... you'll probably want to do a
> commit after the select, and then when you get to the "collect edits"
> part, append a copy of the original select and validate that those
> records have not changed while waiting for the user; no change, do the
> update... changes? commit/rollback, advise user, and redisplay using the
> newer versions)

No, it's far simpler than that.  All the updates can be reconstructed and 
retried forever and a day if need be.  Also, I'm perfectly happy with last write 
wins semantics.

(Question: how do I differentiate a "must retry" event from "something else 
died" events?  is this a Python exception or something where I need to look at 
the error result)