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.
--
Yang Zhang
http://www.mit.edu/~y_z/
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
