Roger Binns wrote:
> Yang Zhang wrote:
>> for i in (str(row[0]) for row in conn.cursor().execute('SELECT key FROM
>> shelf ORDER BY ROWID')):
>
> You are converting the key which is an integer into a string for no
> apparent reason.
I copied and pasted this code straight from my actual application, which
uses blobs instead of integers, which I need to convert into strings
(since Python interfaces with blobs using the `buffer` type, not `str`).
>
> If you also ask for the ROWID you will see that what is happening is a
> new rowid is generated for the replaced row so that if you are iterating
> over the table while modifying it then you effectively have an infinite
> length table.
This is unusual for a RDBMS that claims to provide ACID properties - in
particular, this is not even as strong an isolation level as snapshot
isolation, as a reader transaction is able to see a concurrent writer
transaction's effects. In fact, this is weaker than the weakest
isolation level in (say) Postgresql, which is READ COMMITTED (in which
any statement is guaranteed to not see the effects of a transaction that
is committed after the query has started execution).
>
>> Any way to solve this problem?
>
> You currently have the SELECT results being read back one at a time
> (lazily) on each iteration of the for loop. The simplest solution is to
> read them all in first. Add .fetchall() after the execute.
Unfortunately in the original application the table is large (many GBs).
Any way to solve this problem without first reading everything into
memory, and without manually creating a second copy of the table? Is
there no way to request a stronger isolation level, such as snapshot
isolation?
--
Yang Zhang
http://www.mit.edu/~y_z/
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
