Dennis Lee Bieber wrote:
> Talking to myself... Please take into account that the following are
> strictly hypothetical, and it would take looking at the source code for
> the interface library to determine the truth.
>
> On Sat, 20 Oct 2007 01:28:41 -0700, Dennis Lee Bieber
> <wlfraed-vB5rODKJlosybS5Ee8rs3A@xxxxxxxxxxxxxxxx> declaimed the
> following in gmane.comp.python.db.pysqlite.user:
>
>
>> The first paragraph of section 7 (in that second link) is also
>> confusing...
>>
>> """
>> In autocommit mode, all changes to the database are committed as soon as
>> all operations associated with the current database connection complete.
>> """"
>>
>> ... what is meant by "the current database connection complete"? Is that
>> the same as a con.close() in pysqlite2? I can't say... (and since I run
>> from prebuilt binaries, I can't browse source code to the library to
>> tell)
>>
>>
> Trying to interpret the documentation is leading me to believe that
> a SELECT query doesn't not complete when the .execute() returns, but
> only when the last row has been fetched.
>
> Looking at the C-api .execute (and related cursor) could be
> implemented in two ways -- one of which is highly memory intensive and
> probably NOT used... namely, .execute would perform something like
> (pseudo-code of Python with C-api) retrieving all results into a cursor
> structure (list):
>
> def execute(self, SQL, parm=None):
> stmt_handle = sqlite3_prepare(SQL) #implied BEGIN
> if parm:
> sqlite3_bind(stmt_handle, parm)
> self.result_set = []
> while sqlite3_step(stmt_handle) == SQLITE_ROW:
> cols = []
> for i in range(sqlite3_column_count):
> cols.append = sqlite3_column_xxx(stmt_handle, i)
> self.result_set.append(tuple(cols))
> sqlite3_finalize(stmt_handle)
> #implied COMMIT
>
> def fetch(self):
> if self.result_set:
> return self.result_set.pop(0)
> else:
> return None
>
> What is more likely, and implied by the pysqlite documentation (1> a
> cursor contains a statement handle, 2> pysqlite returns a row_count of
> -1 for select statements because it can't determine the number of rows
> until they have been fetched) is:
>
> def execute(self, SQL, parm=None):
> if self.stmt_handle: #is
> there unfinished?
> sqlite3_finalize(self.stmt_handle) #implied COMMIT
> self.stmt_handle = sqlite3_prepare(SQL, parm) #implied BEGIN
> if parm:
> sqlite3_bind(self.stmt_handle, parm)
> self.code = sqlite3_step(self.stmt_handle) #execute/first row
>
> def fetch(self):
> if self.code == SQLITE_ROW:
> cols = []
> for i in range(sqlite3_column_count):
> cols.append = sqlite3_column_xxx(stmt_handle, i)
> self.code = sqlite3_step(self.stmt_handle) #next row
> return tuple(cols)
> elif self.code = SQLITE_DONE:
> sqlite3_finalize(self.stmt_handle) #implied COMMIT
> self.stmt_handle = None #finished cursor
> return None
> else:
> raise DB exception #I ignored such in the prior
> example
>
> In this form, a SELECT statement doesn't "commit" on the .execute(),
> but only when the last row of the result is explicitly fetched (OR
> another .execute() is started on the same cursor!). If a thread is
> iterating over a large/slow (for example, a one-way scrolling over data
> via some GUI/Web form) result set from a SELECT statement, that may be
> sufficient to lock out any thread trying to modify the database.
>
> The "Definitive Guide..." makes strong recommendations that
> concurrent connections should use explicit "begin immediate" or "begin
> exclusive" if they will be modifying the database.
>
This was very helpful.
I believe you are correct when you suggest that a SELECT doesn't
"commit" on execute(), instead after all returned rows are fetched. In
my python script, I had a thread which performed a SELECT, and read a
returned row, waited 30 seconds, and looped, with the entire thread
running indefinitely. Basically, the SELECT didn't "commit" for minutes
on end, until all the result rows were iterated. The database remained
locked until, like you said, every result row had been explicitly read.
I instead had it read the result rows into a list, then iterated that
separately with my wait time, and my "database is locked" problems went
away entirely.
Thanks a lot for your help.
|
