SQLite doesn't support several simultaneous transactions on the same
connection to database. So in fact your select and insert statements
execute in the same transaction. And even more: your inserts are not
committed until your select is completely finished. So for your task
you should use different connections for select and inserts. But it
will not work anyway, because SQLite doesn't support row-level
locking, it locks entire database. Thus your select statement on one
connection will block execution of inserts on another connection. So
you should stick with one of the solutions mentioned or switch to
another database engine that will fit you better.
BTW, ACID that you mentioned has nothing to do with snapshot isolation
that you want to achieve. AFAIK only Oracle supports this kind of
statement isolation level.
Pavel
On Mon, May 18, 2009 at 12:41 PM, Yang Zhang <yanghates...@gmail.com> 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.
> --
> 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
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
