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 sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users