Re: [pysqlite] database locked

Dennis Lee Bieber wrote:
> On Thu, 29 Jan 2009 10:19:47 -0500, "Eric S. Johansson"
> <[email protected]> declaimed the following in
> gmane.comp.python.db.pysqlite.user:
> 
>> File "/usr/lib/python2.5/site-packages/tpblue/sql_db.py", line 412, in initia
>> l_insert     self.connection.commit()
>> internal_process_message 98: 1:1: filter out: OperationalError: database is locked
>>
> 	Not familiar with "tpblue" but...

my project.  nothing to see here, move along folks...

>> trying to debug some long-running code, I started getting these kinds of errors.
>>  In googling, it sounds like that in the database was locked for a very long
>> time and that something else is clearly wrong.
>>
>> What I need is to know how to detect these errors via exceptions so I can employ
>> an appropriate strategy for recovering from them.  Heck, I think I need to
>> figure out why they happened so I can figure out better ways of preventing them. :-)
>>
> 
> 	... it looks like that package is already trapping the exception
> (OperationalError IS an exception).
>  
>> are there any tools for detecting what's causing this kind of error?
> 
> 	How many connections and cursors are you creating?

it depends. 1 fo every forked process and every cgi run.  (5ish now but as many
as 3-50 in the future)

I have 2 long running procs that have a single connection and cursors each

I will have 3 more db files in the future with similar numbers of connections
and cursors each.

> 
> 	Is there any code that has things like:
> 
> 
> select something
> do long running display/interaction with user, maybe fetching one record
> from the select at a time

no, I do a select then build a generator yielding records from a fetchall if I
need to get many records.

> 
> 	An open select (one in which you have not fetched all records) will
> keep a read lock on the database file. Another transaction trying to
> write will be blocked until all readers have exited (which may not only
> mean "fetch all records" but also a commit() after fetching the
> records).

that might be the problem.  I'm not doing a commit after the fetchall.  sounds
like this would work ok for read and display but updating each of those records
 would be select, fetchone, update, commit.  right? can I batch up records to
update identically?  I could select from a list of record keys and then update
in batch

> 	If part of the "long running display/interaction" is to update data,
> you should probably have code that looks like:

right.  how do you test for a record match?  adhoc field by field?

> 	The goal is to only have transactions open for the length of time
> needed to perform the operations (and in the above, the weakness is the
> "confirm if changed" state -- if the user walks away the database
> remains locked... Might be better to just post a "database changed"
> message and break out of the loop to redo the whole event from the fetch
> all stage)
> 

knew that but didn't know how to tell when i was or wasn't doing that.  thanks
for the clue.