Yang Zhang wrote:
> John Elrick wrote:
>> Yang Zhang wrote:
>>> 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).
>>
>> As I am not an expert in the Python wrapper, I could be incorrect;
>> however, your code as written appears to be equivalent to the following:
>>
>> begin transaction
>> for select(....
>> insert stuff
>> end
>> commit
>>
>> rather than your intended:
>>
>> s = select(...
>> begin transaction
>> for s...
>> insert stuff
>> end
>> commit
>>
>> I say this because your example implies that the Python wrapper starts
>> the transaction automatically inside the execute, and I would not be
>> surprised if it did so BEFORE executing the SQL parameter.
>
> The cursor() method that I call on the conn for the SELECT should give
> me a separate transaction.
I also tried using separate connections, but that just ends up blocking
and failing with a timeout on the lock acquisition because it appears
that SQLite only has full-table locking, and not MVCC/snapshot
isolation. Do I need to manually extract out all the data first into
another store, and then iterate over that to operate on original database?
--
Yang Zhang
http://www.mit.edu/~y_z/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
